Can I join 2 MS Access tables via an intermediary table?

[Clariman]

I'm building a series of MS Access tables to do some analysis. However, before I go much further I'd like to check whether I can do what I'm planning. I have 2 main tables but they will not share a key.

TABLE1
Field1 - Unique Key
Field2
FIeld3
Field4

TABLE2
FieldA - Unique Key
FieldB
FieldC
FieldD

I plan to join them via a 3rd table as shown below. Is this possible in MS Access?

TABLE3
Field1 (to join with Table1)
FieldA (to join with Table2)
FieldX
FieldY

Table 2 will have multiple rows with the same Field1 value. I might want to do a query that shows fields from all 3 tables.

Thanks
C

[pochisoldi]

Create a select query and coerce it into giving you the data you want. (this is the hard bit).
If you don't know what an "inner join" and an "outer join" are, then a google for some tutorials would be a good investment of your time.

First create a query which creates a full data set containing everything from table A and table B.
Then add conditions (if required) to select from that full data set.

Once you have a select query doing what you want, change the query type to "Make Table".

All this assumes that you are doing "ad-hoc"/on the fly analysis rather than creating some kind of application.

[Alaric]

If you don't know what an "inner join" and an "outer join" are, then a google for some tutorials would be a good investment of your time.

From what I recall of sql from many years ago, the idea that you "union" two tables can also be a useful one.

If you are the system "designer", making sure there are shared keys can save a lot of later trouble.

[Clariman]

Thanks. Maybe it would help if I described a bit more about my application and use. There are ancient artefacts that contain a unique set of symbols. I want to do detailed analysis looking at what symbols appear in what combination on which types of artefact and in what geography. I also want to be able to analyse the position of the symbols by artefact etc.

Table 1 (Symbols) contains

Symbol-ID (unique key)
Symbol Category
Symbol Sub-Category
Many other fields which are more for reference and unlikely to be used in queries

Table 2 (Artefacts) contains

Artefact ID (unique key)
Artefact Type (e.g. Stone Class I, Stone Class 2, Jewellery etc.)
Location data
Dimensions
Condition (if a fragment, I may exclude it from my analysis)

Table 3 (Artefact Contents)

Artefact ID (non unique) - link to Artefact Table
Symbol ID (non unique) - link to Symbol Table i.e. what symbols appear on this artefact
Alternative Symbol ID (e.g. if there are alternative interpretations of some similar designs)

(Note. There will be a row for each symbol appearing on each artefact. There may be up to 8 symbols per artefact)

Possible Table 4 or may incorporate into Table 3 (Symbol Arrangement)
This will record how the symbols are grouped e.g. in pairs with A above B, or C beside D etc.

It's getting messy!

[pochisoldi]

Thanks. Maybe it would help if I described a bit more about my application and use. There are ancient artefacts that contain a unique set of symbols. I want to do detailed analysis looking at what symbols appear in what combination on which types of artefact and in what geography. I also want to be able to analyse the position of the symbols by artefact etc.

Table 1 (Symbols) contains

Symbol-ID (unique key)
Symbol Category
Symbol Sub-Category
Many other fields which are more for reference and unlikely to be used in queries

Table 2 (Artefacts) contains

Artefact ID (unique key)
Artefact Type (e.g. Stone Class I, Stone Class 2, Jewellery etc.)
Location data
Dimensions
Condition (if a fragment, I may exclude it from my analysis)

Table 3 (Artefact Contents)

Artefact ID (non unique) - link to Artefact Table
Symbol ID (non unique) - link to Symbol Table i.e. what symbols appear on this artefact
Alternative Symbol ID (e.g. if there are alternative interpretations of some similar designs)

(Note. There will be a row for each symbol appearing on each artefact. There may be up to 8 symbols per artefact)

Possible Table 4 or may incorporate into Table 3 (Symbol Arrangement)
This will record how the symbols are grouped e.g. in pairs with A above B, or C beside D etc.

It's getting messy!

The answer to your original question is "Yes you can".

Have you created a relationship between the tables "ArtifactContents" and "Artifacts" and "ArtifactContents" and "Symbols"?
The relationship should be many (ArtifactContents) to one (the other table) - you need to tick the "Enforce referential integrity" box.
(the exact relationships are: ArtifactContents.ArtifactID (many) to Artifacts.ArtifactID (one) and ArtifactContents.SymbolID (many) to Symbols.SymbolID(one) )

