Database Design - Physical / Postal Address Issue

[PeterPeterson]

I am developing an access database to manage students attending training.
In the table tblStudents, I need to capture the students:

• Physical Address (i.e. where they live)
  
  • address (text);
  • suburb (lookup to suburbID in tblSuburb); and
  
  
• Postal Address (if different)
  
  • postal address (text)
  • postal suburb (lookup to suburbID in tblSuburb)

NOTE: other relevent info e.g. name etc is also collected.

I have another table, tblSuburb which includes:

• suburbID (auto)
• suburb (text)
• state (text)
• post code (text)

I have not normalised the database to have "state" in a seperate table as 99% of students will come from one state.

Some students live in one suburb and their postal address (e.g. PO Box 123) is in a different suburb.

I have established a relationship between:
tblSuburb -> suburbID (one) to tblStudents -> suburb (many);and
tblSuburb -> suburbID (one) to tblStudents -> postalSuburb (many)

Data in the tblStudents looks fine whereby I have 5 students, one of which has a different physical address & suburb to their postal address & suburb.

When I run a query on tblStudents and tblSuburbs showing:

• firstName;
• surname;
• address;
• suburb;
• state;
• postcode;

only 4 student records show - the student with different suburb for their physical versus postal address does not appear.

I would be most grateful if someone could guide me towards where my error is.

Thanking you
Peter

[orange]

It may just be me, but you seem to be focused on suburb. Perhaps you could give us a couple of examples of the issue you are having with real data.

In systems I have worked with where several addresses and address types were involved, we used Address and AddressType tables. I don't recall ever using "suburb".

AddressType included things such as

-Mailing
-Shipping
-Receiving
-PayTo
-PhysicalLocation

Here is info on USPS Addressing standards
http://pe.usps.gov/cpim/ftp/pubs/pub28/pub28.pdf

As for your query, it would be helpful if you would post the SQL for the query.

[PeterPeterson]

Orange,
Thank you for replying and sorry it has taken me so long respond. i have been away from my computer working on the road.
My issue is not with "suburb", that was just the example of where I was having truble.
I believe I have overcome the issue caused by my creating a realtionship with full integrity where both tables where equal.
I have changed this to include all records from tblStudents and only those records from tblSuburbs where the joined fields are equal.
Once again thanks for replying.
Regards
Peter

[orange]

You are welcome - good luck with your project