Taxi fare lookup based on no. passengers

[Stretch617]

Doing a taxi fare form. You enter the number of passengers from a drop down. Depending on the number, depends on the fare.



I have all the fares in a table in two currencies, CI and US. There is a standard fare for 1-3 passengers, and then each extra passenger up to 10 passengers incurs an add on. All calculated fares are in the rates table.

What I want to do: I have a trip table. I want to record each trip with the destination and fare for that trip, which obvo depends on the number of passengers.

How can I set it up so that if you select 4 passengers on the form, it selects the appropriate fare and puts that info into the trip table?

I have attached the db. Please help! Not sure if I need to do a query or write some code for this, very new to access! Thanks!

[pbaldy]

I don't see all the fares accounted for. The base fare appears to be in the destination table, and I assume the Farex4 table is for 4 people? If so, I wouldn't have separate tables like that. What's the fare for 5 people? 7 people? What's the actual structure? Is it a fixed amount per extra person?

[Stretch617]

I don't see all the fares accounted for. The base fare appears to be in the destination table, and I assume the Farex4 table is for 4 people? If so, I wouldn't have separate tables like that. What's the fare for 5 people? 7 people? What's the actual structure? Is it a fixed amount per extra person?

Hey, thanks for the response. I've attached the rates spreadsheet, I've been trying to figure out how to set the tables up for this.

There are 253 destinations. These can be used as a primary key.

Each trip can have 1-3 passengers, for which they will be charged the base rate (see Columns B & C - FareUS & FareCI) then 4 - 10 passengers get charged a different rate.

The Trips form needs to have a drop down option for destination and then number of passengers, and then display the correct fare amount in both US and CI, and then write that information to the Fares table so that the Fares table keeps an accurate track of each fare's information.

Am pretty sure this should be easy!

[pbaldy]

The spreadsheet fell out somewhere over Florida!

[Stretch617]

I don't see all the fares accounted for. The base fare appears to be in the destination table, and I assume the Farex4 table is for 4 people? If so, I wouldn't have separate tables like that. What's the fare for 5 people? 7 people? What's the actual structure? Is it a fixed amount per extra person?

The spreadsheet fell out somewhere over Florida!

Ay ay ay!! Here it is

[pbaldy]

It would have been much easier if the extra passengers were a set amount (base fare $10, each passenger over 3 is $3 type of thing). It looks like they vary on the 6th and 9th passengers. I'm torn between a proper normalized table and one like your spreadsheet.

[Stretch617]

It would have been much easier if the extra passengers were a set amount (base fare $10, each passenger over 3 is $3 type of thing). It looks like they vary on the 6th and 9th passengers. I'm torn between a proper normalized table and one like your spreadsheet.

Thanks so much for your help on this. The rates were worked out as follows:

4 persons = rate x 1.33
5 persons = rate x 1.66
6 persons = rate x 2
7 persons = rate x 2.33
8 persons = rate x 2.66
9 persons = rate x 3
10 persons = rate x 3.33

I applied a formula in excel to calculate the additional passenger rates for both CI and US amounts. Attached is the original spreadsheet. Does this help?

[pbaldy]

Well, your problem is simpler if you can live with an equation:

BaseRate + (BaseRate / 3 * QtyOverBase)

Applied to your spreadsheet, it produces rates pennies off from some, see attached. In that scenario, all you store is the 2 base rates, and perhaps the associated quantity (3). If you want to stay with yours, it's doable, just seems like more work to me.

[Stretch617]

Well, your problem is simpler if you can live with an equation:

BaseRate + (BaseRate / 3 * QtyOverBase)

Applied to your spreadsheet, it produces rates pennies off from some, see attached. In that scenario, all you store is the 2 base rates, and perhaps the associated quantity (3). If you want to stay with yours, it's doable, just seems like more work to me.

I'm not sure if I will have to stay with my figures I will check that out, thanks for this. I'm not sure how I would apply the formula in Access though?