Report that calculates based on 2 queries (or tables)--having issues

[dlingley]

Purpose: To track the balance of funds by account (Encumbrance – Expense = Balance).



Function: I have to make a report that groups the records (employees) by account number* from two tables (tblEncumbrance & tblExpense).

*Issue1: The account number consists of several (6) fields: Fund, Organization, Account, Program, & Activity. However, not all the above account fields have data (often times the activity is blank and sometimes the program is blank, but essentially any one of the 6 fields could be blank – employee error, but it is not yet a forced required field in the database).
Acct string example: Fund - Org - Acct - Prog - Actv

T17

055

C23

50

DCE

Issue2: Sometimes there are expenses for an account (string) that does not have an encumbrance.

I have made two queries: (successfully) (I also made 2 queries that puts the below results in a new table)

• that totals the encumbrance amount by person within the Accounting string.
• that totals the expense amount by person within the Accounting string.

Now I have to make a report that lists all the employees within the account string an calculates the encumbrance – expense. It sounds simple, but I am not able to successfully do this.

Here is the code:

SELECT [tblEncumbranceTotal.Fund] & "-" & [tblEncumbranceTotal.Org] & "-" & [tblEncumbranceTotal.Account] & "-" & [tblEncumbranceTotal.ProgramCode] & "-" & [tblEncumbranceTotal.Activity] AS FOAPAL, tblEncumbranceTotal.Fund, tblEncumbranceTotal.Org, tblEncumbranceTotal.Account, tblEncumbranceTotal.ProgramCode, tblEncumbranceTotal.Activity, tblEncumbranceTotal.EmployeeName, tblEncumbranceTotal.EmployeeID, tblEncumbranceTotal.Encumbrance, tblExpenseTotal.GrossSalary, ([tblEncumbranceTotal.Encumbrance]-[GrossSalary]) AS Balance
FROM tblEncumbranceTotal INNER JOIN tblExpenseTotal ON tblEncumbranceTotal.EmployeeID = tblExpenseTotal.EmployeeID;

Here are the results:

Fund	Org	Account	ProgramCode	Activity	EmployeeName	EmployeeID	Encumbrance	GrossSalary	Balance
F03	020	C05	40		Doe, Jane	329520	$4,500.00	$3,920.00	$580.00
F03	020	C05	40		Doe, Jane	329520	$4,500.00	$2,512.50	$1,987.50
F03	020	C05	40		Doe, Jane	329520	$4,500.00	$8,325.42	($3,825.42)

Here is what it SHOULD produce: (note, I put pale yellow where the fields are not necessary, but just show so you can see what table they are from: Green is encumbrance; Yellow is expense.

F03	020	C05	40	ACT2
					Doe, Jane	Doe, Jane	329520	329520	$4,500.00	$3,920.00	$580.00
					Smith, John	Smith, John	441444	441444	$2,800.00	$2,000.00	$800.00
					Zulu, Fred	Zulu, Fred	552555	552555	$10,950.00	$5,400.00	$5,550.00
									$18,250.00	$11,320.00	$6,930.00
T17	510	C23	50
					Doe, Jane	Doe, Jane	329520	329520	$23,203.44	$8,325.42	$14,878.02
					Zulu, Fred	Zulu, Fred	552555	552555	$25,000.00	$12,500.00	$12,500.00
									$48,203.44	$20,825.42	$27,378.02
T17	055	C23	50	DCE
						Doe, Jane		329520		$2,512.50	($2,512.50)
									$0.00	$2,512.50	($2,512.50)
									$66,453.44	$34,657.92	$31,795.52

Any suggestions/advice on how to achieve this?

[June7]

Your color coding doesn't seem to work but think I get the idea anyway.

Looks like there are multiple expense records with the same accounting info but only one encumbrance record with the same account structure so every expense record must join to the single encumbrance, hence the repetition of encumbrance value.

Think need a subreport for the expenses and in order to deal with the expenses without a related encumbrance, the main report RecordSource would have to include every possible accounting structure and handle null values with Nz([encumbrance,0])

[rpeare]

Can you provide a sample of your database for analysis. Just make a copy of your database, change or delete any private information to something generic then zip up and upload your copy to this site.

[dlingley]

Hi Thank you.

I think I have it working. Although I am having one problem (that stems from a problem with the original database design--which I don't have access to, although I may be able to convince them to let me fix it once I reveal the problem and the ramifications).

The problem I am having is that I am sometimes getting two lines of data for a person within the accounting string (that I call FOAPAL).

I found the reason for this problem is that (in the expense table) there are a few names that do not match (For example: EmployeeID# 335214 may be listed as: Santo, Thomas and Santos, Thomas J. or a name misspelled on one expense record).

I know the solution to this is to have the form look up the employee name via a combo or look-up box.

It has been a little while since I have done this (I'm an accounting student and just haven't done it in a while). I would like to practice it before I try to convince them to let me change their existing database (it is huge and they depend on it and the person who created it is gone).

I'm sure I can find a thread here that can help me do this (but if anyone knows of a one, if you can link me to it that would be great).

What I need is to go into their existing expense and encumbrance forms and change the form to look up the employee name (from the employee table) and have it displayed, as opposed to having to enter it (causing duplicates).

Thanks again for your help.

[June7]

Should not save employee name in expense table. Save the EmployeeID.

Review:
http://datapigtechnologies.com/flash...combobox1.html
http://datapigtechnologies.com/flash...combobox3.html
http://datapigtechnologies.com/flash...combobox3.html

[dlingley]

Okay, awesome ... that is helpful.

In the meantime, I need to identify which records have name issues (don't match).

I made two queries (successfully) that creates one record per employee per account string (Fund, Org, etc.), but it creates two records if the name do not match (even though I'm not matching on name; I'm matching on EmpID & acct string.

So now I just want to find the employeeID that have names that do not match (so I can have them fixed and run a new report), but my code isn't working.

Here is my code:
SELECT tblEncumbranceTotal.EmployeeID, tblEncumbranceTotal.EmployeeName, tblExpenseTotal.EmployeeID, tblExpenseTotal.EmployeeName
FROM tblExpenseTotal INNER JOIN tblEncumbranceTotal ON tblExpenseTotal.EmployeeID = tblEncumbranceTotal.EmployeeID
WHERE tblEncumbranceTotal.EmployeeName!= tblExpenseTotal.EmployeeName;

I thought this would be easy, but Access does not like my code. I'm getting a "syntex error" (missing operator) in query expression 'tblEncumbranceTotal.EmployeeName!= tblExpenseTotal.EmployeeName'

How do I do a select for fields that do NOT match (name) based on fields that do (EmpID).

Thanks again.

[June7]

Not really understanding the issue. Why is the name even in the Encumbrance record? Should have a table of Employees with their EmployeeID and name parts. Each employee should be unique in that table. If the Encumbrances table has the correct EmployeeID then ignore (even delete) the name from Encumbrances table. Retrieve the employee name by joining Encumbrances table with Employees.

If there is more than record for an employee in Employees table, that is another issue.

[dlingley]

Hi, Sorry for the delay (I'm wrapping up my internship).

This is not a normalized database. It is an excel file with two tabs of data (one for encumbrances and one for expenditures). They have a bunch of fields and tons of records. They've asked me to create an access report to determine the encumbrance status (of all employess) by account number. The report (and queries) will be used for a few months until they get another system.

I did get it to work. Howevever, it only works perfectly with good data. I gave ideas about how to ensure the data is clean.

So this issue is resolved.

Thanks again for all those who have responded and this wonderful resource!!!