Showing Total Visits by Month

[LoriP]

I have a research database that I inherited and I need to find out how many
patients we saw in Jan. 07, Feb. 07 etc up to the present.
There are already queries for each month set up, that show me how many
records meet the criteria but how do I run a report or query that tells me
that we saw 5 patients in Jan, 6 in Feb, etc. I am using Access 2003.

Also please let me know if there is an easier way to get this information if
using/combining 15 different queries is not what I should be doing.
Thank you

 

[akphidelt]

Create a form or a report...

Insert a new textbox... in the textbox control source type

=Dcount("field","tableORquery","[field]='Jan'")

This is how it should be set up. If you have a separate query for each month
then you probably have a fairly bad setup going on. But if you don't want to
change it and as an example you have QueryJan and an ID field in the query
you would do.

=DCount("ID","QueryJan")

But you should have all the data in one query, create a column with an
expression like Month: =DatePart("m",[Date]) which will give you a month
number.

Then the dcount would be
=Dcount("ID","YourQuery","Month=1")

 

[Golfinray]

You need a table with patient names and visit dates. The use a form (the one
side) and subform (the many side) as a lookup. The form would look up
patients and the subform would show visit dates, With a form you can continue
to add patients and visit dates and it will save them in your table.

 

[Evan Keel]

Not knowing your table structure it's hard to say.
If your table resembles something like the following:

Visits(PatientID (PK). VisitDate (PK)

Then:

select format(visitdate,"MMM") ,
count(patientID)
from visits
group by
format(visitdate,"MMM")

will return the count of visits by month.

Hope this helps--

Evan

 

[Bob Quintal]

I have a research database that I inherited and I need to find out
how many patients we saw in Jan. 07, Feb. 07 etc up to the
present. There are already queries for each month set up, that
show me how many records meet the criteria but how do I run a
report or query that tells me that we saw 5 patients in Jan, 6 in
Feb, etc. I am using Access 2003.

Also please let me know if there is an easier way to get this
information if using/combining 15 different queries is not what I
should be doing. Thank you

A single query will do it all, and you can build a report on that
query.

To make that query, follow these steps.
Open a new query in design view
select your table.
Bring down the visit date field, and the patient ID or name)
Modify the visit date field name as follows.
Add the word Format( and the opening parenthesis to the left of the
field name.
Add , "yyyymm") to the right of the field name. (that starts with a
comma.
When you exit the field, Access will add Expr1: to the left of the
column. You can change that to Visit_Month:

Now Click on the Summation button in the toolbar (Looks like M on
it's side). A new row will appear in the grid, labeled Total: on the
left. Leave the Group By instruction under the date, and change the
Group By to Count under the patient name. Save and run the query.

 

[LoriP]

Bob, thank you for breaking it down into small steps for me. The trouble I'm
having now it that the database is set up so that there are 14 different
fields that I have to look through that could have a Jan. or Feb, etc visit.
The fields are "Scheduled Visit Date (Mo 1)" etc through Month 48 of the
research study. Can 1 query look through all those different fileds? Thank
you

 

[LoriP]

Well after deciding I had to get rid of all the spaces and parenthesis to
make your formula work, it did for at least month 1. Do I need to do the
same thing for each field now, Mo 4, Mo 8?
When I did Mo 1 I got:

CountofStudyID Visit_Month Scheduled Visit Date Mo4 SVDMo8 etc
1 200701 2/22/2007
6/6/2007
1 200701 3/28/2007
7/23/2007
1 200701 3/26/2007
7/25/2007
1 200702 4/12/2007

Do I then use Visit_Month in a report to count up the CountofStudyID so my
report says Jan 2007 Visits 3
If I do the same step for each field, how do I summarize the results from
each field to get one total?
This is making me crazy and any help is truly appreciated.
Lori

 

[Bob Quintal]

Bob, thank you for breaking it down into small steps for me. The
trouble I'm having now it that the database is set up so that
there are 14 different fields that I have to look through that
could have a Jan. or Feb, etc visit. The fields are "Scheduled
Visit Date (Mo 1)" etc through Month 48 of the research study.
Can 1 query look through all those different fileds? Thank you

Settle for 2? the first query would be a UNION query that stacks the
14, or 48 if necessary coliumns into 1.
General structure
SELECT Field1, field2 FROM table
WHERE field 2 is not null
UNION
SELECT Field1, field 3 FROM table
WHERE field 3 is not null
UNION
SELECT Field1, field 4 FROM table
WHERE field 4 is not null
UNION
SELECT Field1, field 5 FROM table
....

All subqueries must have the same number of fields, and the
datatypes in any column must match. I usually build the first
section in design mode, then switch to SQL mode, and copy, paste fix
field names for the rest.

then use query1 as the source for your totals query.