One or two queries?

[aner1755]

Hi All!
I need help with a SQL query.
I got two tables (tblPXtime and tblBudgetTime) with columns and relationships that can be seen in the attachments. From these tables I’d like to extract the date and time, in such manner that all date and time from tblBudgetTime should be collected along with possible matches from tblPXtime. I thought it could be done with OUTER JOINS but I didn’t succeed. Is it even possible to acquire this with a single SQL query?

One of my try goes like;
SELECT tblPXtime.BudgetID, tblBudgetTime.[Year/Month], tblPXtime.TimePX, IIf(IsNull([tblBudgetTime.COs]),[tblBudgetTime.BudgetTime],[tblBudgetTime.BudgetTime]+[tblBudgetTime.COs]) AS TotBudget
FROM tblBudgetTime LEFT OUTER JOIN tblPXtime ON (tblPXtime.BudgetID=tblBudgetTime.BudgetID) AND (tblBudgetTime.[Year/Month]=tblPXtime.[Year/MonthPX])
WHERE tblPXtime.BudgetID=1;

But the result is showed in pic result.gif. Notice that just some of the dates from table tblBudgetTime is listed, but wanted all of the dates…
Cheers!

 

[duluter]

I used this and it seemed to work:

SELECT tblBudgetTime.BudgetID, tblBudgetTime.[Year/Month], IIF(isnull(tblBudgetTime.COs),tblBudgetTime.BudgetTime,tblBudgetTime.BudgetTime + tblBudgetTime.COs) as TotBudget, tblPXTime.TimePX
FROM tblBudgetTime LEFT JOIN tblPXTime ON (tblBudgetTime.[Year/Month] = tblPXTime.[Year/Month]) AND (tblBudgetTime.BudgetID = tblPXTime.BudgetID) WHERE tblBudgetTime.BudgetID = 1;


Duluter

 

[duluter]

I think the main problem is in your WHERE clause. It should be asking about the BudgetID field in the tblBudgetTime table, not the tblPXTime table. Also, in the SELECT clause, bring in tblBudgetTim.BudgetID, not tblPXtime.BudgetID. If you bring in tblPXtime.BudgetID, the IDs on some rows will be blank (because some rows will not have a matching record in tblPXtime (because it's a left join)).


Duluter

 

[aner1755]

A big SALUT to you for helping me out! :-D

Here is the code I finally used (just some correction of small errata in your code);

SELECT tblBudgetTime.BudgetID, tblBudgetTime.[Year/Month], tblPXTime.TimePX, IIf(IsNull([tblBudgetTime.COs]),[tblBudgetTime.BudgetTime],[tblBudgetTime.BudgetTime]+[tblBudgetTime.COs]) AS TotBudget
FROM tblBudgetTime LEFT JOIN tblPXtime ON (tblBudgetTime.[Year/Month] = tblPXtime.[Year/MonthPX]) AND (tblBudgetTime.BudgetID = tblPXtime.BudgetID)
WHERE tblBudgetTime.BudgetID = 1;


Cheers!

 

[duluter]

Glad it worked!