Help for a newbie with a append query

[Steve Jakeman]

This is the Append query that i'm running.

INSERT INTO [Bid Sheet] ( LotID, [Member ID], [Member
Name], [Species ID], [Species Name], Quantity, BuyersID,
BuyersName, [Max Of Bid] )
SELECT DISTINCTROW Lots.LotID, Lots.[Member ID], Lots.
[Member Name], Lots.[Species ID], Lots.[Species Name],
Lots.Quantity, [Buyers Bid].BuyersID, [Buyers
Bid].BuyersName, Max([Buyers Bid].Bid) AS [Max Of Bid]
FROM [Buyers Bid] INNER JOIN Lots ON [Buyers Bid].LotID =
Lots.LotID
GROUP BY Lots.LotID, Lots.[Member ID], Lots.[Member
Name], Lots.[Species ID], Lots.[Species Name],
Lots.Quantity, [Buyers Bid].BuyersID, [Buyers
Bid].BuyersName
ORDER BY Max([Buyers Bid].Bid);

What I'm trying to do is sort out and delete everything
but the highest bid rows. Then I can make Forms from this
for Members (Sellers) and Buyers (Bidders). I may be
going about this process the wrong way. Any help would be
appreciated.

 

[Dirk Goldgar]

This is the Append query that i'm running.

INSERT INTO [Bid Sheet] ( LotID, [Member ID], [Member
Name], [Species ID], [Species Name], Quantity, BuyersID,
BuyersName, [Max Of Bid] )
SELECT DISTINCTROW Lots.LotID, Lots.[Member ID], Lots.
[Member Name], Lots.[Species ID], Lots.[Species Name],
Lots.Quantity, [Buyers Bid].BuyersID, [Buyers
Bid].BuyersName, Max([Buyers Bid].Bid) AS [Max Of Bid]
FROM [Buyers Bid] INNER JOIN Lots ON [Buyers Bid].LotID =
Lots.LotID
GROUP BY Lots.LotID, Lots.[Member ID], Lots.[Member
Name], Lots.[Species ID], Lots.[Species Name],
Lots.Quantity, [Buyers Bid].BuyersID, [Buyers
Bid].BuyersName
ORDER BY Max([Buyers Bid].Bid);

What I'm trying to do is sort out and delete everything
but the highest bid rows. Then I can make Forms from this
for Members (Sellers) and Buyers (Bidders). I may be
going about this process the wrong way. Any help would be
appreciated.

See if this page explains how to get the data you need:

http://www.mvps.org/access/queries/qry0020.htm
Getting a related field from a GroupBy (total) query

 

[Steve Jakeman]

-----Original Message-----

This is the Append query that i'm running.

INSERT INTO [Bid Sheet] ( LotID, [Member ID], [Member
Name], [Species ID], [Species Name], Quantity, BuyersID,
BuyersName, [Max Of Bid] )
SELECT DISTINCTROW Lots.LotID, Lots.[Member ID], Lots.
[Member Name], Lots.[Species ID], Lots.[Species Name],
Lots.Quantity, [Buyers Bid].BuyersID, [Buyers
Bid].BuyersName, Max([Buyers Bid].Bid) AS [Max Of Bid]
FROM [Buyers Bid] INNER JOIN Lots ON [Buyers Bid].LotID =
Lots.LotID
GROUP BY Lots.LotID, Lots.[Member ID], Lots.[Member
Name], Lots.[Species ID], Lots.[Species Name],
Lots.Quantity, [Buyers Bid].BuyersID, [Buyers
Bid].BuyersName
ORDER BY Max([Buyers Bid].Bid);

What I'm trying to do is sort out and delete everything
but the highest bid rows. Then I can make Forms from this
for Members (Sellers) and Buyers (Bidders). I may be
going about this process the wrong way. Any help would be
appreciated.

See if this page explains how to get the data you need:

http://www.mvps.org/access/queries/qry0020.htm
Getting a related field from a GroupBy (total) query

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

Thanks for the link, That Did everything I needed And
plus gave me insight on a few other things from reading
other Instructions on that site.