Query to pull out unique records

[Guest]

I have been working on this for hours, and short of beating my head against
the wall, have decided to ask, have been unable to find anything in the forum
that answers my question.

I have two tables. One has patient number, patient name, total charges,
expected insurance payment total, etc. all unique records. I also have
another table that has a bill type in it, this has duplicates in it....see
example below:

pat# bill_type
1 111
1 131
7 131
7 131
14 111
14 121
14 111


What I need is to pull the first bill type into the table with the patient
detail. The problem I am having is that it is pulling all bill types and
then duplicates the patient record. Please anybody, is there a way to do
this? The company I am sending this to will not accept it with duplicated
records. I am a frequent basic user of Access, but not a very fluent one,
and not familiar with SQL statements and such.

 

[Michel Walsh]

either


SELECT [pat#], MIN(bill_type) FROM tableName GROUP BY [pat#]




either



SELECT [pat#], bill_type FROM tableName GROUP BY [pat#], bill_type





The first one supplying only ONE record per patient, with the smallest value
for bill_type;

The second query supply ONE record per client, per bill_type.


The table to use is (probably) the one with the bill types having duplicated
values.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thank you for your reply. Though, I can't use the first one because I need
to be able to pull the first occurence of bill type on a patient number, and
the second one still gives me duplicates, which they won't accept.


--
Tasha

either


SELECT [pat#], MIN(bill_type) FROM tableName GROUP BY [pat#]




either



SELECT [pat#], bill_type FROM tableName GROUP BY [pat#], bill_type





The first one supplying only ONE record per patient, with the smallest value
for bill_type;

The second query supply ONE record per client, per bill_type.


The table to use is (probably) the one with the bill types having duplicated
values.


Hoping it may help,
Vanderghast, Access MVP

I have been working on this for hours, and short of beating my head against
the wall, have decided to ask, have been unable to find anything in the
forum
that answers my question.

I have two tables. One has patient number, patient name, total charges,
expected insurance payment total, etc. all unique records. I also have
another table that has a bill type in it, this has duplicates in it....see
example below:

pat# bill_type
1 111
1 131
7 131
7 131
14 111
14 121
14 111


What I need is to pull the first bill type into the table with the patient
detail. The problem I am having is that it is pulling all bill types and
then duplicates the patient record. Please anybody, is there a way to do
this? The company I am sending this to will not accept it with duplicated
records. I am a frequent basic user of Access, but not a very fluent one,
and not familiar with SQL statements and such.

 

[John Spencer]

Excuse me. Try two queries.

First query returns unique records on your Bill Type
SELECT Distinct [Pat#], Bill_Type
FROM BillTypeTable

Save that as qUniqueBillType

Now use that query and your Patient table

SELECT PatientTable.*, Bill_Type
FROM PatientTable Inner JOIN qUniqueBillType
on PatientTable.[Pat#] = qUniqueBillType.[Pat#]


I would suggest that you do this all in one query except your field names
would probably preclude that - Pat# for instance requires the use of
brackets around the field name.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Hi John! Sorry to intrude but the topic seemed like it might shed some light
on my problem as well... In this case you do not recommend using a Union
Query, but a query within a query to specify an occurrence of the Patient
Number Bill Type? Might this technique not also be used in the situation
that you responded to earlier?

-- Why are you asking me? I dont know what Im doing!

Jaybird

Excuse me. Try two queries.

First query returns unique records on your Bill Type
SELECT Distinct [Pat#], Bill_Type
FROM BillTypeTable

Save that as qUniqueBillType

Now use that query and your Patient table

SELECT PatientTable.*, Bill_Type
FROM PatientTable Inner JOIN qUniqueBillType
on PatientTable.[Pat#] = qUniqueBillType.[Pat#]


I would suggest that you do this all in one query except your field names
would probably preclude that - Pat# for instance requires the use of
brackets around the field name.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thank you for your reply. Though, I can't use the first one because I
need
to be able to pull the first occurence of bill type on a patient number,
and
the second one still gives me duplicates, which they won't accept.