Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419

    GROUP by yet SELECT fields not within GROUP BY

    I have this situation where I can only select the fields that I want to group by..... so I could find the largest item within the group.

    For example, bid price 2.65 is the largest so I want to write a query to chose it out of the group.
    Click image for larger version. 

Name:	sample2.png 
Views:	33 
Size:	8.9 KB 
ID:	14065


    This query below does the job and gets what I wanted, however it doesn't include all the fields that I wanted, it only includes fields such
    as seller, iid, starttime, endtime, maxofbidprice
    Code:
    SELECT bid.seller, bid.iid, bid.starttime, auction.endTIME, Max(bid.bidprice) AS MaxOfbidprice, Count(*) AS nBIDsFROM auction INNER JOIN bid ON (auction.[startTIME] = bid.starttime) AND (auction.iid = bid.iid) AND (auction.seller = bid.seller)
    WHERE (((bid.bidtime)<[auction].[endTIME]) AND ((bid.bidprice)>=[auction].[minbid]) AND ((Now())>=[auction].[endTIME]))
    GROUP BY bid.seller, bid.iid, bid.starttime, auction.endTIME;
    So my solution was to join to other table to include other fields, and this is the code below, unfortunately with this code it doesn't get the largest item from the group.
    The reason is because the second user (user_1) table is join with the bid table instead of the auction, but since I want to get buyer I have to join the bid table and not the auction table because the
    auction table doesn't have the bidder field.
    Because of the join to the bidder table, this caused it not to get the largest item in the group, however I could get the other fields, but the result of the query is not accurate anymore.
    Code:
    SELECT winningBIDtwo.winningBID.bid.seller AS Expr1, user.uname, winningBIDtwo.bid.bidder, USER_1.uname, winningBIDtwo.winningBID.bid.iid AS Expr2, item_type.itemname, winningBIDtwo.bidSTARTtime, auction.starttime AS auctionSTARTtime, winningBIDtwo.auctionENDtime, winningBIDtwo.bid.bidtime, auction.minbid, winningBIDtwo.bid.bidprice
    FROM item_type INNER JOIN (auction INNER JOIN ([USER] AS USER_1 INNER JOIN ((SELECT winningBID.seller, bid.bidder, winningBID.iid, winningBID.starttime AS bidSTARTtime, winningBID.endTIME AS auctionENDtime, bid.bidtime, bid.bidprice 
                      FROM  (SELECT bid.seller, bid.iid, bid.starttime, auction.[endTIME], max(bid.bidprice), count(*) AS nBIDs 
    
    
    
                                       FROM auction INNER JOIN bid ON (auction.[startTIME]=bid.starttime) AND (auction.iid = bid.iid) AND (auction.seller = bid.seller) 
                                       WHERE bid.bidtime<auction.[endTIME] And bid.bidprice>=auction.minbid And Now()>=auction.[endTIME] 
                                       GROUP BY bid.seller, bid.iid, bid.starttime, auction.[endTIME])  AS winningBID 
    
    
                      INNER JOIN BID ON (winningBID.seller = BID.seller) AND (winningBID.iid = BID.iid) AND (winningBID.starttime = BID.STARTtime))  AS winningBIDtwo INNER JOIN [user] ON winningBIDtwo.seller = user.uid) ON USER_1.uid = winningBIDtwo.bidder) ON (auction.starttime = winningBIDtwo.bidSTARTtime) AND (auction.iid = winningBIDtwo.iid) AND (auction.seller = winningBIDtwo.seller)) ON item_type.iid = winningBIDtwo.iid;
    Click image for larger version. 

