unique records help please

[ferde]

I have been researching this question in two books I have on access 2003 and
hope someone can help me because I am a beginner. I have two tables that I
have joined in a select query and I have tried setting the query properties
to unique records as well as unique values in order to get the number of
patients who were discharged alive after having a code event. The first
table has the admit info such as the patients name and his discharge status.
The second table has the event date. My results end up with duplicate
records inspite of setting the query for unique values. (i.e the patient is
discharged dead twice) Can someone help direct me? Thank you in advance for
your time.

SELECT DISTINCT LocalAnalysis_Admit.PatientLastName,
LocalAnalysis_ARCEvent.AdmitControlNumber, LocalAnalysis_ARCEvent.EventDate,
LocalAnalysis_Admit.DischargeDispositionAsText
FROM LocalAnalysis_Admit INNER JOIN LocalAnalysis_ARCEvent ON
LocalAnalysis_Admit.AdmitControlNumber =
LocalAnalysis_ARCEvent.AdmitControlNumber;

 

[John W. Vinson]

I have been researching this question in two books I have on access 2003 and
hope someone can help me because I am a beginner. I have two tables that I
have joined in a select query and I have tried setting the query properties
to unique records as well as unique values in order to get the number of
patients who were discharged alive after having a code event. The first
table has the admit info such as the patients name and his discharge status.
The second table has the event date. My results end up with duplicate
records inspite of setting the query for unique values. (i.e the patient is
discharged dead twice) Can someone help direct me? Thank you in advance for
your time.

The DISTINCT clause applies to the fields that you're actually selecting for
display. If you don't want to treat LocalAnalysis_ARCEvent fields in the
distinct, don't include them in the SELECT clause. They can still be included
in the WHERE clause if you use criteria; to do so just uncheck the SHOW box in
the query grid and put a criterion on the Criteria line.

Note that your query is not counting anything and doesn't have any criteria,
so I don't see what you're expecting it to give you.

SELECT DISTINCT LocalAnalysis_Admit.PatientLastName,
LocalAnalysis_Admit.DischargeDispositionAsText
FROM LocalAnalysis_Admit INNER JOIN LocalAnalysis_ARCEvent ON
LocalAnalysis_Admit.AdmitControlNumber =
LocalAnalysis_ARCEvent.AdmitControlNumber
WHERE LocalAnalysis_ARCEvent.<fieldname> = <some value>

 

[ferde]

Thank you for helping me. I see now that I was going about this all wrong. I
have made another attempt and posted new SQL. My goal is to list the admits
with their discharge disposition but also determine the Year and Quarter that
the event took place. I now have a list of all the patients with their
discharge status and it is not double counting events but because I have the
field [EventDate] as NO SHOW , I can not determine when the event occurred.
If I allow the [EventDate] to SHOW then it double counts again. Not sure
what to do from here and would appreciate your help very much.

SELECT DISTINCTROW LocalAnalysis_Admit.PatientLastName,
LocalAnalysis_Admit.DischargeDispositionAsText
FROM LocalAnalysis_Admit INNER JOIN LocalAnalysis_ARCEvent ON
LocalAnalysis_Admit.AdmitControlNumber =
LocalAnalysis_ARCEvent.AdmitControlNumber
WHERE (((LocalAnalysis_ARCEvent.EventDate) Is Not Null))
GROUP BY LocalAnalysis_Admit.PatientLastName,
LocalAnalysis_Admit.DischargeDispositionAsText;

 

[John W. Vinson]

Thank you for helping me. I see now that I was going about this all wrong. I
have made another attempt and posted new SQL. My goal is to list the admits
with their discharge disposition but also determine the Year and Quarter that
the event took place. I now have a list of all the patients with their
discharge status and it is not double counting events but because I have the
field [EventDate] as NO SHOW , I can not determine when the event occurred.
If I allow the [EventDate] to SHOW then it double counts again. Not sure
what to do from here and would appreciate your help very much.

