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.

 

[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.

 

[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.

 

[poet1234]

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