Filter Fleet City to Return Rate (1 Viewer)

[rasras123]

I have created a table containing Locations and Prices for Trailer, Pickup, Boom Truck & Bus,



can somebody help me to make text box to reflect price from the table if i select location and fleet type from 2 drop down list

 

[vba_php]

assuming your form is bound to the table you're talking about, I would assume this would work when put in the control source property of the text box:

=dlookup("price", "tableNameHere", 

"[fleetTypeFieldName] = '" & forms!formNameHere!fleetTypeComboBoxName & "' & AND 

[locationFieldName] = '" & forms!formNameHere!locationComboBoxName & "'")

 

[rasras123]

Re: Need Help

assuming your form is bound to the table you're talking about, I would assume this would work when put in the control source property of the text box:

=dlookup("price", "tableNameHere", 

"[fleetTypeFieldName] = '" & forms!formNameHere!fleetTypeComboBoxName & "' & AND 

[locationFieldName] = '" & forms!formNameHere!locationComboBoxName & "'")

Thanks for your support, i try to apply the code, but the problem is my price list has 4 prices for each location on the basis of fleet type..

 

[vba_php]

Re: Need Help

Thanks for your support, i try to apply the code, but the problem is my price list has 4 prices for each location on the basis of fleet type..

so are you saying that your record entries look like this?

LOCATION / FLEET TYPE / PRICE
iowa / trailer / $50
iowa / pickup / $40
iowa / boomtruck / $30
iowa / bus / $20

or like this?

LOCATION / FLEET TYPE / PRICE

iowa / trailertype1 / $50
iowa / trailertype2 / $40
iowa / trailertype3 / $30
iowa / trailertype4 / $20
iowa / pickuptype1 / $10
iowa / pickuptype2 / $20
iowa / pickuptype3 / $30
iowa / pickuptype4 / $40

I'm sort of getting the feeling that you might have set up your data structure incorrectly....

 

[rasras123]

Re: Need Help

so are you saying that your record entries look like this?

LOCATION / FLEET TYPE / PRICE
iowa / trailer / $50
iowa / pickup / $40
iowa / boomtruck / $30
iowa / bus / $20

or like this?

LOCATION / FLEET TYPE / PRICE

iowa / trailertype1 / $50
iowa / trailertype2 / $40
iowa / trailertype3 / $30
iowa / trailertype4 / $20
iowa / pickuptype1 / $10
iowa / pickuptype2 / $20
iowa / pickuptype3 / $30
iowa / pickuptype4 / $40

I'm sort of getting the feeling that you might have set up your data structure incorrectly....

Actually I have one table contain all my cities like
City ID | City | Fleet Type 1 Price | Fleet Type 2 Price | Fleet Type 3 Price |

1 | City 1 | 50 USD | 60 USD | 70 USD |

2 | City 2 | 150 USD | 160 USD | 170 USD |
3 | City 3 | 250 USD | 260 USD | 270 USD |
4 | City 5 | 350 USD | 360 USD | 370 USD |


And another Table i listed my fleet types


1 | Fleet type 1
1 | Fleet type 2
1 | Fleet type 3

 

[vba_php]

Re: Need Help

Actually I have one table contain all my cities like
City ID | City | Fleet Type 1 Price | Fleet Type 2 Price | Fleet Type 3 Price |

1 | City 1 | 50 USD | 60 USD | 70 USD |

2 | City 2 | 150 USD | 160 USD | 170 USD |
3 | City 3 | 250 USD | 260 USD | 270 USD |
4 | City 5 | 350 USD | 360 USD | 370 USD |

And another Table i listed my fleet types

1 | Fleet type 1
1 | Fleet type 2
1 | Fleet type 3

I SEE. actually I think what you want to do is impossible! or at the very least, totally impractical. DLOOKUP() can only return one field value in one record (i think), unlike other languages like PHP that can intermix arrays inside functions to solve problems like yours here. may I suggest you restructure this data of yours before you go any further with the DB so you don't end up with "spaghettification" like getting too close to a black hole, in terms of being "all over the place" regarding architecture? can you upload your file so we can take a look at what you've got? put some fake data in it so we can use that as a substitute for your real company data.

 

[MajP]

Re: Need Help

actually I think what you want to do is impossible!

That is silly the solution is trivial and far from impossible.

You can leave you table design as is (not recommended) and simply use a normalizing union query to allow you to select the correct price type per city.

SELECT 
  tblData.CityID, 
  tblData.[Fleet Type 1 Price] AS Price, "Fleet Type 1" AS PriceType
FROM tblData
UNION
SELECT 
  tblData.CityID, 
  tblData.[Fleet Type 2 Price] AS Price, "Fleet Type 2" AS PriceType
  FROM tblData
UNION
SELECT 
  tblData.CityID, 
  tblData.[Fleet Type 3 Price] AS Price, "Fleet Type 3" AS PriceType
  FROM tblData
ORDER BY 1,2

This will turn you data into a proper structure

CityID	Price	PriceType
1	150	Fleet Type 1
1	160	Fleet Type 2
1	170	Fleet Type 3
2	170	Fleet Type 1
2	180	Fleet Type 2
2	190	Fleet Type 3

So now you can filter or join by cityID and PriceType

The better solution would be to fix your table structure and normalize it.
Remove the columns with price in it from the main table. Create Price table

tblFleetPrice
  Price  ' use a real currency field
  PriceType ' fleet type 1 to 3
  cityID_FK ' foreign key to the city

Do not use spaces in any field or table names. Adds to lots of problems.

 

[Uncle Gizmo]

City ID | City | Fleet Type 1 Price | Fleet Type 2 Price | Fleet Type 3 Price |

That's an Excel way of thinking, to do it correctly in MS Access:-

You need three tables:-
tblCity
tblFleetType
tblCityFleetType

The tables need these fields

tblCity Fields
CityID
CityName

tblFleetType Fields
fleetTypeID
fleetTypeDesc


tblCityFleetType Fields
CityID
fleetTypeID
cityFleetPrice

In table "tblCityFleetType" you add the ID for the city, and the ID for the Fleet Type and the relevant price.

Next you create two forms, a form based on tblCity, and a form based on "tblCityFleetType"

 

[vba_php]

Re: Need Help

That is silly the solution is trivial and far from impossible.

I'm sure glad you corrected me on that Maj. I was sure I was somewhat on-point. Thanks for rescuing me from the depths on this one...

 

[Uncle Gizmo]

Re: Need Help

I have done a YouTube Video showing how to to take your existing data and transform it into tables suitable for taking advantage of the power of MS Access

Nifty Access - Transpose Excel to Access

 

[Uncle Gizmo]

And in the following video I show how to use the the optimised database setup up to answer the specific question the OP asked...

In this video:-

Filter Fleet City to Return Rate - Nifty Access


I show the answer to the OP Question, based on the new, improved structure. You can see, because I have separated out the city and the fleet, now those items can be looked up in combo-boxes and the results used to perform the search required. Converting a simple Excel table into a complicated three table system seems counter intuitive, it seems like a lot of work for a very little benefit. However, I hope you can see straight away the benefits, it's just not good to go in the un-optimized direction, the un-normalized route.. What's more is the benefits, the simplicity of anything you do with your Database in the future far outweighs the initial extra effort. In fact, if you don't follow this restructuring process of your MS Access databases, especially when moving from Excel to MS Access, then you will find things become more and more difficult, eventually to the point where you just cannot get the results you want with the de-normalized structure... The point is, if you are building anything but the most simplest database, then you just cannot ignore this step... And really, if you've only got a very simple database, then it shouldn't be in MS Access, you should leave it in Excel...