count # of records

G

G.

Not as easy as the subject my indicate:

I have a query with Lic_Num, MR_Num, Admit_Dt, Disch_Dt. The query lists all
patients within a certain date period and the associated physican who treated
them. I need to get the number of times a patient was re-admitted. The MR_Num
identifies the patient and LIC_Num identifies the physician. Both fields
contain duplicate values because there could have been many instances of a
patient admission with the specified date period. However, the query lists
ALL patient admissions, so if a patient was only admitted once, the MR_Num
would show once.

I want to weed out all MR_Num's that have only one admission and so I need a
query that will return all the MR_Num's that have more than one record within
the query. Grouping the records is the closest I've gotted thus far but this
will still return records that are only listed once and therefore, are not a
readmission.

Any help is appreciated. Hope I explained that well. Thank you.
 
J

John Spencer

Could you post the SQL of the query you are using now? (Select View: SQL from
the menu and copy and paste).

Also, do you want all the records or just a count by MR_num for the period.
For a simple count by MR_Num.

SELECT MR_num, Count(MR_Num) as AdmittedCount
FROM [Some Table]
WHERE Admit_date Between #2008-01-01# and #2008-03-31#
GROUP BY MR_Num
HAVING Count(MR_Num) > 1

If you want to list all the records for the period where the MR_Num occured
more than once, then you need a query like the following.
SELECT Lic_Num, MR_Num, Admit_Dt, Disch_Dt
FROM [SOME Table]
WHERE MR_Num in
(SELECT MR_num
FROM [Some Table]
WHERE Admit_date Between #2008-01-01# and #2008-03-31#
GROUP BY MR_Num
HAVING Count(MR_Num) > 1)
AND Admit_date Between #2008-01-01# and #2008-03-31#
ORDER BY MR_Num, Admit_Dt, Lic_Num

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
G

G.

Thanks John,

You make the subject sound easy. I need all the records to show, not just a
count. If one MR_Num shows and a matching MR_Num shows, I need to see if
these two (or three or four etc.) records was actually a re-admission, I
therefore need all the records to make a determination. The table that my
original query was created from actually contains all of the records for the
time period I need so I don't have to specify a date range.

Your SQL seems to have helped me. I altered it a bit as follows:

SELECT *
FROM [Readmit Q 2]
WHERE MR_Num in
(SELECT MR_num
FROM [Readmit Q 2]
GROUP BY MR_Num
HAVING Count(MR_Num) > 1);

Does that look right to you. I checked a few records and it seems to have
doen the job. Being an SQL novice, that nesting throws me a bit. I'm also
getting a grip on the difference between Group BY and HAVING. Thanks for your
help.

John Spencer said:
Could you post the SQL of the query you are using now? (Select View: SQL from
the menu and copy and paste).

Also, do you want all the records or just a count by MR_num for the period.
For a simple count by MR_Num.

SELECT MR_num, Count(MR_Num) as AdmittedCount
FROM [Some Table]
WHERE Admit_date Between #2008-01-01# and #2008-03-31#
GROUP BY MR_Num
HAVING Count(MR_Num) > 1

If you want to list all the records for the period where the MR_Num occured
more than once, then you need a query like the following.
SELECT Lic_Num, MR_Num, Admit_Dt, Disch_Dt
FROM [SOME Table]
WHERE MR_Num in
(SELECT MR_num
FROM [Some Table]
WHERE Admit_date Between #2008-01-01# and #2008-03-31#
GROUP BY MR_Num
HAVING Count(MR_Num) > 1)
AND Admit_date Between #2008-01-01# and #2008-03-31#
ORDER BY MR_Num, Admit_Dt, Lic_Num

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

G. said:
Not as easy as the subject my indicate:

I have a query with Lic_Num, MR_Num, Admit_Dt, Disch_Dt. The query lists all
patients within a certain date period and the associated physican who treated
them. I need to get the number of times a patient was re-admitted. The MR_Num
identifies the patient and LIC_Num identifies the physician. Both fields
contain duplicate values because there could have been many instances of a
patient admission with the specified date period. However, the query lists
ALL patient admissions, so if a patient was only admitted once, the MR_Num
would show once.

I want to weed out all MR_Num's that have only one admission and so I need a
query that will return all the MR_Num's that have more than one record within
the query. Grouping the records is the closest I've gotted thus far but this
will still return records that are only listed once and therefore, are not a
readmission.

Any help is appreciated. Hope I explained that well. Thank you.
Click to expand...
Click to expand...
 
G

G.

