Count records in a query

[Guest]

I have a table that tracks patients seen by the date.

I need to have a report that breaks down patients seen on each day for the
month.

Currently I have a query that asks for the mm/yy for the report.

I want to have a form or report that will further break that down by day.

So the final look will be something like this:

Day 1 2 3 4 5
Total 10 35 23 23 32

Is there an easy way to do this?

 

[Guest]

In your current query, create a field that extracts the day from your date
field. Do this with the Day function. Something like:

TheDay: Day([YourDateField])

Save the query. While in Query on the Objects bar, go to New, Crosstab
Query Wizard and use your previous query as the record source. The Day()
field will be the column headers and you'll want to do a count. Run the
crosstab and see if the results look right.

Now comes the interesting part if you want to use this crosstab for the
record source of a form or report. If you don't have data for a date, the
crosstab won't create a column for it. Therefore the report will choke. One
way to fix this problem is to open the crosstab query in design view; right
click in the area near the tables; and select Properties. Next go into the
Column Headings and put in:

1,2,3,4,5,6,..... up to 31. That way the crosstab will create a column even
if there is no data. Sure it will show 31 days in February, but the data will
be blank.

 

[Guest]

I can see how this would work. However....in the date field of my orginial
query I have [Enter Date (mm/yy)]. When I run the crosstab query it says it
does not recognize this expression.

I want the user to be able to enter the month and year for the monthly report.

How do I do that while still being able to use the crosstab query?

In your current query, create a field that extracts the day from your date
field. Do this with the Day function. Something like:

TheDay: Day([YourDateField])

Save the query. While in Query on the Objects bar, go to New, Crosstab
Query Wizard and use your previous query as the record source. The Day()
field will be the column headers and you'll want to do a count. Run the
crosstab and see if the results look right.

Now comes the interesting part if you want to use this crosstab for the
record source of a form or report. If you don't have data for a date, the
crosstab won't create a column for it. Therefore the report will choke. One
way to fix this problem is to open the crosstab query in design view; right
click in the area near the tables; and select Properties. Next go into the
Column Headings and put in:

1,2,3,4,5,6,..... up to 31. That way the crosstab will create a column even
if there is no data. Sure it will show 31 days in February, but the data will
be blank.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a table that tracks patients seen by the date.

I need to have a report that breaks down patients seen on each day for the
month.

Currently I have a query that asks for the mm/yy for the report.

I want to have a form or report that will further break that down by day.

So the final look will be something like this:

Day 1 2 3 4 5
Total 10 35 23 23 32

Is there an easy way to do this?

 

[Guest]

Try putting the date field with the Day() function in another field. Also
crosstabs do have problems with parameters. Often you must go up to Query,
Parameter and explicitly define the parameter and data type.

Plus it just might be that your parameter verbiage had special characters in
it and sorta looks like it's using the Date function inside of it. I must
admit to never seeing this kind of problem though.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I can see how this would work. However....in the date field of my orginial
query I have [Enter Date (mm/yy)]. When I run the crosstab query it says it
does not recognize this expression.

I want the user to be able to enter the month and year for the monthly report.

How do I do that while still being able to use the crosstab query?

In your current query, create a field that extracts the day from your date
field. Do this with the Day function. Something like:

TheDay: Day([YourDateField])

Save the query. While in Query on the Objects bar, go to New, Crosstab
Query Wizard and use your previous query as the record source. The Day()
field will be the column headers and you'll want to do a count. Run the
crosstab and see if the results look right.

Now comes the interesting part if you want to use this crosstab for the
record source of a form or report. If you don't have data for a date, the
crosstab won't create a column for it. Therefore the report will choke. One
way to fix this problem is to open the crosstab query in design view; right
click in the area near the tables; and select Properties. Next go into the
Column Headings and put in:

1,2,3,4,5,6,..... up to 31. That way the crosstab will create a column even
if there is no data. Sure it will show 31 days in February, but the data will
be blank.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a table that tracks patients seen by the date.

I need to have a report that breaks down patients seen on each day for the
month.

Currently I have a query that asks for the mm/yy for the report.

I want to have a form or report that will further break that down by day.

So the final look will be something like this:

Day 1 2 3 4 5
Total 10 35 23 23 32

Is there an easy way to do this?

 

[Guest]

Thanks, you just ended the month long tirade against my computer!

Try putting the date field with the Day() function in another field. Also
crosstabs do have problems with parameters. Often you must go up to Query,
Parameter and explicitly define the parameter and data type.

Plus it just might be that your parameter verbiage had special characters in
it and sorta looks like it's using the Date function inside of it. I must
admit to never seeing this kind of problem though.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I can see how this would work. However....in the date field of my orginial
query I have [Enter Date (mm/yy)]. When I run the crosstab query it says it
does not recognize this expression.

I want the user to be able to enter the month and year for the monthly report.

How do I do that while still being able to use the crosstab query?

In your current query, create a field that extracts the day from your date
field. Do this with the Day function. Something like:

TheDay: Day([YourDateField])

Save the query. While in Query on the Objects bar, go to New, Crosstab
Query Wizard and use your previous query as the record source. The Day()
field will be the column headers and you'll want to do a count. Run the
crosstab and see if the results look right.

Now comes the interesting part if you want to use this crosstab for the
record source of a form or report. If you don't have data for a date, the
crosstab won't create a column for it. Therefore the report will choke. One
way to fix this problem is to open the crosstab query in design view; right
click in the area near the tables; and select Properties. Next go into the
Column Headings and put in:

1,2,3,4,5,6,..... up to 31. That way the crosstab will create a column even
if there is no data. Sure it will show 31 days in February, but the data will
be blank.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a table that tracks patients seen by the date.

I need to have a report that breaks down patients seen on each day for the
month.

Currently I have a query that asks for the mm/yy for the report.

I want to have a form or report that will further break that down by day.

So the final look will be something like this:

Day 1 2 3 4 5
Total 10 35 23 23 32

Is there an easy way to do this?