Calculated Field in a Form

[joe]

Hai
iam trying to develop a database for a flying club.
i have a form which enables users to enter their flight details called
LogFlight
In this form i have few fields like
Aircraft Used - which is a radio button having 3 options(1-STT,2-DDC,3-POD)
Type Of Flight - which is also a radio button with 2 options (1-Solo,2-Dual)
And two another Time fields TakeOffTime and Landing time
And Another field Duration of Flight in minutes -which is a calculated field
by substrating takeoff and landing time which is DONE
then
Cost of the Flight-which i have to calculate according to User Choice of
Aircraft and Type of Flight and Duation field.

As i have a table of all cost of flight for all choices.

Is there any way off sorting this out......

Thanks in advance...........

 

[Piet Linden]

Hai
iam trying to develop a database for a flying club.
i have a form which enables users to enter their flight details called
LogFlight
In this form i have few fields like
Aircraft Used - which is a radio button having 3 options(1-STT,2-DDC,3-POD)
Type Of Flight - which is also a radio button with 2 options  (1-Solo,2-Dual)
And two another Time fields TakeOffTime and Landing time
And Another field Duration of Flight in minutes -which is a calculated field
by substrating takeoff and landing time which is DONE
then
Cost of the Flight-which i have to calculate according to User Choice of
Aircraft and Type of Flight and Duation field.

As i have a table of all cost of flight for all choices.

Is there any way off sorting this out......

Thanks in advance...........

You can use IIF()

