Confusion over join query

[tezread]

Hi!

I have three tables

tblaudit - has 1600 records - one field has postcode

tblpostcodes - has 230000 records - one field is postcode, another is lsoa

tlbdeprivation - has 32000 records - one field is lsoa, another is score

What I am trying to do is run a query that selects the postcodes from tblaudit - and matches them up with postcodes and respective lsoa from tblaudit - then to match that result up with the lsoa and score found in tlbdeprivation.

Not sure how to set up relations or run query on this guys

 

[vbaInet]

Have you tried using the Query Wizard?

If your PKs and FKs are setup correctly, then it should be a breeze.

 

[tezread]

I can do that but I am not sure what join type to use in relationships

 

[vbaInet]

They would all be Inner Joins which is the default join the wizard uses anyway.

 

[tezread]

Something aint right - I have

SELECT tblrehabaudit.postcode, tblpostcode.SOA1
FROM tblpostcode INNER JOIN tblrehabaudit ON tblpostcode.POSTCODE = tblrehabaudit.postcode;


as a query and I get 2 results

there should be 1609 results because that is how many records there are in tblrehabaudit

 

[tezread]

Just to verify my PK and FK

I have

tblrehabaudit with ref as a PK
tblpostcode with postcode as a PK

An inner join from postcode in tblrehabaudit to postcode in tblpostcode

 

[vbaInet]

Change that to a Left Join if you want to see the other fields. The Inner Join will only display records where the join fields match in both tables.

 

[tezread]

Change that to a Left Join if you want to see the other fields. The Inner Join will only display records where the join fields match in both tables.

I do need need inner join though as I am looking to join fields postcode fieds that are the same in tblrehabaudit and tblpostcode.

I only get 321 records returned though - there should be 1609

 

[vbaInet]

Just to let you know that it wouldn't match this "W1C 5F " with this "W1C 5F". Notice the space in the first one. So my thinking is that your postcodes may contain spaces in some postcodes.

 

[tezread]

Just to let you know that it wouldn't match this "W1C 5F " with this "W1C 5F". Notice the space in the first one. So my thinking is that your postcodes may contain spaces in some postcodes.

Hmmmm - in excel is a space deemed a character - maybe I could apply validation rules to the postcode data?

 

[vbaInet]

Correct! A space is a character.

You should have had referential integrity enforced initially and you wouldn't have had this problem. You would have to "clean up" your data. Maybe an update to get rid of trailling spaces.