December 12, 2006 at 5:24 pm
I am ACCESS and SQL begginer.
I can not find an answer to this simple problem. My head is exploding from googleing, so i turn to you guys for the help, before i destroy my keyboard
(if im posting in a wrong place please point me in the right direction)
How do i combine those 2 queries ?
Query1 (returns job_num from the very last record of Survey table) (this one works)
SELECT TOP 1 Survey.job_num
FROM Survey
ORDER BY Survey.SurveyID DESC;
Query2 (i want it to return a record if there is a record in Projects that matches the resoult of Query1)
SELECT Projects.[Job Number]
FROM Projects
WHERE Projects.[Job Number]= Whatever Query1 gives me;
this must be simple, but im stuck
am i clear enough ?
thank you
December 12, 2006 at 7:55 pm
Two methods:
SELECT Projects.[Job Number]
FROM Projects
WHERE Projects.[Job Number]=(SELECT TOP 1 Survey.job_num FROM Survey ORDER BY Survey.SurveyID DESC)
or
declare @intmyint as integer
set @intmyint = (SELECT TOP 1 Survey.job_num FROM Survey ORDER BY Survey.SurveyID DESC)
SELECT Projects.[Job Number] FROM Projects WHERE Projects.[Job Number]= @myint
December 13, 2006 at 7:31 am
Also, in MS Access you can use a Saved Query just as you would use a Table.
So create your Query 1 and save it. Then design Query 2 and select the saved Query 1 from the Queries tab of Show Table dialog in the query designer as the source of your data, rather than a Table.
December 13, 2006 at 8:54 am
Thank you guys.
December 14, 2006 at 4:05 pm
This could also be completed using one query by joining the two tables like this,
SELECT TOP 1 Survey.job_num, Projects.[Job Number]
FROM Survey Inner join Projects on Projects.[Job Number]= Survey.job_num
ORDER BY Survey.SurveyID DESC
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply