On Nov 5, 2:06*pm, "BruceM via AccessMonster.com" u54429@uwe wrote:
Generally you would have something like the following:

tblInvoice (Invoice header table)
* *InvoiceID (primary key, or PK)
* *CustomerID
* *InvoiceDate
* *InitiatedBy
* *Other information specific to the invoice

tblDetails (line items or details for the invoice)
* *DetailID (PK)
* *InvoiceID
* *Item
* *UnitPrice
* *Quantity

InvoiceID needs to be the same data type (Number, Text) in both tables. *If
InvoiceID is an autonumber in tblInvoice, it needs to be Number (Long Integer)
in tblDetails. *Note that Number can be of various types. *Long Integer,
Double, and Currency are the ones used most of the time.

Go to View Relationships. *Add both tables. *Drag InvoiceID from one table
to InvoiceID on the other. *Click Enforce Referential Integrity when the
dialog appears. *Note that the fields do not need to be the same name, but it
may be easier to keep track of that way.

Create a main form based on tblInvoice. *From the toolbox, add a
subform/subreport control, just as you would add a text box, line, or anthing
else. *With the subform control selected, click View Properties. *Set its
Source Object to tblDetails, and its Link Child and Link Master fields to
InvoiceID if it is not already done. *Click the three dots next to either
Link property, and it should take you through the rest of the process.

Add an Invoice record on the main form. *On the subform, add a few line items
(details). *Create another Invoice record (main form) with a few line items.
Go back to the first record. *The line items should still be there. *Look at
the tables. *Each Detail record should have a value in InvoiceID
corresponding to an InvoiceID value in tblInvoice.

In many cases there is a listing of items that may be added to Details. *If
you were dealing in products it would be a Products table. *In the case of
services there may be a listing of services and prices. *In this case you
would have a Services table:

tblService
* *ServiceID (PK
* *Description
* *UnitPrice
* *Quantity

tblDetails would look like this:

tblDetails (line items or details for the invoice)
* *DetailID (PK)
* *InvoiceID
* *ServiceID
* *UnitPrice
* *Quantity

Add tblService to the Relationship window, and create a relationship between
the ServiceID fields.

Base the subform on tblDetails as before. *Rather than Item you will be
linking to tblService. *Make a combo box. *Set its Row Source to a query
based on tblService. *The first column (hidden) in the combo box is ServiceID.
The other column(s) are visible, allowing you to select the item by name
rather than number. *Set the combo box Control Source to ServiceID.

This is just a sketch, but I will have to leave it at that for now. *For more
information about design and general database principles, this is a list John
Vinson often provides:

Jeff Conrad's resources page:http://www.accessmvp.com/JConrad/acc...resources.html

The Access Web resources page:http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:http://allenbrowne.com/links.html#Tutorials

Crystal's tutorial is as good a place as any to start, IMHO.

To set up an incrementing Invoice number, one technique may be found hehttp://www.rogersaccesslibrary.com/f...ts.asp?TID=395





wrote:
Had I been asked I would have recommended against adding the last sentence to
the previous posting, but the other points are valid. *My understanding of
[quoted text clipped - 76 lines]

- Show quoted text -

Hi Bruce, *Thanks for your detailed post. *You make a much more
convincing argument which has won me over. *Part of my reticence to
construct an invoice header table and a separate one for invoice item
lines lay in my ignorance of the approach I should take. *I did say I
was not designing an accounting system here but just the ability to
print invoices. Can you give me some advice?
I understand what fields should be in the 2 tables but am unclear how
to populate them.

The nature of the business is *a service based one - charges are made
to a range of clients for each enquiry handled. *These are captured
through a basic data entry screen, allocated to a client and populate
an enquiries transaction table. *What is the best approach then to
populate the invoice header and invoice lines tables?

Gordon

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

- 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