Return all records many to many

[Lorna_Jane]

Hello,
I have several many to many relationships. Example:
CatchmentTable: CatchmentID, WasteID, Area

WasteSiteTable: WasteID, Location
WasteLookup: CatchmentID, WasteID

DrainageTable: DrainID, DrainValue
DrainageLookup: DrainID, CatchmentID

I would like to return a table with ALL records from the catchment table,
the sum of DrainValue for each unique CatchmentID, and the count of WasteIDs
for each CatchmentID.
I also have several tables relating one to one to CatchmentTable that I
would like to include in the query.

Any help is appreciated.
Thank-you

 

[Jeff Boyce]

If you open the query in design view, you can change the type of joins
between those tables. You are describing returning ALL of the first table
and ANY of the related records in the other tables. Click on the line
joining the table(s) and select the appropriate type of join.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.