I am new to access. I have a report with a query(Q1) as its data source. Is it possible to use another query(Q2) only for one field in the same report?
My main query is:
SELECT PersonTotalHours.*, Person.* FROM PersonTotalHours INNER JOIN Person ON PersonTotalHours.LastName = Person.LastName;
My report’s structure is like this:
Report header _____________ Page header _____________ Lastname header _____________ report details _____________ Lastname Footer _____________ PageFooter
As you can see, in the report I group my data using the Lastname column then I show details about each peron for current year.
I need to show short form of data about former years of each person in the Last name header (somewhere before the detailed data).
Second query is like this:
SELECT PersonTotalHours.MA, PersonTotalHours.Year, Sum(PersonTotalHours.Hours) AS Sum FROM PersonTotalHours GROUP BY PersonTotalHours.MA, PersonTotalHours.Year
I use this for short form of data.
Important point is that the number of rows can be different. Person A might have 0 previous years and another person has more than 5.
How is it possible to use the second query for parts of report data?
Advertisement
Answer
I solve the problem using a subreport.
http://www.simply-access.com/Multiple-Queries-in-Report.html