Hi, I am re-asking this question because my post did not show up for some reason. I have a many to many table relating a contractor's table with a project's table. There is also a ownertype field with three choices: Owner, Contractor, Owner/Contractor. Is there some way to create a query without repeating records when a project has both a Owner and a Contractor? In the same query I want to have a Contractor's Field and Owner's Field.
Many to many query problem
- Thread starter Jeanette
- Start date
Jon K, I have a Borrower/Contractor table with Company name and several address fields. I have a Projects Table with project name and several fields about that project. Because a project can have many different contractors and a contractor can work on many different projects, I have ConPrjt table joining the two tables together in a many to many relationship. In this table In addition the two primary key fields from each table there are two other fields. A OwnerType field has data describing in what capacity the Owner is working on a project, such as Owner, Owner/Contractor, Contractor, Owner/sponsor, or Sponsor. There is also a Contact field with 1 or 2 when there is more than one owner or 3 or 4 when there is more than one contractor. I want to create a query that has a field called Contractor containing the company's name and I want another field called Owner containing the company's. When the OwnerType field is "Owner/Contractor" and Contact Field is "1", I want the Contractor field to say "Same" or else the company's name if Ownertype field is "Contractor". My problem is when there is a "Owner" and a "Contractor" I get repeating fields. How do I get the query not to repeat fields? I hope this is clearer. Thanks in advance.
I think it maybe pretty easy
When you say repeating do you mean the record repeats exactly?
If so its really easy to have it not repeat. Just hit the SQL button when you are in the design mode. This will pull up a white sheet with the SQL code on it.
You need to change the code. After the word SELECT, you need to add the word DISTINCT (e.g. your SQL should now read SELECT DISTINCT. . . . instead of SELECT. . . .)
Thats it. This will only work if every field in the record is exactly the same. If this is the case SELECT DISTINCT will only pick one record. . . .
When you say repeating do you mean the record repeats exactly?
If so its really easy to have it not repeat. Just hit the SQL button when you are in the design mode. This will pull up a white sheet with the SQL code on it.
You need to change the code. After the word SELECT, you need to add the word DISTINCT (e.g. your SQL should now read SELECT DISTINCT. . . . instead of SELECT. . . .)
Thats it. This will only work if every field in the record is exactly the same. If this is the case SELECT DISTINCT will only pick one record. . . .
What I mean by repeating records is that all the output comlumns are repeated when the Owner Or Contractor are separate entities. I get two records for each project. The record for owners has Owner's field that has the the company's name but the Contractor's field is empty. The record for contractors repeats the same info except for the Contractor's field which has the contractor's name and the Owner's field is empty. In this situation Select Distinct does not work.
