Forms- Subforms trouble

[Mounds]

So this should be pretty easy but I seem to be rusty and can't figure it out. I have a form that I use for each Job. It has all the information generalized for that Job. ( customer, location, ordering info, and Total for the Job,.. etc)


The Totals are where I am falling on my face.
I would like to make a sub forum that I can create a cost entry.
I would like to be able to select a engineer from a drop down box, and expense type, enter a expense, and then a cost.
For example Bill, Expense, Food, 3.00
or Fred, Airfare, United, 150.00
I would then like to have The totals for job expenses seperated in the main form. So Total Airfare and it would display the total for all airfaire.
I used to be able to do this stuff but I've been hacking at it a couple hours now and I know I'm missing something. A querry or something. I just keep getting an entire list of all expenses in each job and because of this I can't get moving towards getting the totals working.
Thanks for any direction help you can give me.

[Robeen]

To clarify:

Do you need to have the Main Form displaying the information for just ONE Job at a time - with each expense type displayed separately on the form at the same time?

Or do you need to see the Total of All Jobs - and the Total Airfare, Total Expense etc for ALL Jobs . . . ?

[Mounds]

What I'm looking for is all expenses for one job in a list.
So Job A, then Total for Airfare of Job A, Then total for expenses job A(not including airfare).
Then I would like to be able to see or access a list of all the expenses for Job A, listed by people who used them, what it was for and the price. Ideally, each cost would be added in individually, and the total would be generated as each expense is entered.
Sometimes we have Jobs that require multiple people, which means multiple expenses and airfare.
I hope that makes sense.

[Mounds]

I got my list working in my subform, but I am having trouble figuring out how to separate the price of each into the proper category, airfare and job expense and then updating the proper total. Any ideas?

[Robeen]

One way to show all those Totals for a job is to write a query that uses a Group By clause.

Create a Query in Query Design View.
Drag all your fields into the query.
Under Job - type in a Job#.
Under Expense - Sort Ascending [this will put all similar expenses together].

If you run the query now - you will see all expenses for that Job# in order.

Go back to Design View and click on the Totals button on top. This will 'Group' all your fields in the query.

Under Cost -> where it says 'Group By' - click the down arrow and select 'Sum'.

Now - when you run your query, you will see one record for each type of Expense [Airfare, Food . . .] - and the total amount for each Expense.

[Mounds]

I tried and I'm getting closer but still not getting the result I want. Here is a screen shot of what I am working with. So what I would like to make happen is a total for all airfare associted with this job to be totaled under the "airfare" and then all other expenses totaled under "expenses". Expense Total will be airfare and expenses.

[Robeen]

Does that picture include the Main Form AND the Sub Form [the lower part being the sub form]?

If so, It looks like you are attempting to get summarized information into the Main Form and the details in the Sub Form. Am I correct so far?

Now that I can see the picture - explain again what you want to do on the Main Form & teh Sub Form.

[Mounds]

First Robeen, thanks for spending time working on this, your help is much appreciated. And yes you've got it correct.
The sub form has an entry for each expense related to that job. (that is the sub form at the bottom) that seems to be working great now.
For the main form where it says airfare, I want it to sum all that are in the price column of the subform under that job. So this one should come out to 410.00
And do the same for all that are labeled job expenses, and sum that under "expenses."

So what you said exactly summarized on main form, details in subform.
thanks again.

[Mounds]

any new advice?

[Robeen]

From what you're telling me, I think your Main Form needs to be based on a Query that does all the Grouping and sub-totalling for you.

What is the Data Source for you Main Form? Is it a Table? Or is it a Query?
If it is a query - can you post the SQL for the query here?

[Mounds]

Well I'll just throw in the whole DB. Its still in a work in progress so you'll have to pardon me for the mess and navigation. Query1 is the one I was going to use to try and give me an airfare sum, however I can't figure out how to get it to sum. I can get it to show the right records, but no sum function.

[Robeen]

I'm looking at your Expenses Table.
I was expecting to see a 'JobID' field in there - since there is a JobID on your Main Form [in the screenshot above].

What is 'ReportNumber' in your Expenses Table?

The reason your Group By won't work is that you're Grouping by ExpenseNumber. So - for each different ExpenseNumber you will get a new 'Group'. Does that make sense?

If each of the records in your Expenses Table had a JobID associated with it - then you could Group on JobID & ExpenseType and Total the Price field.

I created a query on your Expenses Table:
ReportNumber, ExpenseType, Price
Group on ReportNumber & ExpenseType
Sum on Price.

That gives what I think you need [except . . . you have a couple of blank ReportNumbers].

Try it and let me know.

[Mounds]

Robeen you mind uploading it for me and I'll take a look. Your grouping remark makes sense. I believe I may be able to finish this off here soon. Thanks for your help.

[Robeen]

Here it is. [Query2].
I hope this helps!
Let me know if you have questions.

[Mounds]

Yes that helped a lot. I still can't seem to be able to get the sums in the appropriate boxes on the main form. I keep getting the #name? if i try to set control source. I know it should be an update function most likely but I can't seem to get one to work.