Self generating query string based on query results?

[Dragenesis]

Here's my problem. I need to generate a report that says how much of each individual product was produced and as well as the total produced for a specified category in a time period. Something like the following:

05 Catagory A: 02 Product AA, 01 Product AB, 02 Product AC
10 Category B: 07 Product BA, 03 Product BB
04 Category C: 01 Product CA, 01 Product CB, 01 Product CC, 01 Product CD

etc...

I currently have a query that queries a database and pulls out all products that were produced in a specified period and the categories they belong to and dump them into a local access table. Now what I need to do is search through the query results and count up how many of each product were produced (02 AA, 01 AB, etc...) and the totals for each category. The number of categories is pretty limited (6), but there are hundreds of product codes, so I need a way to do this without having to type in each induvidual product code as the requirement by which the query searches. Also, the product codes that get returned are different every day.

I was thinking something along the lines of take the product code of the first row and check for any others in the results that match and write that into another table. Then move onto row 2 and use its product code as a search parameter and search through the query results for any matches. Then continue that until the end of the query results. Can I do that? Is there a better way to achieve what I need?

 

[pbaldy]

Have you tried a query like:

SELECT ProductCode, Category, Sum(AmountField) AS TotalProduced
FROM TableName
GROUP BY ProductCode, Category

Then base a report on that query with Category in Sorting and Grouping.

 

[Dragenesis]

Haven't tried your suggestion, but from reading the query, it sounds like it won't work. The database doesn't store a numerical value for the products produced. Instead, it product produced has a unique identifier and occupies it's own row in the database along with the appropriate product code and category. I need to count up the number of occurances for each product code and category. I still will give your suggestion a try and will repost after work.

 

[pbaldy]

Then perhaps changing the Sum to a Count would do the trick? A sample db might clear up the confusion.

 

[Dragenesis]

I've attached a sample database. The sample table has 3 fields, an auto number, Unique ID to represent the unique identifier for each product, the product code, and the category. I need to the final product to be something along the lines of:

5 board game: 2 monopoly, 3 sorry
2 video game: 2 tetris
1 card game: 1 uno
2 car: 1 Corolla, 1 mustang

The sample database is similar to the local access table that holds the query results taken from the main database. The real one has more fields and data, but those fields are the important ones. As I said before, each time I run the query to update the local table, I may get different product codes (i.e. no monopoly but poker) or different categories (i.e. additional category of boat).

 

[pbaldy]

Like this?

 

[Dragenesis]

that would work. Does your code work no mater what product/categories are populated inside the database?

 

[pbaldy]

Yes, it's totally dynamic. It's the query and report I described earlier, with Count instead of Sum.