Solved

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

Posted on 2013-01-18
4
271 Views
Last Modified: 2013-01-18
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
Comment
Question by:ARampton
  • 2
4 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
ID: 38793907
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 250 total points
ID: 38793918
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38794285
:-0
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38794304
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Outlook Free & Paid Tools
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now