Non matching address query (1 Viewer)

[#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)

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?

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

 

[rzw0wr]

http://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters

I take it you did not care for my answer on the other forum.
It is still the same.
Your table are not correct and need to be normalized.

Dale

 

[#1newbie]

Hello Dale, please elaborate... Can you explain what detail from the example lead you to a normalization issue? Also, I am still looking for an answer to the question posted. (Is that the best approach to retrieve the data?)
Thanks!

 

[rzw0wr]

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

These types of comments lead me to believe you are storing the same data in more than 1 table.
This is against normalization rules. This leads to large amounts of trouble later on in your database design.

We all are here on these forums to help you with problems.
However when a person chooses not to normalize their tables they will back in the future with problems are harder for us to offer good advise. We also would like to offer stable, proper design options as to the correct or possibly the best known way to design.
This is why most forums will stress table normalization so much. If the tables are not correct the database will never be correct.

Hope this helps you.

EDIT:
The answer to you question is No.
Not even an option.
Dale

 

[#1newbie]

Hello Dale, these tables were created from queries that are stored in different databases and were not intended to have a relationship. I concatenated the results in order to create a relationship. I left out this information so the example would be simple and easy to answer. This data is as normalized as I can make it.
You answered the post with "no". Can you please advise how you would retrieve the desired results? (An Iif statement possibly?)