SELECT DISTINCT (multiple columns/tables)

  • Thread starter Thread starter bw
  • Start date Start date
B

bw

SELECT tblModules.MonicsID, tblAddresses.BankID, tblModules.PAnalyst
FROM tblAddresses INNER JOIN tblModules ON tblAddresses.AddrID =
tblModules.AddrID
WHERE (((tblModules.PAnalyst) Is Not Null));

I want to select DISTINCT values from MonicsID and BankID.
Example values are like these:
MonicsID BankID
APS BANK1
APS BANK1
APS BANK2
PBM BANK1
PBM BANK1
PBM BANK1

So the "records" I want are:
APS BANK1
APS BANK2
PBM BANK1

Does "DISTINCT" work on multiple colmns from multiple tables?


--
 
bw said:
SELECT tblModules.MonicsID, tblAddresses.BankID, tblModules.PAnalyst
FROM tblAddresses INNER JOIN tblModules ON tblAddresses.AddrID =
tblModules.AddrID
WHERE (((tblModules.PAnalyst) Is Not Null));

I want to select DISTINCT values from MonicsID and BankID.
Example values are like these:
MonicsID BankID
APS BANK1
APS BANK1
APS BANK2
PBM BANK1
PBM BANK1
PBM BANK1

So the "records" I want are:
APS BANK1
APS BANK2
PBM BANK1

Does "DISTINCT" work on multiple colmns from multiple tables?
Click to expand...

Yes. DISTINCT eliminates duplicates in your *output*. How the query goes
about generating that output doesn't matter.
 
Rick Brandt said:
Yes. DISTINCT eliminates duplicates in your *output*. How the query
goes about generating that output doesn't matter.
Click to expand...
Thanks for the response Rick.
I should have included that I don't know how to code this with multiple
tables. Can you show me?

Bernie
 
bw said:
Thanks for the response Rick.
I should have included that I don't know how to code this with
multiple tables. Can you show me?
Click to expand...

Just open the property sheet in the query designer and set "Unique Values"
to "Yes". That adds the DISTINCT clause to the SQL of whatever query you
end up building. Just join whatever tables you need to get the columns you
want in the output.
 
Rick Brandt said:
Just open the property sheet in the query designer and set "Unique
Values" to "Yes". That adds the DISTINCT clause to the SQL of
whatever query you end up building. Just join whatever tables you
need to get the columns you want in the output.
Click to expand...

I don't get it!!!

I select the query I'm interested changing.
I then click on the "Design" icon in the Database Window.
This brings up a window "tblModules Query: Select Query"
I then click on the "Properties" icon (the hand/finger) on the toolbar
to
open the properties box "Field Properties"
I have no "Unique Values" in the box.

You must be talking about something different. Please explain...
 
bw said:
I don't get it!!!

I select the query I'm interested changing.
I then click on the "Design" icon in the Database Window.
This brings up a window "tblModules Query: Select Query"
I then click on the "Properties" icon (the hand/finger) on the toolbar
to
open the properties box "Field Properties"
I have no "Unique Values" in the box.

You must be talking about something different. Please explain...
Click to expand...

If the cursor is in a "field" when you display the properties box then you
get the properties for that field. If you click in the blank gray area
where the tables are shown you will then see properties for the query.
Unique Values will be the fourth property listed.
 
Rick Brandt said:
If the cursor is in a "field" when you display the properties box then
you get the properties for that field. If you click in the blank gray
area where the tables are shown you will then see properties for the
query. Unique Values will be the fourth property listed.
Click to expand...

Well just plain unbelievable! I did what you said, and there it is. It
NEVER would have occurred to me to click in the gray area. So I did set
the Unique Values to Yes, and it did exactly what I wanted. Just great.

Thanks for leading me by the hand.
Bernie
 
Rick, what if i only need DISTINCT on one of the columns, and not on the entire row? (e.g. I have a select distinct invoice_number, task_name and inv_amount.. lets say it returns:

Invoice_number Task_name inv_amount
1234 A $34.50
1234 B $34.50
1234 C $34.50

I only want ONE of these rows returned.. how do I go about that?

Thanks!

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
 
Back
Top