Trying to Join two Queries

[poet1234]

I am having a problem trying to join two queries. The first query, which is called qry_Planned_By_Month comes from a table (tbl_planned) that captures budget planning information. I created an expression in this query called SumOfPlanned. It takes the sum of all common budget line items (BLI) and is broken down by month.

The second query is called qry_Actuals_By_Month, which comes from a table (tbl_actual) that captures the actual amount spend against each BLI. Again, I created an expression in this query called SumOfActuals. It takes the sum of all common BLIs broken down by month.

Problem: I am trying to create a third query where I can compare planned versus actual. The formulas that I am using to do this are accurate. However the result set contains duplicate data. For example, I may have three entries on the planned side (March, June, and December) and on the actual side for the same BLI, I only have data in February. It displays the February data three times (because it is in the same row as the March, June, and December data.

Is there anything I can do? Thanks very much in advance.

 

[Pat Hartman]

Your join is incomplete. It needs to include year/month as well as account.

 

[poet1234]

Thank you. I added account and year and am still having the same problem. I attached a view of what my output needs to look like. I am truly at a loss and am wondering if I need to re-engineer my tables. Please have a look. Thanks.

 

[Pat Hartman]

I understand what your output needs to look like. You need to post your query and possibly a data sample.

 

[poet1234]

Thank you Pat Hartman, I will post a sample D/B sometime tonight

 

[poet1234]

Attached, please find my sample database. This database contains the queries that I am trying to join. Please refer to my earlier post where I attached a .gif to show sort of what my output should look like. The query that my output should be based upon is called qryCostVariance_byMonth. You will see a table called tbl_BLI. In my customer's case, BLI would is the equivalent of an "account." Any suggestions would be very much appreciated.

 

[Pat Hartman]

You didn't do what I told you to do in my first response. You are joining only on a single field. You need to join on all three fields. You also have a data issue because none of the planned data dates equal any of the actual data dates so if you do an inner join you end up with an empty recordset. I am posting the correct query using left joins so that you will see all the planned amounts even if there were no actuals reported for the same period.

SELECT qryPlanned_byMonth.BLICode, qryPlanned_byMonth.Month, qryPlanned_byMonth.SumOfAmountPlanned AS Planned, qryActuals_byMonth.TransactionMonth, qryActuals_byMonth.SumOfCost AS Actual, [Planned]-[Actual] AS CostVariance, [CostVariance]/[Planned] AS [CostVariance%]
FROM qryPlanned_byMonth LEFT JOIN qryActuals_byMonth ON (qryPlanned_byMonth.Month = qryActuals_byMonth.TransactionMonth) AND (qryPlanned_byMonth.FY = qryActuals_byMonth.FY) AND (qryPlanned_byMonth.BLICode = qryActuals_byMonth.BLICode);

 

[poet1234]

Thank you very much. I will work on this today and may have additional questions (hopefully not!). Thanks again!