DCount to add sequential numbers by group only works in some instances (1 Viewer)

[NCSUAaron]

Banging my head against the wall.

I have a large table (>1M rows), and I have searched various forums for a way to add sequential numbers by Group. The query I have works, but since it's a large table, I broke it up, and did everything what starts with A-E, the F-Q, etc, and appended to a new table.

This query works, on anything that starts with the letter D or later... A-C will not work.

Basically, the source table is a list of all combinations of Part_ID and UPC_Code. I am trying to number the UPC_Code field, per Part_ID. There is an AutoNumber field (ID) that is in the table as well.

This is the SQL. Query1 is the query that is being executed, so the DCount is within this same query, if that makes sense.

SELECT tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code, DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "'")-DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "' AND [ID] > " & [ID]) AS Seq_Num
FROM tbl_upc
GROUP BY tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code
ORDER BY tbl_upc.ID;

The results of this query are that all Part_IDs that start with A through C produces a Seq_Num of 0, but any that start with a "D" or later number correctly - in other words, the first instance of a particular Part_ID is 1, then 2, and so on up to the total count of that Part_ID.

I am really at a loss.

Running Access 2013.

Am I missing something right in front of my face?

 

[CJ_London]

suggest you post some sample data and the result you want, I think I understand what you are trying to achieve but really can't make sense of what you are doing.

Query1 is the query that is being executed, so the DCount is within this same query, if that makes sense

No!

What starts with a-e? Part_ID?

What is a UPC Code? Is that what you are trying to calculate?

 

[NCSUAaron]

Well, I went in and grabbed a sampling of the data that was producing the correct results, and also some that wasn't, but when I re-ran the query the behavior was as I desired on all of the records.

Then i went back to my full data source, and it's working there, now, as well. I didn't change anything from the time I posted this, so I either Bill Gates is messing with me, or I need more coffee. Sorry for the false alarm, and thanks anyway.

:banghead: