Complicated Query

  • Thread starter Thread starter DavidW
  • Start date Start date
D

DavidW

I have got a query that has got 3 different fields for fuel usage. I only
need to total this in this query. What the fields are is the number of
gallons used in each field. In the criteria for each field if you put >0 it
will not return any of the entries unless all 3 are greater than zero.
The problem is I dont have the right criteria.
How would you phrase the criteria, so that no matter which field the data
entry is in of the 3 it would show up unless a vehicle didnt use any(isnull)
fuel.
Another words say if you only had 1 entry in "off" and no entries in ("misc"
and "miscout")or is null)
 
Hi David,

If I understand your question correctly, you should be
able to get what you want by entering >0 on different
lines under each of the three fields. Access treats each
criteria row as an "or" condition, so if you enter >0
under field 1 on line 1, then >0 under field 2 on line 2,
and >0 on line 3 under field 3, it will look for all
records where any of the three fields are greater than 0.

Hopefully this helps. If I misunderstood your problem
please post back.

-Ted
 
Add a new calculated column to your query

FieldName: FuelTotal: NZ(Off) + NZ(Misc) + NZ(MiscOut)
Criteria: > 0
 
Got it to work ,now I need to tweak it a little further
If you add a monthfield, how can you get it to sort(sum) by month (1, 2, 3).
I have got it to show all records by month, but how do you get it to sum
each month?
 
You can do this by using a totals query and summing your calculation and
grouping by month.

In the query grid with the query you have built, select View: Totals from the menu

Then under the MonthField - Select Group by
Under the FuelTotal - Select Sum
 
Back
Top