Formulas and SQL syntax for Microsoft Access 2007

We need to be clear here.

Are you saying that you want to know the number of Different ID values, no matter how many records for each ID,  or do you want the number of IDs which only occur once in the list?

If its the number of Different IDs then does it matter which record goes into the total where there are multiple records for an ID?

Yes, I want the count of number of different ID values.

try this query

select ID, Count(ID)
from tableX
group by ID

And if you don't care which value goes into the total you can do...


Select qry1.Count(ID) as NumIds, sum(qry1.Myval)  as Total from
(Select ID, first(valuefield) as Myval from tablename group by ID) as qry1

The above SQL did not work. This is what I have right now:

SELECT DISTINCT inv.InvoiceID, inv.VendorName
SUM InvoiceTotal
FROM tblInvoicesSubmitted AS inv;

try this



SELECT inv.InvoiceID, inv.VendorName,SUM(inv.InvoiceTotal)
FROM tblInvoicesSubmitted AS inv
Group by  inv.InvoiceID, inv.VendorName

This looks good. Will this be able to count at the bottom the number of unique invoice IDs as I do not want duplicates?

My current query is as follows:
SELECT inv.InvoiceNumber, inv.VendorName, inv.DateSubmitted, inv.DateCheckRequested, inv.IM_Check_Confirmed_Date, inv.NetItemAmount, DateDiff("d",[DateSubmitted],[DateCheckRequested]) AS PaymentTime
FROM tblInvoicesSubmitted AS inv
WHERE NetItemAmount<=100 And DateDiff("d",[DateSubmitted],[DateCheckRequested])<=15
ORDER BY DateDiff("d",[DateSubmitted],[DateCheckRequested]) DESC;

Please advise where I would put the unique count off function in thsi query. Thank you.

can you create an excel file, fill it with records that you want to see as the result of the query..

i really can't understand what you want to happen.

then upload the excel file