Unique encounters

[Guest]

Hi, There are a lot of solutions for counting unique encounters, but I did
not find the one for me, yet.
I have two tables - Main and Encounter, where I track client visits. Tables
are linked by customer ID. I would like to be able to filter for Unique
customer ID, meaning, not counting subsequent visits. I already created cross
table query where I filtered for location, but it pulls all encounters, and
not the first one. Please help

 

[Jeff Boyce]

There's a difference between "first Encounter" and "Unique Customer ID" --
which one are you trying for?

What data elements are you keeping in your two tables? Creating a query to
retrieve data requires know where data is.

Have you looked into the Totals queries that Access offers? If your
"Encounter" information includes a date/time field and a CustomerID field,
you should be able to "GroupBy" the CustomerID and use "Min" on the
date/time field. I believe this would give you the first date/time per
CustomerID.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Guest]

the structure of my main table contains customer name and ID, the encounter
database contains encounters for all customers including their ID and place
where I have seen them. I need a report of all customers that were seen in
one particular place. And I am trying to avoid any duplications from the
encounters. In design view I pulled both tables, linked by customer ID. I
pulled fields: Name <Main>, ID <main>, Data <encounter>, Place<encounter>. I
would like to filter by customer ID, so I will get a list of all the
customers in that particular location without duplication. I hope this is a
little bit more clear explanation. I will appreciate any help.

 

[Jeff Boyce]

Here's another approach:

Create a query on the Encounters table. Use the Totals query to GroupBy
CustomerID and "Min" the EncounterDate. You'll get a list of CustomerIDs
and their minimum encounter dates.

Then create a second query, using the first as one source, and the Customer
table as the second. Join on CustomerID, and pull in whichever fields you
want.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Guest]

I am not getting what expected. That is what I have done
SELECT Encounter.[Patient Name], Encounter.[Medical Record],
Min(Encounter.Date) AS MinOfDate, Encounter.Type, Encounter.HPI
FROM Encounter
GROUP BY Encounter.[Patient Name], Encounter.[Medical Record],
Encounter.Type, Encounter.HPI
ORDER BY Encounter.[Medical Record];

It still pulls all encounters for that ID

 

[Jeff Boyce]

Take another look at my suggestion. I "chained" two queries together. The
first one has only two fields, and is a Totals query. The second one brings
in the other fields.

Regards

Jeff Boyce
<Office/Access MVP>

I am not getting what expected. That is what I have done
SELECT Encounter.[Patient Name], Encounter.[Medical Record],
Min(Encounter.Date) AS MinOfDate, Encounter.Type, Encounter.HPI
FROM Encounter
GROUP BY Encounter.[Patient Name], Encounter.[Medical Record],
Encounter.Type, Encounter.HPI
ORDER BY Encounter.[Medical Record];

It still pulls all encounters for that ID

Here's another approach:

Create a query on the Encounters table. Use the Totals query to GroupBy
CustomerID and "Min" the EncounterDate. You'll get a list of CustomerIDs
and their minimum encounter dates.

Then create a second query, using the first as one source, and the
Customer
table as the second. Join on CustomerID, and pull in whichever fields
you
want.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Guest]

I have done that.. First table worked. Joining table and query added all
additional encounters again.

Take another look at my suggestion. I "chained" two queries together. The
first one has only two fields, and is a Totals query. The second one brings
in the other fields.

Regards

Jeff Boyce
<Office/Access MVP>

I am not getting what expected. That is what I have done
SELECT Encounter.[Patient Name], Encounter.[Medical Record],
Min(Encounter.Date) AS MinOfDate, Encounter.Type, Encounter.HPI
FROM Encounter
GROUP BY Encounter.[Patient Name], Encounter.[Medical Record],
Encounter.Type, Encounter.HPI
ORDER BY Encounter.[Medical Record];

It still pulls all encounters for that ID

Here's another approach:

Create a query on the Encounters table. Use the Totals query to GroupBy
CustomerID and "Min" the EncounterDate. You'll get a list of CustomerIDs
and their minimum encounter dates.

Then create a second query, using the first as one source, and the
Customer
table as the second. Join on CustomerID, and pull in whichever fields
you
want.

Regards

Jeff Boyce
<Office/Access MVP>

the structure of my main table contains customer name and ID, the
encounter
database contains encounters for all customers including their ID and
place
where I have seen them. I need a report of all customers that were seen
in
one particular place. And I am trying to avoid any duplications from
the
encounters. In design view I pulled both tables, linked by customer ID.
I
pulled fields: Name <Main>, ID <main>, Data <encounter>,
Place<encounter>.
I
would like to filter by customer ID, so I will get a list of all the
customers in that particular location without duplication. I hope this
is
a
little bit more clear explanation. I will appreciate any help.

 

[Jeff Boyce]

What are you joining on? If you are ONLY joining on the ID, you will
certainly get all the encounters. If you join on both the ID and the (min)
EncounterDate, you should get only one per ID.

Unless, of course, you have more than one encounter per date <g>!

Regards

Jeff Boyce
<Office/Access MVP>


Post the SQL statement of the second query.

I have done that.. First table worked. Joining table and query added all
additional encounters again.

Take another look at my suggestion. I "chained" two queries together.
The
first one has only two fields, and is a Totals query. The second one
brings
in the other fields.

Regards

Jeff Boyce
<Office/Access MVP>

I am not getting what expected. That is what I have done
SELECT Encounter.[Patient Name], Encounter.[Medical Record],
Min(Encounter.Date) AS MinOfDate, Encounter.Type, Encounter.HPI
FROM Encounter
GROUP BY Encounter.[Patient Name], Encounter.[Medical Record],
Encounter.Type, Encounter.HPI
ORDER BY Encounter.[Medical Record];

It still pulls all encounters for that ID

:

Here's another approach:

Create a query on the Encounters table. Use the Totals query to
GroupBy
CustomerID and "Min" the EncounterDate. You'll get a list of
CustomerIDs
and their minimum encounter dates.

Then create a second query, using the first as one source, and the
Customer
table as the second. Join on CustomerID, and pull in whichever fields
you
want.

Regards

Jeff Boyce
<Office/Access MVP>

the structure of my main table contains customer name and ID, the
encounter
database contains encounters for all customers including their ID
and
place
where I have seen them. I need a report of all customers that were
seen
in
one particular place. And I am trying to avoid any duplications from
the
encounters. In design view I pulled both tables, linked by customer
ID.
I
pulled fields: Name <Main>, ID <main>, Data <encounter>,
Place<encounter>.
I
would like to filter by customer ID, so I will get a list of all the
customers in that particular location without duplication. I hope
this
is
a
little bit more clear explanation. I will appreciate any help.