C
CW
We store customers' goods and bill them quarterly for this, based on unit
prices and quantities etc that are all held in our database.
Each storage job is a separate record, containing date fields such as
LastInvFrom and LastInvTo, and NextInvFrom and NextInvTo, and NextInvDueDate.
I have a query (QryStgBilling) that picks out all the jobs that are going to
become due for next billing within 7 days of the current date.
At present we run this and then have to create each invoice manually (it's a
report that requires very little input before it can be run as all the data
is already held on the job record). Then we have to enter the dates of the
period we've just invoiced, and the dates of the next period to be done in
due course.
I'd like to achieve the following:
The query would be run as a check on the jobs to be invoiced, and then the
relevant data would be displayed as continuous forms i.e. in rows. I would
have a checkbox against each record for the user to flag that they do want
each next invoice to be produced (sometimes e.g. if you know the job is going
out soon, you know you don't want to produce an invoice for the whole next
quarter so you would create one manually).
So far, no problem for me. Now the tricky bits...
1. I would then want to have a single button for the user to hit in order to
generate all the flagged invoices. I was thinking of a query selecting the
flagged jobs, upon which a report (i.e. the invoice) would be generated. Does
that sound right?
2. As soon as that has been run I would want the LastInv dates and the
NextInv dates all to be populated automatically. I guess that's some kind of
Update query but I'm not sure how to make it work!
3. In a similar manner the value of the invoice (generated by the various
fields in the original query) should be stored in an invoice table along with
the job ref and the invoice date, so that we can see that it has been done.
I may be asking too many questions at once here but I'll be very grateful
for any suggestions on any (or all!) of my three issues.
Many thanks
CW
prices and quantities etc that are all held in our database.
Each storage job is a separate record, containing date fields such as
LastInvFrom and LastInvTo, and NextInvFrom and NextInvTo, and NextInvDueDate.
I have a query (QryStgBilling) that picks out all the jobs that are going to
become due for next billing within 7 days of the current date.
At present we run this and then have to create each invoice manually (it's a
report that requires very little input before it can be run as all the data
is already held on the job record). Then we have to enter the dates of the
period we've just invoiced, and the dates of the next period to be done in
due course.
I'd like to achieve the following:
The query would be run as a check on the jobs to be invoiced, and then the
relevant data would be displayed as continuous forms i.e. in rows. I would
have a checkbox against each record for the user to flag that they do want
each next invoice to be produced (sometimes e.g. if you know the job is going
out soon, you know you don't want to produce an invoice for the whole next
quarter so you would create one manually).
So far, no problem for me. Now the tricky bits...
1. I would then want to have a single button for the user to hit in order to
generate all the flagged invoices. I was thinking of a query selecting the
flagged jobs, upon which a report (i.e. the invoice) would be generated. Does
that sound right?
2. As soon as that has been run I would want the LastInv dates and the
NextInv dates all to be populated automatically. I guess that's some kind of
Update query but I'm not sure how to make it work!
3. In a similar manner the value of the invoice (generated by the various
fields in the original query) should be stored in an invoice table along with
the job ref and the invoice date, so that we can see that it has been done.
I may be asking too many questions at once here but I'll be very grateful
for any suggestions on any (or all!) of my three issues.
Many thanks
CW