How to show invoice numbers in a total query?

[supernova]

I have 3 tables: CustomerT, ProductT and InvoiceT and I have related these tables together using foreign key and primary key. Each customer has multiple invoices and each invoice contains several products. I can sum the products of certain invoices in a query using Totals but I can't see which invoices do these products refer to. For example, I might have a sum of 12 computers sold to customer A in 3 invoices 102,204 and 250. I want to see these invoice numbers too. How can I show them in my query for my report?

Thanks in advance

 

[bob fitz]

Try adding the invoice number field to your query and select Group By in the Totals row.

 

[supernova]

Try adding the invoice number field to your query and select Group By in the Totals row.

I did that but didn't work. When I add the invoice number field, it separates the products and doesn't sum them. For example, it shows 3 computers for invoice 102, 4 for 204 and 5 for 250. I want to show the sum of computers sold and in the next column show the invoice numbers like 102,204 and 250. something like that

 

[bob fitz]

can you post a copy of the db

 

[supernova]

can you post a copy of the db

Here is a quick db file I made to show you what I mean. If you go to the SumQ, you see computer is repeated multiple times. If you go to design view and delete InvoiceNumber, you get it right but invoice numbers are not available anymore.

 

[bob fitz]

Can you show us the result you want

 

[supernova]

Can you show us the result you want

Well I want to see the sum of purchased products in all invoices (lets say we have 5 Computer sold) and in the next column the invoice numbers i.e. 101,103,106 (I realized I have misspelled computer and I have 2 products now one competer and the other computer so I have 5 computers and 9 competers). The next row shows 4 Radio and the column next to it shows 103,104. I want all invoice numbers within one cell for each product if this makes sense.My Query fields would be Product Name, Quantity, Invoice Number(s)

 

[arnelgp]

one note is that you dont have a master product table, productT is a transaction detail of your invoice. you should make one, so as to avoid error on typing the product name. you may test your query now.

 

[supernova]

one note is that you dont have a master product table, productT is a transaction detail of your invoice. you should make one, so as to avoid error on typing the product name. you may test your query now.

Thanks for your help. This is exactly what I want but I don't know how you did it specially that module1. This is just a demo db I made to explain my problem so I need to apply what you did to my actual database. In the actual database I have made data entry using forms and comboboxes to avoid misspelling problems.

 

[arnelgp]

then you need to copy the function i made on the Module1 to your db.
since there is no way in a query to output the results you wanted, the function does it for us.
the idea is to open a recordset based on the InvoiceT table joined to ProductT table, and gather all invoices that have same ProductName that we provided in the query.

 

[supernova]

then you need to copy the function i made on the Module1 to your db.
since there is no way in a query to output the results you wanted, the function does it for us.
the idea is to open a recordset based on the InvoiceT table joined to ProductT table, and gather all invoices that have same ProductName that we provided in the query.

I tried hard applying your code to mine but I couldn't because I have no knowledge of vba. Is it possible for you to make a query based on SumQ and do the concatenation in that query? I guess in this case, the code becomes universal and doesn't need INNER JOIN stuff. I know its not a proper way to do it.