Cost total calculation field

[JD2775]

HI all,

I have a maintenance database I want to try and do some calculations with. The calculation I want to occur only relates to one table in the DB. To simplify things:

I have tbl_Maintenance which has 6-7 fields in it, one of which is "Cost" another of which is "Location".

What I want to do is come up with a new summary layout that would add up all the costs, per location, for dates that are chosen. I see this possibly happening in a couple ways:

1. In a column report, user enters in Find Mode a date range of 1/1/2011...3/31/2011, then chooses "A" for Location, and all the costs for location A would show up in the calculated field "Total Cost".

2. A cleaner way to see it would be a dropdown with (Q1, Q2, Q3, Q4), dropdown list of Location (A, B, C, D) then finally the Calculated "Total Cost" field would populate based off of quarter dates and location chosen. I think management would like to see this broken down into quarters, either way.

if you can offer any suggestions on how to do this, or if you can think of a better way I'd appreciate the help. Thanks!

[comment]

Why limit yourself to one location, when you can do all of them at once? Select the quarter in a global field, add two calculation fields (in the Locations table) to calculate the date range, and use a relationship:

Locations::Location = Maintenance 2::Location

AND

Locations::cStartDate ≤ Maintenance 2::Date

AND

Locations::cEndDate ≥ Maintenance 2::Date

[JD2775]

Thanks man, I am running out the door but I will get back to you. Couple things I am unclear of.

Thank you again for your help, you are great!

[JD2775]

Why limit yourself to one location, when you can do all of them at once? Select the quarter in a global field, add two calculation fields (in the Locations table) to calculate the date range, and use a relationship:

Locations::Location = Maintenance 2::Location

AND

Locations::cStartDate ≤ Maintenance 2::Date

AND

Locations::cEndDate ≥ Maintenance 2::Date

Ok so I am somewhat confused by this. Is this adding another table to the database? Or just a global field to the Maintenance table? I have never used Global Fields before (probably should start learning).

"and use a relationship", relationship from what to what?

Also, by "select the quarter in a global field" do you mean use a value list for the global field?

Thanks man