Summing Fields between tables

[marrone12]

So, for discussion's sake, let's say I have three tables. One table that is a list of countries, one table that lists the international spend per country, and one table that lists domestic spend in the united states.



The records in the spend tables have a country column that is lookup/relation linked back to the country table.

I want to run a query that will sum all the spend per country for both tables. I'm not sure exactly how to do this. When I run a query, it lists two columns, one for int'l spend and one for domestic spend.

One of the more complicated issues is that a certain type of domestic Spend is in the international table, so when I run the query through both tables, only the US spend is shown, with two columns, one for the spend in the int'l table, and one for the domestic table.

Is there anyway to consolidate everything I need into one query? I essentially imagine the list of countries in one column and a second column with all spends summed together.

Any ideas?

[alansidman]

I' not sure I understand what you are attempting. Let me take a guess. Do you want the results of the International Spend by Country added to the Domestic Spend by Country? If this is the case, then a simple expression in a new field in your query

Total_Spend: [InternationalSpend] + [DomesticSpend]

Join your tables on the Country Field

If this is not the case, perhaps you should post your db so that we can examine exactly what is happening.

Alan

[marrone12]

Here's essentially what's happening:

country table:
list of countries (country.country)

International table:
intl.country, intl.spend, and other fields.

domestic table:
domestic.country, domestic.spend, and other fields not in common w/intl table


when I do a query, I select the country field(country.country) then intl.spend and domestic.spend.

The query then only shows the united states, with the intl.spend and domestic.spend columns for the united states without showing all the other countries.

e.g.

USA, intl.$$$, domestic.$$$

If i do the sum query for just the international table, I get a list of every country and sum all the spends for every record,

e.g.

UAE, intl.$$$
AUS, intl.$$$
USA, intl.$$$

I want the final query to show everything in the international query, but just sum intl.$$$ and domestic.$$$ for the united states.

so essentially,

UAE, intl.$$$
AUS, intl.$$
USA, (domestic.$$$+intl.$$$)


I understand your idea of adding a new field that is totalspend=dom.spend+intl.spend, but how do i put together the proper table?

Should I just do a query for both domestic and international, and then requery the queries and add the sum field?

[alansidman]

Why don't you post your db to the forum so that we can see it and try to give a logical answer. You need not post all the data, but just some sample data. Then do a compact and repair to reduce the size before uploading.

I think it is a matter of joining the tables properly to get the expected results or creating a Union Query joining the two queries you have.

Also, I am running AC 2007, before you post, make your file compatible with AC2007 or earlier.

Alan

[marrone12]

So I've attached the database, and censored and shortened most of it. So in the first query, titled countries query, is the sum of all spend in the international markets. The second query is from the domestic table. The third query is my attempt to join both queries. Any help you could provide would be greatly appreciated!

[alansidman]

I am running AC 2007 and I can not open all your queries or tables. Are you able to save file as AC 2007 or earlier. There are some compatability issues.

Alan

[marrone12]

Alan, I appreciate all your help, but unfortunately it only gives me the option to save as access 2003 or earlier, and for whatever reason the DB will not export to that format.

Thanks again though.

[June7]

First, there is no reason to join to the Countries table in this query because you are linking on the country name, not the ID. This means the country name is saved in the spend tables and no other information is in the Countries table.

I think you will need a UNION query for this. Either UNION the two tables and then do an aggregate query or do aggregate query on each of the spend tables and UNION those.

There is no designer or wizard for UNION, must type in the SQL View editor of the query designer.

SELECT ID, Day, Country, Cost, Channel FROM International
UNION SELECT ID, Day, Countries, Cost, Channels1 FROM Domestic;

Think I would design these tables differently so this UNION would not be needed.

Why does International have records for United States?

Looks like you started to set up Relationships. The relationship for Partners and Channels is wrong.

[marrone12]

First, there is no reason to join to the Countries table in this query because you are linking on the country name, not the ID. This means the country name is saved in the spend tables and no other information is in the Countries table.

I think you will need a UNION query for this. Either UNION the two tables and then do an aggregate query or do aggregate query on each of the spend tables and UNION those.

There is no designer or wizard for UNION, must type in the SQL View editor of the query designer.

SELECT ID, Day, Country, Cost, Channel FROM International
UNION SELECT ID, Day, Countries, Cost, Channels1 FROM Domestic;

Think I would design these tables differently so this UNION would not be needed.

Why does International have records for United States?

Looks like you started to set up Relationships. The relationship for Partners and Channels is wrong.

It's not so much an international table as it is a certain type of spend, which we mostly do internationally, but also some domestically. We track different data in the international table than the domestic table which is why the tables are different. The only thing that both tables have in common is money being spent, and we would like to have a country by country break down of the money.

How would you change the relationship between partners and channels? I had to delete a lot of it, but the way it goes is that each partner generally has multiple channels, and we would want to track things by the macro partner level as well.

[June7]

The Relationship for Partners and Channels should be on the Partners ID key fields. You have the link hitting the wrong field in one of the tables, Channels I think, not in front of me now.

What is macro partner?

Do the queries work?