Sequential List of numbers

[Guest]

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?

 

[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

 

[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?

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]

 

[Guest]

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?

 

[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

 

[Guest]

Thanks. Thank you both.

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

 

[Guest]

Thanks John.

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?

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]