Name:	sample.png 
Views:	33 
Size:	10.1 KB 
ID:	14064
    Here is the fields I wanted but I want the largest value from the group too, how can I do that?
    Last edited by johnseito; 10-16-2013 at 06:15 PM.

  2. #2
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    By the way you coded the first query, i'm assuming that iid is the item ID for a particular seller, but is not unique. Two different sellers in the same auction therefore might have the same item id.

    I would not design the database that way - I'd use a unique autokey (say, iiPK) for every item that gets auctioned, unique across the entire database. However, I'm making the assumption that it's not unique because you're currently using all three fields to match, and if iid was unique, you wouldn't have to go to all that trouble.

    I'd also give each bid record a unique autokey (say, bidPK), and it would simplify the coding immensely.

    Another item that looks fishy is bid.starttime. I have no idea why you would duplicate the starttime data from the Auction table into every bid on an item.

  3. #3
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Two different sellers in the same auction therefore might have the same item id.
    Yes you are correct.

    And you are right, no matter how I do my query I couldn't get an accurate result as well as having all the fields I wanted.
    So now I have to restructure my database.

    I would not design the database that way - I'd use a unique autokey (say, iiPK) for every item that gets auctioned, unique across the entire database. However, I'm making the assumption that it's not unique because you're currently using all three fields to match, and if iid was unique, you wouldn't have to go to all that trouble.
    iid in the item table is unique, but is not unique (primary key) in the auction table, because it can't be unique, different people can sell the same item.
    Auction table has composite key as primary key that includes seller id, iid, and starttime.

    Another item that looks fishy is bid.starttime. I have no idea why you would duplicate the starttime data from the Auction table into every bid on an item.
    Because it serves as a primary key, in the auction table, the primary keys are a composite key that includes seller, item_id (iid) and auctionStartTime (startTime).

    bid.starttime is duplicated because different buyer (bidder) can buy the same item, and same item is uniquely identify by a composite key (seller, iid, and starttime).

    But you are correct, if I want an accurate query that also includes all the fields that I want, I would have to restructure the database, because I have tried numerous ways
    and have not gotten any result. Unless you think it can, just that I didn't write the query correctly.

  4. #4
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    You're making your life too complicated by not giving the Auction table an autokey field. Leave your current primary key in place for now, but just add an autonumber key field. Then write your query using that new field, and you'll find the autokey field makes it almost trivial.

    Of course, after you add the autokey, you'll have to write an update query to copy that as a foreign key into the bid table, and update the bid logic to add it as well, but after that it's a piece of cake.

  5. #5
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    I think that the way I have my database setup, it can be query correctly in ms SQL server but not access.

    Giving an autokey field is good because when you do data input access automatically increment the autokey, But is also not so easy because
    What if you have a list of data and your want to append it to the list, how would access increment the autokey, OR how would access then
    know which is the matching foreign key in the other table?


    you'll have to write an update query to copy that as a foreign key into the bid table, and update the bid logic to add it as well,
    how do you do this, if you have millions of records from two tables, how are you going to match the primary key from one table to the foreign key in another table?

  6. #6
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    the primary keys are a composite key that includes seller, item_id (iid) and auctionStartTime (startTime).
    Which means that each combination of those three fields identifies a unique record. As soon as the records have a new autokey primary key, you can set the correct foreign key in any other table with a single SQL command.

    You do all this in a test version of the database first, until you're sure you know how it works.

    1) You add the autokey field to the first table. (autopopulated)

    2) You add a foreign key field to the second table. (unpopulated)

    3) You create an update query that sets the foreign key based upon your current primary key combination. It looks vaguely like this:
    Code:
    UPDATE ForeignTable AS FT, PrimeTable AS PT
    SET FT.NewFK = PT.NewPK
    WHERE FT.Seller = PT.Seller AND FT.item_id = PT.item_id AND FT.auctionStartTime = PT.auctionStarttime;
    4) As soon as that update is complete, all three of those fields in the old composite primary key become redundant on the foreign table. You may choose to keep some of them for convenience, but they aren't needed, and your SQL code becomes vastly simpler.

  7. #7
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Thanks I will try that code and let you know.

    So let's say that someone add more info/data to the primary table, that means we would have to run the script that you just created again
    to fill the foreign key, or could you make it that when someone enters data to the primary table, the foreign key is automatically filled (the script
    is automatically run)?

  8. #8
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    You should find, everywhere, all the places that you are currently doing stuff to populate the foreign key fields, and alter those processes to populate the new, simpler, foreign key instead (or as well).

    That's why I said you have to test the process, thoroughly, in a test version of the database. This is a great improvement, and will make your life much easier when you do it, but if you miss something, then yes, you'll have to keep running that update occasionally to set the foreign key. (That would be a sloppy system.)

  9. #9
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Code:
     
    UPDATE bid AS FT, auction AS PT SET FT.bidID = PT.auctionID
    WHERE (((FT.seller)=[PT].[seller]) AND ((FT.iid)=[PT].[iid]) AND ((FT.startTIME)=[PT].[startTIME]));
    Cool thanks, my code works!!!

    I guess for the foreign table, if new datas are enter (BID), it needs a clever way example a user-form that when a person
    enters or inputs the data they are constraint to select what is in the primary table (auction) and the VBA script can update the foreign table,
    and update the foreign and primary keys but is needs a clever (logical) way to do it that is efficient and none redundant.

  10. #10
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    The real question is, where are you adding the bids. at that place, just enter as a foreign key the primary key of whatever they are bidding on, instead of (or, temporarily, in addition teo, the three fields that made up the original composite key.

  11. #11
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    After doing that the issue is still not resolved, and I think the reason why is because after the initial result (getting the highest bid using GROUP BY) which is correct, however
    once I join the result to the bid table with the ID key (because the bid table has a lot more fields that I want to show in the result), the highest bid is not the only result shown and the query is inaccurate.
    The reason why is because the foreign key (bidID) has many relationship. The id from the result joins to the bidID will pull all the values, including the not highest bid amount.

    This is the correct result before adding the fields I am looking for,
    bid table has new foreign key (bidID):

    Code:
    SELECT bid.bidID, bid.seller, bid.iid, bid.starttime, max(bidprice) as highestBID 
                      FROM bid INNER JOIN auction ON bid.bidID = auction.auctionID
                      WHERE (bidprice > minbid) AND (bidtime<endtime) AND (now()>endtime)
                     GROUP BY bid.bidID, bid.seller, bid.iid, bid.starttime
    This is the code after joining the bid table and pulling in the necessary field (the result is not correct again).

    Code:
    SELECT bid.bidID,  bid.seller, bid.iid, bid.starttime, bid.bidtime, bid.bidprice
    FROM 
    (
                      SELECT bid.bidID, bid.seller, bid.iid, bid.starttime, max(bidprice) as highestBID 
                      FROM bid INNER JOIN auction ON bid.bidID = auction.auctionID
                      WHERE (bidprice > minbid) AND (bidtime<endtime) AND (now()>endtime)
                     GROUP BY bid.bidID, bid.seller, bid.iid, bid.starttime
    
    
    ) AS winningBID
    
    
    INNER JOIN bid ON winningBID.bidID = bid.bidID
    This is the new bid table, as you could see it has a new foreign key that I added. After the initial query (result were correct), the 9 from the max value of the initial result 2.65 will pull all the datas from the
    BID table, I need to join it to the bid table because it has most of the necessary fields that I am looking for, like the buyer which no other table has. And there is no way to join the primary key in the bid table
    because then I can't GROUP BY and get the largest bidprice.

    So any suggestion or solution to this problem?
    Click image for larger version. 

Name:	newBIDtable.png 
Views:	16 
Size:	5.4 KB 
ID:	14138

  12. #12
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Okay, I'm missing something. If BidID is the primary key on the bid, why is it being joined to auctionID?
    There should be a unique primary key for each auction (one item put up for bid by one seller at one time), and there should be a unique primary key for each BID (one bidder bidding a certain amount on one item in one auction).

  13. #13
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Okay, I'm missing something. If BidID is the primary key on the bid, why is it being joined to auctionID?
    Sorry the bidID is not the primary key on the bid table but the foreign key.
    And the auctionID is the primary key on the auction table.

    My design is this:

    Click image for larger version. 

Name:	myDesign.png 
Views:	15 
Size:	24.8 KB 
ID:	14151
    the primary key for the auction table used to be seller, iid, and starttime, now is just auctionID,
    the primary key for the bid table is bidder, seller, iid, starttime, and bidtime and still is, and is foreign key is the bidID.

    The first query got the result I wanted, but it lacks the fields I was looking for, example:
    Click image for larger version. 

Name:	firstCODE - GROUP BY highest amount.png 
Views:	15 
Size:	14.6 KB 
ID:	14152
    this result is then joined to the new bid table (below) with the newly added foreign key
    Click image for larger version. 

Name:	bid table JOIN.png 
Views:	15 
Size:	42.5 KB 
ID:	14154
    and as you could see the 5 from the query bidID which list only once after joining the bid table bidID will pick it up twice since in the bid table is shown as twice,
    and the 9 will pick up three times since is showing on the bid table three times.
    Attached Thumbnails Attached Thumbnails newBIDtable.png  

  14. #14
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Okay, change the name of BIDID to AuctionID, and add an autokey to give each BID a unique key. then we're in business.

  15. #15
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    change the name of BIDID to AuctionID,
    you meant on the bid table?

    and add an autokey to give each BID a unique key. then we're in business.
    Ok, so how can this be join to the any table and create the query with the correct result?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-24-2013, 02:34 PM
  2. Replies: 0
    Last Post: 02-25-2013, 04:43 PM
  3. Replies: 2
    Last Post: 06-21-2012, 07:40 PM
  4. Group title carrying forward to next group
    By Amandasr12 in forum Reports
    Replies: 3
    Last Post: 06-06-2012, 02:13 PM
  5. columns for group detail but not group header?
    By Coolpapabell in forum Reports
    Replies: 0
    Last Post: 08-21-2009, 08:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums