Access 2003 Selecting a certain record with multiple duplicates

[Guest]

Greetings,
I have some mediacre fee shcedules that contain "CPT Codes" which contain
codes that have several different fee's associated with them.
Example:
CPT CODE
10021 $$$$
10021 $$$$
10021 $$$$

What I would like to do is have a query select just the first record when
there is are multiple selections. Is this possible?

Thanks

Brad

 

[Tomas C]

Open your query in design view.
Right-click in the gray area in the upper portion where you see your tables.
Select Properties.
Set Unique Values to "Yes."

See if that works for you.

 

[John W. Vinson]

Greetings,
I have some mediacre fee shcedules that contain "CPT Codes" which contain
codes that have several different fee's associated with them.
Example:
CPT CODE
10021 $$$$
10021 $$$$
10021 $$$$

What I would like to do is have a query select just the first record when
there is are multiple selections. Is this possible?

What is "the first record"? There's no such concept in a Table - a table is an
unordered bag of data.

If it doesn't matter which record you pick, use a Totals query grouping by CPT
Code and use the First aggregate function on the cost (it will return an
arbitrary record, the first in disk storage order; note that this may be the
first record entered but you have NO guarantee that it is).

If you want the cheapest price use Min, if you want the highest use Max.

John W. Vinson [MVP]

 

[Guest]

Brad:

Follow John's advice if the table has only the two columns, grouping the
query by CPT Code and returning MIN, MAX or whatever is appropriate of the
fee column. However, if there are other columns in the table on which you
can't group then you can use a subquery to identify the MAX (or whatever) fee
per CPT Code and use the subquery to restrict the result set of the outer
query to those rows, e.g.

SELECT *
FROM [Fee Schedule] AS FS1
WHERE [Fee] =
(SELECT MAX([Fee])
FROM [Fee Schedule] AS FS2
WHERE FS2.[CPT Code] = FS1.[CPT Code]);

Note how the aliases FS1 and FS2 are used to distinguish the two instances
of the table to allow the subquery to be correlated with the outer query.

Ken Sheridan
Stafford, England