Parameter Query Question

[cstanley]

Here's a question:

I have about 7 tables. Table #1 is the primary table, with a unique list of systems and unique system numbers. The other tables contain data that are associated attributes of the systems, but can apply to more than one system.

So, I want to structure a query where the user can enter a system number, and all the associated attributes will be pulled from each table into the query, which can then be used to create a report. Whenever I do this, I get a blank table. How best to do this>

Thanks,

Chris

 

[llkhoutx]

You're probably using equi-joins in your query. Use left joins.

[This message has been edited by llkhoutx (edited 05-29-2002).]

 

[Tonyi]

Sounds quite normal. (I used to get this a lot)
When I investigated, it was because there were no records which satisfied the query.
Another other effect is when you get a huge number of results. In this case the query does not have limitations, so it provides all possible combinations!

Some basics.
Have you set up relationships.
Does each auxiliary table have a reference back to the primary table, and each record of the auxiliary table hold a reference to a system number, (or ID) of the primary table.
The default relationship (join) is when both fields (one from each table) are equal. If they are not identical, (eg spelling error or name in one table joined to a autonumber in the other table) you will not get an output.

So basically,
1] Set up the relationships
2] Create the query. add all the tables and fields of interest. The relationshps set up will show.
3] check for valid data in the link fields.(check the tables).
4] In the field of interest insert the criteria.

If still having a problem, start with only the primary table, check for data, add a table, and check for data out again, then add another table etc.

It is not clear if the blank output is before or after inserting criteria. If after, check the criteria (system number) exists in the field being tested.
Hope at least some of the above is of use
Cheers

 

[Pat Hartman]

Unless the 7 tables represent a hierarchial relationship, you can't use a single query to gather the data.

To make your report, you'll need a main report for the system from table #1 and subreports for each of the other tables. Each report and subreport will be based on a separate query. The master/child links will keep the reports synchronized.