Query for adding totals from several tables

[rosscortb]

Hi.



I am trying to work out a query for adding the totals of each table then display the total over cost.

One to many relationship.

Personal details table
Salary table
2 enteries
1/12/2014 £3549.50
1/1/2015 £3549.50)

Allowances Table-
3 enteries
1/12/2014 MISC £3036
1/12/2014 COLA £1272.45
1/12/2014 Fuel £2280

So how do I get it to add total the salaries and the total of the allowances then display overall total?

Thanks

Ross

[June7]

Presuming each table also has Employee ID field.

What level do you want to summarize? By pay period, year?

Perhaps do two aggregate queries then join the queries.

Or maybe do a UNION query the use that query as source for aggregate query.

[rosscortb]

each employee does have a employee id field.

At the moment I would like to have a query that look like this for example:
1/12/2014 £3549.50
1/1/2015 £3549.50
Total: £7099
1/12/2014 MISC £3036
1/12/2014 COLA £1272.45
1/12/2014 Fuel £2280
Total: £6588.45
Overal Total: £13,687.45

At the moment I have a salary then one allowance on one line then a salary and a different allowance etc etc

[June7]

Can't do subtotals in query. This is what reports do with Grouping & Sorting features and aggregate calcs in group footers.

[rosscortb]

Thanks- do you know how I get over this issue "At the moment I have a salary then one allowance on one line then a salary and a different allowance etc etc"

So the salary shows far too many times
.
Ross

[June7]

Possible options:

1. build a report with groupings on employee ID and pay period with salary data in pay period group header and allowances in detail section, however, report sum of all pay period salaries will probably be wrong

2. report/subreport arrangement

3. union query

[rosscortb]

Hi Probably not going about it the right way but created a report for salary and allowances and then added them to Total Costing report which I like the look of. Is there any way of putting a criteria on a report as when I have added a new record(id 5) it goes into the report, is there a way to add a criteria asking for id number before running?
See attached.

Ross

[June7]

Options:

1. dynamic parameter in query (input popup or reference to control on form) - I don't do this

2. code that applies filter criteria to report when it opens by referencing textbox on form - I do this with VBA
DoCmd.OpenReport "report name", , , "ID=" & Me.ID