Access SQL Seq Numbering

[bernman]

Need to number a group of records( Focus_Group field/ Date ascending/ Tail is the value) SeqN should be 1,2,3,etc

This works in another database same sql!! All I get here is "1's"!

SELECT Defer_sorted.Focus_Group, Defer_sorted.Created_Date, Defer_sorted.Tail, (SELECT Count(*) FROM Defer_sorted as X
WHERE (((Defer_sorted.Focus_Group) Is Not Null))
and X.Focus_Group = Defer_sorted.Focus_Group
and X.Tail >= Defer_sorted.Tail
and X.Created_Date =Defer_sorted.Created_Date) AS SeqN
FROM Defer_sorted
ORDER BY Defer_sorted.Focus_Group, Defer_sorted.Created_Date;

Table Sample, "*" is the field seperator

Focus_Group *Created_Date *Tail *SeqN
ACARS *5/25/2015 *858 *1
ACARS *5/26/2015 *4053 *1
APU *5/26/2015 *951 *1
APU *5/26/2015 *4011 *1
APU *5/26/2015 *511 *1
APU *5/27/2015 *4276 *1
APU *5/27/2015 *245 *1

 

[pbaldy]

Does your date field have a time component?

 

[spikepl]

Does your date field have a time component?

Great call - I was staring at this thing .. and staring ...

 

[bernman]

Paul,
It was originally Date and Time. I past it through to another table as "Short Date". Really don't need "Date or Time" for this exercise. We just want each Focus Group to have the SeqN 1,2,3 numbering and Tail (value).
thanks
Bernie

 

[vbaInet]

In Defer_sorted Group By Focus_group > Sort by Created_Date ASC > Sort by Tail ASC and it should work:

SELECT 	 Defer_sorted.Focus_Group, 
	 Defer_sorted.Created_Date, 
	 Defer_sorted.Tail, 
	 (
	 SELECT Count(*) 
	 FROM	Defer_sorted AS X
	 WHERE 	Defer_sorted.Focus_Group IS NOT NULL
		AND X.Focus_Group = Defer_sorted.Focus_Group
		AND X.Tail >= Defer_sorted.Tail
		AND X.Created_Date >= Defer_sorted.Created_Date
	 ) AS SeqN
FROM 	 Defer_sorted
ORDER BY Defer_sorted.Focus_Group, 
	 Defer_sorted.Created_Date;

 

[pbaldy]

My guess is that there is a time component, which would make each record unique, thus the "1" for each. What happens if you exclude the date field from the subquery?

 

[pbaldy]

I'll get out of the way.

 

[bernman]

Took the Date out of the SQL just now and still get "1's". My thinking is that I need to tell it to group by Focus_Group but don't want to group by Tail, as that's a value.

 

[bernman]

Everyone many THANKS!!!

Will review to see what I missed

 

[vbaInet]

Your data just needed to be sorted like this:

Focus_Group	Created_Date	Tail	SeqN
ACARS		5/25/2015	858	1
ACARS  		5/26/2015  	4053  	2
APU		5/26/2015	511	1
APU		5/26/2015	951	2
APU		5/26/2015	4011	3
APU		5/27/2015	245	4
APU		5/27/2015	4276	5

And the other thing you missed was >= in this criteria:

AND X.Created_Date >= Defer_sorted.Created_Date

And by the way, looking at your data you don't really need this criteria:

AND X.Tail >= Defer_sorted.Tail

Lastly, if there are a significant amount of Nulls, it's best to create a query that filters out the Nulls and use that query in the subquery then you can also remove this line:

Defer_sorted.Focus_Group IS NOT NULL

 

[vbaInet]

I'll get out of the way.

Why Paul?