• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

need help with an Access query

Please see attached example.

I want a list of all the award numbers in table 1 that do not have a match in table 2.  But I want Access to only search awards in table 2 that have calendar year 2009, calendar quarter 3, and last update 10/1/2009.  In this example, the list would include award numbers:  xoexf, eisld, and clien.  

I want a query where I can simply change the year, quarter, and last update and get a new list, based on those parameters.  Is this possible or do I have to separate table 2 into a table for each date within the last update field and do separate queries?
example.xlsx
0
nblassen
Asked:
nblassen
  • 8
  • 7
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
select T1.awardnumber
table1 As T1 left join Table2 as T2
on T1.awardnumber=t2.awardnumber
where T2.year=2009 and t2.quarter=3 and t2.lastupdate=#10/1/2009# and
t2.awardnumber is null
0
 
peter57rCommented:
You need to create a saved query that does the selection from table2; you can use parameters for the criteria if you wish.

You can then then use the Find Unmatched query wizard , starting from table1 and using the saved query as the second table.
0
 
peter57rCommented:
Cap1- a rare day this is, but that SQL won't work Im afraid.  
The selection from table2 will destroy the Left join and convert it into an Inner join.

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
nblassenAuthor Commented:
peter57r - you're right. It didn't work.  I had tried it before as well.  Do you have an alternative solution?
0
 
Rey Obrero (Capricorn1)Commented:


try this then,

select T1.awardnumber
table1 As T1 left join
(select t2.awardnumber, t2.[year],t2.lastupdate from Table2 as T2
where T2.year=2009 and t2.quarter=3 and t2.lastupdate=#10/1/2009#
) as t3
on T1.awardnumber=t3.awardnumber where
t3.awardnumber is null
0
 
peter57rCommented:
"Do you have an alternative solution? "

in addition to the one i already posted ?
0
 
Rey Obrero (Capricorn1)Commented:
oops...


try this then,

select T1.awardnumber
from table1 As T1 left join
(select t2.awardnumber, t2.[year],t2.lastupdate from Table2 as T2
where T2.year=2009 and t2.quarter=3 and t2.lastupdate=#10/1/2009#
) as t3
on T1.awardnumber=t3.awardnumber where
t3.awardnumber is null
0
 
nblassenAuthor Commented:
Sorry peter57r, forgot you had already posted a solution.  Thank you.
0
 
Rey Obrero (Capricorn1)Commented:
nblassen,
did you try the query posted at http:#a35029127
0
 
nblassenAuthor Commented:
capricorn1:
Yes, I did, but I can't seem to get it to work.  I'm a novice user.  What is T3 in your example?  Do I need to create another table?
0
 
Rey Obrero (Capricorn1)Commented:
<Do I need to create another table?>  No
you will use the two tables that you have..

post the sql statement of the query you are using..

or upload a copy of the db
0
 
nblassenAuthor Commented:
SELECT [Table 1].[AWARD NUMBER], [Table 2].CALENDAR_YEAR, [Table 2].CALENDAR_QUARTER, [Table 2].TIME_STAMP_OF_LAST_UPDATE
FROM [Table 1] LEFT JOIN [Table 2] ON [Table 1].[AWARD NUMBER]=[Table 2].AWARD_NUMBER
WHERE ((([Table 2].AWARD_NUMBER) Is Null) AND (([Table 2].CALENDAR_YEAR)=2009) AND (([Table 2].CALENDAR_QUARTER)=3) AND (([Table 2].TIME_STAMP_OF_LAST_UPDATE)=#10/1/2009#));
0
 
Rey Obrero (Capricorn1)Commented:
what fields do you want to see in the result?
0
 
nblassenAuthor Commented:
I want to see the award number from Table 1.
0
 
Rey Obrero (Capricorn1)Commented:
copy and paste this in the SQL view of a new query


SELECT T1.[AWARD NUMBER]
FROM [Table 1] As T1 LEFT JOIN
(Select T2.CALENDAR_YEAR, T2.CALENDAR_QUARTER, T2.TIME_STAMP_OF_LAST_UPDATE From [Table 2] as T2 WHERE T2.CALENDAR_YEAR=2009 AND T2.CALENDAR_QUARTER=3 AND T2.TIME_STAMP_OF_LAST_UPDATE=#10/1/2009#) As T3
ON T1.[AWARD NUMBER]=T3.AWARD_NUMBER
WHERE T3.AWARD_NUMBER Is Null

0
 
nblassenAuthor Commented:
I pasted it into the SQL view, but when I tried to run it, a pop up box appeared that said Enter Parameter Value, T3.Award_Number.

0
 
Rey Obrero (Capricorn1)Commented:
oops, try this one


SELECT T1.[AWARD NUMBER]
FROM [Table 1] As T1 LEFT JOIN
(Select T2.AWARD_NUMBER,T2.CALENDAR_YEAR, T2.CALENDAR_QUARTER, T2.TIME_STAMP_OF_LAST_UPDATE From [Table 2] as T2 WHERE T2.CALENDAR_YEAR=2009 AND T2.CALENDAR_QUARTER=3 AND T2.TIME_STAMP_OF_LAST_UPDATE=#10/1/2009#) As T3
ON T1.[AWARD NUMBER]=T3.AWARD_NUMBER
WHERE T3.AWARD_NUMBER Is Null
0
 
nblassenAuthor Commented:
It worked!  Thanks for all your help!
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 8
  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now