What's the problem with this query? 1

[Zybron]

SELECT Table1.Field1,
(SELECT DISTINCT Table2.Field2
FROM Table2
WHERE Table2.FieldID = Table1.FieldID AND
Table1.Field3 >= #1/1/200# AND
Table1.Field3 < #7/7/2003# AND
Table1.Field4 > 40)
FROM Table1
WHERE Table2.FieldID = Table1.FieldID
ORDER BY Table1.Field1 DESC

Here's my query with representations for the tables and fields to, hopefully, make this more readable. Field3 is a date field and Field4 is an integer field, so I'm fairly certain that the issue is not data related, but I can't get this query to process, even though I've seen similar queries work before. I would just use a join on these two tables but I don't think you can do so with a distinct query and get the query to really retrieve distinct data. If I'm wrong on that, please let me know.

Otherwise, my goal is to retrieve the distinct data from Field2 of Table2 that falls within the listed criteria and retrieve the data from Field1, Table1 that corresponds to the data retrieved from Table2, in descending order.

 

[scriverb]

Give this a try. I can't test this so I will have to rely on your postings for any problems. Post back with problems.

SELECT Table1.Field1, Table2.Field2
FROM Table1 INNER JOIN (SELECT DISTINCT Table2.Field ID, Table2.Field2
FROM Table2
WHERE Table2.FieldID = Table1.FieldID AND
Table1.Field3 >= #1/1/2003# AND
Table1.Field3 < #7/7/2003# AND
Table1.Field4 > 40) on Table1.FieldID = Table2.FieldID
ORDER BY Table1.Field1 DESC;


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

The second line of code has an errant space. Replace with
Table2.Field ID with Table2.FieldID this.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Zybron]

Hmmm, I seem to still be having problems with this. Let me show the actual query text and maybe what I'm trying to get will make more sense.

SELECT Items.PartNum, Auctions.CurrentPrice
FROM Auctions INNER JOIN
(SELECT DISTINCT Items.ItemID, Items.PartNum
FROM Items
WHERE Items.ItemID = Auctions.ItemID AND
Auctions.EndDate >= #07/01/2003# AND
Auctions.EndDate < #07/07/2003# AND
Auctions.StatusID > 40) ON
Items.ItemID = Auctions.ItemID
ORDER BY Auctions.CurrentPrice DESC

Essentially, I have one table that is basically an inventory list and another that is a table of auction records based on that inventory list. Unfortunately, there exists the possibility for that inventory table to have items with duplicate part numbers and certainly there could be more than one record with the same part number where the auction status is greater than 40.

What I want to generate here, is a distinct recordset of part numbers with the above criteria in descending order of current price.

This looks like it should work, except maybe for the addition of the ItemID field, but I can't seem to test it because I get an error in the JOIN clause, though I don't see anything wrong with it.

 

[lespaul]

Does the sub-select query
(SELECT DISTINCT Items.ItemID, Items.PartNum
FROM Items
WHERE Items.ItemID = Auctions.ItemID AND
Auctions.EndDate >= #07/01/2003# AND
Auctions.EndDate < #07/07/2003# AND
Auctions.StatusID > 40) return any rows when you run it by itself?

What is the error message you are getting?

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[Zybron]

Ah, it does once the following change is made:

FROM Items, Auctions

I didn't notice that before, but it's still giving me a syntax error in the JOIN operation.

 

[Zybron]

One other question about the subquery? Let's suppose I have:

ItemID PartNum
1 123
2 321
3 123

Wouldn't the above subquery produce:

ItemID PartNum
1 123
3 123

Since it finds distinct values for each row, or am I mistaken? I don't see any duplicate values for partnum's after running the query, but I have several thousand records to look through, I could just be overlooking a duplicate.

 

[scriverb]

ACCESS Help: DISTINCT - If the SELECT clause contains more than one field, the combination of values from all fields must be unique for a given record to be included in the results.

Keeping this statement in mind the combination of Items.ItemID and Items.PartNum is unique for all three records in your example above so all three would be returned.

Let's just try running this code to see if we get a recordset we can work with:
SELECT DISTINCT I.ItemID, I.PartNum
FROM Items as I INNER JOIN Auctions as A
ON I.ItemID = A.ItemID
WHERE Auctions.EndDate >= #07/01/2003# AND
Auctions.EndDate < #07/07/2003# AND
Auctions.StatusID > 40;

Post back your results and findings.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Sorry I didn't replace the WHERE clause tables with the Alias's:

SELECT DISTINCT I.ItemID, I.PartNum
FROM Items as I INNER JOIN Auctions as A
ON I.ItemID = A.ItemID
WHERE A.EndDate >= #07/01/2003# AND
A.EndDate < #07/07/2003# AND
A.StatusID > 40;


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Zybron]

Ok, here's a portion of the results, sorted by PartNum to illustrate a point:

