Calculations based columns in a entry form

[Jamescdawson]

Hello everyone.

Hope someone can point me in the right direction...

I've got a data entry form that is used to enter incoming waste consignment data. (I've attached a screen grab of the form) Each screen represents one record and the data is recorded in a table called "IncomingWaste". The top of the form (light blue) refers to the customer/producer of the waste. Most of that detail is held in another table on the db. The bottom (Dark Blue) part is used to record the consignment data. It gives the user a grid to enter the individual waste components within the consignment (choosing from Drop down menus for the various descriptive elements) Most crucially, the weight of each component is recorded.

I have a calculated field at the bottom which gives a Total weight for that consignment. It simply adds the weight fields together and delivers a total in the box at the bottom of the screen which displays the total weight in that consignment using "Nz([weight1])+Nz([weight2)]+ ...etc - which works fine on the form. However, I can't seem to get this calculated total recorded and appended to each record in the table and I assume it must be re-calculated each time the screen loads (?)



The main issues is.... I need to create a report which adds the total weights from each customer over a monthly/quarterly period and I simply cannot seem to do it.

I have created the report which groups all the consignments from each customer and lists them in date order, but I can't identify or define the method I need to use to collect that Total Weight calculation from each consignment record and add them up to give a grand total for that customer for a particular period. I'm assuming if I can crack the first part and get the total recorded in the table, then I can call the value into the report.

Can anyone help ?

[Bob Fitz]

(I've attached a screen grab of the form)

I can't see this.

I can't seem to get this calculated total recorded and appended to each record in the table

Nor should you. Calculations should normally be done when and where they are required, not stored in tables.

[Jamescdawson]

Bob,

Many thanks for the super fast response. Also, the first I've had from the UK.



>>> I can't see this.

Oops, I forgot to attach. I was working remotely on a company machine, so I'll have to get the file and post it in a few minutes..

>>>Nor should you. Calculations should normally be done when and where they are required, not stored in tables.[/QUOTE]

Can you give me a pointer on how to organise the query to report the totals according to the customer ?

Regards

=Jimbo=

[Bob Fitz]

Many thanks for the super fast response. Also, the first I've had from the UK.

Maybe the reputation of the English being "reserved" is deserved after all.

Can you give me a pointer on how to organise the query to report the totals according to the customer ?

I'm sure that there are others that could advise on this better than I can myself. However, If I am wrong, and you get no other help in this regard, I will do what I can.

[Jamescdawson]

Many Thanks

I'll await other responses. Thanks for taking the time.

regards

=Jimbo=

Maybe the reputation of the English being "reserved" is deserved after all.
I'm sure that there are others that could advise on this better than I can myself. However, If I am wrong, and you get no other help in this regard, I will do what I can.[/QUOTE]

[RayMilhon]

Unfortunately I don't know the structure of your Database but the best way to do this is create a query to get the data from the tables and then base your report on the query. for example you could group on the customer and do a sum of the weight fields. Your criteria would be the start and end dates of your time period. More detailed help could be provided if the structure of your tables were known.

[Jamescdawson]

Many Thanks Ray,

The structure of the table for the incoming consignments is simple and flat. Each record/screen has the entire record on a single row with each element recorded in a column starting with Description1, EWC1, Weight1 etc, then Description2, EWC2 Weight2 - all the way to 10. There are never more than 10 component part s to each consignment.

I'll try to construct a query that adds all the weights

Thanks again

=Jimbo=

Unfortunately I don't know the structure of your Database but the best way to do this is create a query to get the data from the tables and then base your report on the query. for example you could group on the customer and do a sum of the weight fields. Your criteria would be the start and end dates of your time period. More detailed help could be provided if the structure of your tables were known.

[RayMilhon]

If possible you might want to think about restructuring your DB. Flat File Tables in a Relational Environment Are at best a pain in the ass. (in my opinion.)

[Jamescdawson]

I'm still struggling with this problem. (See above & attachment) On a change of tack, can anyone suggest an on-line tutorial or place where I can learn a little more about using queries to add up the contents of fields and create totals based on specific groups. ??