• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Creating Access Query with Criteria data from first row of another query

I wish to create a query which findsall dates greater than the one defined in the first row of another unique query

I have done this with the criteria date showing in a live form but don't know how to bypass the form to go to the first date shown inquery directly

IE something like

SELECT [Q-ProjectTime].HoursDate,[Q-ProjectTime].Hours
FROM [Q-ProjectTime]
WHERE ((([Q-ProjectTime].HoursDate)>"[Q-ProcessPeriods].[StartDate](first row only)"));

Any suggestions?
0
ARampton
Asked:
ARampton
  • 2
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
try this one


SELECT [Q-ProjectTime].HoursDate,[Q-ProjectTime].Hours
FROM [Q-ProjectTime]
WHERE [Q-ProjectTime].HoursDate > (select Top 1 [StartDate] from [Q-ProcessPeriods]);

might need more tweaking depending on the result of  [Q-ProcessPeriods]
0
 
Dale FyeCommented:
How do you define the "first row" of the other query?

Assuming that your other query is already sorted, you could do something like:

Something like:

SELECT [Q-ProjectTime].HoursDate,[Q-ProjectTime].Hours
FROM [Q-ProjectTime]
WHERE [Q-ProjectTime].HoursDate> (SELECT Top 1 [StartDate] FROM yourQuery)
0
 
Rey Obrero (Capricorn1)Commented:
:-0
0
 
hnasrCommented:
Try this: pk is the primary key field of the table.

SELECT [Q-ProjectTime].HoursDate,[Q-ProjectTime].Hours
FROM [Q-ProjectTime]
WHERE ([Q-ProjectTime].HoursDate>(Select Max([Q-ProcessPeriods].[StartDate]) from [Q-ProcessPeriods] WHERE pk =(select min(pk) from [Q-ProcessPeriods]))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now