If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Limiting join-matches to first match

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2005
Posts: 183
Limiting join-matches to first match

I have a query between two tables, one is an imported table, and the other a fixed product database.

With a query I match the imported tables product name with the one in our product database, usually resulting in multiple matches per product, and eventually a mile long match list.

What I'd like is for the query to rerutn just one match per product, as many of the similar named products in the product database are in fact completely alike, set aise minor differences like table finish, chair cloth color etc. etc.

Essentially the list simply works to tell the salesperson if the warehouse requested are available in at least one color, finish, model etc. etc. based on the form filled out by the customer. So, I only need that 1 match. Its not very precise, but enough to give the order an initial green light.

Cheers, Trin
__________________
IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....
Reply With Quote
  #2 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,878
Take a look at GROUP BY in SQL
http://w3schools.com/sql/sql_groupby.asp
Eg:
Code:
people
firstname lastname age
george smith 24
george smith 19
geoff smith 33
john smith 85

SELECT firstname, lastname
FROM people
GROUP BY firstname, lastname

Results:
george smith
geoff smith
john smith
__________________
George
Home | Blog
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Oct 2005
Posts: 183
I was aware of the Group By clause, but not that it had a First() operator. Brilliant!
__________________
IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....
Reply With Quote
  #4 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,878
Quote:
Originally Posted by Trinsan
I was aware of the Group By clause, but not that it had a First() operator. Brilliant!
It does? Fancy posting a link - I want a read!
__________________
George
Home | Blog
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Oct 2005
Posts: 183
Knock yourself out -LINKY. (Aggregate functions)
__________________
IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....
Reply With Quote
  #6 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 10,878
Not supported in 2k - that might be why I've not come across it yet
Thanks for that - it's going to be a very useful one to add to the arsenal!
__________________
George
Home | Blog
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Oct 2005
Posts: 183
Exactly the same here. I've been used to 97/2000 for a long while. Only last year went 2003, and I've barely started and going 2007 already.

Then again, I should've known about it, since I do SQL-Server too.
__________________
IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On