Change Merge Crosstab Queries

[corbimae]

I have a list of 20 hospital patients that I am trying to use to try and establish a list of supplies or services billed on Day of Surgery, Post Op Day 1, Post Op Day 2, Post Op day Three and Post Op Day 4. My problem is when I ran Crosstab Summaries on their accounts the column headings were the actual dates of services. The dates range from January 2014 to July 2014. Is there a way I can change the dates object to the text objects listed above and then merge the accounts so the items and quantities are tabulated. If I can't do this with Access I'll have to tabulate them manually by either creating a new table and data form or by setting up an Excel Spreadsheet.

Current Crosstab
Item # Description Total Qty Jan 1 Jan 2 Jan 3 Jan 4 Jan 5
201 Gauze 3 2 1
203 Misc 1 1

What I want is:
Item # Description Total Qty DOS POD1 POD2 POD 3 POD 4
201 Gauze 3 2 1
203 Misc 1 1


Keep in mind that there are 20 accounts with varying dates that have to combined into one table or query.

If someone could help me with this I would be grateful.

 

[MarkK]

You need to make the DOS available to the query somehow, and then calculate the day count as . . .

DayCount: ExpenseDate - DOS

. . . and then expand the criteria of the query to include all patients. You might also need to write a sub-query to prepare all this before you do the crosstab, so you base the crosstab not on a table, but on another query that collates everything you need.