Quering Three tables causes duplicates

[BatmanMR287]

I have a report to run that gathers info from three tables. If I make individual queries to get the information I need the three queries gather the correct information. If I make one query it makes duplicates. If I combine the three queries that work the main query mixes up the information and duplicates it. How do you make a query that gets information from three tables and not have it be garbled?

[June7]

This indicates the tables have one-to-many or many-to-many relationships. The 'duplication' and 'garbling' would be normal consequences of a query that joins tables with those relationships.

Options:

1. report/subreport arrangement

2. do aggregate queries and then join aggregate queries to each other or to a parent table

[BatmanMR287]

First table is a one, second is connected and is a many, third is connected to the second and is a many to many.
Policy Number 2120----------------Administrative Procedure for Policy1 and 2 -----------------Exhibit for Administrative Procedure 1 and 2.
This has policy name this has ap name this has date of revision and revision notes.
2120 name 1 2 and names for each date and revision. These are what is need from each table.
Only data on first tbl this has AP nums(1&2) and policy num this has policy num, AP numbers, and E numbers(1&2) notes and date.

Not sure how to do a report/subreport and print it.

[June7]

Sorry, none of that made any sense to me.

[BatmanMR287]

Ok, basically I have three table with info I want. the query to get info from all tables garbles and duplicates the data. The tables are set up correctly. How do you make a query that gets info from three tables. I have found no reference on the internet on how to do this. Not even anything that says it is or isn't possible.

[June7]

I already gave options for data output.

[BatmanMR287]

Aggregate is for numbers isn't it? I am gathering text, policy numbers, date, and notes for a report.

[June7]

Aside from numeric calcs (Sum, Avg, StDev) aggregate can count, get maximum/minimum (even text can have maximum/minimum).

If you need detail info, not aggregation, then use report/subreport (up to 7 levels I think).

[BatmanMR287]

I got it. Main table joined to second table as a one to many policyNum to policyNum. Second table joined to third table by TWO joins; PolicyNum to PolicyNum and ENum to ENum. No more duplicates.

[June7]

Glad you figured it out. Helps to understand the data.

[BatmanMR287]

This should have worked:
SELECT Policies.PolicyNum, Policies.PolicyName, PolicyE.ENum, PolicyE.ExhibitName, EUpdates.DateRev, EUpdates.ERevNotes
FROM PolicyE INNER JOIN (Policies INNER JOIN EUpdates ON Policies.PolicyNum = EUpdates.PolicyNum) ON (Policies.PolicyNum = PolicyE.PolicyNum) AND (PolicyE.PolicyNum = EUpdates.PolicyNum)
WHERE (((Policies.PolicyNum)=[Forms]![EViewOnlyFrm]![Combo2]));
This actually worked:
SELECT EUpdates.PolicyNum, Policies.PolicyName, EUpdates.ENum, PolicyE.ExhibitName, EUpdates.DateRev, EUpdates.ERevNotes
FROM Policies INNER JOIN (PolicyE INNER JOIN EUpdates ON (PolicyE.PolicyNum = EUpdates.PolicyNum) AND (PolicyE.ENum = EUpdates.ENum)) ON Policies.PolicyNum = PolicyE.PolicyNum
WHERE (((EUpdates.PolicyNum)=[Forms]![EViewOnlyFrm]![Combo2]));
No examples anywhere on the internet. Found it by trial and error and logic process.