Access Reports

[dj4uk]

You can download a copy of the database from: www.hrocupload.co.uk/dj4uk/Timebase.mdb

I need some help with reports. If you take a look at the report "Detailed Costings Grouped by Month" you can see it produces a report with total hours for each job for each member of staff. This is fine but I'd also like to add a total for the entire job like so:

Staff Member Total Hours on this Job
Debbie 15.5
David 5.5
Total: 21

Has anyone got any idea how I could do this?

Regards

DJ

[doctorgonzo]

Add a footer for the Job grouping. Copy the totalDuration textbox from the Detail section to the Job footer. Change the Control Source to "=Sum([totalDuration])"

That should do it.

[dj4uk]

I've got you with the "=SUM(totalDuration)" but how do I add a footer to the job grouping? I can't find anyway to do this.

DJ

[doctorgonzo]

Right click on the report and go to "Sorting and Grouping". Go to the Job line, and there are options that allow you to select whether each grouping will have a header or footer.

[dj4uk]

Fantastic it worked! Thanks!

Now I've just got to work out how to reformat a number into a currency format. Don't know of any good reference websites for access?

Thanks again.

DJ

[dj4uk]

Forget my last post - managed to format to currency.

Cheers!

DJ

[dj4uk]

OK new problem. This ones a bit difficult to explain.

I want to populate a textbox in a report with a value from a table query however I want the query to be conditional on the data in the report. Can anyone point me in the direction of how to do this? Can I reference report data in any way?

DJ

[doctorgonzo]

What data do you need from the report, and why can't you just put it in a query? Technically, you can reference data from an (open) report and use it elsewhere, but that is a pretty ineffective solution. Just use a query.

[dj4uk]

Well my report is built on a query but I can for the life of me get the additional field I need into the query - its a tad complex:

SELECT c.Client, p.Name AS Job,
p.JobNumber,
s.Full_Name,
w.Month AS MonthID,
m.Month,
w.Year,
(m.Month & " " & w.Year) AS FullDate,
SUM(e.Duration) AS totalDuration
FROM tbl_entry_log AS e,
tbl_week_numbers AS w,
tbl_staff AS s,
tbl_projects AS p,
tbl_clients AS c,
tbl_months AS m
WHERE e.Entry_date Between dateadd("d",-6,w.EndDate)
And w.EndDate And e.StaffID=s.UserID
And p.ProjectID=e.ProjectID
And p.ClientID=c.ClientID
And w.Month=m.MonthID
GROUP BY c.Client, p.Name, p.JobNumber, s.Full_Name, w.Month, m.Month, w.Year
ORDER BY w.Year DESC , w.Month DESC , c.Client, p.JobNumber;

I need to get the Maximum EndDate (MAX(w.EndDate)) for the monthID (w.Month).

Just ain't playing ball. I'll upload the database again if you need it.

DJ

[doctorgonzo]

Why don't you upload it again and I will play around with it.

[dj4uk]

Ok its up same URL as before.

Thanks for all your help on this by the way.

[doctorgonzo]

Okay, let me see if I understand this.

The query shows the Client, Job, JobNumber, FullName, MonthID (and then the Month), Year, FullDate (Month + Year), and the duration. You want to add the MaxEndDate for every month. So, for all the records in October, you want to have the MaxEndDate for month 10, for the records for September, you want to have the MaxEndDate for month 9, and so on. The MaxEndDate will be the same for every record with that month, so the MaxEndDate for the first four records in that query, because they are all in the month of October, should be the same (and 11/02/03, if I am seeing this right).

Is this correct?

[dj4uk]

Yep you got it!

[doctorgonzo]

Okay, then I think I have what you want.

First, I created a separate query that will generate the MaxEndDate. It is called qryMaxEndDate, and this is it:

SELECT Max(tbl_week_numbers.EndDate) AS MaxEndDate, tbl_week_numbers.Month
FROM tbl_months INNER JOIN tbl_week_numbers ON tbl_months.MonthID = tbl_week_numbers.Month
GROUP BY tbl_week_numbers.Month;

Then, I changed the Detailed Costings query to look like this:

SELECT c.Client, p.Name AS Job, p.JobNumber, s.full_name, w.Month AS MonthID, m.Month, w.Year, (m.Month & " " & w.Year) AS FullDate, Sum(e.Duration) AS totalDuration, qryMaxEndDate.MaxEndDate
FROM (tbl_projects AS p INNER JOIN (tbl_entry_log AS e INNER JOIN tbl_staff AS s ON e.StaffID = s.UserID) ON p.ProjectID = e.ProjectID) INNER JOIN tbl_clients AS c ON p.ClientID = c.ClientID, qryMaxEndDate INNER JOIN (tbl_months AS m INNER JOIN tbl_week_numbers AS w ON m.MonthID = w.Month) ON qryMaxEndDate.Month = w.Month
WHERE (((e.Entry_Date) Between DateAdd("d",-6,[w].[EndDate]) And [w].[EndDate]))
GROUP BY c.Client, p.Name, p.JobNumber, s.full_name, w.Month, m.Month, w.Year, qryMaxEndDate.MaxEndDate
ORDER BY w.Year DESC , w.Month DESC , c.Client, p.JobNumber;

You probably noticed that instead of all of the WHERE clauses, I used JOIN syntax instead. When you are using tables that have relationships between each other, it is better to use JOINs that WHERE clauses. However, the end result is the same.

[dj4uk]

I usually use the JOIN syntax but didn't have a way to join tbl_week_numbers. I didn't think of joining through tbl_months as I added this later and just as a lookup table. Amazing what getting another fresh pair of eyes does!

Also didn't realise you could join queries in Access - learn a new thing every day!

Thanks for that! I'll give it a go.

DJ

[dj4uk]

It worked perfectly thanks loads!

[dj4uk]

OK not quite perfectly - but this is my fault rather than a problem with the query.

I've made some changes to the report but when you view it there are extra pages with small bit of grey on them (look like the end of the client headings). Now they are there I can seem to get rid of them. Must be a width problem - but I can't find where.

I've uploaded the new version.

DJ

[doctorgonzo]

I'm not getting any extra pages. I only get three, and they all have info.

Very nice layout, by the way. It looks excellent.

[dj4uk]

You don't get extra pages? hmmmm weird - does it have the month ends bit right at the top of pages?

Thanks! Its the first report I've ever done so it been quite a learning curve.

[doctorgonzo]

Nope, no extra pages, and it does have the month end up in the upper right-hand corner.

It may be a page setup problem or a margin problem. Maybe if you reduced the margins slightly it would go away.

[dj4uk]

Ok I'll try that.

Thanks again for all your help - give me a shout if you are ever in Birmingham, UK - I'll buy you a drink!

DJ

[doctorgonzo]

Birmingham is a long way from St. Paul, but if I am ever there I will keep that in mind!