Data for 12 Months by Person

[MarieD]

How would I get 12 months of data laid out like this in a report? I can design the report, but how do I get the individual data from a query? Would I have to make 12 queries for each month? Then how do I get that information into one report?

...........Jan Feb Mar Apr
Name 1 276 260 270 280
Name 2 170 180 145 130
Name 3 300 325 285 290

 

[Alansidman]

Two thoughts: Try a crosstab query or try a pivot table to get your results to look like that.

Here is Allen Browne's explanation of crosstab queries.

http://allenbrowne.com/ser-67.html

and MS KB on Pivots

http://msdn.microsoft.com/en-us/library/aa139960(v=office.10).aspx

 

[MarieD]

The crosstab query gives me the layout I need. How do I get the criteria to bring in the count each month individually? I've tried entering the ([Confirmed Date])=Year(Date()) but that is not working. Is there a formular that will recognize the month I am in (when running the query) and put that count under the correct month for each physician?

 

[MarieD]

I've been working with this crosstab query and the data is exactly correct as the corsstab stands originally. My table has several years of data. How can I just pull out the months of year 2012?

 

[MarieD]

Thanks to everyone who was looking at this with me....I figured it out. I created a separate query for just year 2012 and then crosstabed that query. January is showing up now, so if in March, February will show up...I will be in business.

 

[vbaInet]

Your Column Header field will be Format([Confirmed Date], "mmm")

 

[vbaInet]

Thanks to everyone who was looking at this with me....I figured it out. I created a separate query for just year 2012 and then crosstabed that query. January is showing up now, so if in March, February will show up...I will be in business.

I didn't see this post before I responded. You could have put 2012 as criteria in the Year([Confirmed Date]).

 

[MarieD]

Putting Like "*2012*" in the Confirmed Date will not bring back every date listed. If there are two procedures dated 1/25/2012 for a physician and a particular patient, only one procedure is returned, making the count incorrect. Anyway to correct that in a query?

 

[vbaInet]

Have a read of my last post again. Note that Year() returns only the year portion of a date.

 

[MarieD]

Yes, but I need all of the procedures for 2012, even if the dates are the same for the same physician and patient. The Cross Tab is ignoring these, but I need all of them to be counted as well. How can I make the Cross Tab recognize every procedure listed in the query? Example, in the select query, Dr. Smith has a procedure count of 279, but the cross tab query is bring back a count of 264. Checking on why... the cross tab is not counting Dr. Smith, Patient Dooley, Procedure ECG, 1/25/2012 and Dr. Smith, Patient Dooley, Procedure Echo, 1/25/2012. It is bringing back a count of 1 instead of 2.

 

[vbaInet]

Do you have any other criteria apart from the Year part?

 

[MarieD]

No criteria in the cross tab query. 2012 is in the criteria in the Year ([Confirmed date]) column that the cross tab is pulling data from.

 

[vbaInet]

Is your crosstab query based on another query? If it is, does that query have any criteria? If the answer is No, upload a sample db and we'll take a look.