Query using a table linked to 2 others

[dave_p]

Hi I have a problem trying to build a query where I have 2 different foreign keys pointing to one primary key, Im not sure if a CTE in SQL would be the way forward to deal with this as my SQL isnt that great:

tbl_site:
SiteKey (P)
SiteDescription
AddressKey (F)

tbl_school:
SchoolKey (P)
SchoolName
SiteKey
AddressKey (F)

tbl_Address:
AddressKey (P)
Add1
.
.
Add6
PostCode

I am trying to return a query that shows:

SiteKey (All records)


SiteName
SchoolKey (schools are joined to sites on a zero to many relationship)
SchoolName
Add1
.
.
Add6
PostCode

My Issue is that where there is a school against a site the address is held at school level, but where there is no school the address is held at site level. I can only ever seem to get one level of address returned no matter which way I try and write the query. All other ideas I have tried all lead to a duplication of the 6 address and postcode columns.

Any have any ideas how to get round this?

[pbaldy]

Same answer as here:

https://www.accessforums.net/queries...eld-11933.html

[dave_p]

Thanks for the answer although I am still encoutering the same problem, unless I duplicate the columns to show the fields required in the query from the alias table "Address_1" it still only returns the values in the table "Address".

(Not sure if this is because the alias table is linked to a different table rather than different fields within the same table?)

[pbaldy]

Can you post the db, or at least the SQL of your query?

[dave_p]

Hi, thanks Paul, will post the DB on Monday when have access to it again

[dave_p]

Please find attached copy of DB:

you will see 2 queries:
address at building level - this highlights the problem that I can only work out how to use the address table against one other table at a time, at current all of the addresses for "schools" show up, but those held at a higher level (site) do not show up (where school key is null in returned data)
address at site level - shows the address matching for where there is no join to the school table

I am essentially trying to get the data from the address columns on the site level query to show up alongside the data already returned in the building level query (ie match addresses from both school and site tables at once in one set of columns)

[pbaldy]

Is this what you're after?

SELECT School.UPRN AS [Code/UPRN], Site.SiteKey, Site.Description AS [Site Name], School.SchoolKey, School.SchoolName AS [Building Name], Address.Address1, Address.Address2, Address.Address3, Address.Address4, Address.Address5, Address.Address6, Address.PostCode, SiteAddress.Address1 AS Site1Add, SiteAddress.Address2 AS Site2Add, SiteAddress.Address3 AS Site3Add, SiteAddress.Address4 AS Site4Add, SiteAddress.Address5 AS Site5Add, SiteAddress.PostCode AS SitePostCode, SiteAddress.Address6 AS Site6Add
FROM ((Address RIGHT JOIN School ON Address.AddressKey = School.AddressKey) RIGHT JOIN Site ON School.SiteKey = Site.SiteKey) LEFT JOIN Address AS SiteAddress ON Site.AddressKey = SiteAddress.AddressKey;

[dave_p]

Hi Paul, that does return the type of data Im looking for but it duplicates the address columns (what I was trying to avoid.) I have since been informed that the use of an IIF statement to choose which of the 2 address tables the query calls (original or aliased) should help:

IIf([Address_1].[Address1] Is Null,[Address].[Address1],[Address_1].[Address1])

Thanks for taking the time to help with this though

[pbaldy]

Sorry, I'm guilty of not reading carefully enough.

Glad you got it sorted out.