Query on many-to-many

[mnevitt]

Ok I have looked all over the forum for this but can't find a good answer. I have seen mention of a UNION query but not sure how and if that would work here.

I have a from with multiple combo boxes to select criteria for a report. The below SQL statement works great but for records that have multiple Activities or multiple Types, I get the parent record (Location) multiple times. I hope that makes sense.

SELECT tblLocation.*, tblCommunity.comm_name, tblBoro.Boro_name, 
tblLocation_type.loc_type_name, tblActivities.act_name
 
FROM ((tblCommunity INNER JOIN (tblBoro INNER JOIN tblLocation ON 
tblBoro.Boro_id=tblLocation.loc_boro) ON 
tblCommunit.comm_id=tblLocation.loc_comm) INNER JOIN (tblActivities 
INNER JOIN tblLINKloc_act ON tblActivities.act_id=tblLINKloc_act.act_id)
ON tblLocation.location_id=tblLINKloc_act.location_id) INNER JOIN 
(tblLocation_type INNER JOIN tblLINKloc_type ON 
tblLocation_type.loc_type_id=tblLINKloc_type.loc_type_id) ON 
tblLocation.location_id=tblLINKloc_type.location_id
 
WHERE ((([comm_name]=forms!frmRptSelect!cmbComm OR forms 
frmRptSelect!cmbComm Is Null)=True) AND (([Boro_name]=forms 
frmRptSelect!cmbBoro OR forms!frmRptSelect!cmbBoro Is Null)=True) AND 
(([loc_type_name]=forms!frmRptSelect!cmbType OR forms!frmRptSelect 
cmbType Is Null)=True) AND (([act_name]=forms!frmRptSelect!cmbAct OR 
forms!frmRptSelect!cmbAct Is Null)=True));

For example, I pick a community, a boro, and a type. If the returned records have multiple activites attached to it then i get that record multiple times for each activity. It does the same thing if you select an activity and no type. Activity and Type are my two many-to-many relationships/tables. How can i get it to give my just the records that fit in the select community, boro and type regardless of the activitys involved?

Hope i explained it well enough... Thanks

Mike

 

[georgedwilkinson]

You didn't really tell us what you want, so I'll answer the only question you asked:
Don't include a reference to the activities anywhere in your SQL statement.

 

[mnevitt]

Sorry. The report is generated from a form that has 4 combo boxes. I want the user to be able to select one or all 4 to generate a report. So if it works they should be able to look at all records in a community, records in a community within a boro, records in a community within a boro that are a particular type....so on and so forth... any combination of the combo boxes should work.

As it is right now if i pick a community and nothing else, i get every location in that community returned multiple times (a returned record for each item in the activity table it has and a record for each item in the type table it has).

So if Bob's Bodega at 123 main st is in community A and has the type of business and residence and activites of fundraising and criminal activities.... when i select community A, i get 4 records returned for Bob's Bodega.

That is my issue and what i am asking for help with. I would love to put a copy of the DB up here for you guys to look at but i work in a place that has a closed intranet and no means for pulling files off of it. If i need to explain anything better please let me know.

 

[georgedwilkinson]

I'm still not exactly sure what you're doing (probably my fault, not yours).

If you have 4 different reports you should literally create 4 different reports with 4 different queries. Do not include tables in the query that have nothing to do with the data you are reporting on. Use "group by" (in Access help) to make rows unique.

Have you been using the report wizard?

 

[mnevitt]

It's not 4 different reports. I guess it is more like a dynamic report that is created with the criteria (the combo boxes) you select. The goal is for the user to select a general picture of the data or drill down to something more specific by select more than just the top level (most broad section).

If I created a different report for each option it would 100's of reports. That is what i am trying to prevent.

Let me see if I can recreate this part of my DB on my "internet" system vs. my intranet so you can see better what's going on.

As confusing as it is, I really appreciate you trying to help.