I mean the difference between Group By and Order By, not Having - I do have
some ocnnections in that thing that houses my brain that still work.

G. said:
Thanks John,

You make the subject sound easy. I need all the records to show, not just a
count. If one MR_Num shows and a matching MR_Num shows, I need to see if
these two (or three or four etc.) records was actually a re-admission, I
therefore need all the records to make a determination. The table that my
original query was created from actually contains all of the records for the
time period I need so I don't have to specify a date range.

Your SQL seems to have helped me. I altered it a bit as follows:

SELECT *
FROM [Readmit Q 2]
WHERE MR_Num in
(SELECT MR_num
FROM [Readmit Q 2]
GROUP BY MR_Num
HAVING Count(MR_Num) > 1);

Does that look right to you. I checked a few records and it seems to have
doen the job. Being an SQL novice, that nesting throws me a bit. I'm also
getting a grip on the difference between Group BY and HAVING. Thanks for your
help.

John Spencer said:
Could you post the SQL of the query you are using now? (Select View: SQL from
the menu and copy and paste).

Also, do you want all the records or just a count by MR_num for the period.
For a simple count by MR_Num.

SELECT MR_num, Count(MR_Num) as AdmittedCount
FROM [Some Table]
WHERE Admit_date Between #2008-01-01# and #2008-03-31#
GROUP BY MR_Num
HAVING Count(MR_Num) > 1

If you want to list all the records for the period where the MR_Num occured
more than once, then you need a query like the following.
SELECT Lic_Num, MR_Num, Admit_Dt, Disch_Dt
FROM [SOME Table]
WHERE MR_Num in
(SELECT MR_num
FROM [Some Table]
WHERE Admit_date Between #2008-01-01# and #2008-03-31#
GROUP BY MR_Num
HAVING Count(MR_Num) > 1)
AND Admit_date Between #2008-01-01# and #2008-03-31#
ORDER BY MR_Num, Admit_Dt, Lic_Num

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

G. said:
Not as easy as the subject my indicate:

I have a query with Lic_Num, MR_Num, Admit_Dt, Disch_Dt. The query lists all
patients within a certain date period and the associated physican who treated
them. I need to get the number of times a patient was re-admitted. The MR_Num
identifies the patient and LIC_Num identifies the physician. Both fields
contain duplicate values because there could have been many instances of a
patient admission with the specified date period. However, the query lists
ALL patient admissions, so if a patient was only admitted once, the MR_Num
would show once.

I want to weed out all MR_Num's that have only one admission and so I need a
query that will return all the MR_Num's that have more than one record within
the query. Grouping the records is the closest I've gotted thus far but this
will still return records that are only listed once and therefore, are not a
readmission.

Any help is appreciated. Hope I explained that well. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

GROUP BY combines rows based on the same value being in the fields that you
group by. A side affect of this is that the records will be put in order by
the grouping unless you apply an order by that is different. As a guess, what
happens is that to do the group by all the records are ordered (in the
background) so it is easy to compare record n with record n+1 and see if the
two records should be combined into one

ORDER BY puts records into a sorted order after the records have been selected
(and grouped if you are using an aggregate query). In (almost?) all cases the
last thing a query does is ORDER the records.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

G. said:
I mean the difference between Group By and Order By, not Having - I do have
some ocnnections in that thing that houses my brain that still work.

G. said:
Thanks John,

You make the subject sound easy. I need all the records to show, not just a
count. If one MR_Num shows and a matching MR_Num shows, I need to see if
these two (or three or four etc.) records was actually a re-admission, I
therefore need all the records to make a determination. The table that my
original query was created from actually contains all of the records for the
time period I need so I don't have to specify a date range.

Your SQL seems to have helped me. I altered it a bit as follows:

SELECT *
FROM [Readmit Q 2]
WHERE MR_Num in
(SELECT MR_num
FROM [Readmit Q 2]
GROUP BY MR_Num
HAVING Count(MR_Num) > 1);

Does that look right to you. I checked a few records and it seems to have
doen the job. Being an SQL novice, that nesting throws me a bit. I'm also
getting a grip on the difference between Group BY and HAVING. Thanks for your
help.

John Spencer said:
Could you post the SQL of the query you are using now? (Select View: SQL from
the menu and copy and paste).

Also, do you want all the records or just a count by MR_num for the period.
For a simple count by MR_Num.

SELECT MR_num, Count(MR_Num) as AdmittedCount
FROM [Some Table]
WHERE Admit_date Between #2008-01-01# and #2008-03-31#
GROUP BY MR_Num
HAVING Count(MR_Num) > 1

