Re: Multiple Fields in record

it doesn't matter whether an invoice has 3 line items, or 300, or 1. *each
line item will be an individual record in tblInvoiceDetails.* and you don't
store the total invoice amount anywhere. you calculate it at runtime,
anywhere you want to see it, by adding all the line item amounts for an
invoice to get the total.

as Philip said, now is when you want to learn how to structure your tables
and relationships correctly, or you'll spend 10 times as long, down the
line, going back and fixing them. recommend you read up/more on relational
design principles (normalization) so you'll understand better one-to-one,
one-to-many, and many-to-many relationships, and how to support them in
Access. for more information, see
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

hth


"acss" <joekru98@xxxxxxxxxxx> wrote in message
news:452C5DAD-4012-4785-85A2-71810BDAABC1@xxxxxxxxxxxxxxxx
I have two tables, one for Invoices which contain fields such as InvID-PK,
Inv description, date, Reference. Then i have an Invoice Details table,
DetailId-PK, InvID-FK, Detail Description, InvAmt. Not all invoices have 3
line items that add up to the total amount so while some records will have
detail amounts others will be blank and only a totals amount would be in
the
InvAmt. Should i have a named field for each line item that will lead up
to
the total? Also should i have an InvAmt in the Invoice Table and
InvDetails
table as well?

"Philip Herlihy" wrote:

acss wrote:
Begining stages of design in DB so i want to be sure on steps. It will
be to
track expenses on invoices and each invoice has several line items
(expenses
with dollar amounts) that add up to the final total. Do I need to have
a
field in my table for each line item so it an be populated by a combo
box on
a form?

You're asking the right questions at the right time. Get your tables
right and everything else falls into place - otherwise it quickly
becomes an impossible tangle.

This is a familiar pattern - "one-to-many". That's normally represented
by two tables, one with a field to carry references to records in the
other - a "foreign key".

If you structure your data this way, and then use the Form Builder
wizard, Access will (if you look out for the options) create you forms
which will get you working with your data right away.

You'd typically use a combo box when you want to provide the user with a
convenient way of choosing one from multiple stable options - an easy
example would be "Male / Female" in one situation, or a list of charge
rates in another. In the first case, the options would be stored among
the "Properties" of the combo box control. In the second case, the
charge rates would probably be stored in a dedicated table. although
Access allows "lookup" options in tables, it's better keep your tables
as pure and simple as possible.

You might like to look at a couple of my favourite web references on
table design:

http://www.lynda.com/home/Player.aspx?lpk4=31001
http://tinyurl.com/ms-table-design-tutorial

You might also like to have a look at the samples which come with Access
(although they can be omitted at install time). You can create a
database from a template (File, New, and look for the template options)
or study the Northwind sample database (under Help, Samples - oddly
enough). They can seem a bit daunting for a beginner, but you'd be
welcome to ask questions here.

Helps to know which version you're using. The fundamentals are the same
but the "dashboard" is different, especially in Access 2007.

HTH

Phil, London



.

Relevant Pages

  • Re: SUM on 1 uniques record from primary table
    ... I haven't managed to do this so, I modified my original query that I used as a record source and I Summarize the totals there. ... If it is then the fact that the amountPaid appears repeatedly for each item of an invoice must also be bad design right? ... now it displays only the last amount paid. ...
    (microsoft.public.access.reports)
  • Re: Billing Question
    ... you would put the line items onto the Rate Quote by adding records to a table named tblProjectDetail. ... The records would contain the line item information for each item including description, amount, quantity etc. ... From there you would generate an invoice based on the tblInvoiceDetail records. ... You'll probably also want to have something in place that totals the tblInvoiceDetail records since the total of ALL of the records should balance to the total on the RATE QUOTE. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Billing Question
    ... Thanks David, ... invoice percentage". ... > line item information for each item including description, amount, ... > probably also want to have something in place that totals the ...
    (microsoft.public.access.modulesdaovba)
  • Rookie:Form_AfterUdate?
    ... Currently I can print an invoice with the subtotal and totals on it, but can't get it to automatically subtract the amount of items ...
    (microsoft.public.access.forms)
  • Re: Newbie, Help needed with rounding errors
    ... This is why tax should only be computed on the sub-grouped total values, ... The Revenue's method of subtracting the computed price net of tax from the ... price per item on the invoice, so I round to get 21.57 each. ... incorrect totals." ...
    (microsoft.public.access.gettingstarted)