CODEWITHSUNDEEP
mysqlms-access
Andrew Tsay
Andrew Tsay

Reputation: 1953

MS-ACCESS How to create query to select all records with fields matching from another query?

I know this question may sound confusing, but let me try to simplify it.

I have a query, let's call query1, and a much larger table of all products. Here is query1:

Item_Code    Description    Order_Qty    Option
1000         Prod1          5            Blue
1005         Prod5          3            Brown
1602         Prod6          1            Red
5620         Prod8          6            Yellow
9865         Prod2          1            Brown
1624         Prod3          3            Brown
9876         Prod12         4            Blue

Now in my table, I have a much bigger list of products with the same format. I want to make a new query that contains ALL that are blue, brown, red, and yellow. It works, but there is always a duplicate.

I'm not sure how to post my attempt but I'll explain what I tried. I made a new query and included the table and query1. I made a relationship between the two, to include only rows where the "option" is equal. But for some reason the resulting query will come out with repeats. Such as:

Item_Code    Description    Order_Qty    Option
1000         Prod1          5            Blue
1009  <--    Prod2          6            Blue    
1009  <--    Prod2          6            Blue    
1010  <--    Prod9          7            Blue    
1010  <--    Prod9          7            Blue    
1011  <--    Prod11         9            Blue    
1011  <--    Prod11         9            Blue    
9876  <--    Prod12         4            Blue    
9876  <--    Prod12         4            Blue
1005  <--    Prod5          3            Brown
1005  <--    Prod5          3            Brown
9865  <--    Prod2          1            Brown
9865  <--    Prod2          1            Brown
1624  <--    Prod3          3            Brown
1624  <--    Prod3          3            Brown
9877         Prod99         7            Brown
1111  <--    Prod67         8            Brown   
1111  <--    Prod67         8            Brown    
1602         Prod6          1            Red
1752         Prod56         2            Red
5620         Prod8          6            Yellow

And the worst part is, it won't always repeat. Maybe I'm approaching it wrong.

I know this may be a case of tldr, but if anyone could help that would be great.

Thanks.

Upvotes: 0

Views: 2379

Answers (3)

user9166085
user9166085

Reputation: 33

If I understand your question correctly, you want to use Query1 as a type of lookup table to select rows from Table.

So you could do:

select * from table
where color in 
(select color from query1)

If you want to make the selection criteria based on 2 fields you could do:

select * from table
where color1&color2 in 
(select color1&color2 from query1)

If you want to look for omissions, you could do:

select * from table
where color not in 
(select color from query1)

Note that this only selects data from table. It doesn't do any joins between table and query1.

I think the others are correct. You are getting weird duplicates with a join because you have multiple rows for the join item in each table.

Upvotes: 0

spencer7593
spencer7593

Reputation: 108400

Sounds like you need to add a GROUP BY clause or a DISTINCT keyword to the query.

SELECT DISTINCT Item_Code, Description, Order_Qty, Option

(or click on whatever option in Access does the same thing.)

It's impossible to diagnose why the query is returning "duplicate" rows given the information you've provided.

The query is returning "duplicate" rows when a row in all_products is matching two or more rows from query1, so you get a copy of the row from all_products for each matching row from query1.

You might get better performance if you got a distinct list of Option from query1. (I don't really "do" MS Access. I brought up the question because it was tagged "mysql". The Jet database engine is cool and all, but it just doesn't work well in a multiuser environment.) In SQL Server, Oracle, MySQL, DB2, Teradata, et al. we'd write the query something like this:

SELECT p.Item_Code
     , p.Description
     , p.Order_Qty
     , p.Option
  FROM mytable p
  JOIN ( SELECT q1.Option
           FROM query1 q1
          GROUP BY q1.Option
       ) q
    ON q.Option = p.Option

Upvotes: 2

Brad
Brad

Reputation: 12255

Your query is returning duplicates because you are probably only selecting columns form one table but your join is not specific enough so the Cartesian product generated multiplies your results. I.e. because your only join is option = option that means every single blue will join to every other blue. You probably want more restraints in your ON clause in the join.

Using distinct will probably make it look like the right answer but you are just masking the problem.

Upvotes: 2

Related Questions