Count Distinct Values

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

I have a query (qryInvoiceDetails) built on two related tables, tblInvoices
and tblInvoiceLineItems. There is a one to many relationship between the
invoices and the line items contained in each invoice. Using
qryInvoiceDetails, I want to build another query that will give me the total
number of invoices and the total number of line items. Since each invoice
has multiple line items, I expect the number of line items to be greater than
the number of invoices. I tried to do this by running a totals query, but it
didn't work. It gave me the same value for both fields. I think that a
SELECT DISTINCT statement would fix it, but I can't get it to work - I keep
getting syntex errors. Is there a way to get the total number of invoices
and line items from the same query, or do I have to run two separate queries?

Thanks in advance for any help you can offer.
 
I generally use a separate query for each total and then bring those two
small queries back into your main query.
 
Back
Top