I am reposting this question with some added detail as I didn't get any useful responses previously. I'm basically trying to duplicate the cross-tab query function in Access. I have a database that contains production and status tables. The production table has department, part number, status_date and status_ID. The status table contains an entry for each status Id along with the quantity. Every day entries are made in it by part number showing the quantity of each part in each status. I can pull a query that shows all the entries for a given period of time, but I want to count them up and show totals by department/by part number with totals for each status type. So for example, rather than show each value entered for WidgetA for the past month, I want to show the total number of WidgetA's worked on as well as the subtotal of each status type. I should also add that I don't know how many departments, part numbers or status types there will be until I query the tables. I'm thinking an array would be the way to go, but I'm not sure how to get started. I can't get my head around how to step through the returned query and count everything up and then output it the way I want.
The last time I posted this, with less info, someone suggested using SQL SUM and GROUP BY WITH ROLLUP. That shows me the data, but not in the format I need it.
This is the output I am looking for:
Department part# total status1 status2 status3 status(n)
Dept1 widgetA 100 25 25 25 25
Dept1 widgetB 50 10 0 40 0
SUBTOTALS 150 35 25 65 25
Dept2 widgetC ----and so on.
