MS Access Report question....

[jazaddict]

I realize this is stretching the topic a bit....please indulge me

I have an Airplane AAL1987 in the "Airplanes" table.
I have Criteria that airplanes meet in the "Criteria" table.

Theres a nicely functioning 1(Airplane) to many (Criteria) relationship established.

I also have Prompts that the airplanes say in the "Prompts" table.

Theres also a nicely functioning 1(Airplane) to many (Prompts) relationship established.

I have a query that essentially mulltiplies the Criteria recs and the Prompt recs of each airplane (3 different Criteria and 2 different Prompts results in 6 recs in the query)...

What I'm unable to accomplish is a report that looks like this:

Airplane1:
Criteria:
C1
C2
Prompts
P1
P2
P3

Airplane2:
Criteria:
C1
Prompts
P1
P2
P3
P4

The "grouping" wants to place all the prompts w/ every Criteria. So Airplane 1 above would show all the prompts twice for instance. OR ..... the grouping shows only 1 Criteria & each of the Prompts only once........very annoying

I'd like the report to show 2 independent groups for each airlpane....

Any suggestions?
Hmmmmmm........a "Union Query"?

[chrisJumbo]

I haven't worked with Access Reports in some time, but how about a Sub-report for the prompts?

[techmaniac]

With the information given, you are either going to need a JOIN query or correlated subquery.

[cinth]

I have a query that essentially mulltiplies the Criteria recs and the Prompt recs of each airplane (3 different Criteria and 2 different Prompts results in 6 recs in the query)...

I don't understand the reason for this query. If you have a relationship of Airplanes to Criteria, and a relationship of Airplanes to Prompts, essentially you want to query the Airplane table, Criteria table and Prompt table and then run your report from that. For the table relationship in the query, show all in Airplane only those that match in Criteria and Prompt. Group it only on Airplane, and show the Criteria and Prompts.

Did I misunderstand your intention? It might be more helpful if you showed your table structure.

[NJ]

The "grouping" wants to place all the prompts w/ every Criteria. So Airplane 1 above would show all the prompts twice for instance. OR ..... the grouping shows only 1 Criteria & each of the Prompts only once........very annoying

That is called Cartesian multiplication. It's a pain when it happens, but the fact that Criteria and Prompts don't have anything in common is causing your problem. Are you are trying to bring up Airplanes that have either Criteria or Prompts, both or all of the above?

Techmaniac is right - you have to do a JOIN, but it will be an interesting JOIN puzzle!

The problem with learning to use the tools in Access is that you don't learn what is really happening in the process. Then when you want something a little more complicated, you get weird results. I know because I used to teach Access at the University of Montana. It was like, "Make sure that you take them through these skills, but don't give the students time to learn what is really happening." So, I gave them a day of MySQL queries for extra credit.

[rwil02]

I haven't worked with Access Reports in some time, but how about a Sub-report for the prompts?

This is correct.
You need Either

Group Header - > Aircraft
Detail -> Criteria
Group Footer -> Aircraft
Subreport -> Prompts

Or

Detail -> aircraft
Subreport -> Criteria
Subreport -> Prompts

In either case, the master child relationship would be on the aircraft primary key.