Access Newbie - can't work this out.

[amthorpe2000]

I have 3 tables which are imported from Excel. Each table has many columns but 3 are consistent (team, month, value).

1st i want to get the distinct / unique combinations accross all 3 tables for the team and month, which i did with a union query (I think)

SELECT team, period
FROM Table3

UNION SELECT team, period
FROM Table1

UNION SELECT team, period
FROM Table2
ORDER BY team, period;

but then I want the sum of the value from each of the 3 tables against the unique combination. So each table may have 1000's of records, which i want to end up with the query results in the right hand red set in the image

Everything i try comes up with data mismatch or produces 100's more entries than the original.

Any help appreciated

 

[amthorpe2000]

Sorry couple of duplicate lines on the right (my bad) so the totals don't match in the pick but they should.

 

[Micron]

My guess would be that you'd use a Select query, joining 3 tables on Team (outer joins?), having 3 value fields (1 from each table) and then create a Totals Query and group by Team. The reason being that you want the totals query to transform the value fields by table. That's just a guess.
Or a totals query based on your Union query. However, I think your main problem is that you appear to have 3 identical tables when you should have only one. Methinks you've designed your tables as you would spreadsheets, which unfortunately is very common when Excel users migrate to Access. I'd suggest you research db normalization if you're not familiar with the concepts because if I'm right, your design will continue to frustrate you and in some cases it will present insurmountable problems.

 

[amthorpe2000]

My guess would be that you'd use a Select query, joining 3 tables on Team (outer joins?), having 3 value fields (1 from each table) and then create a Totals Query and group by Team. The reason being that you want the totals query to transform the value fields by table. That's just a guess.
Or a totals query based on your Union query. However, I think your main problem is that you appear to have 3 identical tables when you should have only one. Methinks you've designed your tables as you would spreadsheets, which unfortunately is very common when Excel users migrate to Access. I'd suggest you research db normalization if you're not familiar with the concepts because if I'm right, your design will continue to frustrate you and in some cases it will present insurmountable problems.

Maybe, the three tables are from spreadsheets of data that are for different snapshots over time, what i was hoping was that to avoid VBA in a particular spreadsheet which i could do that i could store the "heavy" data in a workbook, pull it into Access get access to sum it up and then import the resulting query into another workbook. I have done quite a lot of access years ago, but this is eluding me.

 

[Micron]

Lacking a complete understanding of the process I'm left to wonder why not just do the summing in Excel since doing complex calculations is what it's made for? If importing/linking within Access is necessary, a typical approach is to link the spreadsheets as tables then use queries to move the data to the properly designed table(s). One could use transferspreadsheet function as well, but sometimes that fudges the data types, so for example, you get text instead of numbers. That would also give you the option to overwrite or just append.

 

[stumac]

Change your union query to include value field too and use UNION ALL then use that query as source of a group query with it being grouped by team and period and sum of the value... Untested:

SELECT team, period, sum([value]) AS TotalValue
FROM

(SELECT team, period, [value] 
FROM Table3

UNION ALL SELECT team, period, [value] 
FROM Table1

UNION ALL SELECT team, period, [value] 
FROM Table2) MyUnion

GROUP BY
team, period;

I have assumed your field is called value, however this is a reserved word within Access and shouldn't be used for field names.

 

[stumac]

Sorry, missed the totals by table part out... Still untested.

SELECT team, period,
sum(iif(source = "T1", [value], 0) AS T1Total,
sum(iif(source = "T2", [value], 0) AS T2Total,
sum(iif(source = "T3", [value], 0) AS T3Total,
sum([value]) AS TotalValue

FROM

(SELECT team, period, [value], "T3" as source FROM Table3

UNION ALL SELECT team, period, [value], "T1" as Source FROM Table1

UNION ALL SELECT team, period, [value], "T2" as Source FROM Table2) MyUnion

GROUP BY team, period;