Query for total payment of each order number

[b-ballstyle]

I have a table, Cheque Orders Details that has only the basic information (Contract Number, Supplier Name, etc) of the order and with 3 one to many relationship. The 3 relationships are the tables for each production centers, Cheque Orders 0088, Cheque Orders 0091 and Cheque Orders 0097. So for each order, we add the actual order info (Type, Quantity, Cost/M, Date of delivery) in these tables.

For example, for Cheque Orders 0088, we have the Quantity and Cost/M field and to calculate the Payment of the order you do it like that :

[Cheque Orders 0088].[Quantity Received]*[Cheque Orders 0088].[Cost/M]/1000

Im trying to do a query that would calculate all the payments in each order in each of the 3 tables for each Main order details to have a big total of the payment.

So if we have these payments for the order 001 :
Cheque Orders 0088 = 1$
Cheque Orders 0088 = 1$
Cheque Orders 0091 = 1$
Cheque Orders 0097 = 1$

The big total for the order 001 would be 4$

 

[b-ballstyle]

This is what I have at the moment but it calculates only the first record of each order

So for
Cheque Orders 0088 = 1$
Cheque Orders 0088 = 1$
Cheque Orders 0091 = 1$
Cheque Orders 0097 = 1$

The big total for the order 001 would be 3$

 

[plog]

Anytime you have tables with the exact same fields, you haven't structured your database properly. Another sign is when a table name holds specific information about the data it contains.

Cheque Orders 0088, Cheque Orders 0091 and Cheque Orders 0097 shouldn't exist independently. All that data should be in the same table. Call this table 'ChequeOrders' (spaces in table and field names should be avoided) and add a new field called ChequeOrderNumber in which you will put '0088', '0091' or '0097'.

Do this and the query you want becomes simple.

 

[b-ballstyle]

Yes, but the reason i'm doing it that way is because I want my form to look like this :

And Im not sure if you can do 3 subforms from one table with a filter on each for the CenterNumber?

 

[plog]

You don't paint the walls before the foundation is laid. Proper table structure drives everything else. Even with that said, your form is still achievable with the data structure I proposed.

From a user's perspective, I have no idea why you have 3 sub-sections on that form--that's a bad user interface. From a person who knows you have 3 Cheque Orders tables and their structure, I assume each section ties back to a specific Cheque Order table.

If that assumption is correct, you can still achieve your layout. However, what's wrong with one sub-form that has a drop down that allows the user to enter what ChequeOrderNumber (0088, 0091, 0097) they want to use for each record of the subform? Even with your current form they implicitly have to choose which ChequeOrderNumber.


Also, looking at your form and table structure the fields 'Total Payment' and 'Total Frieght' should be eliminated. You don't need to store calculated data--these values can easily be determined by other data. It seems you did this with the field on the form called '5%'--you calculated it but didn't store it. That's how totals should work as well.

 

[b-ballstyle]

The only reason I designed it this way is because I'm doing a conversion from excel for people that were using this method for a long time... But we can always change it if I find a better way.

Also, yes thats what I this with 5%, Total Payment and Total freight on the form. Its calculated directly on the form. The Total Payment and Total freight in the tables was just some testing and I know I need to remove that.

But the reason I want to store it or calculate it with a query is because I need that Total Payment on another form were the user enter up the order number, and I want the total Payment to appear when they enter that.