ItemID PartNum
13630 31D12450C06
14330 31D12451G0B
13639 31D12452J1B
13617 31D12456C06
13674 31D12457C07
13603 31D12459J1B
14620 31D13143G0B
13823 31D13143G0B
14642 31D13144G0B
13787 31D13144G0B

Please note the last four results. What I need from the query is a set that consists of unique PartNums with the above criteria sorted in descending order of CurrentPrice. I was thinking that by including the ID in the subquery I would get duplicates, but I don't see how to properly join the two queries without including ItemID in the subquery?

Any thoughts?

 

[lespaul]

So, if according to your example you only want Part Num: 31D13144G0B to show up ONE time, which of the Item IDs would you want to include with it?



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[Zybron]

Actually, I don't need the ItemIDs at all, though I see what your question is getting at. If the data is sorted by CurrentPrice DESC, then the highest amount is always the one that I want, so it's not an issue as to which one of the itemIDs I get. As long as it's the one with the highest CurrentPrice, which is why I wanted the data sorted by that field.

Perhaps I should reiterate my goal, maybe I didn't state it well before. I wish to produce a table consisting of unique PartNums from the Items table associated with their CurrentPrice as listed in the Auctions Table where the EndDate falls between two date ranges and its Auction StatusID is > 40 all sorted by CurrentPrice in descending order. And I guess, by the question above, that the distinct PartNums should be determined by highest CurrentPrice?

Does that make sense? Am I asking for something that can only be done via two (or more) queries? If so, then I'll just continue to use the routines I do now, that parses through the distinct PartNums to retrieve the other data I need. I was just hoping to be able to combine this into one query to speed things up dramatically and do away with a large amount of code to get the data sorted properly.

 

[scriverb]

Leslie is correct. When you are matching back to the table Auctions this can only be done with the ItemID. Each ItemID has one or many partnumbers. There have to be multiple records with this situation.

Also, after looking at the entire query I am still trying to figure out why the main query and subquery design. The subquery design now includes the inner join between both tables and the CurrentPrice is available for display. I don't see the purpose of the connection between Auctions and the subquery. Wouldn't this work.

SELECT DISTINCT I.ItemID, I.PartNum, A.CurrentPrice
FROM Items as I INNER JOIN Auctions as A
ON I.ItemID = A.ItemID
WHERE A.EndDate >= #07/01/2003# AND
A.EndDate < #07/07/2003# AND
A.StatusID > 40
Order by A.CurrentPrice Desc;


Post back with your thoughts.



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[lespaul]

I think Bob's last query should do it for you if you remove the I.ItemId from the SELECT portion of the query.



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[scriverb]

Given the overlapping posts how about the following:

SELECT I.PartNum, Max(A.CurrentPrice) as CurrentPrice
FROM Items as I INNER JOIN Auctions as A
ON I.ItemID = A.ItemID
HAVING A.EndDate >= #07/01/2003# AND
A.EndDate < #07/07/2003# AND
A.StatusID > 40
Group By I.PartNum
Order by A.CurrentPrice Desc;

Let's try this query and see if it is what you are looking for.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Zybron]

Hmm, well, sort of. The above query will produce duplicate PartNums, as they might exist more than once with different CurrentPrices, but it would definately be faster for me to parse through a sorted table of PartNums and pull out the duplicates with lower CurrentPrices than the way I'm doing it currently.

Don't know why I didn't think of that before.

Thanks everyone.

 

[scriverb]

Did you run the query or just review it? The Group By on PartNumber should give you one row for each partnumber as the only other field in the row is a Max of the CurrentPrice which should be the same for all rows with the same partnumber. Thus it should be one row.

Post back an example of the actual results please. I don't have a table to test this against.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Zybron]

First, my last post overlapped (we gotta stop doing that

Second, Bob, that does look like it would work, but I get a syntax error in the Having clause.

Any ideas?

 

[scriverb]

Let's change it to this:
SELECT I.PartNum, Max(A.CurrentPrice) as CurrentPrice
FROM Items as I INNER JOIN Auctions as A
ON I.ItemID = A.ItemID
WHERE A.EndDate >= #07/01/2003# AND
A.EndDate < #07/07/2003# AND
A.StatusID > 40
Group By I.PartNum
Order by A.CurrentPrice Desc;

Post back with the results. I had the HAVING before the GROUP BY which is not proper. But, this should be a WHERE clause anyways so the WHERE should be in front of the GROUP BY.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Zybron]

Hmm, well when I attempt to run the new query I get &quot;You tried to run a query that does not include the specified expression 'A.CurrentPrice' as part of an aggregate function.&quot; When I include A.CurrentPrice or CurrentPrice in the Group By I get duplicate PartNums (which I would kind of expect, since it's in the Group By):

PartNum CurrentPrice
CH34D13083E3E 1.99
CH34D13083E3E 4.99
CH34D13423D3E 0.99
CH34D13423D3E 1.99

If there's some way to run this query without including A.CurrentPrice/CurrentPrice in the Group By then I'm probably in business.