On 5 Nov, 20:34, "BruceM via AccessMonster.com" u54429@uwe wrote:
Are you saying there are invoices with several line items, and that there may
be several such invoices per month for a client? *If so, are you further
saying that if there are three invoices per month for a customer, each
invoice with three line items, that you want the summary report to show all
nine line items, or that you want it to show three lines: *the summaries of
the individual invoices?

Either may be done, but if you are summarizing an invoice into a single line
what detail information can you show other than grand total? *You could add
up the total quantity, but that may not make sense if the three line items
are for different things.

It could be that the report's Sorting and Grouping capabilities could give
you what you need. *However, I need to stress that if you have a conventional
invoice system with several line items grouped under a single invoice number,
you should proceed with related tables. *If you are set up properly on that
first level any big picture data can be produced. *This is true on every
level. *If your design is properly normalized you can do almost anything with
the data.

I am struggling to convert a project from my earlier Access days that is not
properly normalized. *On some things I knew better but decided it was too
much bother. *On others I just didn't know better. *Both are proving equally
vexing now.





wrote:
Generally you would have something like the following:

[quoted text clipped - 112 lines]

- Show quoted text -

Hey, thanks for taking the time to spell all that out for me. *I
really appreciate that. *I think I was looking for a different
solution, which reflected the work flow process. *You see, all those
enquiries I talked about are entered through the month, then at the
end of the month I was looking to devise a routine that with the click
of a button would print out for each client an invoice (Access report)
listing the summary details of each charged for enquiry processed that
month with a unit price etc and a total invoice charge. *The enquiries
table would then be updated with a flag (tick box) or the invoice no/
ID showing that the enquiry had been invoiced.

Perhaps that solution is a bridge too far and I should concentrate on
devising a main form/subform combination - combo box with clients in
the main form, enquiries relative to that client in the subform, with
a button to click to produce an individual invoice for that client for
all outstanding (ie uninvoiced) enquiries. *In doing that I could put
some codein ther at the same time to write the relevant data to an
invoice header file and an invoice transactions file. *Does that seem
feasible to you or can you think of a better approach?

Gordon

--
Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/20...- Hide quoted text -

- Show quoted text -

Normally only 1 invoice per client per month - that invoice could have
one or more item lines (each one representing a unique enquiry with
its own characteristics). I have a table of those enquiries - what I
am struggling with is how do I convert the enquiries into invoice item
lines and invoice headers without going through each client and
creating an invoice header and then selecting the item lines to add to
that invoice or worse still re-keying the item lines (enquiries).
Unless you can suddenly see what I am driving at (which cannot be easy
I know), I will adopt the approach I outlined in my previous post
above.


Gordon