Access Calculations

[Rogue]

Hi, i am just new to access and i want to learn how to apply calculations in access. Currently I have two tables namely:budget & hired. Budget table contains, department,position and number of requirements for each positions, while hired table contains complete listing of hired staffs including respective position and department they were hired. I want to make a query that automatically deducts the positions in hired table from the positions in my budget table. I will really appreciate if someone can help me on this. Thank you in advance.

[June7]

What do you mean by 'deduct' - you want to delete a record? Or you want to calculate difference of total count of positions in each table? This requires aggregate (Totals) queries or use of DCount domain aggregate function. Access Help has guidance on both.

[Rogue]

Yeah I mean to calculate difference of total count of positions in each table. Hope you could give me sample how to do it. tnx

[June7]

Do an aggregate (Totals) query of the hired table that will count the positions by whatever grouping you prefer. Access Help has guidance on creating Totals query.

Join that query to the budget table. Create field with expressions that calculate the difference. Or build a report with that query as data source and do calculation in textbox.

If you want to try creating an all-in-one nested query in the SQL View window, review http://allenbrowne.com/subquery-01.html especially the example for Aggregation: Counts and Totals.

[Rogue]

Tnx for your help, Now I have a query that sums total hired by department and by position. And another query displaying all the vacancies while in another column I want the total hired to be displayed so i tried dlookUp with the following formulas:Hired: DLookUp("[hired]","qry hired","[position]=" & [position]).Wherein "hired" is the column that summarizes all positions hired, "qry hired" is the query name.

[June7]

Why don't you do another query that joins the two data sources on the department and position fields?