Query Many to Many to find single record (1 Viewer)

I am trying to use a query to search 2 many to many relationships but i keep getting duplicate results because each record is slightly different.

I have a table with cameras, another users and another formats. I have a table linking cameras to users and another linking cameras to formats so it looks somthing like this:

tblCameras - tblFormatlink - tblFormats
tblcameras - tblUserslink - tblUsers

When i add all the tables into a query i get loads of results as expected because i get a result for every possible combination.

What i want is when i search for all cameras that are suitable for a user who is a beginner is to get just a list of unique cameras. When i do that search at the moment i get a duplicate record for each different format the camera can do.

Is there a way round this?

Thanks
Jonathan

Post sample data as well as what results you want based on that sample data. Use this format:

Table1NameHere
Field1Name, Field2Name, Field3Name
John, 4/16/2002, 1
Sally, 7/19/2003, 4
Jim, 8/26/2001, 4

Table2NameHere
Field1Name, Field2Name
1, Beginner
2, Expert

There is a free video tutorial on resolving many to many relationships at
https://www.youtube.com/watch?v=7XstSSyG8fw

Thanks, that is really helpful. I have already created a junction table between my main table that contains all the camera information and another table that holds information about which type of user the camera is suited to which could be many. I have the same set up for the recording format, many cameras can record on many formats.

I have tried using a union query to group this info into one field however if i search two criteria, eg find a camera suitable for a beginner that records onto DVCAM, then if one camera meets both criteria it will display twice. As i do not want to actually display any information from the joining tables (i only want to search it) is there a way to only return one occurance of each camera even if more than one criteria is met?

Thanks
Jon

Keep your tables, but cut your query down to the minimum amount of fields you ABSOLUTELY NEED to display. Try strating with just your camera and just two other fields.

Then use "group by" i.e. click the sigma symbol (it looks like this: Σ Totals).

See if that doesn't get you closer to what you want.