Formulas and SQL syntax for Microsoft Access 2007

mmcgillo88
mmcgillo88 used Ask the Experts™
on
If I have a data table in Access and I want to get the number of unique ID's in a column (called ID). I then want to get the sum total of everything in a column as long as it is a unique ID.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?
Try this:

SELECT DISTINCT <ID Column>, SUM(<column you want to get the sum for>)
FROM <Table you want to query>;

Open in new window

Author

Commented:
Yes, I want the count of number of different ID values.
Top Expert 2016

Commented:
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

Author

Commented:
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;
Top Expert 2016

Commented:
try this



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

Author

Commented:
This looks good. Will this be able to count at the bottom the number of unique invoice IDs as I do not want duplicates?
Top Expert 2016
Commented:
what Count, do you want to see ?

post sample db and the expected result you want to see..



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

Author

Commented:
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.
Top Expert 2016

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial