similar code, one works, the other doesn't

[johnseito]

Anyone know why this code below doesn't work, it ask for a seller parameter,


however the bottom code works just fine.

SELECT countBID.iid
FROM 
         (
                SELECT iid, seller, startTIME , count(*) as cntBID
                FROM bid 
                GROUP BY seller, iid, startTIME
                HAVING count(*) = 1
           ) AS countBID

SELECT countBID.seller
FROM 
         (
                SELECT iid, seller, startTIME , count(*) as cntBID
                FROM bid 
                GROUP BY seller, iid, startTIME
                HAVING count(*) = 1
           ) AS countBID

[Dal Jeanis]

switch around the group by statement so iid is first and see if it works.

[johnseito]

I never figure out why or what was going on with that code.
So then I just created another query by going to create, query design and left click SQL view and then I entered the two codes that I have and both works.

There was a glitch with the query that I created before, I never seen a glitch like that before. I still have the other query in my queries object and it still doesn't
work with the codes. The weirdest thing is that the top code works, and the bottom doesn't the other day and now, both of the codes doesn't work.

It asked for a seller parameter. and this is the outcome of it.

[johnseito]

switch around the group by statement so iid is first and see if it works.

I did this and it doesn't work either, it also asked for seller's parameter!!!!
So I just created another query and just copy and paste the code and run again and both code works.

[Dal Jeanis]

try these two

SELECT countBID.iid
FROM 
         (
                SELECT bid.iid, bid.seller, bid.startTIME , count(*) as cntBID
                FROM bid 
                GROUP BY bid.iid, bid.seller, bid.startTIME
                HAVING count(*) = 1
           ) AS countBID;

SELECT countBID.iid
FROM 
         (
                SELECT bid.iid, bid.seller, bid.startTIME , count(*) as cntBID
                FROM bid 
                GROUP BY bid.iid, bid.seller, bid.startTIME
                HAVING count(*) = 1
           ). AS countBID;

[johnseito]

The first one I got an error message asking for parameter, as I had with my first two code.
The second one I have error with the FROM syntax, because it has a .

). AS countBID;

so I take the . away and I got an error message asking for seller's parameter.

[Dal Jeanis]

Is there no seller on the bid table? Is the field name something different, or is it not there at all?

[johnseito]

Is there no seller on the bid table? Is the field name something different, or is it not there at all?

There is seller in the bid table, is called seller, and is an id.
and on the bid table, there is also buyer called bidder (integer field), and this is the only table that has the bidder.

[Dal Jeanis]

That's bizarre. Please go into query builder and build a query on the bid table, selecting only the iid, the seller, and the starttime. post the resulting SQL.

because it has a .

FYI - sometimes a subquery needs that period, which is why I gave it to you as an option. Didn't help, in this case.

[johnseito]

this is my result.

SELECT BID.iid, BID.seller, BID.STARTtime
FROM BID;

:-)

[Dal Jeanis]

Okay, I set up test tables and ran your original queries, and neither one had a problem.

So here's a couple of things I would look at -

1) I often don't trust Count(*), because Jet sometimes complains. I'd much rather be counting an actual field like Count(iid). Of course, if you want to count the number of records, then you have to make sure it is a field that won't be Null.

2) I notice the field alias CntBID and the query alias CountBID. If those happened to become the same, the query might produce questionable results.

3) I don't know why you are looking for auctions that have exactly one bid record, so I don't understand the purpose of the clause "HAVING Count(*)=1".

[johnseito]

because Jet sometimes complains

What does jet sometimes complains mean?

I notice the field alias CntBID and the query alias CountBID.

Ok and luckily it wasn't the same, otherwise something weird might happen.

3) I don't know why you are looking for auctions that have exactly one bid record, so I don't understand the purpose of the clause "HAVING Count(*)=1".

This is so I only show all the item (iid in this case) that only received one bid above the minimum price and this is the query that would do it. :-)

[Dal Jeanis]

"Jet sometimes complains" means that the Jet engine (which is the database engine used by Access for storage and retrieval of records) sometimes generates odd error messages, the details of error number and wording which escape me at the moment, and if I recall correctly vary widely.

It's not always easy to find a technical explanation why something works correctly when written one way in Access and fails to work when written a different apparently valid way - a way that seems like it would work, but doesn't - so I end up with rules of thumb like "count a field, not star".

This is so I only show all the item (iid in this case) that only received one bid above the minimum price and this is the query that would do it. :-)

Hmmm. Okay, that makes sense, now.

But, since iid is a foreign key to a different table, it just tells you that one or more of that kind of item had only a single bid. There may have been other copies of that iid that got multiples.

[johnseito]

But, since iid is a foreign key to a different table, it just tells you that one or more of that kind of item had only a single bid. There may have been other copies of that iid that got multiples.

Yes is foreign key to the auction and bid table, because iid is an item (as integer) that is auction and bid and different people can auction the same item and also different people can bid on the same item as well,
that is why is foreign key to those two tables.

[Dal Jeanis]

Okay, as I said before, you'll save yourself much grief if you give each bid record a unique autokey. It simplifies the SQL immensely.

It could be simplified even more, though.

Myself, I would probably also set up a procedure that "tags" the winning bid, losing bids, and invalid bids, so I didn't have to pull it out in complicated SQL.
For instance, give each auction record a status, and give each bid record a status, something like this:

auction status
0  inactive (being entered)
1  active, incomplete 
2  active, complete  {maybe not needed}
3  being adjudicated
7  canceled
8  complete, no valid bids
9  complete, winner

bid status
0  inactive
1  active
3  being adjudicated
7  invalid
8  did not win
9  won

When you run the routine that determines winners, the routine goes something like this:

First, each auction in status 1 or 2 where the endtime is past, gets moved to status 3 "being adjudicated".
Then, for each auction in status 3, all bids get marked as "being adjudicated".
Then, for each auction in status 3, all bids that were low or late get marked as "invalid".
Then, for each auction in status 3 with no valid (status 3) bids, the entire auction gets moved to status 8.
Then, for each auction in status 3, all bids below the max bid are marked as "did not win".
Then, for each auction in status 3, the earliest bid in status 3 is marked as "won".
Then, for each auction in status 3, all remaining bids are marked as "did not win" or "invalid" (depending on how you think about a duplicate bid amount occurring after the first).
Then all auctions in status 3 are moved to status 9.

Yes, that's eight update queries in a row the VBA adjudication routine has to run.

However, after that runs, you only have to query against a simple inner join against the auction records and bid records in status 9 to determine all facts about the winning bids.