SELECT DISTINCTROW LocalAnalysis_Admit.PatientLastName,
LocalAnalysis_Admit.DischargeDispositionAsText
FROM LocalAnalysis_Admit INNER JOIN LocalAnalysis_ARCEvent ON
LocalAnalysis_Admit.AdmitControlNumber =
LocalAnalysis_ARCEvent.AdmitControlNumber
WHERE (((LocalAnalysis_ARCEvent.EventDate) Is Not Null))
GROUP BY LocalAnalysis_Admit.PatientLastName,
LocalAnalysis_Admit.DischargeDispositionAsText;

I don't have your database in front of me. I can't see your table structures
or your data. For that matter I don't understand the question: you want to see
each admission only once, but you want to see the date and time of "the
event"? The whole point of the structure is that there are MULTIPLE events
(which I presume might have different dates); which of the events?
How can Access (or I, or for that matter you!) determine WHICH of the ARCEvent
records? Just the last chronological date? Is there some field in ARCEvent
which pinpoints the event?

 

[ferde]

I'm sorry ...I see your point. I will need to pull the First chronological date

Thank you for helping me. I see now that I was going about this all wrong. I
have made another attempt and posted new SQL. My goal is to list the admits
with their discharge disposition but also determine the Year and Quarter that
the event took place. I now have a list of all the patients with their
discharge status and it is not double counting events but because I have the
field [EventDate] as NO SHOW , I can not determine when the event occurred.
If I allow the [EventDate] to SHOW then it double counts again. Not sure
what to do from here and would appreciate your help very much.

SELECT DISTINCTROW LocalAnalysis_Admit.PatientLastName,
LocalAnalysis_Admit.DischargeDispositionAsText
FROM LocalAnalysis_Admit INNER JOIN LocalAnalysis_ARCEvent ON
LocalAnalysis_Admit.AdmitControlNumber =
LocalAnalysis_ARCEvent.AdmitControlNumber
WHERE (((LocalAnalysis_ARCEvent.EventDate) Is Not Null))
GROUP BY LocalAnalysis_Admit.PatientLastName,
LocalAnalysis_Admit.DischargeDispositionAsText;

I don't have your database in front of me. I can't see your table structures
or your data. For that matter I don't understand the question: you want to see
each admission only once, but you want to see the date and time of "the
event"? The whole point of the structure is that there are MULTIPLE events
(which I presume might have different dates); which of the events?
How can Access (or I, or for that matter you!) determine WHICH of the ARCEvent
records? Just the last chronological date? Is there some field in ARCEvent
which pinpoints the event?

 

[John W. Vinson]

I'm sorry ...I see your point. I will need to pull the First chronological date

Then include the datefield in your query and use the Min totals function:

SELECT DISTINCTROW LocalAnalysis_Admit.PatientLastName,
LocalAnalysis_Admit.DischargeDispositionAsText,
Min(LocalAnalysis_ARCEvent.EventDate) As FirstDate
FROM LocalAnalysis_Admit INNER JOIN LocalAnalysis_ARCEvent ON
LocalAnalysis_Admit.AdmitControlNumber =
LocalAnalysis_ARCEvent.AdmitControlNumber
WHERE (((LocalAnalysis_ARCEvent.EventDate) Is Not Null))
GROUP BY LocalAnalysis_Admit.PatientLastName,
LocalAnalysis_Admit.DischargeDispositionAsText;

 

[ferde]

Works great... Thank you so much for your help.

Then include the datefield in your query and use the Min totals function:

SELECT DISTINCTROW LocalAnalysis_Admit.PatientLastName,
LocalAnalysis_Admit.DischargeDispositionAsText,
Min(LocalAnalysis_ARCEvent.EventDate) As FirstDate
FROM LocalAnalysis_Admit INNER JOIN LocalAnalysis_ARCEvent ON
LocalAnalysis_Admit.AdmitControlNumber =
LocalAnalysis_ARCEvent.AdmitControlNumber
WHERE (((LocalAnalysis_ARCEvent.EventDate) Is Not Null))
GROUP BY LocalAnalysis_Admit.PatientLastName,
LocalAnalysis_Admit.DischargeDispositionAsText;