Append query help (1 Viewer)

[guinness]

I want an append query to duplicate a record I create but change the date. Let me try to explain.

• I create a record that records someones activity between a given start date and end date.
• I have a query that works out how many working days between the start and end
• Let's say the query says 9 working days between the 30th of June and 7th July
• I have a table with all the working dates for the next couple of years
• I want to create 8 new records identical to the original but using sequential start dates from my dates table

Can anyone start me off in the right direction?

Cheers

Guinness

 

[plog]

This isn't a job for a query, but for VBA. In general this is the process:

Read in that query, loop through every record, then run another loop from the start date to the end date and inside that loop you build and run an INSERT query that creates a record for every day you need.

Specifically, you will need to use a Recordset, 2 loops and the DoCmd.RunSQL method. I'm sure this has been done before on this forum you might be able to find the code in the VBA section.

 

[namliam]

Instead of using a join with your workdays query, simply use:
Workdate between startdate and enddate

I hope you get the idea...