Solved

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

Posted on 2013-01-18
4
274 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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

776 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