Reporting on a weeks worth of data

  • Thread starter Thread starter Caroline Middlebrook
  • Start date Start date
C

Caroline Middlebrook

Hi,

I'm working on a database for a drainage contractor. Part of the
database functionality is to be able to allocate staff to various jobs
that are running and then report on it. The way they work is to first
plan out jobs on a weekly basis and then go into more detail on a
daily basis. I have one table to represent the weekly plans which are
just an overview of the job being worked, the location they are at,
whether they are on a day or night shift and some other bits and bobs.
Then I have a table which represents a weekly plan which goes into
detail about which guys are working on which day, and what they are
doing.

The planning functionality works fine but I am having difficulties
when I try to report on it. For all of the reports, they want to see a
whole week at a glance, but they want to see the detail that is in the
daily table as well as the overview that is in the weekly table. The
way I have solved this up until now is to build an overview report and
then embdedd subreports to represent each day. Here's where I get
stuck. Each subreport represents a slice of data from just one day of
the week but I simply cannot find a way to tell each one only report
on a specific date. So what I have done is create seven different
subreports - Monday through to Sunday for each day of the week and
filter each one accordinly! It works but it seems crap. It wasn't too
bad when I only had one report like this but I now need to work on my
3rd - we're talking 21 subreports here when I'm sure there must be a
way to do it in 3!!

I hope I'm explaining myself properly. My problem seems to be that I
can't find a way to pass in a parameter to each instance of the
subreport. Any help would be greatly appreciated.

I'm using Access 2000 on Windows 2000.

Thanks,
Caroline M.
 
Caroline:

Why not add a field to your report called WeekDay, using the DatePart
function to return 1, 2, 3, etc. from the date field in your table. Then
you can use that field as a group by in your sub report, creating a single
sub report to encompass all seven days of the week.
 
Hi Caroline,

Not sure if I understand this correctly, but here goes
nothing. First and foremost, you need to have a field
between the Weekly and Daily tables that join the 2
together. If you have a field that uniquely id's a row in
the Weekly table - ie; weekly_job_id, then this should
also occur in the Daily table (which would be your foreign
key). As long as you have that, you're 80% there.

If the folks you're doing this for want to see a week at a
glance you would just need to join the tables via a left
join in access. You can specify this in the query grid
when you link your 2 tables together, click line that
joins the 2 tables and select join properties. This is a
much more efficient way to do what's being asked of you.
This way, there would be 0 subreports involved.

Hope this makes sense. Let me know if it doesn't.

Regards,
Jen
 
Not sure if I understand this correctly, but here goes
nothing. First and foremost, you need to have a field
between the Weekly and Daily tables that join the 2
together. If you have a field that uniquely id's a row in
the Weekly table - ie; weekly_job_id, then this should
also occur in the Daily table (which would be your foreign
key). As long as you have that, you're 80% there.
Click to expand...

Yup everything is joined okay. Thats not where I get the problem.
If the folks you're doing this for want to see a week at a
glance you would just need to join the tables via a left
join in access. You can specify this in the query grid
when you link your 2 tables together, click line that
joins the 2 tables and select join properties. This is a
much more efficient way to do what's being asked of you.
This way, there would be 0 subreports involved.
Click to expand...

I can create a query that gives me all the information and I could
group by date. Trouble is that they wish to see the week displayed
horizontally with Monday on the left of the page and Sunday on the
right so that the whole week shows up on a simple sheet of A4. Picky I
know, but I did try it the traditional way and it looked a bit crap :)

Does anyone know if it is actually possible to have the SAME subreport
displayed several times on one report with some kind of parameter or
filter limiting the data that is displayed? If it was, then I could
have just the one subreport with the data for the entire week, and
simply filter each one on the appropriate date.

Thanks,
Caroline M.
 
Back
Top