I'm not sure why you wrapped the entire statement in ()s.
SELECT Query1.[Case Number],Query1.[Begin Date]
FROM Query1
WHERE Query1.[Begin Date] In
(SELECT TOP 3 [Begin Date]
FROM [Query1] B
WHERE [Query1].[Case Number] = B.[Case Number]
ORDER BY [Begin Date])
ORDER BY Query1.[Case Number];
--
Duane Hookom
Microsoft Access MVP
:
Actually, this was the second error. The first indicated that the syntax was
wrong and I needed to add parentheses around the SELECT statment. I did
that, and got the error referred to above.
This is the SQL the brought up the "you have written a subquery that can
return more than one field.....":
(SELECT Query1.[Case Number],Query1.[Begin Date]FROM Query1 WHERE
(((Query1.[Begin Date]) In (SELECT TOP 3 [Begin Date] FROM [Query1] B
WHERE [Query1].[Case Number] = B.[Case Number] ORDER BY [Begin Date])))
ORDER BY Query1.[Case Number]
:
Please provide the exact SQL that is causing this error.
--
Duane Hookom
Microsoft Access MVP
:
Well, now I'm getting an error message:
"You have written a subquery that can return more than one field without
using the EXISTS reserve word in the main query's FROM clause..."
:
I just re-created this query in the Northwind and it worked for me. Do you
have any records where Begin Date is null?
This is my exact SQL copied from a working query:
SELECT Query1.[Case Number], Query1.[Begin Date]
FROM Query1
WHERE (((Query1.[Begin Date]) In (SELECT TOP 3 [Begin Date]
FROM [Query1] B WHERE [Query1].[Case Number] = B.[Case Number] ORDER BY
[Begin Date])))
ORDER BY Query1.[Case Number];
--
Duane Hookom
Microsoft Access MVP
:
Here's what I entered
SELECT * FROM [Query1] WHERE [Begin Date] IN (SELECT TOP 3 [Begin Date]
FROM [Query1] B WHERE [Query1].[Case Number] = B.[Case Number] ORDER BY
[Begin Date])
Query Name is Query1 Field Names are Begin Date and Case Number
The error message says "The syntax of the subquery is incorrect" ...check
the subquery's syntax and enclose the subquery in parentheses.
I appreciate your efforts here...you're working with a novice at best.
:
What exact sql did you try? Did you change the query name? Are the field
names correct? Did you really have spaces and symbols in your field names?
--
Duane Hookom
Microsoft Access MVP
:
When I enter this in the Criteria line, I get an "invalid syntax" message.
:
Try something like:
SELECT *
FROM [QueryName]
WHERE [Date of Service] IN
(SELECT TOP 3 [Date Of Service]
FROM [QueryName] B
WHERE [QueryName].[Case #] = B.[Case #]
ORDER BY [Date Of Service])
--
Duane Hookom
Microsoft Access MVP
:
I have a query that contains two tables: One contains Case Numbers for one
group of clients (children), the other contains case numbers and service
dates for all clients. The query asks to match case numbers from both tables,
and displays case number and service date for only the child clients:
Case # Date of Service
1 1/1/07
1 1/2/07
1 1/3/07
1 1/4/07
2 2/5/07
2 2/6/07
2 3/7/07
2 4/7/07
2 5/7/07
I want to retrieve only the first three
dates for each case number.
Any ideas?
