Why use Unique Records for Update Queries?

Update queries that return duplicate records seem to work just fine, so is there any reason to set their Unique Records property to Yes?
LVL 1
MilewskpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
>>so is there any reason to set their Unique Records property to Yes?

Always assign an Unique Identifier to your record, so that you can identify and retrieve your data.

>>Update queries that return duplicate records seem to work just fine

Yup, it's just fine, but it may update all duplicate records.
0
Dale FyeCommented:
Can you provide an example?
0
wittysloganCommented:
Are asking if duplicates are okay in general?
I'd say that they are not okay.  
A few reasons:
1. duplicates take up space
2. duplicates mean that some actions may take place more than once.  This becomes a problem if the database is for a call centre or mail delivery company.
3. makes queries less efficient and therefore slower.

Or are you asking something else
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
" so is there any reason to set their Unique Records property to Yes?"
If you want to exclude duplicated Records across ALL *Fields* in the underlying data source - which may include 1 or more tables and/or queries ... then the answer is Yes >>> SELECT DISTINCTROW

If you want to exclude duplicated Records across ALL *Fields* displayed only in the datasheet view of the query, then the answer is Yes >>> SELECT DISTINCT

mx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Milewskp
What about your Q from last night with the combo boxes wherein I found the reason for the issue.  Can you close out that Q please ?

mx
0
MilewskpAuthor Commented:
<Can you provide an example?>

I have attached an example mdb file with two tables:
- Members (of a library).
- Books (that have been loaned out).
It also has a query that updates the OverDue field of the Member table.

Note that it doesn’t matter how many books a member has on loan, the query provides correct results even though the UniqueRecords property=No.


db2.mdb
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
That's because there are no duplicates in the Book table for a given member/book.


MemberID      BookID      Due
4      23      12-31-2010
5      13      05-05-2011
5      120      11-21-2010
6      16      04-20-2010
6      18      04-13-2010
6      200      12-01-2010

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
In fact ... your Book table does not allow dups on the MemberID/BookID combination.

IF ... as a test you were to allow dups (I added one) ... then w/o Distinct ... you get seven records returned:

OverDue      MemberID      BookID
0      5      13
0      5      120
0      5      120
-1      6      200
-1      6      16
-1      6      18
0      4      23

If you use SELECT DISTINCT you get the 6 records with no dups:

OverDue      MemberID      BookID
-1      6      16
-1      6      18
-1      6      200
0      4      23
0      5      13
0      5      120

mx
0
MilewskpAuthor Commented:
Hi mx,
I didn't say the book table has dups; it is the update query that returns duplicate records.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"it is the update query that returns duplicate records."
I guess I'm confused.  'Query1' returns this:

OverDue      ID      BookID
0      4      23
0      5      13
0      5      120
-1      6      200
-1      6      16
-1      6      18

Where are the dups ?

mx
0
MilewskpAuthor Commented:
Hi mx,
Query 1 returns 1 record for Member 1 (not overdue), 2 records for Member 5 (neither overdue), and 3 records for Member 6 (2 overdue, 1 not overdue).
Therefore, members 5 and 6 have 2 duplicate records each.

Book ID is not used by Query1.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"Book ID is not used by Query1."
Well, I changed it temporarily to a SELECT query to see what would be returned.

OK ... what is your definition of duplicate.  I the results I posted above, all combination's of MemberID and BookID are unique. So, I'm confused.  Is the query not returning (or updating) what you expect?

Also ... your update expression .. [Due]<Date() doesn't seem right.  Shouldn't that be the criteria ?

UPDATE Member INNER JOIN Book ON Member.ID = Book.MemberID SET Member.OverDue = [Due]<Date();


?

mx
0
MilewskpAuthor Commented:
<[Due]<Date() doesn't seem right.  Shouldn't that be the criteria ?>
Yes, you're correct. It should be:
   UPDATE Member INNER JOIN Book ON Member.ID = Book.MemberID SET
   Member.OverDue = Yes
   WHERE (((Book.Due)<Date()));
Sorry about that.

With this new SQL, you'll see that the query returns two records, both for member 6, both with OverDue= True (ie, a duplicate).

The query updates the Member table correctly (ie., sets Overdue for member 6 to True), in spite of the fact that UniqueRecords=No. So, the question is, is there any reason to ever set Unique Records property to Yes?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"So, the question is, is there any reason to ever set Unique Records property to Yes?"

I already answered that question @http:#a31878753
That is the reason ... and it's the same reason given in the Help file.  It's just that simple.

mx
0
MilewskpAuthor Commented:
Hi mx,
<If you want to exclude duplicated Records across ALL *Fields* in the underlying data source - which may include 1 or more tables and/or queries ... then the answer is Yes >>> SELECT DISTINCTROW>
This explains what setting UniqueRecords to Yes does, but the question is: is there anything to be gained by doing so?

I seem to get the same results either way. Is this always the case? Is there a performance adavnatge?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"Is there a performance adavnatge?"
Not really an issue of performance ... it's an issue of eliminating duplicates.

mx
0
MilewskpAuthor Commented:
Hi mx,
When I ask <so is there any reason to set their Unique Records property to Yes>,
what I'm asking is: is there any practical advantage to setting UniqueRecords to Yes?

To me, setting it to No just for the sake of eliminating duplicate records, without any practical advantage, is a waste of a double mouse click. If however, you do get different results under certain circumstances, or 'Yes' makes the query run faster, then that would be a practical advantage to setting the Unique Records property to Yes.

Are you aware of any practical advantage?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"Are you aware of any practical advantage?"
Exact same answer as before.  Eliminate duplicates. That is what it's for and that's what it does.  There are many 'practical' situations where this is useful, for example when you need to ... yep ... you guessed ... eliminate duplicates.

mx
0
MilewskpAuthor Commented:
The question has not been answered (see my last post).
0
upsfaCommented:
This is why to use Unique Records for Update Queries.

If you want to use criteria from one table in a query, you will need to set Unique Records property to Yes.

Here is how: Create a delete query; bring both tables into the query. Make the appropriate link between the tables. Only drag the fields you need as criteria from table one down into the query grid. Notice that these fields are listed as “Where”. Enter selection criteria. Then, click on the asterisk in the field list of the table you wish to delete records from and drag it down into the query grid. Notice that this will be listed as “From”. Set the query’s Unique Records property to Yes. When the query is run the records from table two will be deleted based on the criteria on table one.

If you do not set the query's Unique Records property to Yes, Access will display the error message “Could not delete from specified tables”

Michael
0
upsfaCommented:
Sorry, I left out a few words out of the last post.

This is why to use Unique Records for Update Queries.

If you want to use criteria from one table in a two-table delete query, to delete records in the other table you will need to set Unique Records property to Yes.

Here is how: Create a delete query; bring both tables into the query. Make the appropriate link between the tables. Only drag the fields you need as criteria from table one down into the query grid. Notice that these fields are listed as “Where”. Enter selection criteria. Then, click on the asterisk in the field list of the table you wish to delete records from and drag it down into the query grid. Notice that this will be listed as “From”. Set the query’s Unique Records property to Yes. When the query is run the records from table two will be deleted based on the criteria on table one.

If you do not set the query's Unique Records property to Yes, Access will display the error message “Could not delete from specified tables”
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.