Access Query Count (1 Viewer)

jalopez453 · Nov 13, 2018

Hello, I was wondering if someone would be able to tell me if this is possible or not. I want to build a query with a count function that also includes duplicates.

For example:
STREET 1
ROAD 1
AVE 1
AVE 2
WAY 1
WAY 2
WAY 3

I am looking to count the duplicates in order rather than the query tagging it as 2 or 3 for each line item.

plog · Nov 13, 2018

Yes, but not with the data you have provided. You are assuming that your data has an implicit order--it does not. If you simply have 3 "WAY" values in your table, it will not be able to differentiate which WAY is which. You need another field to let the computer know that this WAY is first, this WAY is second and so on.

So, you need a field that makes your rows unique (or at least unique with regard to the value you want to order). Do you have such a field? Perhaps a Date/Time field or an autonumber ID field?

jalopez453 · Nov 13, 2018

I do, but there is a chance the other field will have the same detail in the line item, all other fields I have are exactly the same. What if I add an ID (Primary Key)? Would this help me?

plog · Nov 13, 2018

Yes, if you add an autonumber that will do it. What you would then do is use this SQL to generate what you want:

Code:

SELECT FieldName, DCount("[FieldName]", "TableName", "[ID]<=" & [ID]) AS FieldNameRunningTotal 
FROM TableName

You didn't provide table nor field names, so update the above code as appropriate.

jalopez453 · Nov 13, 2018

Thank, yes sorry about that, I wasn't sure if it was possible or not which is why I just used generic words. I tried the code you provided above but it is counting the line items in order rather than how many instances there are. These are the fields I have below and this is how I want it to count, the amount of Acct Numbers there are.

Code:

ID	Acct	AcctCnt
1	0110	1
2	0111	1
3	0111	2
4	0112	1
5	0113	1
6	0114	1

plog · Nov 13, 2018

Sorry forgot to include the Acct criteria:

Code:

SELECT Acct, DCount("[Acct]", "TableName", "[ID]<=" & [ID] & " AND [Acct]='" & [Acct] & "'"  ) AS AcctRunningTotal
FROM TableName;

jalopez453 · Nov 13, 2018

YES!! This is exactly what I wanted it to do! Thank you so much for your help and patience, truly appreciate it. Have a great day.

jalopez453 · Nov 13, 2018

Would you happen to know why when I run my export (transferspreadsheet) it is showing the old format of my query name on my excel sheet name. I updated the name on my query from _Ins202 to Ins202, but when I run the export it keeps showing the sheet name as _Ins202 even though the query states Ins202.

plog · Nov 14, 2018

No I don't. If its important rename your existing query back, copy it and paste it with the new name then delete the one with the name you don't want and just use the properly named one.

Access Query Count (1 Viewer)

jalopez453

Registered User.

plog

Banishment Pending

jalopez453

Registered User.

plog

Banishment Pending

jalopez453

Registered User.

plog

Banishment Pending

jalopez453

Registered User.

jalopez453

Registered User.

plog

Banishment Pending

Similar threads

Users who are viewing this thread