I have a table of cement shipping document,every shipping document may consists of different coupon number.every coupon number has a quantity and a material description.
I want to calculate the totals of quantities form different fields depending on the material description form different fields also.
ex: coupon no - 101,Material description - OPC,QNT - 20
coupon no - 102,Material description - SRC,QNT - 50
coupon no - 103,Material description - OPC,QNT - 50
coupon no - 104,Material description - SRC,QNT - 80
what i need is a fields to calculate the following.
so the OPC totals must be 70
and the SRC totals must be 130
and the shipment total qnt is 200
Your problem is actually that your tables are normalized incorrectly.
Contrary to your description, you actually have different fields for different material descriptions, and you save the quantity shipped in those differing fields.
You're not going to like my advice, but it's the only way to get a handle on this and save yourself from a HUGE amount of unnecessary work down the road:
Re-design the tables.
Some rules to keep in mind:
You should never, ever, under any circumstances store data in the field names.
A table should be about one 'thing', and every field in the table should directly relate to that 'thing' and only that 'thing'
You should never use forms that don't allow for a way to close them. (Seriously, no X button, no close button, and it's full-screen? Not cool, man.)
Never use spaces in table or field names. It makes building the application a nightmare due to extra grammar rules involved, and can actually cause weird interactions in the code on certain rare occasions. Use either CamelCase or Words_With_Underscores.
Never use sequentially-numbered fields (address line 1 and line 2 are an exception, but that's because it's actually a standard format). If you need to use them, then they need to be a different table.
Basically, your coupon information needs to be split out from [Bags Pickups Shipments], and your [Pick Up] table needs to be split into something like Orders and Line Items.
For [Pick Up], you can keep [SAP ID], [Company Name], [Area], and [Manaul ID] in the main table (although normally you'd have a companies or clients table and just insert their ID instead of their name), and would create a LineItems table that included LineItemID, SAPID, MaterialType, and QtyOrdered. (SAPID would be a foreign key that you then relate back to SAPID in [Pick Up].
***
Now if you don't want to do all this - and I'm not going to lie, it's going to be a LOT of work - then you can technically write queries that sum the values of fields (using the Nz function to return 0 when a field is null), but be aware that if you EVER add more materials types, then EVERY SINGLE REFERENCE TO THOSE MATERIALS TYPES THROUGHOUT THE ENTIRE APPLICATION WILL NEED TO BE UPDATED. That said, the format would be something like
Code:
QtyOrdered: Nz([OPC],0) + Nz([SRC],0)
added into a query.
Total summary depends on where you want to do it. On a form or report, it's easiest to just put a text box with Sum([QtyOrdered]) in the form footer or the report/group footer.
Remember, however, that taking this route means you're pushing the resolution of the real problem into the future, and until you fix it, you're going to continue making life harder for yourself every single solitary time you touch these tables for any reason.
The description is confusing but I don't see a normalization issue. There are two ways to approach this. One is normalized and one is not. The normalized way uses two queries. One to sum the values by material description and a second to sum the whole thing and the other is to create a single query where you hard-code the material descriptions. I'll go with door #1
Select MaterialDesc, Sum(QNT) As QtySum
From your table
Group By Material Desc;
I think you also need another grouping column. Perhaps date, perhaps shipped to. The query as I wrote it will sum everything in the table and I doubt that is what you want.
The second query sums the first query
Select Sum(QtySum) as TotalQtySum
From query1;
Based on his description, there's no normalization issue. Looking at the tables, however, there absolutely is one. His example bears absolutely no resemblance to reality.
There is no Materials Description field; the fields are literally SRC and OPC, and they are BOTH quantity fields. That's not even getting into the other table having identical column series for Coupon1, Coupon2, and Coupon3.
That'll teach me to trust the written word. You are absolutely correct Frothingslosh. The tables need to be restructured before moving on. The query I suggested is not possible given the table layout.