If you want to list all the records for the period where the MR_Num occured
more than once, then you need a query like the following.
SELECT Lic_Num, MR_Num, Admit_Dt, Disch_Dt
FROM [SOME Table]
WHERE MR_Num in
(SELECT MR_num
FROM [Some Table]
WHERE Admit_date Between #2008-01-01# and #2008-03-31#
GROUP BY MR_Num
HAVING Count(MR_Num) > 1)
AND Admit_date Between #2008-01-01# and #2008-03-31#
ORDER BY MR_Num, Admit_Dt, Lic_Num

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

G. wrote:
Not as easy as the subject my indicate:

I have a query with Lic_Num, MR_Num, Admit_Dt, Disch_Dt. The query lists all
patients within a certain date period and the associated physican who treated
them. I need to get the number of times a patient was re-admitted. The MR_Num
identifies the patient and LIC_Num identifies the physician. Both fields
contain duplicate values because there could have been many instances of a
patient admission with the specified date period. However, the query lists
ALL patient admissions, so if a patient was only admitted once, the MR_Num
would show once.

I want to weed out all MR_Num's that have only one admission and so I need a
query that will return all the MR_Num's that have more than one record within
the query. Grouping the records is the closest I've gotted thus far but this
will still return records that are only listed once and therefore, are not a
readmission.

Any help is appreciated. Hope I explained that well. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Use John's query named Multi-Admit in this query --
SELECT *
FROM [Some Table] INNER JOIN [Multi-Admit] ON [Multi-Admit].[MR_num] = [Some
Table].[MR_num];

--
KARL DEWEY
Build a little - Test a little


G. said:
Thanks John,

You make the subject sound easy. I need all the records to show, not just a
count. If one MR_Num shows and a matching MR_Num shows, I need to see if
these two (or three or four etc.) records was actually a re-admission, I
therefore need all the records to make a determination. The table that my
original query was created from actually contains all of the records for the
time period I need so I don't have to specify a date range.

Your SQL seems to have helped me. I altered it a bit as follows:

SELECT *
FROM [Readmit Q 2]
WHERE MR_Num in
(SELECT MR_num
FROM [Readmit Q 2]
GROUP BY MR_Num
HAVING Count(MR_Num) > 1);

Does that look right to you. I checked a few records and it seems to have
doen the job. Being an SQL novice, that nesting throws me a bit. I'm also
getting a grip on the difference between Group BY and HAVING. Thanks for your
help.

John Spencer said:
Could you post the SQL of the query you are using now? (Select View: SQL from
the menu and copy and paste).

Also, do you want all the records or just a count by MR_num for the period.
For a simple count by MR_Num.

SELECT MR_num, Count(MR_Num) as AdmittedCount
FROM [Some Table]
WHERE Admit_date Between #2008-01-01# and #2008-03-31#
GROUP BY MR_Num
HAVING Count(MR_Num) > 1

If you want to list all the records for the period where the MR_Num occured
more than once, then you need a query like the following.
SELECT Lic_Num, MR_Num, Admit_Dt, Disch_Dt
FROM [SOME Table]
WHERE MR_Num in
(SELECT MR_num
FROM [Some Table]
WHERE Admit_date Between #2008-01-01# and #2008-03-31#
GROUP BY MR_Num
HAVING Count(MR_Num) > 1)
AND Admit_date Between #2008-01-01# and #2008-03-31#
ORDER BY MR_Num, Admit_Dt, Lic_Num

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

G. said:
Not as easy as the subject my indicate:

I have a query with Lic_Num, MR_Num, Admit_Dt, Disch_Dt. The query lists all
patients within a certain date period and the associated physican who treated
them. I need to get the number of times a patient was re-admitted. The MR_Num
identifies the patient and LIC_Num identifies the physician. Both fields
contain duplicate values because there could have been many instances of a
patient admission with the specified date period. However, the query lists
ALL patient admissions, so if a patient was only admitted once, the MR_Num
would show once.

I want to weed out all MR_Num's that have only one admission and so I need a
query that will return all the MR_Num's that have more than one record within
the query. Grouping the records is the closest I've gotted thus far but this
will still return records that are only listed once and therefore, are not a
readmission.

Any help is appreciated. Hope I explained that well. Thank you.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

List all records that appear more than once 1
Why is this query not updatable? 5
eliminate data subset from query 2
can't execute update query 1
Query Help Filtering Multiple Records 3
Passing information from one form to another 5
Annual Census 4
Matching related records from a single table 2

Top