Multiple Fields in record

[acss]

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?

 

[Philip Herlihy]

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

 

[Philip Herlihy]

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

 

[acss]

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?

 

[acss]

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?

 

[tina]

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

 

[tina]

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

 

[Philip Herlihy]

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?

Your table definitions sound correct to me. I don't follow some of what
you've written: ".. only a totals amount would be in the InvAmt".

This is how I approached a similar situation, with unnecessary details
omitted:

I have a table of customers. I have a table of "Goods and Services
supplied", and another of "workperiods". So if I spend half an hour
installing some network card, which I've supplied, this customer will
have a record in each. The tables are related one Customer to many
"Goods..." and to many "WorkPeriods". I have a form which allows
convenient data entry when I've done some work.

When it comes to invoicing, I have a main form of customer records. I
use a combo box on the customer-ID field to trigger (using an Event
handler in VBA) a filter to the selected customer only.

On the form I have two subforms, linked by Customer-ID, displaying
"Goods..." and "WorkPeriods" respectively. Each displays however many
records there happen to be for that customer. Sometimes there are none,
so it simply shows a line for a new record (pretty sure you can stop
this, but I've never bothered). In each subform's footer, I have a
(hidden) text box whose control source sums the appropriate field of
records displayed in the subform. On the main form, just under the
subform, I have another text box, not hidden, which refers to the
corresponding text box on the subform. This is done so that a further
text box at the bottom of the main form can total the subform-totals.

So, having selected/filtered a particular customer, the Grand Total box
shows the total of the two "subtotal" boxes, each of which get their
value from a total of the subform's displayed records. (Phew - hope you
follow this!).

So, to (finally) answer your questions:

No, you shouldn't have a named field in the Invoices table for each
line-item. You store the foreign key at the other end of the
relationship. Line-items are records in a separate table, related to
the Invoice by a field in the line-item which stores the Invoice's key.
Note that I've done this differently. I invoice some customers only
monthly so the more important relationship is between customer and
line-item: the Invoice is filled in later when the invoice is generated.

No, you shouldn't store the InvAmt in two places. You can always derive
the Invoice total from the line-items.

Phil

 

[Philip Herlihy]

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?

Your table definitions sound correct to me. I don't follow some of what
you've written: ".. only a totals amount would be in the InvAmt".

This is how I approached a similar situation, with unnecessary details
omitted:

I have a table of customers. I have a table of "Goods and Services
supplied", and another of "workperiods". So if I spend half an hour
installing some network card, which I've supplied, this customer will
have a record in each. The tables are related one Customer to many
"Goods..." and to many "WorkPeriods". I have a form which allows
convenient data entry when I've done some work.

When it comes to invoicing, I have a main form of customer records. I
use a combo box on the customer-ID field to trigger (using an Event
handler in VBA) a filter to the selected customer only.

On the form I have two subforms, linked by Customer-ID, displaying
"Goods..." and "WorkPeriods" respectively. Each displays however many
records there happen to be for that customer. Sometimes there are none,
so it simply shows a line for a new record (pretty sure you can stop
this, but I've never bothered). In each subform's footer, I have a
(hidden) text box whose control source sums the appropriate field of
records displayed in the subform. On the main form, just under the
subform, I have another text box, not hidden, which refers to the
corresponding text box on the subform. This is done so that a further
text box at the bottom of the main form can total the subform-totals.

So, having selected/filtered a particular customer, the Grand Total box
shows the total of the two "subtotal" boxes, each of which get their
value from a total of the subform's displayed records. (Phew - hope you
follow this!).

So, to (finally) answer your questions:

No, you shouldn't have a named field in the Invoices table for each
line-item. You store the foreign key at the other end of the
relationship. Line-items are records in a separate table, related to
the Invoice by a field in the line-item which stores the Invoice's key.
Note that I've done this differently. I invoice some customers only
monthly so the more important relationship is between customer and
line-item: the Invoice is filled in later when the invoice is generated.

No, you shouldn't store the InvAmt in two places. You can always derive
the Invoice total from the line-items.

Phil