Appointment report

[funkytunaa]

I have a sql database that houses appointment data. I'm trying to create a report that will match up "required" appointments with actual appointments.

Currently I've written a query that counts the number of appointments for each date and returns the day name (ie. "Monday", Tuesday etc..tippin you know the rest), the count and the date, I've filtered this to only give me back the next couple of weeks, so I can get the actual number of apointments back, not a problem.

Where my problem is, I have another table that has the days of the week as titles for the field and a serial number that matches each person and in each field I have a number of "required" appointments for that day of the week.

I need to have a report that will have the dates for the next 2 weeks listed and beside that how many appointments are required and then how many appointments there actually are then the difference.

Hopefully I haven't confused anyone.

Cheers!!!!

 

[dhookom]

It would really help if we knew your table structures, data types, and could see some sample records and desired output. The first mistake I think you are making is "returns the day name". When you do this in a query, you have lost a ton of functionality that exists with an actual date value. Displaying the day name is the responsibility of the format of a control on your report.

Duane
Hook'D on Access
MS Access MVP

 

[funkytunaa]

What I have been working on has changed considerably.

At the end I need a report like this.

Person 05/01/10 06/01/10 07/01/10
Adam 4 3 0
geoff 0 3 2


With Start Date on the report being selected from a field on a main menu and the dates to the right automatically generate from that (easy part)

The query I have got returns data as such.

Person Date Require Actual Need
Adam 05/01/10 10 6 4
Adam 06/01/10 9 6 3
geoff 06/01/10 4 1 3
geoff 07/01/10 2 0 2

Thanks for your help! Appreciate it muchly.

 

[dhookom]

Check faq703-5466 for a dynamic crosstab report.

Duane
Hook'D on Access
MS Access MVP

 

[funkytunaa]

That seems to be just creating the crosstab report so it gives you columns to work with though the data that populates it comes from a table and as such, is return queried by rows. How do I populate the report?


Am I making sense? Or have I been thinking about this too much. I think I need break.

 

[dhookom]

If you always want the next 14 days beginning today, the query SQL view for the crosstab will be:

TRANSFORM Val(Nz(Max([Need]),0)) AS Expr1
SELECT Person
FROM qryFunky
GROUP BY Person
PIVOT "D" & DateDiff("d",Date(),[Date]) In ("D0","D1","D2","D3","D4","D5","D6","D7","D8","D9","D10","D11","D12","D13");

If you want a different date range, you will need to changet Date() in the expression to reference a control on a form.

Duane
Hook'D on Access
MS Access MVP

 

[funkytunaa]

Just thought I'd update on what the outcome was.

how I ended up doing it was I used a query to sum the actual appointment numbers, a query that looked up the appointments required and these both together with a query that gave the appointments needed.

From there, had a report that listed the "people" and got a date from a main form, and added dates across the screen for a week after the selected date.

I had a few fields on the report for each date with a dlookup that looks up the "person" cross refrencing the date from above and gives the "needed" figure in the corresponding column.

This does take about 10 seconds to fill the report with the dlookup, aaaaaand it's probably going about it all wrong, but at the end of the day.....it works!!!