Solved

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

Posted on 2008-10-22
3
174 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
  • 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Alter an update query which rounds 7 33
SQL Query resolving a string conversion issue 26 42
MS SQL Inner Join - Multiple Join Parameters 2 31
Running Total in Access 4 47
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

813 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

11 Experts available now in Live!

Get 1:1 Help Now