Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query using Year in 2 unrelated tables

Status
Not open for further replies.

Myron60

Technical User
Jan 31, 2013
10
US
Hello,
I have a table called "Jobs" with JobNumber and EntryDate fields and I have a table with MileageYear and MileageRate fields. What I am trying to do is use a query to take the EntryDate and link it to the MileageYear and show me the MileageRate for that year. I have tried everything I can think of but I can't get it to show me the rate. If I did it right here is a pic of my query.

 
A starting point:
SQL:
SELECT *
FROM Jobs J, yourMileAgeTable M
WHERE Year(J.EntryDate) = M.MileageYear

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Thank you for responding so quick, I'm not sure I understand.

SELECT Jobs.EntryDate, Jobs.JobNumber, Year([EntryDate]) AS JobYear, Year([MileageYear]) AS RatePerYear
FROM Jobs LEFT JOIN MileagePerYear ON Jobs.EntryDate = MileagePerYear.MileageYear
GROUP BY Jobs.EntryDate, Jobs.JobNumber, MileagePerYear.MileageYear
ORDER BY Jobs.JobNumber;

This is what I have right now.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top