Solved

Select values from one table to use as parameters in another SQL in Access 2000

Posted on 2008-10-22
3
177 Views
Last Modified: 2010-05-18
I have a simple table named duedates that contains 1 record.  This record contains an OpenDate and CloseDate.

I want to grab these two values from this table and use them in a different SQL select to pull data from two tables that have been submitted between these two dates.

The code I have provided shows these dates as being 'hard coded' in the SQL select statement.

Is there a way from within Access 2000's SQL query view to create a SQL statement that will grab these two dates and use their values instead of having to hard code it or having to enter it manually each time the query is run.

Thanks in advance for your help!
SELECT Applications.Resa, Applications.ApplicationID AS ApplicationID, Applications.Equipment, Applications.ServiceLearning, Applications.MathScience, Applications.BudgetRequest, Applications.ProjectTitle, Applications.SubjectArea, Applications.GradeLevel, Applications.Summary, Applications.Objectives, Applications.ParentsInvolved, Applications.TeachersInvolved, Applications.StudentsInvolved, Applications.Caucasian, Applications.AfricanAmerican, Applications.Asian, Applications.LatinoHispanic, Applications.NativeAmerican, Applications.Other, Applications.EvaluateProject, Applications.Schedule, Budget.ApplicationID AS BudgetApplicationID, Budget.LineItem, Budget.Supplier, Budget.Quantity, Budget.UnitCost, Budget.TotalCost
FROM Applications INNER JOIN Budget ON Applications.ApplicationID=Budget.ApplicationID
WHERE Applications.DateSubmitted Between #7/31/2007# And #10/26/2007# And Applications.Resa=[Enter Resa Number]
ORDER BY Applications.Resa, Applications.ApplicationID;

Open in new window

0
Comment
Question by:sadoman
[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
3 Comments
 
LVL 7

Expert Comment

by:Cedric_D
ID: 22777343
use CROSS JOIN:
SELECT Applications.Resa, Applications.ApplicationID AS ApplicationID, Applications.Equipment, Applications.ServiceLearning, Applications.MathScience, Applications.BudgetRequest, Applications.ProjectTitle, Applications.SubjectArea, Applications.GradeLevel, Applications.Summary, Applications.Objectives, Applications.ParentsInvolved, Applications.TeachersInvolved, Applications.StudentsInvolved, Applications.Caucasian, Applications.AfricanAmerican, Applications.Asian, Applications.LatinoHispanic, Applications.NativeAmerican, Applications.Other, Applications.EvaluateProject, Applications.Schedule, Budget.ApplicationID AS BudgetApplicationID, Budget.LineItem, Budget.Supplier, Budget.Quantity, Budget.UnitCost, Budget.TotalCost
FROM Applications INNER JOIN Budget ON Applications.ApplicationID=Budget.ApplicationID
CROSS JOIN DueDates
WHERE Applications.DateSubmitted Between DueDates.OpenDate And DueDates.CloseDate And Applications.Resa=[Enter Resa Number]
ORDER BY Applications.Resa, Applications.ApplicationID;

Open in new window

0
 

Author Comment

by:sadoman
ID: 22799644
I tried the cross join but it gives me an error message.  I've been searching information on cross joins and I'm not sure if it works in Access 2000.  
0
 
LVL 7

Accepted Solution

by:
Cedric_D earned 250 total points
ID: 22799882
well, then any CROSS JOIN can be replaced by INNER JOIN ON 1=1
or INNER JOIN ON T1.SOMEFIELD=T1.SOMEFIELD
0

Featured Post

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

691 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