Am I understanding DISTINCT correctly?

[jrg24]

I am creating a database to help with some of my financial and accounting responsibilities. I am most certainly a novice at access so any help you can provide will be appreciated.

This database will be used once a month and the source transnational data will be replaced each time.

In the database I will be keeping a customer table which will not be replaced, but I will need to append it based off of the new transaction data. I will be adding all new unique customerIDs to the customer table along with their associated customer names.

I wrote the code below to come up with the list of unique customer ID's in the transaction table. It does appear to return a list of unique customerIDs but I want to clarify if that is indeed what it is doing.

 SELECT DISTINCT tbl_PRODetail.[Customer ID], tbl_PRODetail.[Customer Name]FROM tbl_PRODetail;

Is the code actually returning all unique customerIDs and their associated names?
Is it returning all unique combinations of CustomerID and CustomerName?
Is it returning all unique customerIDs and unique customer names?

I know that a customer's name does sometime change, but in reality I am not concerned with these changes because even if the name changes, the entity that name represents stays the same (Think someone changing their name after they get married i.e. the customerID will never be used for an actual different customer). I just want to make sure that I have an list of unique customerIDs only. The customer Name should only be a descriptive field for the CustomerID.

The next step is to use this list and append my customer list. Is the code above even taking me in the right direction to achieving this goal?

Thanks for your help.

 

[Micron]

DISTINCT checks only the fields listed in the SQL string and then eliminates the duplicate rows in the results. Therefore, no combination of fields across the returned record will be the same from one record to the next.
DISTINCTROW checks all fields in the table that is being queried, and eliminates duplicates based on the entire record. If your ID field is unique as you say, I doubt you have a need for a DISTINCT clause, unless there is something about the data structure and joins between two or more tables that would result in duplicate records. If that's the case, check if you can add a join to make the results more restricted. To answer your question definitively, one would have to know more about your data structure.

With respect to your comment on name changes, it should not be an issue to change a name if the join between this record is based on a primary key (such as ID) and its related foreign key in other tables.

 

[jrg24]

DISTINCT checks only the fields listed in the SQL string and then eliminates the duplicate rows in the results. Therefore, no combination of fields across the returned record will be the same from one record to the next.
DISTINCTROW checks all fields in the table that is being queried, and eliminates duplicates based on the entire record. If your ID field is unique as you say, I doubt you have a need for a DISTINCT clause, unless there is something about the data structure and joins between two or more tables that would result in duplicate records. If that's the case, check if you can add a join to make the results more restricted. To answer your question definitively, one would have to know more about your data structure.

With respect to your comment on name changes, it should not be an issue to change a name if the join between this record is based on a primary key (such as ID) and its related foreign key in other tables.

Thanks for the reply. I believe I have found my answer from a similar question on another forum. Code is below:

SELECT tbl_PRODetail.[Customer ID], tbl_PRODetail.[Customer Name]FROM tbl_PRODetail LEFT JOIN tbl_Customer ON tbl_PRODetail.[Customer ID] = tbl_Customer.[CustomerID]
WHERE (((tbl_Customer.CustomerID) Is Null));

This code was created by the Query wizard in Access. I will convert this new query into an append query and I should have what I need. Thanks!

 

[Micron]

Welcome - not that I think I did much to help...

 

[xenou]

Hi, it looks like you shouldn't use customer name here. For instance, if your query returns:
14548 ABC Incorporated

And you now have a new record:
14548 ABC Inc.

The customer ID is all that really matters. The name is just candy on top. As a matter of housekeeping you might want to update names to their latest versions. In most databases, you will want to have a customer table that has the "one true version of the truth" - one customer ID, and one customer name to go with it.

In fact, if your customer list has a unique index on CustomerID, you can just append all your "new" records and just let the existing CustomerID's silently be discarded.