append Query

[Guest]

I am using MS Access 2003.I have a client who is using our service a certain
number of times during the contract. The information is entered using forms
and the details given are the Start date, end date and frequency(either
weekly, fortnightly etc.) I would like to calculate the dates the service is
to be given and append these records into the table using an append query. I
hope this makes sense.

 

[Tom Ellison]

Dear Marianne:

If a query can generate these dates "in advance" then why bother storing
them "in advance". As long as you can generate them whenever you need them,
this should be sufficient.

Later, when there's some data to be stored along with those dates you can
add the rows to a table.

Does this make sense?

Whichever way you decide to do it, I recommend starting with a table of
dates. Just one column. Put in dates will into the future. This can be
automated.

Using a cross-product with this query, a DateDiff function call can give you
the intervals to all future dates up to and including the end date. Use
Modulo division of this number of days to filter to only those dates that
are at the desired interval (7 days, 14 days, etc.) This will provide a
query set of dates for the future events. Make sense?

This would be a fairly direct way of approaching this problem. I don't know
how well you can digest and implement this. I'll stop now and see what you
think.

Tom Ellison

 

[Guest]

I don't think I explained what I was trying to do very well.The table I'm
trying to enter information into is a table of all the service dates and
times that our carers are scheduled to do.The information is currently in an
Excel spreadsheet with the Contract Nos and the staff member rostered to do
that shift, the Start Date and end date of the contract and the frequency the
shift is to be done, (weekly,fortnightly etc.) When this information is
entered into the form I would like to be able to click a button on the form
to run an append query to add each shift automatically. I hope this is a bit
clearer?

Marianne

 

[Tom Ellison]

Dear Marianne:

I do not understand your situation any differently than before. I crafted
an answer that should still suffice for what you want. Sorry if you don't
see what I was suggesting. If you don't know something about how to do what
I suggested, but want to try it, please let me know what that is.
Otherwise, I just suggest you look it over again, ask any questions you need
to, and then see what you may want from my offering.

Tom Ellison