If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Unique Values vs Unique Records
I have a query that produces a two diverent records for
the same individual. He holds two positions in the company. On the organization report he should appear in two places but in the head count report he should appear in only one. I thought Unique values would take care of this, The help screen says: (1)"You can use the UniqueValues property when you want to omit records that contain duplicate data in the fields displayed..." but the next line in the help screen says: (2)"...if a query's output includes more than one field, the combination of values from all fields must be unique for a given record to be included in the results." and those two statements are not the same. Then in the setting box on the help screen it says: (3)"Displays only the records in which the values of all fields displayed in Datasheet view are unique." This sounds to me as if it aggrees with (1), which is what I want, and not with (2) Then if you go look at the help screen for UniqueRecords it says: "You can use the UniqueRecords property to specify whether to return only unique records based on all fields in the underlying data source, not just those fields present in the query itself." This is still a different matter and it applies the uniqueness criteria to values of fields which are not shown in the results. It seems to me that there are four conditions: 1)Are the records shown in the result distinct in that at least one field (in the result) contains a different value from otherwise similar records. 2)Are the records shown in the result distinct in that no field (in the result) contains duplicate values. 3)Are the records shown in the result distinct in that at least one field (in the underlying tables) contains a different value from otherwise similar records. 4)Are the records shown in the result distinct in that no field (in the underlying tables result) contains duplicate values. How can I achieve situation 2)? In my case since I know where the duplication must lie I can fix the problem with an additional subquery, but as a general rule....... |
#2
|
|||
|
|||
I would do this in the report...not the query.......or set
up two queries to run each report. If one query....on the Organization report Group by Name, with Title in the detail. In the Head Count Report....group by Name, no title included on the report. How do you have these reports/query(ies) set up? -----Original Message----- I have a query that produces a two diverent records for the same individual. He holds two positions in the company. On the organization report he should appear in two places but in the head count report he should appear in only one. I thought Unique values would take care of this, The help screen says: (1)"You can use the UniqueValues property when you want to omit records that contain duplicate data in the fields displayed..." but the next line in the help screen says: (2)"...if a query's output includes more than one field, the combination of values from all fields must be unique for a given record to be included in the results." and those two statements are not the same. Then in the setting box on the help screen it says: (3)"Displays only the records in which the values of all fields displayed in Datasheet view are unique." This sounds to me as if it aggrees with (1), which is what I want, and not with (2) Then if you go look at the help screen for UniqueRecords it says: "You can use the UniqueRecords property to specify whether to return only unique records based on all fields in the underlying data source, not just those fields present in the query itself." This is still a different matter and it applies the uniqueness criteria to values of fields which are not shown in the results. It seems to me that there are four conditions: 1)Are the records shown in the result distinct in that at least one field (in the result) contains a different value from otherwise similar records. 2)Are the records shown in the result distinct in that no field (in the result) contains duplicate values. 3)Are the records shown in the result distinct in that at least one field (in the underlying tables) contains a different value from otherwise similar records. 4)Are the records shown in the result distinct in that no field (in the underlying tables result) contains duplicate values. How can I achieve situation 2)? In my case since I know where the duplication must lie I can fix the problem with an additional subquery, but as a general rule....... . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Counting unique values and all values in same query | John Morrissey | Running & Setting Up Queries | 1 | August 6th, 2004 12:14 PM |
From-the-front truncation when unique values: on? | Ballzooka | General Discussion | 2 | July 9th, 2004 12:30 AM |
LIST unique values | James Gilbert | Worksheet Functions | 3 | May 31st, 2004 11:16 AM |