SELECT DISTINCT (multiple columns/tables)

[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?


--

 

[Rick Brandt]

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?

Yes. DISTINCT eliminates duplicates in your *output*. How the query goes
about generating that output doesn't matter.

 

[bw]

Yes. DISTINCT eliminates duplicates in your *output*. How the query
goes about generating that output doesn't matter.

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

 

[Rick Brandt]

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?

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.

 

[bw]

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.

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...

 

[Rick Brandt]

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...

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.

 

[bw]

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.

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

 

[Carlos Labastida]

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