Jump to content

Cost total calculation field


This topic is 4809 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 4809 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.