Adding fields together

[joeatact]

Question on improving of Adding fields together

Is there a more effective way to add these fields together?

=nz([mbCost])+nz([videoCost])+nz([processorCost])+nz([MemCost])+nz([caseCost])+nz([modemCost])+nz([nicCost])+nz([cdrwCost])+nz([hdCost])+nz([floppyCost])+nz([kbCost])+nz([mouseCost])+nz([windowsCost])+nz([monitorCost])+nz([dvdCost])

Doing a running total this way is slow,


Joe

 

[Rich]

Yes, normalise your structure, at the moment you have a spreadsheet type layout within Access. One field for cost and another to describe the item is the correct way. Do some reading on normalize/normalise

 

[joeatact]

Re: Question on improving of Adding fields together

Is there a more effective way to add these fields together?

=nz([mbCost])+nz([videoCost])+nz([processorCost])+nz([MemCost])+nz([caseCost])+nz([modemCost])+nz([nicCost])+nz([cdrwCost])+nz([hdCost])+nz([floppyCost])+nz([kbCost])+nz([mouseCost])+nz([windowsCost])+nz([monitorCost])+nz([dvdCost])

Doing a running total this way is slow,


Joe

Sorry,

I have a table that stores quotes. All information is derived from other tables that store the inventory. On this form is

Field1 Field2
------- --------
Item 1 $5.00 (field name is ItemCost1)
Item 2 $6.00 (field name is ItemCost2) and so forth
etc
etc
Item 15 $6.00

The above meothad is the way I have been adding all the items up. I know there must be a better way else I am missing something. Thanks for your help.

 

[joeatact]

The costs are a set of data that is commonly referred to as a repeating group. Costs have a many-to-1 relationship with your main table. You have created a spreadsheet type table rather than a properly normalized one. You need a second table with a structure something like:

tblCost:
CostID (autonumber pk)
ForeignKeyID (foreign key to your main table)
CostTypeID (foreign key to the lookup table for cost types)
Amount
CostDate

Using this structure you would use the Sum() function to aggregate the amounts.

Select ForeignKeyID, Sum(Amount) As SumOfAmount
From YourTable
Group by ForeignKeyID;

Your existint structure requires that you add a new column to a table if there is a new cost type that you want to track. Adding a new column requires changes to forms/reports/queries and possibly subs and functions. Using the proper structure, the addition of a new cost type would entail adding a new row to the CostType table.

------------------------------------------------------------------------------
Thank You I understand a bit better now

Joe

 

[joeatact]

I am sorry to have to ask more dumb questions. No matter how much one reads there never seems to be enough examples. I have learned much about normalization, and I know I have a few more to work out with my db. I have not be able to figure out how to bring all the itemcosts that I have been storing in my db to the tblCost as suggested above. CostTypeID is where I am drawing a blank. I have attached copy of my db for any suggestions.

 

[joeatact]

Forgot to attach file above..This file contains my main table with the information mentioned above

 

[joeatact]

I noticed that you mentioned make a new table. My main table now holds all the totals for a quote. including the 15 items that were quoted. I saved the total cost for each of the items because the prices would be locked at that price. Shouldn't I use the data from the table instead of pulling it from the orginal table it came from? I just need to show the total of all the amounts on the form and report. I have tried using a quiery to total which also works.
Sorry for my ignorance