Then you can do a query which contain all three tables, select ArtifactName and SymbolName and you'll get what's in ArtifactContents, but with meaningful names instead of key numbers. (this is as simple as creating a new query, selecting all three tables from the Show table window, then drag drop Artefact.ArtefactName and Symbols.SymbolName into the query and running it)

You might also want to set up a primary key on both the "ArtifactID+SymbolID" fields in ArtifactContents if you want this table to have unique entries (e.g. if a specific artefact has three specific instances of a given symbol, you only want one single entry).
(Remove any primary key, select both fields and then click Primary Key)

PochiSoldi

[modellingman]

I'm building a series of MS Access tables to do some analysis. However, before I go much further I'd like to check whether I can do what I'm planning. I have 2 main tables but they will not share a key.

TABLE1
Field1 - Unique Key
Field2
FIeld3
Field4

TABLE2
FieldA - Unique Key
FieldB
FieldC
FieldD

I plan to join them via a 3rd table as shown below. Is this possible in MS Access?

TABLE3
Field1 (to join with Table1)
FieldA (to join with Table2)
FieldX
FieldY

It is perfectly feasible In MS Access to join Table 3 to Table 1 on Field1 and simultaneously to join Table 3 to Table 2 on Field A. Access' design view makes constructing a Query to do this very straightforward, and you will be able to select the columns (fields) from all three tables that you want to appear in the output of the Query.

Table 2 will have multiple rows with the same Field1 value. I might want to do a query that shows fields from all 3 tables.

I had a bit of trouble understanding that statement (typo possibly?) but your subsequent post was helpful...

Thanks. Maybe it would help if I described a bit more about my application and use. There are ancient artefacts that contain a unique set of symbols. I want to do detailed analysis looking at what symbols appear in what combination on which types of artefact and in what geography. I also want to be able to analyse the position of the symbols by artefact etc.

Table 1 (Symbols) contains

Symbol-ID (unique key)
Symbol Category
Symbol Sub-Category
Many other fields which are more for reference and unlikely to be used in queries

Table 2 (Artefacts) contains

Artefact ID (unique key)
Artefact Type (e.g. Stone Class I, Stone Class 2, Jewellery etc.)
Location data
Dimensions
Condition (if a fragment, I may exclude it from my analysis)

Table 3 (Artefact Contents)

Artefact ID (non unique) - link to Artefact Table
Symbol ID (non unique) - link to Symbol Table i.e. what symbols appear on this artefact
Alternative Symbol ID (e.g. if there are alternative interpretations of some similar designs)

(Note. There will be a row for each symbol appearing on each artefact. There may be up to 8 symbols per artefact)

Presumably, Table 3 will have multiple rows containing the same Artefact ID (because each artefact may contain more than one symbol). If the same symbol type (as identified by its Symbol ID) occurs on different artefacts then Table 3 will also contain multiple rows with the same Symbol ID.

You don't say whether the combination of Artefact ID and Symbol ID is unique to each row in Table 3. This could be the case if each symbol type appeared at most once on each artefact but wouldn't necessarily be the case if a particular symbol can occur more than once on an artefact.

Apologies if I'm teaching granny to suck eggs here, but It is generally a good idea to make each row of a table unique some way. Uniqueness is helpful because it makes it easier to reconcile results of queries with the inputs - particularly counts of the numbers of rows that result from a query. Such reconciliations might be helpful if you are thinking in terms of even just basic descriptive statistics let alone the tests of significance and multivariate analyses that have been the subject of your previous posts.

