Calculating Through Sql

[Loony22]

I am trying to create a query that calculates the sum of specific trip expanses. I have two talbes:

1) TripInfo :

TripId
TripName
CalculatedExpences (The sum of all expenses detailed on the TripExpence Table) .

2) TripExpences :
TripId (Linked to TripInfo Table)
TripExpenceId
TripExpenceName
TripExpenceSum (Sum of the spesific Expence)


I would like to create a query that will sum up all my expenses for the specific trip and will calculate the final sum into TripInfo.CalculatedExpences.

 

[Rich]

You generally shouldn't store calculated values at all, you are storing two.
Both of these can be calculated at form / report level or even in a query, use a TotalsQuery if you must.

 

[Loony22]

Thanks for the tip. However, how do i do it?

 

[The_Doc_Man]

First, define a relationship between the two tables if you haven't already. Obviously, a one (trip) to many (expenses) relationship.

Next, build a query that contains the trip ID and expenses along with the trip name and any other stuff unique to the trip rather than the expense.

OK, from here you can go two ways.

Way 1 - build a report based on that query as-is, using the query wizard to group by TripID. Tell the wizard you want totals for the expenses. In the report you will be able to see totals per trip (in the TripID footer) as well as trip details (in the Details section.)

Way 2 - build another query based on the first one. Make sure that the Totals button is pressed in the tool bar. (Looks like the Greek letter Sigma.) Bring in the TripID, and in the Totals row select GroupBy. Bring in the Expense, and in the Totals row select Sum.

Way 2 does not allow you to see the details but does give you a total.

Either in way 1 or way 2, you can include a criterion in the TripID column to make it selective for a single trip or a range of trips.