Using Calculation in REPORT

[maisam]

I am trying to create a report (rptHBSummary) that has all the fields in one table (tblJob) and an extra field that calculates the maximum value of a particular field (Percent) in a different table (tblJobStatus).

More detail: in this database jobs are inputted into the system (into tblJob) and then user can add status info on the job/s (notes while working on the job). each job can have number of status notes (stored in tblJobStatus and these to tables are linked by JobID). In each status note there is a field called 'Percent', which is changed every time there has been a progress on the job. This basically shows what percent of the job is done!

Now in this report I want all the job detail fields plus a field showing the maximum Percent value in tblJobStatus for each JobID.

i.e.

Job ID|| Entry Date|| ... ||Job Description|| Job Status
4 || 12/11/05 || ... || bla bla bla || 98 >>> (this will the max value of in Percent field in tblJobStaus for JobID 4)

6 || 13/11/05 || ... || bla bla bla || 100


I tried making a query to pull out this data for me but it didn't work, this is qryTest. I can get the max value of Percent for a given JobID using a query (done in qryMaxPercent) but I don't know how to do this in the report! I am new to Access and I would appreciate any help or suggestions, This is quite urgent and i'm starting to panic! Please help! Cheers (Database is attached!)

 

[MStef]

Hello misam!

Here it is, I think it is what you want.
Look at "rptMaxPercentSubreport", Query1Percent (SQL),
"rptHBSummary" (VBA).

 

[Krava]

Hi maisam!

I am not sure but I would do it e.g. like this: check attachment

 

[maisam]

Thanks for your help guys!

the db1.mdb didnt open for some reason! but the first suggestion works fine, exactly what i was looking for! cheers, appreciate the time you guys have put into this!

maisam