A table can have several columns acting as its key, provided that the combination of values across those columns is unique to each row (if the data in a table doesn't conform to this requirement, Access flags this up either when defining the key columns or when data is added to the table).

The keys for Tables 1 and 2 provide this uniqueness quality for those tables. If the Artefact ID/Symbol ID values are unique in each row of Table 3 then that also takes care of Table 3. However, if a particular symbol can occur more than once on an artefact and you want to represent such multiple occurrences within Table 3 then you'll need to think through how you want to deal with that. There are 3 possibilities that occur to me

• you can ignore the uniqueness requirement (whilst a good idea, it is not essential, but non-uniqueness does make quality assurance of results more problematic)
• you could add a column to Table 3 indicating the number of occurrences of the symbol. This would mean you only needed to include one row for each Artefact ID/Symbol ID but the downside is you wouldn't be able to distinguish easily between the two (or more) occurrences of the same type of symbol on a particular artefact - such distinction may or may not be important to your analysis task
• you could add a column to Table 3 which allows each symbol to be distinguished on the artefact - this might be via a physical label on a photograph of the artefact, or some other means of distinguishing between symbols of the same type such as position, size or whatever is appropriate. In this case, whilst you would have multiple rows with the same Artefact ID/Symbol ID combination in Table 3 the combination of Artefact ID/Symbol ID/Distinguishing Characteristic would be unique.

Possible Table 4 or may incorporate into Table 3 (Symbol Arrangement)
This will record how the symbols are grouped e.g. in pairs with A above B, or C beside D etc.

Table 4 is really a new set of information and potentially creates an explosion of data. If an artefact has 8 symbols then potentially there are 28 pairs of symbols to deal with - 56 if you allow duplication via "Symbol 1 is above Symbol 2" and "Symbol 2 is below Symbol 1". To avoid this duplication you may want to think in terms of a further table which contains relationship types and only allow that table to contain "is above" rather than additionally having "is below" within it. Irrespective of this, one approach would be to make each row of Table 4 refer to 2 rows in Table 3 and the "relationship types" table.

Its getting messy!

Clarity will eventually prevail, just keep at it and keep your data backed up (Access can make it easy to mess up - and that always surprises people who are used to Excel's forgiving ways).

Good luck with compiling your data.

[genou]

Table 4 is really a new set of information and potentially creates an explosion of data. If an artefact has 8 symbols then potentially there are 28 pairs of symbols to deal with - 56 if you allow duplication via "Symbol 1 is above Symbol 2" and "Symbol 2 is below Symbol 1". To avoid this duplication you may want to think in terms of a further table which contains relationship types and only allow that table to contain "is above" rather than additionally having "is below" within it. Irrespective of this, one approach would be to make each row of Table 4 refer to 2 rows in Table 3 and the "relationship types" table.

Its getting messy!

I have not done this for a long time, so be sceptical.

You don't want "above" or "below" . For each symbol on an artefact you want a position_id, so that you can select on greater_than, less_than. So your Table three is artefact_id, symbol_id, position_id ( which is unique in combination ). Table 4 is symbol_id (unique ) plus alternative_symbol_id.

I think that covers the joins you have discussed.

[Clariman]

Thanks everyone. This is really helpful. To answer some of the questions (apologies for not 'quoting' the questions) ....

• Symbols usually occur only once per artefact but there are a handful of exceptions where they appear twice, so Table 3 needs to cater for the exception.
• I need to record relative positions so position could be used to make Table 3 have a unique key i.e. artefact-symbol-position would be unique.
• I don't think I'll have a 'data explosion' but point taken. There are only about 300 artefacts in total and most have 2 or 3 symbols on them.
• Recording the positions is going to be tricky - see comments below ....

Recording Symbol Positions
I want to record all symbols on a given artefact and to record how they are grouped in relation to each other. Again some background info may be useful. Scholars agree that symbols are usually paired, but sometimes appearing with a 3rd one, in a lesser position, which is thought to 'qualify' the pair. However, there are exceptions. One of the things I want to do is to prove/disprove the pairing (or pairing+1) hypothesis, because there are exceptions to this 'rule' e.g. sometimes they appear singly, sometimes in other combinations. These exceptions might be due to being on a different type of artefact or possibly a fragmentary or reused artefact.

I need to cater for an artefact having any of the following:

• Multiple pairs
• Groupings other than a pair (e.g. 3 together)
• Being on more than one side of an artefact
• Symbols positioned above and below each other
• Symbols positioned beside each other
• Being unable to identify clearly how they are grouped (N/A or unknown or maybe even alternative interpretations!)

One scholar recorded positioning using "/" to record above and below, and "=" to record being beside. I think this works quite well within a group, but need extended to cater for multiple groups. So I could have rows with, as an example, Row 1 Group1, A/B=C and Row 2 saying Group2, D/E/F where those letters are symbol IDs. While this is quite neat, I need to decide who I could encode that in a table for analysis.

Here are a few examples.

This is a fairly simple one https://canmore.org.uk/collection/1113054 which I might record as - Serpent/double-disc/mirror=comb

This is a more complex one https://outreach.mathstat.strath.ac.uk/ ... ?image=241 which has 4 pairs around the cross. These could be recorded as Groups 1 to 4 with a symbol above/below format. Note that the cross is not treated as a symbol for my purposes. This artefact is more complicated because the reverse side (not pictured) also has symbols and other graphic elements.

Thanks again
C