sum first before calculation (1 Viewer)

[luzz]

Hi all, just a brief understanding of my database, I have multiple lines of colours, pounds qty and yards qty for the same fabrication. I have a button which will calculate the gross weight, so in order to calculate the gross weight, I will need to calculate it line by line. I have zero issue with the calculation part. After I calculate, I will need to generate a report, this where the issue surface.

The issue I am facing is when I do a combine PO, for example, if the first PO blue color has very small qty, then I will combine it with the second PO blue color. The total sum of the pound qty and yards qty is correct when it is displayed in the report. The only issue is that the gross weight is wrong when I calculate the gross weight using the total pound qty in the report. I believe this is due to the gross weight being calculated line by line.

Hence, I want to seek help from the expert here in solving this issue.

PS: I have tried grouping the same color together, but this can be done if the multiple lines of color belong to the same PO number.

 

[MajP]

There is not a lot of detail here how you are calculating. Is this in a query, at the report level?
I am going to take a guess, because it is a common error trying to sum a calculated control (if that is what you are doing). You cannot sum([nameofcalculatedControl]) you need to sum([orignal] * [calcualtion])
see discussion
http://www.jegsworks.com/lessons/databases/formsreports/step-totals-calculated.htm
Just guessing. A screen shot would help.

 

[luzz]

There is not a lot of detail here how you are calculating. Is this in a query, at the report level?
I am going to take a guess, because it is a common error trying to sum a calculated control (if that is what you are doing). You cannot sum([nameofcalculatedControl]) you need to sum([orignal] * [calcualtion])
see discussion
http://www.jegsworks.com/lessons/databases/formsreports/step-totals-calculated.htm
Just guessing. A screen shot would help.

I only need to know how to sum up the the pound qty for the same color even tho the colour belong to different PO NO. After summing up the pound qty then i will calculate the grossweight. I only need the linkage to the summing the total pound qty before calculting it

 

[Pat Hartman]

You can't have two separate aggregations within a single query or within the same report. You will need a report with two subreports. One to aggregate by line item and the other to aggregate by PO.

 

[luzz]

You can't have two separate aggregations within a single query or within the same report. You will need a report with two subreports. One to aggregate by line item and the other to aggregate by PO.

hmm, that's sound a bit complicated. Is there any simpler way where i can like maybe tick the textbox and it will store the value somewhere and it will automatically sum up the value in the textbox that is tick?

 

[Minty]

It's not complicated, unfortunately it's a simple fact of the way that grouping data works.

If you group by data in field A, but want to see a total of things grouped by data in field B you can't do it on one fell swoop. It's simply not mathematically possible in one go.

 

[Pat Hartman]

Are you old enough to remember the library when it actually had a physical card catalog instead of computer stations to search for books? There were three standard indexes. By title, by author, by subject. How would that be possible if you tried to put the groupings all on one cabinet? If you need to look at data from two different perspectives, you need two different forms/reports/queries. If the aggregated data is small enough that it makes sense to view it together, you use a form or report as a container and then one or more subforms/subreports to display each set of data.

 

[plog]

Can you demonstrate your issue with data? Provide 2 sets:

A. starting data from your database. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what data you hope to end up with when you feed in the A data.

 

[luzz]

Can you demonstrate your issue with data? Provide 2 sets:

A. starting data from your database. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what data you hope to end up with when you feed in the A data.

Hi there! I have attached my database.

B:
As you can see from my database that i have 4 dataset, which consist of two PO number with two different colour each. What i want to acheive is that since PO 1234 and 1235 have the same color with diff our qty and supplier qty, i want to combine the color together. Which i am able to do so, however, i want it to calculate in the form like for example; i combine PO 1234 and 1234 color White together, i want the form to be able to sum the our qty and supplier as one before i calculate the grossweight and loss. Is this possible?

My issue now is that because calculation are based on line by line, when i do preview the report the total supplier qty is correct but my grossweight is wrong. Because my grossweight have to total nettweight of both PO 1234 and 1235 to come up with a grossweight.

The correct grossweight for the combine black and white should be :219.2 and 153.2 respectively.

If i do the grossweight calculation based on line by line, and then i sum the total grossweight in the report it will show black : 233 and white: 181
which might cause difference in the loss later on.

 

[plog]

I don't want a walk through, I want data. Just data. So please try again.

A. Starting sample data. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me the data you hope to end up with.

You can post a database for A, but only include what I need. I see reports, forms a few modules and some queries in there. Don't give me anything I don't need.

For B, again, I just want data. No words, just data

 

[luzz]

I don't want a walk through, I want data. Just data. So please try again.

A. Starting sample data. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show me the data you hope to end up with.

You can post a database for A, but only include what I need. I see reports, forms a few modules and some queries in there. Don't give me anything I don't need.

For B, again, I just want data. No words, just data

I have include everything that you need in the database.

For B: the image that i attached, i want too combine the same colour together and sum up the nettweight and yards, but not the grossweight. The grossweight have to be calculated after summing up the nettweight

 

[plog]

No where in the data from A (the database you posted) do you have the color values which are present in B (the image you just posted). It is not possible to generate your B from the A data you supplied.

Last chance. 2 sets of data, starting and ending. B must jive with A.

 

[luzz]

No where in the data from A (the database you posted) do you have the color values which are present in B (the image you just posted). It is not possible to generate your B from the A data you supplied.

Last chance. 2 sets of data, starting and ending. B must jive with A.

Okay, i have attach.

 

[plog]

Looking at the starting data and your expected results, you simply want an aggregate query:

https://support.office.com/en-gb/ar...-a-query-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a

To get you started here is the SQL to get the total gross weight by color:

SELECT Colour, SUM(GrossWeight) AS ColorWeight
FROM MaximMainTable
GROUP BY Colour

To add in other totals by colour you simply add fields like the ColorWeight one I have. Then when you want to total the GrossWeight of all Colours, you do that in a report. You add a footer section and then sum all the ColorWeight values there.

 

[luzz]

Looking at the starting data and your expected results, you simply want an aggregate query:

https://support.office.com/en-gb/ar...-a-query-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a

To get you started here is the SQL to get the total gross weight by color:

SELECT Colour, SUM(GrossWeight) AS ColorWeight
FROM MaximMainTable
GROUP BY Colour

To add in other totals by colour you simply add fields like the ColorWeight one I have. Then when you want to total the GrossWeight of all Colours, you do that in a report. You add a footer section and then sum all the ColorWeight values there.

Hmm, i want to sum the lbs for the same color first before calculating the grossweight.

 

[plog]

I see nothing labeled 'lbs' on the expected output. What field on the expected output are you refering to?

 

[luzz]

I see nothing labeled 'lbs' on the expected output. What field on the expected output are you refering to?

I need to solve the issue whereby I am able, to sum up, the 'lbs'' for the same colour on my form first before calculating the gross weight.

If I am able, to sum up, the 'lbs' for the same colour then my issue is solved.

 

[plog]

If I am able, to sum up, the 'lbs' for the same colour then my issue is solved.

Then most likely a DSUM(https://www.techonthenet.com/access/functions/domain/dsum.php) is what you want.

 

[luzz]

Then most likely a DSUM(https://www.techonthenet.com/access/functions/domain/dsum.php) is what you want.

Hmm, so i need to put the dsum in my query? or on my form?

If i using dsum means i will need to have two criteria.
The first criteria is PO number
The second criteria is colour

meaning, i need to dsum it by the same PO number with the same color

 

[plog]

The dsum would go on the form. The link I posted demonstrates how to implement criteria with it.