Question on Days

[Guest]

I have a simple table at the moment that records the date and time, e.g.
01/08/2005 13:33:00 for a particular person.

Here is the table schema:

ID :: Autonumber, 4
Event_Name :: Text, Joe Bloggs
Date :: Date/Time, 08/09/2005 01:00
EndDate :: Date/Time
Location :: Text
Description :: Memo, I will be on holiday
Category :: Text, Holiday
noofdays :: 5

I have a record for each event that occurs. What I need to do is to create
a query that replacates the record x number of times depending on the number
of days selected. For instance, if I select 5 in my table as the noofdays,
then I will have in my Query x5 records that are identical to the one above
corresponding to the days 09/09/2005, 10/09/2005 etc.... for 5 days.

Please help!

skc

 

[Duane Hookom]

You can use a cartesian query. Create a table of numbers (tblNums) with a
numeric field (Num) and values from 1 to the maximum number of days in your
table.

Then create a query with your unnamed simple table and tblNums. Add the Num
field to the query grid and set a criteria under it to <=[NoOfDays]

This should create 5 similar records if the NoOfDays is 5.

 

[Guest]

Good stuff, but for the repeated days I want the date to be incremented.

So, if I have 5 entries for Bill, I want his dates to reflect noofdays -
e.g. 01/08/2005, 02/08/2005 etc...

skc

You can use a cartesian query. Create a table of numbers (tblNums) with a
numeric field (Num) and values from 1 to the maximum number of days in your
table.

Then create a query with your unnamed simple table and tblNums. Add the Num
field to the query grid and set a criteria under it to <=[NoOfDays]

This should create 5 similar records if the NoOfDays is 5.

--
Duane Hookom
MS Access MVP

I have a simple table at the moment that records the date and time, e.g.
01/08/2005 13:33:00 for a particular person.

Here is the table schema:

ID :: Autonumber, 4
Event_Name :: Text, Joe Bloggs
Date :: Date/Time, 08/09/2005 01:00
EndDate :: Date/Time
Location :: Text
Description :: Memo, I will be on holiday
Category :: Text, Holiday
noofdays :: 5

I have a record for each event that occurs. What I need to do is to
create
a query that replacates the record x number of times depending on the
number
of days selected. For instance, if I select 5 in my table as the
noofdays,
then I will have in my Query x5 records that are identical to the one
above
corresponding to the days 09/09/2005, 10/09/2005 etc.... for 5 days.

Please help!

skc

 

[Duane Hookom]

Take your date field (Date is not a good name for a field) and add Num-1 to
get all the dates:
SchedDate: [Date] + ([Num]-1)

--
Duane Hookom
MS Access MVP

Good stuff, but for the repeated days I want the date to be incremented.

So, if I have 5 entries for Bill, I want his dates to reflect noofdays -
e.g. 01/08/2005, 02/08/2005 etc...

skc

You can use a cartesian query. Create a table of numbers (tblNums) with a
numeric field (Num) and values from 1 to the maximum number of days in
your
table.

Then create a query with your unnamed simple table and tblNums. Add the
Num
field to the query grid and set a criteria under it to <=[NoOfDays]

This should create 5 similar records if the NoOfDays is 5.

--
Duane Hookom
MS Access MVP

I have a simple table at the moment that records the date and time, e.g.
01/08/2005 13:33:00 for a particular person.

Here is the table schema:

ID :: Autonumber, 4
Event_Name :: Text, Joe Bloggs
Date :: Date/Time, 08/09/2005 01:00
EndDate :: Date/Time
Location :: Text
Description :: Memo, I will be on holiday
Category :: Text, Holiday
noofdays :: 5

I have a record for each event that occurs. What I need to do is to
create
a query that replacates the record x number of times depending on the
number
of days selected. For instance, if I select 5 in my table as the
noofdays,
then I will have in my Query x5 records that are identical to the one
above
corresponding to the days 09/09/2005, 10/09/2005 etc.... for 5 days.

Please help!

skc

 

[Guest]

I tried this but when I have 25/08/2005 and add 10 days, it does not spill
onto September! Instead I get 01/08/2005 instead of 01/09/2005.

 

[Duane Hookom]

Maybe try the DateAdd() function and realize that MS likes dates formatted
as m/d/yyyy.