Duplicated values yielded from report combining two tables / queries

[BishoyMaher]

Hi all ... i m new here and I have a silly problem facing me and couldn't solve for long ago.
My database is mainly composed of two tables, Actuals & Budget.
Actual table collects records on a monthly basis for certain projects' cost elements (Labor, Equipment, Material & Sub-Contractors), whilst Budget contains the fixed budget of each project broke down to the same project cost elements.
What I need to do is to make a report that shows the total actual amounts for each cost element of each project and also shows the budget for same ones.

For Example, consider project A has a labor budget of 1,000 $ and has actuals of 150$ in the 1st month and 220$ in the second month, so the report should show the budget cell at 1,000$ and the actuals as 370$.


What I keep getting from many trials is that the budget values are repeated and yield wrong values (the report shows the budget to be 2,000$ and the actuals as 370$)
It keeps repeating budget records as much No's of actual records existing.

I really tried different approaches and built my database many times but couldn't find a resolution to this issue.

I m desperate for your help.

Note: If further data needed please let me know

[Minty]

Your report is doing a running sum over both fields, when it should be only summing the Actuals.
without seeing how you have constructed the report can't do a lot more to help. Consider stripping out sensitive info and zipping a small set of data and your report into a database to upload here.

[BishoyMaher]

Minty ... the attachment is here ...
Just consider the two tabled Budget & Cost and try to make a report to compare field "LaborFC4" from Budget Table with "LaborTot" from Cost Table as a sample report.

Thanks in advance.

[June7]

CostCode is not unique in either table (not even unique in CostCodes table) therefore when you join the tables on CostCode you get more records than expected. You have set up relationships on CostCode fields yet your report query joins on [PFSR Index] fields. You are not relating tables on Primary and Foreign keys.

Consider this query for a start:
SELECT [PFSR Indices].[PFSR Index], Budget.LaborFC4, Cost.LaborTot, Cost.EqptActual, Cost.EqptAccrual, Cost.EqptDeduction, Cost.EqptTotal
FROM Cost RIGHT JOIN (Budget RIGHT JOIN [PFSR Indices] ON Budget.[PFSR Index] = [PFSR Indices].[PFSR Index]) ON Cost.[PFSR Index] = [PFSR Indices].[PFSR Index];

There should be 71 records, however, the query shows 81. So this means the PFSR is not unique in Budget and/or Cost.

3 PFSR in Budget have 2 records each

PFSR Index	CountOfID
5.J.Roof Canopy	2
5.L.Sky Light	2
6.H.Earthing and Lightning	2

12 PFSR in Cost have 2 records each

PFSR Index	CountOfID
1.E.Retaining Walls	2
1.F.SOE Mass Concrete	2
2.A.1.Excavation&Muck-Away	2
2.A.Excavation&Muck-Away	2
2.B.Backfilling	2
3.A.1.Formwork	2
3.B.1.Concrete Works	2
3.C.1.Waterproofing and insulation	2
3.D.Earthing & Lightning Protection	2
4.A.1.Rebar Work	2
4.B.Rebar Threadening	2
6.B.Plumbing	2

Also, there are PFSR values in Budget (7) and Cost (9, 5 of which are in the duplicate list) that are not in [PFSR Indices].

Taken together, this accounts for the 10 additional records in the query - 3 from Budget and 7 from Cost.

If it is correct that Budget and Cost can have duplicate PFSR records then probably need to first do two aggregate queries, one for each Budget and Cost, then join those aggregate queries to [PFSR Indices] as the record source for report.


Advise no spaces nor punctuation/special characters (underscore only exception) in naming convention.

[BishoyMaher]

Thanks June7 for your reply.
I have cleaned the file and removed unnecessary duplications.
It's ok to have duplicated cost codes in the cost files as it captures data for same records on a timely basis.
I also created 2 queries for Cost & Budget as you suggested, I just need to know how to link them to the report, because as far as I know reports are built on tables and not queries (Not sure about this).

Looking forward to your valuable bits of advice.

[BishoyMaher]

here is the revised filePFSR Simple R5.zip

[Minty]

because as far as I know reports are built on tables and not queries (Not sure about this).

Looking forward to your valuable bits of advice.

No - a report can and probably should be built around queries, dragging in all the tables required to make it work.

I hadn't replied as I was also struggling to make the connection between the tables make sense.

[June7]

I would not have suggested using query as RecordSource for report if it were not possible.

If you set up relationships, don't include queries in Relationship builder, only tables.

Still spaces in naming convention.

Instead of saving number keys, you are saving full descriptive text (the PFSR values). Indexing on text data is slower. Replicating text data takes more storage.

[BishoyMaher]

Thanks for your valuable input ... I followed your advises and found a way to get the reports done ...
Appreciate it ...