Simple Report Problem? 1

[ajhts]

I can't seem to find an answer to this, seems simple to me but I am a beginner. I have a very simple table, query, and report. I need to create a service log that should look like this. I do have it grouped by CPT code.

The report should look like this;

CPT Code Day 1 2 3 4 5
97110 10 15 17 10 2
97115 12 0 5 5 1

Instead, this is what I am getting as I try to get them to stay grouped, yet print across the screen under the appropriate day.

Error report is looking like this;

CPT Code Day 1 2 3 4 5
97110 10
97110 15
97110 17
97110 10
97110 2

97115 12
97115 0
97115 5
97115 5
97115 1

As you can see, the concept is there, it is putting the correct numbers under the correct day, however it is printing them as separate lines and not combining them onto 1 line per CPT Code. I can't figure this one out.

I could really use the help here if it can be done. I have messed around with columns but that keeps getting me further away. From what I can see, my grouping is working fine, my days and numbers are working fine, the only problem is the way it is displaying on the report.

Thanks
AJ

 

[dhookom]

The summary can be done in the query your report is based on. You can try create a query with SQL like:

SELECT [CPT Code], Sum(Day1) as Day1, Sum(Day2) as Day2,...
FROM
tblYourTable
GROUP BY [CPT Code];

Do you actually have separate fields for each day? Do you understand that this is somewhat un-normalized?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ajhts]

I will give that a try. No, I do not have separate fields for each day. The problem is, it is a healthcare form they want created from a simple data entry screen.
My fields are as follows;
cpt code
day
minutes
They wanted it basic like this but yet it will flow into the form broken out by cpt code and listing the days across in the appropriate boxes. Very confusing and not normal. Thanks for your help, will let you know if it works!

AJ

 

[ajhts]

Sorry, it didn't work. If I use separate fields for my days, example day 1, day 2, day 3, etc.. it will work fine because it already is in the same row.

They do not want it that way. They want the data entry screen to look like this
CPT Code
Day Number
Minutes

Once this is filled in, then they want the report to show
cpt code day 1 2 3 4 5
97110 10 20 30
97115 15 20 20

I am not even sure that this can be done?
THanks
AJ

 

[dhookom]

Please provide your table and field names as well as a few sample records. Then provide how you would like to display the same data in a report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ck11]

You could use a combobox on your form for the user to select which day and then be able to have the day selected in the combobox make the hours go to that particular field

For example:

if me.combo1 = 1 then [fieldname] = txtbox2

 

[ajhts]

Table:
Patient
Day
CPT
Description
Minutes

My Form is setup the same way as the table. They will enter the data as follows:
Patient: John Doe
Day: 2
CPT: 97110
Description: ADL
Minutes: 20

Then lets say the next record that they enter is:
Patient: John Doe
Day: 3
CPT 97110
Description: ADL
Minutes: 20

Another record different CPT
Patient: John Doe
Day: 1
CPT: 97115
Description: Hip
Minutes: 10

Now the tricky part. I need my report to show these records grouped by cpt and on the same line going across as shown below: (the minutes for the day will show up under the day number)

Patient: John Doe

CPT Description Day> 1 2 3 4
97110 ADL 20 20
97115 Hip 10

I hope this helps explain it better. I know it seems a little weird, but they want to keep this same report (Service Log) and have them only enter into the fields that I have listed on the table. Thanks so much for your time.

AJ

 

[dhookom]

You need a simple crosstab query with a Row Headings of [Patient], [CPT], and [Description]. The Column Heading is [Day] and the Value is Sum of [Minutes]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ajhts]

I did the simple crosstab query and it works great! You helped out tremendously!
They added one more trick to this. They want a field called units. I noticed in the crosstab query, you can only have 1 value. It will not let me have minutes and units. Here is my example;
Table and Form:
Patient
Day
CPT
Description
minutes
units

Example of a record would be:
Patient: John Doe
Day: 1
CPT: 97110
Description: ADL
Minutes: 20
Units: 1

The report would now look like this:

Patient: John Doe

CPT Description Day> 1 2 3 4 5
97110 ADL 20 30
3 6

If you can't answer this, I understand. You have been extremely helpful to this point!

 

[dhookom]

MS suggests two crosstabs that get combined into a single select query. I prefer the method I describe in faq701-4524.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.