Access LAST function and MAX group by function in sql statement

dzirkelb
dzirkelb used Ask the Experts™
on
I have a query which in Access, I would just use the last function to get a particular field.  I understand sql doesnt' recognize this functionality, so I tried using the MAX function on the ID field of the table.  This didn't work, however, as I still get two results when there should only be one.  here is the sql statement:

SELECT     TOP 100 PERCENT dbo.QUOTES.CustNbr AS CustNum, dbo.QUOTES.COMPANY, dbo.QUOTES.PHONE# AS CustPhone,
                      dbo.QUOTES.[FAX #] AS CustFax, dbo.QUOTES.CustQteNbr, dbo.BuyerListMaster.BuyerName, dbo.BuyerListMaster.BuyerPhone,
                      dbo.BuyerListMaster.BuyerFax, dbo.BuyerListMaster.BuyerEmail, dbo.QUOTES.[QUOTE #] AS Quote, dbo.QUOTES.CurrentDate AS LastChangeDate,
                      dbo.QUOTES.[DATE]
FROM         dbo.QUOTES LEFT OUTER JOIN
                      dbo.BuyerListMaster ON dbo.QUOTES.NAME = dbo.BuyerListMaster.BuyerName AND
                      dbo.QUOTES.CustNbr = dbo.BuyerListMaster.CustomerNbr INNER JOIN
                      dbo.[CUSTOMER MASTER] ON dbo.QUOTES.CustNbr = dbo.[CUSTOMER MASTER].[CUST #] INNER JOIN
                      dbo.[ACCT ADMIN NAME LEGEND] ON dbo.[CUSTOMER MASTER].[INSIDE CONTACT] = dbo.[ACCT ADMIN NAME LEGEND].[INSIDE TERR]
WHERE     (dbo.QUOTES.DECIDED = 0) AND (dbo.[ACCT ADMIN NAME LEGEND].[INSIDE TERR] = 'A') AND (dbo.QUOTES.[ACCT ADMIN] = 'DEBBIE EDMONDS')
GROUP BY dbo.QUOTES.CustNbr, dbo.QUOTES.COMPANY, dbo.QUOTES.PHONE#, dbo.QUOTES.[FAX #], dbo.QUOTES.CustQteNbr,
                      dbo.BuyerListMaster.BuyerName, dbo.BuyerListMaster.BuyerPhone, dbo.BuyerListMaster.BuyerFax, dbo.BuyerListMaster.BuyerEmail,
                      dbo.QUOTES.[QUOTE #], dbo.QUOTES.CurrentDate, dbo.QUOTES.[DATE]
HAVING      (dbo.QUOTES.[QUOTE #] <> 209101) AND (dbo.QUOTES.[QUOTE #] <> 211976) AND (dbo.QUOTES.[QUOTE #] <> 212143)
ORDER BY dbo.QUOTES.COMPANY

Basically, that is returning duplicate results due to BuyerListMaster table having multiple entries on the link to Quotes.

I would like to make this return the last buyer added into the buyerlistmaster table.  So, I did the MAX on theID of buyerlistmaster as that is an autonumber.  The query still returns two results.

I do understand the data structure isn't the best (the BuyerName should not allow duplicates); however, this is what I have to work with (didn't create it).

SQL-2000 is the database
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Is there any date added column in the BuyerListMaster table? Also can you post some sample data of your current query and the expected result?
Top Expert 2012

Commented:
Something like this perhaps:

SELECT  q.CustNbr AS CustNum,
        q.COMPANY,
        q.PHONE# AS CustPhone,
        q.[FAX #] AS CustFax,
        q.CustQteNbr,
        b.BuyerName,
        b.BuyerPhone,
        b.BuyerFax,
        b.BuyerEmail,
        q.[QUOTE #] AS Quote,
        q.CurrentDate AS LastChangeDate,
        q.[DATE]
FROM    dbo.QUOTES q
      INNER JOIN dbo.BuyerListMaster b ON q.NAME = b.BuyerName
                                               AND q.CustNbr = b.CustomerNbr
        INNER JOIN (
            SELECT      MAX([theID of buyerlistmaster]) LastID,
                  BuyerName,
                  CustomerNbr
            From      dbo.BuyerListMaster
            GROUP BY
                  BuyerName,
                  CustomerNbr) d ON b.[theID of buyerlistmaster] = LastID
        INNER JOIN dbo.[CUSTOMER MASTER] c ON q.CustNbr = c.[CUST #]
        INNER JOIN dbo.[ACCT ADMIN NAME LEGEND] a ON c.[INSIDE CONTACT] = a.[INSIDE TERR]
WHERE   q.DECIDED = 0
        AND a.[INSIDE TERR] = 'A'
        AND q.[ACCT ADMIN] = 'DEBBIE EDMONDS'
        AND q.[QUOTE #] NOT IN (209101, 211976, 212143)
ORDER BY q.COMPANY
>> do understand the data structure isn't the best (the BuyerName should not allow duplicates); <<
What is confussing is that you're saying that CustomerNbr is an autonumber (Identity column?) therefore there shouldn't be duplicate CustomerNbr. What might be happening is that you have customers with more than one email/fax, etc. Please advise.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2012
Commented:
Not strictly necessary, but it looks like I missed an alias:
SELECT  q.CustNbr AS CustNum,
        q.COMPANY,
        q.PHONE# AS CustPhone,
        q.[FAX #] AS CustFax,
        q.CustQteNbr,
        b.BuyerName,
        b.BuyerPhone,
        b.BuyerFax,
        b.BuyerEmail,
        q.[QUOTE #] AS Quote,
        q.CurrentDate AS LastChangeDate,
        q.[DATE]
FROM    dbo.QUOTES q
      INNER JOIN dbo.BuyerListMaster b ON q.NAME = b.BuyerName
                                               AND q.CustNbr = b.CustomerNbr
        INNER JOIN (
            SELECT      MAX([theID of buyerlistmaster]) LastID,
                  BuyerName,
                  CustomerNbr
            From      dbo.BuyerListMaster
            GROUP BY
                  BuyerName,
                  CustomerNbr) d ON b.[theID of buyerlistmaster] = d.LastID
        INNER JOIN dbo.[CUSTOMER MASTER] c ON q.CustNbr = c.[CUST #]
        INNER JOIN dbo.[ACCT ADMIN NAME LEGEND] a ON c.[INSIDE CONTACT] = a.[INSIDE TERR]
WHERE   q.DECIDED = 0
        AND a.[INSIDE TERR] = 'A'
        AND q.[ACCT ADMIN] = 'DEBBIE EDMONDS'
        AND q.[QUOTE #] NOT IN (209101, 211976, 212143)
ORDER BY q.COMPANY

Author

Commented:
I do have a date entered field in the table.  It appears it was added at a later time as some are null.

The data results are with the DateEntered field at the end.  That is the date entered per each record for BuyerListMaster.

You will see three 290080 Quote Number entries.  I would only like there to be one of those entries as there is only one entry for the quote data.


data.xls
@acperkins, I too wanted to suggest something along that line, but the problem is that the asker mentioned that the ID is an autonumber. Is that too crazy to think that the ID he is talking about is the CustomerNbr column?
In that case, use this
SELECT  dbo.QUOTES.CustNbr AS CustNum, 
	dbo.QUOTES.COMPANY, 
	dbo.QUOTES.PHONE# AS CustPhone, 
	dbo.QUOTES.[FAX #] AS CustFax, 
	dbo.QUOTES.CustQteNbr, 
	b.BuyerName, 
	b.BuyerPhone, 
        b.BuyerFax, 
	b.BuyerEmail, 
	dbo.QUOTES.[QUOTE #] AS Quote, 
	dbo.QUOTES.CurrentDate AS LastChangeDate, 
        dbo.QUOTES.[DATE]

FROM         dbo.QUOTES 
LEFT OUTER JOIN (	
			select * from dbo.BuyerListMaster a
			where dateentered = (select max(dateentered) from dbo.BuyerListMaster where a.CustomerNbr = CustomerNbr)
		) b ON 	dbo.QUOTES.NAME = b.BuyerName AND 
			dbo.QUOTES.CustNbr = b.CustomerNbr 
INNER JOIN dbo.[CUSTOMER MASTER] ON dbo.QUOTES.CustNbr = dbo.[CUSTOMER MASTER].[CUST #] 
INNER JOIN dbo.[ACCT ADMIN NAME LEGEND] ON dbo.[CUSTOMER MASTER].[INSIDE CONTACT] = dbo.[ACCT ADMIN NAME LEGEND].[INSIDE TERR]

WHERE   (dbo.QUOTES.DECIDED = 0) AND 
	(dbo.[ACCT ADMIN NAME LEGEND].[INSIDE TERR] = 'A') AND 
	(dbo.QUOTES.[ACCT ADMIN] = 'DEBBIE EDMONDS') AND
	(dbo.QUOTES.[QUOTE #] not in (209101,211976,212143)

Open in new window

Top Expert 2012

Commented:
>>Is that too crazy to think that the ID he is talking about is the CustomerNbr column?<<
I have no idea, but until we get the CREATE TABLE structure for all the tables involved and some sample data, all we can do is hazard wild guesses.
Try adding DISTINCT:

SELECT DISTINCT TOP 100 PERCENT ...

Author

Commented:
This did the trick, thanks a ton!  I'll research it and figure out how it is working :)

Author

Commented:
The ID autonumber field is called ID...thanks guys for your help.
Top Expert 2012

Commented:
As an aside notice how we have both removed the SELECT TOP 100 PERCENT.  We know why you are doing it (it is a kluge to get the VIEW to compile), but in conjunction with the ORDER BY clause it is totally pointless and should never be used.
such a waste of time.
Top Expert 2012

Commented:
>>SELECT DISTINCT TOP 100 PERCENT ...<<
Pray tell, what is the use of using DISTINCT with a GROUP BY clause?  And please do not encourage the use if TOP 100 PERCENT with an ORDER BY clause (see my previous comment)

Author

Commented:
Ya, I never use the top 100%...I just create the view in SQL Enterprise manager, then remove that portion when I copy / paste the code into my web pages :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial