Line numbers in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I assign a line numbers in a query....I have a table that lists events
and the date of the event, this table contains many events and some events
can occur twice in one day...in that case we use an event number such as
64050506-1 and 64050506-2. I have a parameter query that shows only the
records between 2 dates...most of the time it's for a full month...example
5/1/06 thru 5/31/06. I need to be able to number the lines in consequitive
order but when I have 2 events in the same day it does not number the lines
individually. If event 64050506-1 is 4 then event 64050506-2 ends up being
numbered as 4 as well. How do I get around this problem? Thank you for all
the help extended to a novice such as myself...it is very appreciated.

Doris
 
Sorry I don't understand what you mean by posting the SQL that I am currently
using. Can you explain in more detail?

Doris
 
Open the query, then select View | SQL View from the menu. The SQL
associated with the query will be presented. Copy it, and paste it into your
reply.
 
Here is the SQL statement
SELECT (Select count(1) FROM tblEvents A WHERE
A.EventDate<=tblEvents.EventDate) AS Sequence, tblEvents.EventDate,
tblEvents.EventNum, tblEvents.EventName, tblEvents.RecNumID
FROM tblEvents
ORDER BY tblEvents.EventDate;

Doris
 
Do you have some field that's unique between the rows and is increasing? If
so, use that field for the comparison in your Sequence subselect instead of
EventDate.
 
Yes, I have an autonumber field in the table. I will give it a try...thanks
for all your help, I sure do appreciate your input...will let you know how it
works...Doris
 
Oh boy, am I ever dumb...I just realized that I gave you the wrong SQL
statement when I tried to change the Sequence statement...this is a subform
by the way and the subform is using a query of which the original SQL
statement is based on...here is the correct one...so sorry about that...

SELECT qryVolSignUps2.Sequence, qryVolSignUps2.EventDate,
qryVolSignUps2.EventNum, qryVolSignUps2.EventName, qryVolSignUps2.RecNumID
FROM qryVolSignUps2 GROUP BY qryVolSignUps2.Sequence,
qryVolSignUps2.EventDate, qryVolSignUps2.EventNum, qryVolSignUps2.EventName,
qryVolSignUps2.RecNumID HAVING (((qryVolSignUps2.EventDate) Between [Enter
beginning Date] And [Enter ending date]));
 
Here is the SQL statement in the query

Sequence: (Select count(1) FROM tblEvents A WHERE
A.RecNumID<=tblEvents.RecNumID)

The RecNumID fields is an autoNumber field and the subform is still not
numbering the line numbers correctly. Events are entered randomly that is an
event date could be entered for the month of June and then an event date
could be 2 dates in May.
 
Try replacing the 1 with * in your Count function and see whether that makes
any difference.
 
Tried inserting * IPO 1 and that didn't seem to work...is there anyway that I
can send you the tables, form and query so you can see what I'm doing?

Doris
 
OK...thanks I do appreciate your help...but the * IPO the 1 did not work...do
you have any other suggestions? I am really stumped on this one.
 
Back
Top