Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Appointment report

Status
Not open for further replies.

funkytunaa

Technical User
Oct 9, 2005
116
AU
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!!!!
 
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
 
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.



 
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.
 
If you always want the next 14 days beginning today, the query SQL view for the crosstab will be:
Code:
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
 
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!!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top