Weekly Charts in Reports

N

Neil M

Hiya all,

I have yet another problem I can't seem to get my head around.

Here's the problem...

Part of the same database is a system to book of rooms dependent on days.
The database works fine and I can print normal 'listing' type reports which
will make do for now.

But what I really would like to do is print off a report where it has the
times and dates like a chart
(like below;)

ROOM (header)

DATE DATE DATE DATE DATE DATE DATE
(Mon > Sun)
TIMES
in hours <NAME>
here
from
8am to
8pm?

Get my drift, so the block of staff names appear in the times theyt have the
room booked.

Anyone got any suggestions on how to do this. I have found an example
database for this sort of thing (Doctors, etc) but I can't seem to get my
head around the rather complicated coding and reporting. I need to
understand how its done not just how to do it.

Thanks,


Neil
 
S

SusanV

Hi Neil,

I think if you use as the report source a crosstab query you'll be able to
get the report you're looking for.
 
S

SusanV

Create a query containing all the data you will need to display, either from
a table or tables, or an existing query. Save it and while in Design View,
change it from a select query to a Crosstab query either in the Query Menu
or from the toolbar.

Choose "Row Heading" for the times
Choose "Column Heading" for the dates
Choose Value for the Name, and set the "Total" option from GroupBy to Either
First or Last.

Open the query in datasheet view and you should see the layout you are
looking for. Save the query, and use that query as the data source for your
report.

Here's a link to a site with a tutorial in case I wasn't clear:
http://www.jmu.edu/computing/ittraining/tutorials/microsoft/access/queries/crosstab.shtml
 
N

Neil M

thanks. got the crosstab query done and it appears to be running ok but
when i link it to my report it states that it doesn't recognise 'staff' as a
valid field name or expression.
any suggestions on how (lame man's terms) i link it to the report - as the
report field name list does not have staff on it.

Thanks,


Neil
 
S

SusanV

What field is using "Staff" as a record source (view via Properties of
fields)? What is the equivalent field in your crosstab query? Change the
source to the appropriate field.
 
N

Neil M

Right here the layout -
I got a report named REPORT which is linked to "Qry_Report" query

Here is the SQL for the Query;
TRANSFORM First(TBL_Main.Staff) AS FirstOfStaff
SELECT TBL_Main.Date, TBL_Main.Room
FROM TBL_Main
GROUP BY TBL_Main.ID, TBL_Main.Date, TBL_Main.TimeIn, TBL_Main.Room
PIVOT TBL_Main.TimeOut;

Is there a special way to display the times, staff (names) and other info on
the REPORT, as it appears that if I select field list on the report design
only the actual dates and data is shown and not the field names that I can
recognise like Staff, TimeIn, TimeOut, etc..

Hope this explains it a little better.
 
N

Neil M

the report is now display (i created a new report to be sure) but it still
leaves me with the problem where I want to display the date/times in chart
form (weekly format)
at the moment its just a list (which I can do without the extra coding)

Any suggestions or ideas on how I would do this?

Neil
 
S

SusanV

Hi Neil,

For the "staff" problem, change the AS part of the query to whatever you
like - AS simply is giving the calculated field a name. Perhaps:

TRANSFORM First(TBL_Main.Staff) AS StaffName
SELECT TBL_Main.Date, TBL_Main.Room
FROM TBL_Main
GROUP BY TBL_Main.ID, TBL_Main.Date, TBL_Main.TimeIn, TBL_Main.Room
PIVOT TBL_Main.TimeOut;
 
S

SusanV

Is the layout correct? Are you just looking to change the column and row
names or the data in the cells?
 
S

SusanV

I'm sorry - I didn't realize there was something you wanted me to view -
uploaded it where?
 
S

SusanV

Sorry but I don't view this via the web - I use a newsreader so the
attachment is not available. Do you have a link I could follow?

Susan
 
S

SusanV

If you uploaded it to the forum, I should be able to get it there - which
forum? Can't you send a link?

:)
 
S

SusanV

Good morning Neil,

I went through all the previous posts in this thread and saw no attachment,
but no bother - I downloaded your db and took a look. I changed the layout
of the query and think I have what you're looking for - I've emailed you the
zipped mdb so you can see the actual query and report layout.

Here's the SQL I used for the Crosstab Query:

TRANSFORM Last(TBL_Main.Staff) AS LastOfStaff
SELECT TBL_Main.Room, TBL_Main.TimeIn, TBL_Main.TimeOut
FROM TBL_Main
GROUP BY TBL_Main.Room, TBL_Main.TimeIn, TBL_Main.TimeOut
PIVOT TBL_Main.Date;

The output in the report, ordered by Room and then by Time In:

Room TimeIn TimeOut 1/1/2005 1/3/2005 1/4/2005 2/4/2005
_______________________________________________________
Common 9:00 10:00 NEIL NEIL
Common 10:00 12:00
NEIL
JobShop 9:00 10:00 NEIL NEIL
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top