Sequential List of numbers

S

Steven Cheng

I have a table setup with fields for start_date, end_date, and name. I am
trying to create a ticketing system so that a unique number for each day
between start and ending dates. Can I do this with queries or do I have to
go into the VBA environment?
 
M

Madhivanan

Try this

Select Sno=(Select count(distinct start_date) from Setup where
Start_date<=T.Start_date) , * from Setup T group by
Start_date,End_Date,name

Madhivanan
 
J

John Vinson

I have a table setup with fields for start_date, end_date, and name. I am
trying to create a ticketing system so that a unique number for each day
between start and ending dates. Can I do this with queries or do I have to
go into the VBA environment?
Click to expand...

I find it handy (for this and many other uses) to have a table Num
with one field N, containing values from 0 through the largest
sequential number you're likely to need. Be generous; it's a tiny
table even with 100,000 rows.

You could create a Query returning these records by including Num in a
query with your table, with a criterion on N of

<= DateDiff("d", [Start_Date], [End_Date])


John W. Vinson[MVP]
 
S

Steven Cheng

So, is this an SQL statement as I don't recognize the SNO= and doesn't the
"Setup T" have the word AS in between?
 
D

Douglas J. Steele

While I believe that dialect of SQL works in some DBMS, it don't think it'll
work in Access.

Try

SELECT (SELECT COUNT(DISTINCT start_date)
FROM Setup WHERE Start_date<=T.Start_date) AS Sno, *
FROM Setup AS T
GROUP BY Start_date,End_Date,name
 
S

Steven Cheng

Thanks. Thank you both.

Douglas J. Steele said:
While I believe that dialect of SQL works in some DBMS, it don't think it'll
work in Access.

Try

SELECT (SELECT COUNT(DISTINCT start_date)
FROM Setup WHERE Start_date<=T.Start_date) AS Sno, *
FROM Setup AS T
GROUP BY Start_date,End_Date,name
Click to expand...
 
S

Steven Cheng

Thanks John.

John Vinson said:
I have a table setup with fields for start_date, end_date, and name. I am
trying to create a ticketing system so that a unique number for each day
between start and ending dates. Can I do this with queries or do I have to
go into the VBA environment?
Click to expand...

I find it handy (for this and many other uses) to have a table Num
with one field N, containing values from 0 through the largest
sequential number you're likely to need. Be generous; it's a tiny
table even with 100,000 rows.

You could create a Query returning these records by including Num in a
query with your table, with a criterion on N of

<= DateDiff("d", [Start_Date], [End_Date])


John W. Vinson[MVP]
Click to expand...
 
Top