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?
Can you provide an example?
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
<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
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
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
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
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
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
ASKER
Hi mx,
I didn't say the book table has dups; it is the update query that returns duplicate records.
I didn't say the book table has dups; it is the update query that returns duplicate records.
"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
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
ASKER
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.
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.
"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
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
ASKER
<[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?
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?
"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
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
ASKER
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?
<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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
"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
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
ASKER
The question has not been answered (see my last post).
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
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
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”
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”
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.