Create a distinct Join

  • Thread starter Thread starter webicky
  • Start date Start date
W

webicky

Using Access 2003, It is easy for me to a distinct Left join: Table 1
ID has 157 unique ID records. table 2 has 249 records some duplicate
records in order id.

SELECT DISTINCT [table1].id, [table2].CountOforderid
FROM [table1] LEFT JOIN table2 ON [table1].id = table2.id;

basically counts the order id uniquely among the 157 table 1 id's,

However, I would like to add another field one that has duplicates in
table2, but I only want the data for the unique 157

How do I get unique records in table 1 with some of the table 2 data?

When I add the new field in access i get:

SELECT DISTINCT [table1].id, [table2].CountOforderid, table2.product
FROM [table1] LEFT JOIN table2 ON [table1].id = table2.id;

I understand why its giving me the dups since its looking at
table2.product which has duplicate records.

What I don't understand is how to get all the unqiue data from table
1 (only the 157 unique id's THEN get the data from table2.

1) what's the correct SQL.

2) Is there a way to use the Access functions (rather than the custom
SQL to get this work)?
 
(1) Write a query to get the rows/columns you want from table 1
(2) Write a query to get the rows/columns you want from table 2
(3) Write a third query to join query 1 and query 2
Its sometimes easier to solve things a piece at a time like this. Once you
get it working, you can see if you can combine all three queries into one.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Dorian:

Thanks. I'm guessing there is a way to do it - the (Max) option
seems to be working.


(1) Write a query to get the rows/columns you want from table 1
(2) Write a query to get the rows/columns you want from table 2
(3) Write a third query to join query 1 and query 2
Its sometimes easier to solve things a piece at a time like this. Once you
get it working, you can see if you can combine all three queries into one..
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".



webicky said:
Using Access 2003, It is easy for me to a distinct Left join:  Table 1
ID has 157 unique ID records.  table 2 has 249 records some duplicate
records in order id.
Click to expand...
SELECT DISTINCT [table1].id, [table2].CountOforderid
FROM [table1] LEFT JOIN table2 ON [table1].id = table2.id;
Click to expand...
basically counts the order id uniquely among the 157 table 1 id's,
Click to expand...
However, I would like to add another field one that has duplicates in
table2, but I only want the data for the unique 157
Click to expand...
How do I get unique records in table 1 with some of the table 2 data?
Click to expand...
When I add the new field in access i get:
Click to expand...
SELECT DISTINCT [table1].id, [table2].CountOforderid, table2.product
FROM [table1] LEFT JOIN table2 ON [table1].id = table2.id;
Click to expand...
I understand why its giving me the dups since its looking at
table2.product which has duplicate records.
Click to expand...
 What I don't understand is how to get all the unqiue data from table
1 (only the 157 unique id's THEN get the data from table2.
Click to expand...
1) what's the correct SQL.
Click to expand...
2) Is there a way to use the Access functions (rather than the custom
SQL to get this work)?
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
Instead of using the DISTINCT operator, use a totals (aggregate) query.

SELECT [table1].id
, First([table2].CountOforderid)
, First(table2.product)
FROM [table1] LEFT JOIN table2 ON [table1].id = table2.id
GROUP BY [table1].id



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Dorian:

Thanks. I'm guessing there is a way to do it - the (Max) option
seems to be working.


(1) Write a query to get the rows/columns you want from table 1
(2) Write a query to get the rows/columns you want from table 2
(3) Write a third query to join query 1 and query 2
Its sometimes easier to solve things a piece at a time like this. Once you
get it working, you can see if you can combine all three queries into one.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".



webicky said:
Using Access 2003, It is easy for me to a distinct Left join: Table 1
ID has 157 unique ID records. table 2 has 249 records some duplicate
records in order id.
SELECT DISTINCT [table1].id, [table2].CountOforderid
FROM [table1] LEFT JOIN table2 ON [table1].id = table2.id;
basically counts the order id uniquely among the 157 table 1 id's,
However, I would like to add another field one that has duplicates in
table2, but I only want the data for the unique 157
How do I get unique records in table 1 with some of the table 2 data?
When I add the new field in access i get:
SELECT DISTINCT [table1].id, [table2].CountOforderid, table2.product
FROM [table1] LEFT JOIN table2 ON [table1].id = table2.id;
I understand why its giving me the dups since its looking at
table2.product which has duplicate records.
What I don't understand is how to get all the unqiue data from table
1 (only the 157 unique id's THEN get the data from table2.
1) what's the correct SQL.
2) Is there a way to use the Access functions (rather than the custom
SQL to get this work)?
.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
Click to expand...
 
Back
Top