[FergieF]

The Subtotal field in my Expenses tbl contains the following expression:

IIf([Expenses]="Tuition/Fees" Or "Airfare" Or "Taxi" Or "Ground Transportation" Or "Registration Fee" Or "Miscellaneous" Or "Salary for the period",[OneTimeCost],([Rate]*[Duration]))

However, it is not producing the result I need.
The result from the truepart is accurate, but nothing is resulting from the falsepart.
i.e. I do not see the calculation for [Rate]*[Duration] in the results.

Any input would be greatly appreciated.

[ScottGem]

OK, you are getting closer. But you have to fully qualify each condition. So it has to be:


IIf([Expenses]="Tuition/Fees" Or [Expenses]="Airfare" Or [Expenses]="Taxi" Or [Expenses]="Ground Transportation" Or [Expenses]="Registration Fee" Or [Expenses]="Miscellaneous" Or [Expenses]="Salary for the period",[OneTimeCost],([Rate]*[Duration]))

What I would recommend is that you create a lookup table for Expense types. And bunch all the OneTimeCost types together. So you can do it this way.

IIf([Expenses]<20 ,[OneTimeCost],([Rate]*[Duration]))

[FergieF]

I keyed in the first expression, but I'm getting the same result as before. There is no return for the false part.

[ScottGem]

Can you show me the whole SQL statement and attach an example of the results of the query?

[FergieF]

I have not created any queries. Would I still be able to view a SQL statement?
The file is still not uploading.

[ScottGem]

I'm confused. Where are you putting the IIF statement if not in a query?

Are you trying to attach a file here? What type of file and how big?

[FergieF]

I'm using the IIF statements as an expression for the Subtotal field.
I want the subtotal to include [Rate]*[Duration] as well as OneTimeCost where the former is not applicable

The file is 3.0 MB. 1the file name extension is .bmp

[ScottGem]

Aha, So you are using the Calculated field feature in Access 2010? I do not recommend using that feature, especially for complex expressions. It would probably work with the Rate*Duration, but the IIF() might not work.

Calculations should be done using a query. So you use Query Design mode and enter Subtotal: IIF(... ) in a blank column.

As for the BMP, 3 Mg is too large. The Attachments box tells you the max file sizes for attachments. Try saving the screen shot as a JPG or zip it.

[FergieF]

I zipped it, but the file still exceeds the max file size. The compressed size of the file is 125KB.

Since the database I was working on is at work, I created a Sample Database with an Expenses tbl. I run the query, but I'm getting the same results

[ScottGem]

You can use one of the cloud storage sites like Skydive and upload it there and send me a link.

[FergieF]

That site is currently blocked. So I will send the link later this evening.
But, I am still having problems with the syntax of the expression. Like I mentioned earlier, the query is producing the same results.

The expression used in the Subtotal (calculated) field is:

IIF([Expenses]= "Per Diem" OR "Hotel Accommodation", ([Rate]*[Duration]), [OneTimeCosts])

[ScottGem]

The expression used in the Subtotal (calculated) field is:

IIF([Expenses]= "Per Diem" OR "Hotel Accommodation", ([Rate]*[Duration]), [OneTimeCosts])

As I've said, you have to fully enter each condition so it needs to be:

IIF([Expenses]= "Per Diem" OR [Expenses] = "Hotel Accommodation", ([Rate]*[Duration]), [OneTimeCosts])

But if that doesn't work, I need to work with the data to see why.

[FergieF]

The database contains the following tables:

tblExpenses

Employee Id
Expenses
Allocation Code/Work Order
Rate
OneTimeCost
Duration
Subtotal

tblOverseas Visit

Place to be visited
Purpose of visist
Start Date
End Date

tblEmployees Expenses

Employee Id
First Name
Last Name
Department

Note: The Overseas Visit tbl is a subdatasheet of the Employees Expenses tbl and the Expenses tbl is a subdatasheet of the Overseas visit tbl.

1. Each employee completed one or more course(s). Jim (an employee) completed two courses in different places.
He did a Driving course in Alabama and a First Aid course in The Bahamas.
This is how the screenshot may have looked:

For the first course:

tblEmployee Expenses

Employee ID First Name Last Name Department 1234 Jim Doe Customer Relations

tblOverseas visit

Place to be visited Purpose of Visit Start Date End Date
Alabama Driving Course June 4, 2012 June 10, 2012


tblExpenses

Expenses Rate Duration OneTimeCosts Subtotal

Per Diem $ 80 7 $ 560Airfare $300 300
Hotel Accommodation 98 6 588
Ground Transportation
Taxi
Registration Fee
Tuition/Fees
Salary For The Period
Miscellaneous 200 200

TOTAL $1,648


This is for the second course:

tblEmployee Expenses

Employee ID First Name Last Name Department
1234 Jim Doe Customer Relations

tblOverseas visit

Place to be visited Purpose of Visit Start Date End Date
The Bahamas First Aid Course July 1, 2012 July 3, 2012


tblExpenses

Expenses Rate Duration OneTimeCosts Subtotal

Per Diem $ 87 3 $ 261Airfare $450 450
Hotel Accommodation 106 2 212
Ground Transportation
Taxi
Registration Fee
Tuition/Fees
Salary For The Period
Miscellaneous 200 200

TOTAL $1,123


Note: The Employee Data, i.e. Employee ID, First Name, Last Name, etc. is only shown once. There is an expand button (+) attached the Employee Expenses tbl. If clicked. You will see the Overseas visit tbl:

Place to be visited Purpose of Visit Start Date End Date
+ Alabama Driving Course June 4, 2012 June 10, 2012
+ The Bahamas First Aid Course July 1, 2012 July 3, 2012

If you click the expand button for this subdatasheet, you will see the Expenses tbl... shown above.

The values shown in the Expenses table is what I would like to see. However, since I entered the expenses for the First Aid course last, those are the values shown in both of the expenses tbl. The values in the other table, (that is, for the course held in Alabama) automatically changed to match the most recent values I keyed in (that is, for the course held in The Bahamas)

How can I stop this from occurring? The values are supposed to be different.

2. I keyed in the expression you just gave me, but I am still getting the same results. Should I just manually calculate the fields?

[FergieF]

I keyed in the expression again and it worked. Thanks a lot!

But, I still need assistance with the issue mentioned above.

[FergieF]

This is the URL for the Screenshot:

https://skydrive.live.com/?cid=4af81...1124&sc=photos

[ScottGem]

Did you make it public? Because its not allowing me access.

Your relationships are unclear. Your OverseasVisit table should have EmployeeID as a Foreign Key and tblExpenses should have VisitID as a Foreign Key. That's how you tie the expense to a specific visit and visits to a specific employee.

[FergieF]

I reproduced the OverseasVisit and Expenses tables and inserted the fields you mentioned. However, the records are still simultaneously updating.

As for the screenshot, I will be able to change the setting to public in approximately three hours.

[FergieF]

I have another issue. I am trying to create a form based on three tables which are linked. However, I am only allowed to use one table as the Record Source. I tried using the wizard, but it did not produce the result that I need. Any idea about what to do?

[ScottGem]

I have another issue. I am trying to create a form based on three tables which are linked. However, I am only allowed to use one table as the Record Source. I tried using the wizard, but it did not produce the result that I need. Any idea about what to do?

Use subforms. I don't recommend using a multi-table query as a recordsource, especially if you want to use the form to enter data.