Table design

[Guest]

I am creating a table that will have these fields:
TransactionID
SupplierName
Total Amount
Accounting Code
Cost Center

My question/problem is that there will be more than one allocation for the
total amount. I have thought about creating various fields like Accounting
Code 1, Accounting Code 2, Cost Center 1, Cost Center 2, etc. each with its
allocation. I would have to determine which will be the maximum number of
allocation I would grant but this will also mean a waste of space since not
all transactions will have the maximum number I grant.

How can I go about this and how will the form will support this (ie. create
boxes as more allocations are needed).

Thanks.

 

[Jeff Boyce]

Luis

As soon as you hear yourself saying "more than one", you aren't in Excel any
more! Yes, if you were still using Excel, you'd pretty much have to use
repeating fields.

But Access is a relational database, and you'll need to look into the topic
of normalization to get the best use of Access' features and functions.

I suspect you are describing a one-to-many relationship. This requires, at
a minimum, two tables, one for the one-side information, and one to keep
track of the many-side info.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks for your answer. I think I get the idea. One table would have the
total amount and the other will have the detail (hard to grow out of Excel).

Should I use a subform to accomodate this on a form?

Regards

 

[PSKelligan]

For your data input/editing Yes, a sub-form is the way to go. When I hear
the word totals I think of a calculated value. It is [usually] not the best
idea to store calculated values. in a database. If you have all of the
components available in existing records in the database, that is all you
should need to store. The calculated values can appear on forms and reports
without using any storage for the data.

Thanks for your answer. I think I get the idea. One table would have the
total amount and the other will have the detail (hard to grow out of Excel).

Should I use a subform to accomodate this on a form?

Regards

Luis

[quoted text clipped - 35 lines]

 

[Guest]

Thanks. Total means total amount, not a calculated field.

For your data input/editing Yes, a sub-form is the way to go. When I hear
the word totals I think of a calculated value. It is [usually] not the best
idea to store calculated values. in a database. If you have all of the
components available in existing records in the database, that is all you
should need to store. The calculated values can appear on forms and reports
without using any storage for the data.

Thanks for your answer. I think I get the idea. One table would have the
total amount and the other will have the detail (hard to grow out of Excel).

Should I use a subform to accomodate this on a form?

Regards

Luis

[quoted text clipped - 35 lines]

Thanks.

 

[Bob Quintal]

Thanks. Total means total amount, not a calculated field.

Total amount should be a calculated field, the total of the
detail amounts in the subform.

For your data input/editing Yes, a sub-form is the way to go.
When I hear the word totals I think of a calculated value.
It is [usually] not the best idea to store calculated values.
in a database. If you have all of the components available
in existing records in the database, that is all you should
need to store. The calculated values can appear on forms and
reports without using any storage for the data.

Thanks for your answer. I think I get the idea. One table
would have the total amount and the other will have the
detail (hard to grow out of Excel).

Should I use a subform to accomodate this on a form?

Regards

Luis

[quoted text clipped - 35 lines]

Thanks.