CODEWITHSUNDEEP
ms-access
DoubleJ
DoubleJ

Reputation: 15

Query Across Multiple Tables to Join Data

I have multiple access tables with a field called Attribute and a field called Source. I am wanting to run a query that lists all attributes across all access tables and also list each Source that the particular attribute shows up. I am assuming this is two separate queries. I am at a loss of how to write this particular query.

Table1
Attribute | Source
Mem | Mem
Mem.Address | Mem
Mem.Last | Mem
Mem.First | Mem

Table2
Attribute | Source
Mem | Dep
Dep.Address | Dep
Dep.Last | Dep
Dep.First | Dep

Table3
Attribute | Source
Prov | Prov
Prov.Address | Prov
Prov.Last | Prov
Prov.First | Prov
Mem | Prov

ExpectedResultsQuery
Attribute | Source
Mem | Mem, Dep & Prov
Mem.Address | Mem
Mem.Last | Mem
Mem.First | Mem
Dep.Address | Dep
Dep.Last | Dep
Dep.First | Dep
Prov | Prov
Prov.Address | Prov
Prov.Last | Prov
Prov.First | Prov

Upvotes: 0

Views: 34

Answers (1)

June7
June7

Reputation: 21370

A UNION query can combine the tables for first stage.

SELECT Attribute, Source, "T1" AS Src FROM Table1
UNION SELECT Attribute, Source, "T2" FROM Table2
UNION SELECT Attribute, Source, "T3" FROM Table3

Second query could use first query as source for a VBA function that will concatenate Source values to each Attribute. One such function at http://allenbrowne.com/func-concat.html.

MEM appears to be only Attribute with multiple Source. VBA function is a lot of processing for such minimal result and can perform slowly on large dataset. Could instead just build a report that groups by Attribute.

Alternative to concatenating Source values to single string is a CROSSTAB query using UNION query as source:

TRANSFORM First(TableUNION.Source) AS FirstOfSource
SELECT TableUNION.Attribute
FROM TableUNION
GROUP BY TableUNION.Attribute
PIVOT DCount("*","TableUNION","Attribute='" & [Attribute] & "' AND  Source<'" & [Source] & "'")+1;

Upvotes: 1

Related Questions