Printing only if count > X

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that lists all pts who came into the ED over the last year. .
.. I would like to limit the printing of the report to only people who have
had X number of visits or more (they all get registered with a Medical Record
Number which is unique to them but is consistant through all their visits).
I cannot figure out how to have the report only print those people who have
multiple visits. Any help will be greatly appreciated.
 
RGFlade said:
I have a report that lists all pts who came into the ED over the last year. .
. I would like to limit the printing of the report to only people who have
had X number of visits or more (they all get registered with a Medical Record
Number which is unique to them but is consistant through all their visits).
I cannot figure out how to have the report only print those people who have
multiple visits.
Click to expand...


Definitely need to create a query to do this. Once the
query is selecting the desired data, the report will be
easy.

Try using a query something along these lines:

SELECT MRN, Count(*) As NumVisits
FROM thetable
WHERE visitdate Between [startdate] And [enddate]
GROUP BY MRN
HAVING Count(*) >= X

You probably want more data than just the MRN, but I would
need to know which fields and their type before I could
decide the best way to add them to the query (memo field are
definitely an issue). How you define "last year" will need
to be refined as well.
 
I'm a tad bit confused. . . I assume that you aren't talking about a query
that you do from the query object (tab under tables) becasue I can't define
variables nor enter "HAVING Count(*) >= X"

And, while I do somethings in visual basic. . . I have never seen these
statements. . .

The database is Pt List and the fields are:
ID (auto number)
DOS (short date)
MR No (text)
Pt Name (text)
HPICC (text)

Thanks.




Marshall Barton said:
RGFlade said:
I have a report that lists all pts who came into the ED over the last year. .
. I would like to limit the printing of the report to only people who have
had X number of visits or more (they all get registered with a Medical Record
Number which is unique to them but is consistant through all their visits).
I cannot figure out how to have the report only print those people who have
multiple visits.
Click to expand...


Definitely need to create a query to do this. Once the
query is selecting the desired data, the report will be
easy.

Try using a query something along these lines:

SELECT MRN, Count(*) As NumVisits
FROM thetable
WHERE visitdate Between [startdate] And [enddate]
GROUP BY MRN
HAVING Count(*) >= X

You probably want more data than just the MRN, but I would
need to know which fields and their type before I could
decide the best way to add them to the query (memo field are
definitely an issue). How you define "last year" will need
to be refined as well.
Click to expand...
 
Yes, I did mean for you to create a query using the query
design window.

There are no variables in the query I posted (although the X
was (unknown to me) supposed to be replaced by something.
Your original question said you wanted to "limit the
printing of the report to only people who have had X number
of visits or more" so that's what the X I used was supposed
to represent. Similarly for the startdate and enddate. If
you leave them they way I wrote them, Access will prompt you
for the values of these **parameters** when you open the
query or the report.

Once you create the query, test it by opening it (from the
query design tool bar) and look at the data to see if it's
really what you need for the report.

Once the query is producing the desired data, the report
should be relatively simple.

From your latest post, I think the query will be:

SELECT [MR No], [Pt Name], HPICC,
Count(*) As NumVisits
FROM [Pt List]
WHERE DOS Between [startdate] And [enddate]
GROUP BY [MR No], [Pt Name], HPICC
HAVING Count(*) >= [X]

I'm still not sure that all three of those fields are
appropriate, but that should get you started. Just create a
new query (database window, query tab, New button), switch
to SQL view, then Copy/Paste the above SQL statement over
whatever Access automatically put in the query window. Try
to open it and go from there.
--
Marsh
MVP [MS Access]


I'm a tad bit confused. . . I assume that you aren't talking about a query
that you do from the query object (tab under tables) becasue I can't define
variables nor enter "HAVING Count(*) >= X"

And, while I do somethings in visual basic. . . I have never seen these
statements. . .

The database is Pt List and the fields are:
ID (auto number)
DOS (short date)
MR No (text)
Pt Name (text)
HPICC (text)

Marshall Barton said:
Definitely need to create a query to do this. Once the
query is selecting the desired data, the report will be
easy.

Try using a query something along these lines:

SELECT MRN, Count(*) As NumVisits
FROM thetable
WHERE visitdate Between [startdate] And [enddate]
GROUP BY MRN
HAVING Count(*) >= X

You probably want more data than just the MRN, but I would
need to know which fields and their type before I could
decide the best way to add them to the query (memo field are
definitely an issue). How you define "last year" will need
to be refined as well.
Click to expand...
Click to expand...
 
Thanks. . . I changed a few things and it works great. . . thanks for your
help. . .


Marshall Barton said:
Yes, I did mean for you to create a query using the query
design window.

There are no variables in the query I posted (although the X
was (unknown to me) supposed to be replaced by something.
Your original question said you wanted to "limit the
printing of the report to only people who have had X number
of visits or more" so that's what the X I used was supposed
to represent. Similarly for the startdate and enddate. If
you leave them they way I wrote them, Access will prompt you
for the values of these **parameters** when you open the
query or the report.

Once you create the query, test it by opening it (from the
query design tool bar) and look at the data to see if it's
really what you need for the report.

Once the query is producing the desired data, the report
should be relatively simple.

From your latest post, I think the query will be:

SELECT [MR No], [Pt Name], HPICC,
Count(*) As NumVisits
FROM [Pt List]
WHERE DOS Between [startdate] And [enddate]
GROUP BY [MR No], [Pt Name], HPICC
HAVING Count(*) >= [X]

I'm still not sure that all three of those fields are
appropriate, but that should get you started. Just create a
new query (database window, query tab, New button), switch
to SQL view, then Copy/Paste the above SQL statement over
whatever Access automatically put in the query window. Try
to open it and go from there.
--
Marsh
MVP [MS Access]


I'm a tad bit confused. . . I assume that you aren't talking about a query
that you do from the query object (tab under tables) becasue I can't define
variables nor enter "HAVING Count(*) >= X"

And, while I do somethings in visual basic. . . I have never seen these
statements. . .

The database is Pt List and the fields are:
ID (auto number)
DOS (short date)
MR No (text)
Pt Name (text)
HPICC (text)

RGFlade wrote:
I have a report that lists all pts who came into the ED over the last year. .
. I would like to limit the printing of the report to only people who have
had X number of visits or more (they all get registered with a Medical Record
Number which is unique to them but is consistant through all their visits).
I cannot figure out how to have the report only print those people who have
multiple visits.
Click to expand...
Marshall Barton said:
Definitely need to create a query to do this. Once the
query is selecting the desired data, the report will be
easy.

Try using a query something along these lines:

SELECT MRN, Count(*) As NumVisits
FROM thetable
WHERE visitdate Between [startdate] And [enddate]
GROUP BY MRN
HAVING Count(*) >= X

You probably want more data than just the MRN, but I would
need to know which fields and their type before I could
decide the best way to add them to the query (memo field are
definitely an issue). How you define "last year" will need
to be refined as well.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top