Invoicing database design

On Nov 6, 12:43*pm, "BruceM via AccessMonster.com" u54429@uwe wrote:
It sounds as if your term for line items is enquiry. *It would help to know a
little more about the structure, but I will use part of the field list you
showed in an earlier post as a starting point. *Work on a copy of the
database, of course.

invoice date
invoice no
customerID
productID
item description
Quantity
Unit price

I will assume InvoiceDate, InvoiceNo, and CustomerID are header items, and
the rest are line items, or enquiries. *I will asume further that the
InvoiceDate is the same for all records with a particular InvoiceNo. *Same
for CustomerID. *You could make a SELECT DISTINCT query of InvoiceDate,
InvoiceNo, and CustomerID, or a totals query in which you group on those
three fields. *In any case the query will include just the header fields, and
will show only unique records. *Use the query to make a table containing the
same information. *This could be a make-table query, or I think you can copy
a query and paste it as a table. *There are other adaptations you could use,
but in the end there will be only one record per InvoiceNo.

Now make a copy of the original table. *Delete InvoiceDate and CustomerID
fields. *Create a one-to-many relationship between CustomerID in the Header
table created from the query and CustomerID in this new Details table. *Use
the Header table as the basis for an Invoice form, and the Details table as
the basis for a subform as described in the posting in which I sketched the
table structure.

This should leave you with Header (Invoice) information on the main form and
related LineItem/Detail/Enquiry records on the subform.

Whatever the details, the general idea is that repeated Header information
will be consolidated into one table with no repetition, and Header fields
deleted from a copy of the original table to leave you with the Detail
information.

As for printing a report, you could filter or group the records as needed,
but I am not clear on how TransactionDate from your original posting of table
structure fits into this. *I assume it is part of a Detail record, but I
don't know if the TransactionDate month will always be the same as the
InvoiceDate month. *You can make it do what you want either way, but it is a
little more involved if the Header and Detail months are not the same in all
cases.

BTW, whenever I mention basing a form on a table you could (and probably
should) use a query based on the table instead. *This will allow you to sort
by InvoiceNunber, among other things.





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
[quoted text clipped - 54 lines]

- 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

--
Message posted viahttp://www.accessmonster.com- Hide quoted text -

- Show quoted text -

Thanks Bruce, that's great. I can see through the fog now! Thanks for
all your help and your patience.

Gordon

This is where I learned a lot of what I know about Access. Glad to pass
along some of it in turn. Good luck with the project.

wrote:
It sounds as if your term for line items is enquiry. Â*It would help to know a
little more about the structure, but I will use part of the field list you
[quoted text clipped - 71 lines]

- Show quoted text -

Thanks Bruce, that's great. I can see through the fog now! Thanks for
all your help and your patience.

Gordon

--
Message posted via http://www.accessmonster.com