Solved

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

Posted on 2013-01-18
4
284 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 48

Accepted Solution

by:
Dale Fye 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

Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 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