DCount to add sequential numbers by group only works in some instances

[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
WHERE (((Left([Part_ID],1)) Between "C" And "D"))
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 "C" produces a Seq_Num of 0, but any that start with a "D" 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 - as I said, A-C won't work, but D-Z work as expected.

Am I missing something right in front of my face?

[burrina]

Are you using the Nz Function? Have you verified the actual results of C ?

[NCSUAaron]

No, the NZ function isn't being used anywhere. There source table is (tbl_UPC) is just this:

ID	member	Part_ID	mfg_code	part_number	desc	subline	upc_code	dc pack code	uom qty_sell incr	whseqty	whsepack
1	***	AA20-2169	AA	20-2169	*******	0	0760***16**	EA	1	1	EA

And then the SQL I provided is query1 off of that table. There is nothing in between.

I am not sure if this is what you mean, but I tested this (which is what I am filtering on - the first letter of Part_ID):

Nz(Left([Part_ID],1))

and it came back "C", so it's not finding Null values.

[burrina]

I always make it a habit to allow for both Nulls and 0.

If(IsNull(SomeField), 0, SomeField) Then 'Check Null and 0.
'Do Something

Anyway, did that help?

[NCSUAaron]

Nope. Even if I drop the Where clause, and run what I have below on the whole table, I get the same behavior: Seq_Num calculates as 0 if Part_ID starts with A, B, or C, and the correct number sequence for anything else.

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;