Hey,
I have an update query that adds a certain number of dates to dates from a
table. I want the query to keep on adding dates to the first date until it
reaches or exceeds 31/12/2006. How do I do this?
Please note, the query will need to create new records too, since the user
will only be inputting the start dates!

If you need to add records you can't do it with an update query; you would
need an append query. But append queries are really designed to merge new
records already created with the existing records, not to "dynamically"
create new records as you are describing; especially not a variable number of
multiple new records. I think the task is too complex for a simple query and
the better solution is to create your new records via a macro or VBA code.

To give details I would need to know how the process is to be initiated (how
is your user entering the date; is it from a form?) and also how you know
what dates to add to your database (are you adding new records for each day
up to 3/12/2006, or every week, month, ?) and also if any other information
needs to go into these new records.
--
- K Dales


"Sidz" wrote:

> Hey,
> I have an update query that adds a certain number of dates to dates from a
> table. I want the query to keep on adding dates to the first date until it
> reaches or exceeds 31/12/2006. How do I do this?
> Please note, the query will need to create new records too, since the user
> will only be inputting the start dates!

Let me try to explain the project to you.

This is a booking system for a community centre. The centre is usually
rented out to individuals or organisations, but at the start of every year,
the Committee decides on a group of organisations to hold their activities
there for free. These bookings are made for the whole year. The intervals
between each of their activities vary from Year to Year. here is the list for
this years activites:

Play Group: Monday and Wednesday Mornings
Guides and Brownies: Tuesday Evening
Cubs and Scouts: Thursday Evening
Women's institute: Third Tuesday in every month
Line Dancing: every other Monday Evening.

The user will be inputting the dates via a form. The user should insert the
first booking date for each of the activities, their time intervals, and then
the last date until which bookings need to automatically made! All values
need to be dynamic since the number of sundays, saturdays, etc varies from
year to year!

A bit about the system I have now:
The details of these organisations are held in the tblClient.
All booking details need to stored in the tblBookings, which is linked with
the tblClient via ClientID and the tblSlot via the SlotID
The tblSlot holds details of the booking slots - there are only 21 slots
available per week (three shifts a day - morning, afternoon, evening). Thus
this table only has 21 records. Each slot has its own unique SlotID (Monday
Morning being one Slot, while Monday Afternoon is another!)

I would be very grateful if you could help me or point me in the right
direction. I don't know much about VB, but I guess I can follow a book!

Thanks for taking time to read! ;-)

"K Dales" wrote:

> If you need to add records you can't do it with an update query; you would
> need an append query. But append queries are really designed to merge new
> records already created with the existing records, not to "dynamically"
> create new records as you are describing; especially not a variable number of
> multiple new records. I think the task is too complex for a simple query and
> the better solution is to create your new records via a macro or VBA code.
>
> To give details I would need to know how the process is to be initiated (how
> is your user entering the date; is it from a form?) and also how you know
> what dates to add to your database (are you adding new records for each day
> up to 3/12/2006, or every week, month, ?) and also if any other information
> needs to go into these new records.
> --
> - K Dales
>
>
> "Sidz" wrote:
>
> > Hey,
> > I have an update query that adds a certain number of dates to dates from a
> > table. I want the query to keep on adding dates to the first date until it
> > reaches or exceeds 31/12/2006. How do I do this?
> > Please note, the query will need to create new records too, since the user
> > will only be inputting the start dates!