Getting a total of all 24 values in one record

[TheMegaSage]

I have a table with 28 fields. The first four are: Date, Product, Shift, Waste Type. The next 24 are unique 'waste points'. Each date/shift can have any number in any of those 24 fields. So yesterday (for example) we could have had 500 KG on waste point 10, 20 on waste point 16, and 400 on waste point 20.



I am going to code a form that will allow users to select against a range of the first four criteria (not a problem), and then show the resultant numbers as a percentage for each waste point. This will require me to total all 24 waste point fields in each record to be the denominator. So in the example above, I need an easy way to say the denominator is 920. Keeping in mind I can have anywhere from 0 to 24 fields that have a value in them and need to be added to the total.

Is there an easy way to do this? Or do I need to create a custom field that says something like: [point1]+[point2]+[point3]+[point4] ... up to 24? That would be a very long custom field.

Any help would be greatly appreciated.

[pbaldy]

In my opinion the design isn't normalized. The 24 fields should be records in a related table. Given your example, that table would have 3 records in it for that date. Then your solution is a simple Sum(). As it is, yes, you have to add them up one-by-one.

[TheMegaSage]

Thanks pbaldy; that is pretty much what I thought. I actually got around the problem by realizing I'm basically looking at a pareto here, so I created a form in Access that allows a date range to be chosen, and a Run button that updates the querydef of a specific query and copies the data to the system clipboard. I have an Excel file that I wrote earlier this year that auto generates a pareto chart, so I made a few modifications to that and bam, I have the user select the date range and click a button in Access, and then in Excel paste into a specific cell, click the pareto generator button, and BA bam, data sorted and a spiffy graph available.

Good times.

[pbaldy]

Glad you got it sorted out.