create date records

[M. Murphy]

Hi,
Been struggling with this to no avail.
I have a begin date record and a end date record,
and I would like to create the records in another table that corrospond.
Example, table a has 1-1-80 for the begin and 1-10-80 for the end, I want to
create 10 records in table b: 1-1, 1-2, 1-3, and so on.

Is a query the correct way to do this? I tried append, and update but I am
just not getting it.

TIA

 

[Evi]

This would be probaby an append query but your description is too vague for
us to be able to understand what you want.
what do you mean
1-1, 1-2. 1-3?
Give us table names, field names and data types.

Evi

 

[M. Murphy]

Sorry about being vague,
I will try again.
I have tableA which has 2 fields, StartDate, and EndDate, which both prompt
for a date.
Next, I want to create records in tableB, which are all the dates from
StartDate to EndDate.

for example, StartDate=January 1, 1980, EndDate=January 10, 1980.
I want the query to create these records in tableB: January 1, 1980,
January 2, 1980, January 3, 1980, and so on, up to January 10, 1980.
Is this a job for a query? I've spent a lot of the day trying to figure
this one out, and its eluding me!!

 

[Evi]

Got it. You want all the combinations of start and end date. Time to use
Dale Fye's number table again.
Create a table TblNumbers with one field intNumber

Enter the numbers 0 to 9

Create a new query (name it QryNumbers) Click on the View button and paste
in the following

(each line begins with a word in capitals so mend this if the email breaks
it up

SELECT hundreds.intNumber*100+Tens.intNumber*10+Ones.intNumber AS intNumber

FROM TblNumbers AS Hundreds, TblNumbers AS Tens, TblNumbers AS Ones

ORDER BY hundreds.intNumber*100+Tens.intNumber*10+Ones.intNumber;



You will now have a series of numbers going 1 to 1000



Step 1: Click on the (closed) QryNumbers and go to Insert Query.

Drag intNumber into this new query (QryStartDate)

In the next column type

StartDates: #01/01/2008#+[intNumber]

to generate a sequence of 1000 dates starting from 01/01/08



Filter this query so that it only shows the dates you require



Repeat the above from Step1 but this time, call the query QryEndDate and
your function will now say

QryEndDate, again, filter the query so that it shows the same series of
dates



Create yet another query.

Add both QryStartDate and QryEndDate to the grid but don't join them in any
way.

Click the downarrow next to the QueryType button

Choose Append Query

Append these dates to your table

Evi

 

[M. Murphy]

Got it, Thanks for helping me Evi!!

Got it. You want all the combinations of start and end date. Time to use
Dale Fye's number table again.
Create a table TblNumbers with one field intNumber

Enter the numbers 0 to 9

Create a new query (name it QryNumbers) Click on the View button and paste
in the following

(each line begins with a word in capitals so mend this if the email breaks
it up

SELECT hundreds.intNumber*100+Tens.intNumber*10+Ones.intNumber AS intNumber

FROM TblNumbers AS Hundreds, TblNumbers AS Tens, TblNumbers AS Ones

ORDER BY hundreds.intNumber*100+Tens.intNumber*10+Ones.intNumber;



You will now have a series of numbers going 1 to 1000



Step 1: Click on the (closed) QryNumbers and go to Insert Query.

Drag intNumber into this new query (QryStartDate)

In the next column type

StartDates: #01/01/2008#+[intNumber]

to generate a sequence of 1000 dates starting from 01/01/08



Filter this query so that it only shows the dates you require



Repeat the above from Step1 but this time, call the query QryEndDate and
your function will now say

QryEndDate, again, filter the query so that it shows the same series of
dates



Create yet another query.

Add both QryStartDate and QryEndDate to the grid but don't join them in any
way.

Click the downarrow next to the QueryType button

Choose Append Query

Append these dates to your table

Evi

Sorry about being vague,
I will try again.
I have tableA which has 2 fields, StartDate, and EndDate, which both prompt
for a date.
Next, I want to create records in tableB, which are all the dates from
StartDate to EndDate.

for example, StartDate=January 1, 1980, EndDate=January 10, 1980.
I want the query to create these records in tableB: January 1, 1980,
January 2, 1980, January 3, 1980, and so on, up to January 10, 1980.
Is this a job for a query? I've spent a lot of the day trying to figure
this one out, and its eluding me!!

 

[Dale Fye]

Yeah, it really does come in handy.

Glad you have found it so useful.

Dale

Thanks Dale - your number table has had so many uses since you taught me
how
to do it. It has found missing cheque numbers, ensured that I could view a
whole series of dates with a left join, allowed me to append dates - all
stuff I'd been coding previously
Evi

You will now have a series of numbers going 1 to 1000

Actually, the series goes from 0 to 999

;-)

Got it. You want all the combinations of start and end date. Time to
use
Dale Fye's number table again.
Create a table TblNumbers with one field intNumber

Enter the numbers 0 to 9

Create a new query (name it QryNumbers) Click on the View button and paste
in the following

(each line begins with a word in capitals so mend this if the email breaks
it up

SELECT hundreds.intNumber*100+Tens.intNumber*10+Ones.intNumber AS
intNumber

FROM TblNumbers AS Hundreds, TblNumbers AS Tens, TblNumbers AS Ones

ORDER BY hundreds.intNumber*100+Tens.intNumber*10+Ones.intNumber;

Step 1: Click on the (closed) QryNumbers and go to Insert Query.

Drag intNumber into this new query (QryStartDate)

In the next column type

StartDates: #01/01/2008#+[intNumber]

to generate a sequence of 1000 dates starting from 01/01/08



Filter this query so that it only shows the dates you require



Repeat the above from Step1 but this time, call the query QryEndDate
and
your function will now say

QryEndDate, again, filter the query so that it shows the same series of
dates



Create yet another query.

Add both QryStartDate and QryEndDate to the grid but don't join them in
any
way.

Click the downarrow next to the QueryType button

Choose Append Query

Append these dates to your table

Evi









Sorry about being vague,
I will try again.
I have tableA which has 2 fields, StartDate, and EndDate, which both
prompt
for a date.
Next, I want to create records in tableB, which are all the dates from
StartDate to EndDate.

for example, StartDate=January 1, 1980, EndDate=January 10, 1980.
I want the query to create these records in tableB: January 1, 1980,
January 2, 1980, January 3, 1980, and so on, up to January 10, 1980.
Is this a job for a query? I've spent a lot of the day trying to figure
this one out, and its eluding me!!

:

This would be probaby an append query but your description is too vague
for
us to be able to understand what you want.
what do you mean
1-1, 1-2. 1-3?
Give us table names, field names and data types.

Evi

Hi,
Been struggling with this to no avail.
I have a begin date record and a end date record,
and I would like to create the records in another table that
corrospond.
Example, table a has 1-1-80 for the begin and 1-10-80 for the end, I
want
to
create 10 records in table b: 1-1, 1-2, 1-3, and so on.

Is a query the correct way to do this? I tried append, and update
but
I
am
just not getting it.

TIA