Complex query used in report

[Meena]

Hi,

can someone tell me if there is a way to summarize a report based on a query? If so can somen tell me how to build a query that takes in information from 4 tables that have one field in common (User ID), and produce a report that summarizes all the information related to that User ID?

 

[Chris RR]

Create the query first. You'll need to join the four tables in the query. Remember that there are different kinds of joins. You will select the userid by putting a value in the Criteria row for your main table (look up Parameter Queries in Help, to see how you can have the query call for this value at runtime.)

To summarize data in a query, you need to click on the "Totals" icon (it's a greek letter, looks like half an hourglass). This gives you a Totals row (in query design view) that you can use to sum, count or whatever.

When the query contains all the info that you want, summarized the way you want, save it, and build a report based in the query to pretty up the data.

 

[Meena]

Hi,

I tried doing that but the output doesn't produce what I really want.

the four tables that I have are: Table 1 is the one side and other tables are the many sides of the table1. its one to many relationship.

Table1:
*UserID
Location

Table2:
*UserID
*Tabletwofield

Table3:
*UserID
*UserGroup
*Folder
*Type of Access

Table4:
*UserID
*Project Number

Since a user can be working on more then one project and could have access to more then one folder, theres more then one row for the user in table 2, 3 , and 4. what I want to do is summarize all the information about all the users in one nice report. How can I write this query. I tried doing what You had told me to, but some of the users whose information was entered, didnt show up in the query.

 

[Pat Hartman]

You cannot combine these four tables in a single query. The resulting recordset would make no sense. Tables 2, 3, and 4 each have a 1-to-many relationship to table 1, but they have no relationship to each other.

To produce a report that combines all this data in a sensible way use a mainreport to show data from table1 and create 3 subreports. One for each of the other tables and place the subreports in the detail section of the main report. The master/child links will keep the subreports in sync with the main report.

 

[Meena]

thank you for explaining. now it makes sense cause some of the items were on one table and not the other and my query was join query and didnt produce any results. Thanks again,