Unmatched Addresses Query

[#1Newbie]

Hello, I am trying to compare mailing addresses between two tables.
Table 1: Products Shipped (Contains Product #'s and addresses they were shipped to)


Table 2: Correct Address (Contains all Product #'s and their correct mailing address)

Q1. Is this the best approach?:
Query table 1 to concatenate Product number, street #, city, & state
Query table 2 to concatenate the same as above.
Create a query that joins table 1 & 2 by the concatenated columns & change the join to show all table 1 records and only records from table 2 that match
Query the results to show all fields where the concatenated address column from table 2 is blank ( = " ") (For some reason is null doesn't work)

Q2.If the above is the best approach, why do I receive more records in the results then what is listed in table 1? I need the results to match the total of items in table 1. (There currently isn't a primary key set up & I haven't grasped that concept yet; if that is the cause of all the extra records pulling in, can you please explain which columns should be the primary key & why?

Q3. I tried the unmatched query, but as stated above, no results generate because "is null" doesn't work. Is that because it's reading the conc formula even though the cell is blank? (Selecting = " " works)

Thank you for taking the time to read and respond!!!

[rzw0wr]

I would suggest that you read a few articles on table normalization before going any further.

Sounds like your tables are not normalized at all.

Dale

[alansidman]

This link may be helpful in understanding RDMS structure and table normalization

http://www.deeptraining.com/litwin/d...aseDesign.aspx

[orange]

It isn't clear to me what exactly you have and what you are trying to do. As Dale and Alan have suggested, some reading/research with concepts and Normalization may be a good starting place.

In general terms, you attempt to get "authoritative tables" and use those for references (the values are considered correct). For example Addresses - You have a table of Customers with their Addresses. When you get a new Order from aCustomer and you want to ship a product to them, you refer to their latest authoritative Address. If during the ordering process, you are informed that their Address has changed, then you update your address table. But you start with the latest authoritative record for that Customer. You will be in constant confusion mode if you have a ShippedTo Address and continue to attempt a reconciliation with the "Correct Address".

Here's a link to a tutorial that may help
http://www.rogersaccesslibrary.com/T...lationship.zip