Combining Records with Sum

[ano]

i have his example copied. and with simple 1 query got his result, with comment that sales regio should be in the group by.


i not under stand why u need help query it only slow down and more difficult to understand and explain and yes a better way is to splits up the table with a reference for vendor and for adress and for sales region. but the query is the same just joins for the text fields

[mike60smart]

There is something wrong with your calculations.

For Vendor ABC there are two Regional Totals:

1. the Total for Sales region West = 13,505.00
2. the Total for Sales region East = 7,146.00

For Vendor DEF there are also two Regional Totals:

1. the Total for Sales region West = 3,920.00
2. the Total for Sales region South = 14,127.00

It would also help if you showed us a screenshot of your actual relationship window.

[bill1234]

Did the question change while I was away?

I answered your original question. Maybe you need to explain it better with an example of what you're starting with and what your expected results are.

Hi, no. Sorry for so much confusion, everyone. I don't know much about Access, so I was searching for what was quickest/easiest for me. Madpiet, I was able to use your solution, but I did have to add DISTINCTROW to the second query to make it work for my needs.

Thanks!

[madpiet]

If you look at the data, the Vendor isn't always in the same sales region (ABC is in both East and West, DEF is in South and West). If you summarize by Vendor and region together, you get two records for ABC and two for DEF. But your total that was in your data was a total for the Vendor, not broken down by Region.

[madpiet]

good to know! thanks for the feedback.

post back if you need more help.

[ano]

There is something wrong with your calculations.

For Vendor ABC there are two Regional Totals:

1. the Total for Sales region West = 13,505.00
2. the Total for Sales region East = 7,146.00

For Vendor DEF there are also two Regional Totals:

1. the Total for Sales region West = 3,920.00
2. the Total for Sales region South = 14,127.00

It would also help if you showed us a screenshot of your actual relationship window.

i agree i tolo already here u have the test database i created with screen shots