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]

If we keep trying we will finally get this thing right. Even a blind squirrel finds acorn every once in a while, right?

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

Let's try this one.


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

 

[Zybron]

Yeah, I just wish my acorn wasn't so hard to find, or I wasn't so blind.

I still get the same error message.

Perhaps I should have mentioned this earlier, but the database in question is in Access 2000 format. Is this a limitation of that version?

 

[scriverb]

Send me a small database with a sample of your tables and the query. Let me get it right here. Since I don't have data I can't seem to get it right. See my email address in my profile.

I will post back the final answer and I have it right.

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

 

[scriverb]

I created some tables with data and I think the following will give you the results requested.
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 Max(A.CurrentPrice) DESC;

Post back with the results.

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

 

[Zybron]

Bob,

Great! That works. I sent you an email with a sample database. It took a while to pare it down to something manageable by email. I guess you can just delete it.

Thanks again for your help.

 

[scriverb]

I told you we would get this thing right. Some days I can't think past my nose. Good luck with your project.

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