IIF([TypeOfFlight]="A",{Calc for A},IIF([TypeOfFlight]="B",{Calc for
B}, Default) and do a little nesting. Build one part at a time or
you'll get dizzy fast.

 

[Michael Gramelspacher]

Will the cost for a unit of time (minute?) ever change? I would think it would. This week's costs
may not be next week's costs. That means your costs table has to make allowances for this.

It may be a good idea to disclose you table names, column names and data types.

 

[joe]

hai
Thanks for the reply,
Actually whtat i was saying is that i have a table called Charges which have
Cost of Flight according to user choices of Aircraft Used,Type of Flight and
Duration.
Typical my Charge Table Look like

Duration Charge1 Charge2 Charge3 Charge4
10min $ 20 $ 20 $ 20 $ 20

In this Charge1 will be Charge for following Aircraftused=1or 2 ,
TypeOfFlight=1
Charge2 will be Charge for following Aircraftused=1or 2 ,
TypeOfFlight=2
Charge3 will be Charge for following Aircraftused=3 , TypeOfFlight=1
Charge4 will be Charge for following Aircraftused=3 ,
TypeOfFlight=2

I think it makes a bit more clear........
Thnks for Your help in advance.................

 

[Michael Gramelspacher]

hai
Thanks for the reply,
Actually whtat i was saying is that i have a table called Charges which have
Cost of Flight according to user choices of Aircraft Used,Type of Flight and
Duration.
Typical my Charge Table Look like

Duration Charge1 Charge2 Charge3 Charge4
10min $ 20 $ 20 $ 20 $ 20

In this Charge1 will be Charge for following Aircraftused=1or 2 ,
TypeOfFlight=1
Charge2 will be Charge for following Aircraftused=1or 2 ,
TypeOfFlight=2
Charge3 will be Charge for following Aircraftused=3 , TypeOfFlight=1
Charge4 will be Charge for following Aircraftused=3 ,
TypeOfFlight=2

I think it makes a bit more clear........
Thnks for Your help in advance.................

Well, ok, but do your charges for 10 minutes ever change?

Also, what is the rule for the 10 min intervals? Say the duration was 36 min. Is that 3 units, 4
units or 3.6 units?

The problems are always in the details. Nothing is as simple as it seems at first.

 

[joe]

hai
Thanks for the reply,
as i was saying i have fixed rate of charges for every Duration
which i have in a fixed values for charges in table from 5-900 minutes in a
gap of 5 minutes (it is take n as duration will be in between this values)
some wht like this
Duration Charge1 Charge2 Charge3 Charge4
5min $ 5 $ 15 $ 20 $ 25
10min $15 $ 25 $ 28 $ 32
15min $ 20 $ 28 $ 34 $ 40
20min $ 20 $ 20 $ 20 $ 60
soon upto a value
900min $ 1800 $ 2000 $ 2500 $ 2800
about duration iam plaaning to make it to round to nearest fives multiple
like if Duartion is calculated as 36 i only want the system to take the
value of duration to nearest multiple of five tht is 35
same like if duration is 44 then i will take it 40
i think it makes clear for u...

thanks in advance......

 

[joe]

hai Piet

Thbks for the reply i tried to use the nested IFF statement for this in the
control box of the text field of cost of field using expression builder.,
but it doesnt seems working as this is nearly too big so tht i cant even
save it...
is there any other place i can use the if statement like Before Update or
some thing like tht......
iam using access 2007 .......
thnks for ur help....

Hai
iam trying to develop a database for a flying club.
i have a form which enables users to enter their flight details called
LogFlight
In this form i have few fields like
Aircraft Used - which is a radio button having 3 options(1-STT,2-DDC,3-POD)
Type Of Flight - which is also a radio button with 2 options (1-Solo,2-Dual)
And two another Time fields TakeOffTime and Landing time
And Another field Duration of Flight in minutes -which is a calculated field
by substrating takeoff and landing time which is DONE
then
Cost of the Flight-which i have to calculate according to User Choice of
Aircraft and Type of Flight and Duation field.

As i have a table of all cost of flight for all choices.

Is there any way off sorting this out......

Thanks in advance...........

You can use IIF()

IIF([TypeOfFlight]="A",{Calc for A},IIF([TypeOfFlight]="B",{Calc for
B}, Default) and do a little nesting. Build one part at a time or
you'll get dizzy fast.

 

[Piet Linden]

hai
Thanks for the reply,
as i was saying i have fixed rate of charges for every Duration
which i have in a fixed values for  charges in table from 5-900 minutesin a
gap of 5 minutes (it is take n as duration will be in between this values)
some wht like this
Duration    Charge1  Charge2   Charge3    Charge4
5min        $ 5        $ 15        $ 20        $ 25
10min        $15        $ 25        $ 28        $ 32
15min        $ 20        $ 28        $ 34       $ 40
20min        $ 20        $ 20        $ 20       $ 60
soon upto a value
900min        $ 1800        $ 2000        $ 2500        $ 2800
about duration iam plaaning to make it to round to nearest fives multiple
like if Duartion is calculated as 36 i only want the system to take the
value of duration to nearest multiple of five tht is 35
same like if duration is 44 then i will take it 40
i think it makes clear for u...

This will round your minutes down to 5 minute intervals.
(intMinutes\ 5) * 5

Is there a linear relationship between time an price? (like $5/
minute)? If so, you can do this easily with a formula. Otherwise,
you may be stuck using a table to store intervals. And table reads
are really slow.

 

[Piet Linden]

hai
Thanks for the reply,
as i was saying i have fixed rate of charges for every Duration
which i have in a fixed values for  charges in table from 5-900 minutesin a
gap of 5 minutes (it is take n as duration will be in between this values)
some wht like this
Duration    Charge1  Charge2   Charge3    Charge4
5min        $ 5        $ 15        $ 20        $ 25
10min        $15        $ 25        $ 28        $ 32
15min        $ 20        $ 28        $ 34       $ 40
20min        $ 20        $ 20        $ 20       $ 60
soon upto a value
900min        $ 1800        $ 2000        $ 2500        $ 2800
about duration iam plaaning to make it to round to nearest fives multiple
like if Duartion is calculated as 36 i only want the system to take the
value of duration to nearest multiple of five tht is 35
same like if duration is 44 then i will take it 40
i think it makes clear for u...

Normalization is a beautiful thing...

Rate(Duration*, PlaneType*, Cost)
OR
Rate(TailNumberID*, Duration*, Cost)

Then you can add planes until you're blue in the face, and all the
math will still work.

But having a separate column for each plane is definitely wrong.
That's Excel, this is Access.

 

[joe]

hai Piet
thanks for the reply ,,,

there is know linear relation in between cost and time tht is making thinks
worse for me as this vlue are outside of my hands iam stuck on this.....
my Charges typical look like this.....

TimeOfFlight Charge1 Charge2 Charge3
5 10.00 11.00 13.00
10 19.00 23.00 26.00
15 29.00 34.00 39.00
20 38.00 45.00 52.00
25 48.00 57.00 65.00
30 58.00 68.00 79.00
35 67.00 79.00 92.00
40 77.00 91.00 105.00
45 86.00 102.00 118.00
50 96.00 113.00 131.00
55 105.00 125.00 144.00
60 115.00 136.00 157.00
65 125.00 147.00 170.00
70 134.00 159.00 183.00
75 144.00 170.00 196.00
80 153.00 181.00 209.00
85 163.00 193.00 222.00

 

[Michael Gramelspacher]

hai Piet
thanks for the reply ,,,

there is know linear relation in between cost and time tht is making thinks
worse for me as this vlue are outside of my hands iam stuck on this.....
my Charges typical look like this.....

TimeOfFlight Charge1 Charge2 Charge3
5 10.00 11.00 13.00
10 19.00 23.00 26.00
15 29.00 34.00 39.00
20 38.00 45.00 52.00
25 48.00 57.00 65.00
30 58.00 68.00 79.00
35 67.00 79.00 92.00
40 77.00 91.00 105.00
45 86.00 102.00 118.00
50 96.00 113.00 131.00
55 105.00 125.00 144.00
60 115.00 136.00 157.00
65 125.00 147.00 170.00
70 134.00 159.00 183.00
75 144.00 170.00 196.00
80 153.00 181.00 209.00
85 163.00 193.00 222.00

To me you seem to be resisting all advice on how to represent this in a database.

Piet Linden has shown you the table structure to use.

You need a table to store rates.

Rates (AircraftType,FlightType,DurationMinutes,Cost)

You have 3 AircraftType * 2 FlightType * 180 different minute values = 1080 rows in the table.

Just do a simple rate lookup in a query based on AircraftType,FlightType and DurationMinutes and you
are done.