1 report , multiple queries

[Wendy]

I'm not very experience in MS Access, but ZI have managed to create reports,
and queries, mostly via the Wizard. I have a report whose main source of
data is a linked MS Excel sheet. I created a query for Guaranteed Salaries
and another query for Variable Salaries, some people get both. So far so
good. I created a report from the first query (guaranteed salary) which add
up all sources (accounts) and does a percent calculation. This reports shows
one person per page with all compensation. At the bottom of each page I
would also like to show what their variable compensation for the year was,
but I can't pull data from a separate query, especially since both come from
the same linked Excel sheet. I tried just putting all comp in one query, but
then the total compensation includes the variable compentsation too, and I
want to have that separate. Any suggestions?

 

[Wayne-I-M]

Hi Wendy

I assume that each person has a unique ID (like an autonumber in a table or
something that you use to tell the difference beteen people)

You can create a second report based on the variable saleries and insert
this report into the main report as a sub-report. (hopoe that makes sence).
Use the ID (which should be on both reports as the linking control.

Select the subreport option option from the tool box (in desgin view of the
main report) and use the wizard to set up the report conection

 

[Wendy]

Thanks. Do I create the second report as any other report, or it is done
differenly? Alos, how do I link the to reports. The identifier will be the
"employee ID"

 

[Wayne-I-M]

Yes thas it. Just create the 2 reports and make sure that somewhere in each
report you have employee ID (just a point if you can I would change this name
to
employeeID - with the space - as this will make is simpler once you start to
write code).

So you have you 2 reports.
Open the main report in desgin view
Select view
Select toolbox
Select Subreport
Click somewhere on the main report - try at the bottom 1st to see what it
looks like
From the wizard drop down select the 2nd report
In the lists that appear select employee ID on both reports
Save and view

You can move the subreport around if you want
You can also refer to the subreport (you will need to ask another question
about this maybe) if you want to add the totals from the th subreport to any
thing on the main report etc etc

Best bet would be t et it up so it workd basicaly then get all the other
bits working afterwards.

Good luck - hope this helps