Filtering Dynamic Combo Boxes

[orange]

I agree with your thoughts. I just wanted to point out that there was more to the model than described if the OP was going to have options that were available only in certain model years or combinations of selected/selectable options. I felt the number of such would be extremely large and that maintaining them could be all consuming.


I think we need to see how much info is required --- level of detail, how often etc --- and design to that.
All models, all options, all years etc may just be too big a chunk to undertake.
There may be a practical amount of info requiring a practical amount of maintenance that will give the dealership/OP what is required.

[Topflite66]

First of all thank you all for making attempts to find a solution for my problem. I have been doing some soul searching and decided to back off on the requirements. Because the data that I will be working with on a yearly basis is so convoluted and dynamic I have decided to can the cascading combo box idea. If I was working with a static set of data this would be a fairly easy solution.

I did come up with something but not sure if it is workable and correct. I attached a spreadsheet of the proposed relationships (my handwriting is terrible). I believe, Micron, you had mentioned Junction Tables in an earlier post. Instead of the cascading combo's I would like to just limit the selections based on Model Year and Model. This would help trim down the mass of options that are available for the entire line of vehicles. For example, just for 2015 there are 77 different Trims, 61 Groups & Packages, and 108 Wheels. Those can change from year to year. I really don't want to have to scroll through 100+ options for 1000 orders for each year. So if I can at least filter the options based on Model Year and Model, at most I am looking at 15-20 options. Plus with 100+ options to scroll through I will have more errors because of choosing the wrong 19" Wheel.

I can build several forms to enter the options for each year with a Y/N field for Discontinued. I can then also build forms for the Junction tables to create the links. The other option would be to have a separate junction table for each table including the MdlYrID and ModelID in each.

I know you guys have spent quite a bit of time on this and again I thank you for your patience and thoughts, but if you could look at the attached spreadsheet I would greatly appreciate it. Also if your wondering why I split the Junction tables, I found out that the max number of fields you can have as keys in the Junction tables is 10 and I have 13 tables.

[Topflite66]

This is what I was thinking of regarding having the junction tables. I actually separated the junction tables to include the Model Year and Model with each of the other options. A lot more relationships, tables, queries, and forms but it seems to work. Not sure if it follows the "normalization" rules though.

The filtering works great except for one thing, on the Vehicle Entry Form I am not able to get the Series, Body Code, and Pep Code to pull into the form. When you select the Model Year and the Model, the queries for each of Series, Body Code, and Pep Code work fine but not sure why it doesn't pull into the form. I know it seem like a lot of work to have all the forms, queries and tables but I'm not able to think of a way to get everything to filter on the Model Year and Model.

Please let me know what you think. Thank you.

[orange]

toplite66,
I'm not following your model. Can we go back and identify what seems to be part of a hierarchy to me, but may in fact just be a logical flow of events.
If I were to walk into the dealership and purchase a vehicle, what are the steps to identify the vehicle and attributes? (At least to the level that you want to keep stats on)

My guess it we would start with:
What Year? An when I say 2017, this may/would affect the next question:
What Model? And when I select, this may/would affect the next question:
What BodyCode? etc, etc, etc, continuing until all choices are satisfied/answered.
I believe this is a scenario that will lead to a practical data model.

For me, and most readers I would presume, we don't know what a PepCd, Series, BodyCode, GrpsPkgs etc are? So it is difficult to help.

I looked up one of your GrpPkg values Cold weather Pkg and found this from Ford

The SE Cold Weather Package on the Ford Escape adds a 110-volt power
outlet, all-weather floor mats, dual-power heated exterior mirrors with
an integrated blind spot mirror, heated front seats, a windshield wiper
de-icer, and a supplemental PTC heater. The PTC heater is perfect for
when you’re in a rush, since it will give you warm air right away before
your engine has a chance to heat up.

Again, not knowing the subject matter as well as you, it seems that if I select the Cold Weather pkg, then I might not be able to select some other pkg(s).

For example: The Cold Weather Package is available on the 2017 Ford Fiesta SE and comes standard on the Fiesta Titanium.

Also, it seems possible that the Cold weather pkg could change from 1 year and/or model and another.

I also found this 2015 EDGE TRIM comparison (for what its worth)


The number of "legit" options may be a maintenance nightmare--perhaps not depending on how much detail you and the dealership want to have access to (stats). I'm sure there is a cut off point beyond which it requires more effort/cost/resources than expected (or willing to invest).

Also, going back to my question re VIN

I found this link that can get your build info from VIN ---may be useful???

[Topflite66]

toplite66,
I'm not following your model. Can we go back and identify what seems to be part of a hierarchy to me, but may in fact just be a logical flow of events.
If I were to walk into the dealership and purchase a vehicle, what are the steps to identify the vehicle and attributes? (At least to the level that you want to keep stats on)

My guess it we would start with:
What Year? An when I say 2017, this may/would affect the next question:
What Model? And when I select, this may/would affect the next question:
What BodyCode? etc, etc, etc, continuing until all choices are satisfied/answered.
I believe this is a scenario that will lead to a practical data model.

For me, and most readers I would presume, we don't know what a PepCd, Series, BodyCode, GrpsPkgs etc are? So it is difficult to help.

Most people that are not in the business would have a hard time understanding the lingo i/we use to identify options for vehicles. Sometimes I forget that. A lot of customers that walk into our dealership have already built a or several vehicles online and pretty much know what they want but are not concerned with the coding of the different options. I, as the person that orders all the new vehicles, have to be up on all the different codes or at least know where to find them. It's similar to when people post different acronyms of an access db that I don't get because I've never dealt with it before. Luckily there is the internet and Google. LOL

None of these explanations are straight out of the book, they are in my own words.

Year, Model, Engine, Transmission, Wheels, and Exterior Color are pretty self explanatory.
Series is what's called a Trim Level of the model of a vehicle - SE, SEL, XLT, Sport. The F-150 has several Series XL, XLT, Lariat, King Ranch, Limited, Platinum, Raptor.

Body Code is an identifier that describes whether a vehicle is Front Wheel Drive (FWD), All Wheel Drive (AWD), 4x4, 4x2, Regular or (Single) Cab, Super or (Extended) Cab, Crew or (Double) Cab

Pep Code is a code that describes the trim level within a series. For example for the F-150 there are 3 Pep Codes describing the XLT Series (300A, 301A, 302A). The higher Pep Codes within a series add more standard options.

Wheelbase mostly deals with pickups. For example a Regular Cab F-150 can have either 6 1/2' or 8' bed, whereas a Crew Cab can have either a 5 1/2' or 6 1/2' bed.

Axle describes the different gear ratio's available. Again this primarily deals with the pickups. Higher gear ratio's have higher towing capacity and worse gas mileage and lower gear ratio's have the opposite.

Groups & Packages are option packages that Ford has available to add to most Pep Codes. For example, XLT F-150, the 300A does not offer a Chrome or Sport Package, the 301A offers both the Chrome and Sport Packages as separate options (either/or), and the 302A comes with the Chrome Package standard but you can switch that out for the Sport Package. The Chrome Package comes with Chrome wheels, bumpers, door and tailgate handles, step bars. Whereas the Sport Package comes with Machined Aluminum Wheels, Body Color bumpers, door and tailgate handles, Accent colored step bars, and black Sport Cloth interior.

Trim or Interior describes the carpet color and whether the vehicle has vinyl, cloth or leather seats and the different colors.

Again, not knowing the subject matter as well as you, it seems that if I select the Cold Weather pkg, then I might not be able to select some other pkg(s).

For example: The Cold Weather Package is available on the 2017 Ford Fiesta SE and comes standard on the Fiesta Titanium.

Also, it seems possible that the Cold weather pkg could change from 1 year and/or model and another.

That can be the case. Some option packages can eliminate the ability to add other option packages. There are literally thousands of different combinations across the entire Ford and Lincoln line of vehicles that are possible. That's why I built my first version of the db the way I did. I did a lot of copy and pasting in the tables themselves but I was able to get the cascading combo boxes to work correctly and was able to cover all of the different options I wanted. Unfortunately there is a lot of redundancy. I wanted to have this many different fields and tables to keep the typo errors to a minimum but as you mentioned the maintenance would be a nightmare. The tables that I have are the main groupings that pretty much cover all the necessary areas. Except for the Groups and Packages, there are many more options that I didn't include.

As for the logical order, I organized them on the form of my original db that way because each each option was based on the previous options. For example, Pep Code options are dependent on the Year (they don't normally change from year to year but can change), Model (each model has their own group of Pep Codes), Series (limits the available Pep Codes within the each model), and Body Code (determines what Pep Codes are available within each model and series). The problem is that just about every Model has a 200A Pep Code, however only a few have a 700A Pep Code.

I hope this helps clarify some of the lingo that I have used on previous posts. Thanks again for taking the time to dive into this massive undertaking.

[orange]

Thanks for the clarification --there certainly is some complexity beneath the surface.
Did you look at the VIN code/build sheet link? You might try it with a few valid VINs and see what the build info can give you. It may simplify some of the complexity by giving you a fact sheet of the vehicle in Ford terms.

[Topflite66]

Thanks for the clarification --there certainly is some complexity beneath the surface.
Did you look at the VIN code/build sheet link? You might try it with a few valid VINs and see what the build info can give you. It may simplify some of the complexity by giving you a fact sheet of the vehicle in Ford terms.

I haven't had an opportunity yet but will take a look at it. Thank you.

[Topflite66]

Also, going back to my question re VIN

I found this link that can get your build info from VIN ---may be useful???

I followed the link that you provided. Unfortunately that just gives the ability to pull up the window sticker (Monroney Sticker) for each unit. We actually have that ability for any vehicle through a Ford maintained web site. We can pull up any window sticker or dealer invoice back to, I believe, 10 years in PDF format.

The window sticker does give most of the information regarding the unit, pretty much all of the tables I have listed except for Body Code. The site creates an image, in PDF, that can be printed or emailed. Here is a VIN for a current unit that we have on our lot - 1FA6P8CF9H5286911 - for a 2017 Mustang, So you can see what I am referencing. Also this site does not seem to keep a history of vehicles in it's files as I attempted to enter a 2015 Edge that I had purchased and the site wasn't able to pull up the window sticker. I'm thinking they may only go back 1-2 years. But as I mentioned, I have a Ford site that I can go back approximately 10 years. However, I'm not sure how I would actually get the information from the PDF file to download into the db?

Here is a link that describes what each set of characters determines for each VIN: http://www.blueovaltrucks.com/tech-a...v-i-n-decoder/

The VIN gives some basic information for each vehicle but not a lot of detail as to, (color, pep code, transmission, axle, trim, etc.). If there is a way to use the VIN to auto-populate all of the fields in the db, that would make my life much easier. I'm just not sure how that would work? Thank you.

[orange]

I ran your VIN through this link then took the resulting pdf to a free online converter.

I converted to
- csv, and
- excel, and
- word (the image below came via Word)

None could be easily translated/converted to a quickly usable format.
You might be able to decipher the csv or excel with some creative coding and lots of patience.
However, there certainly is a wealth of info on the sticker.

[Topflite66]

However, there certainly is a wealth of info on the sticker.

They do provide a lot of information, regarding the vehicle info. It is federally mandated and if we get inspected and don't have it on the vehicle there is a $10,000 fine per vehicle without the sticker.

[orange]

You said this in post 53

The VIN gives some basic information for each vehicle but not a lot of detail as to, (color, pep code, transmission, axle, trim, etc.).

What info exactly do you require that isn't on the sticker?

I think the question for me would be ---Is there a way to process a pdf to get the info/data in some usable format for database?

I don't have any real experience with pdf or Adobe products that might apply. I'm sure there are other readers who have.
My line of thinking is--Since you have
"I have a Ford site that I can go back approximately 10 years. However, I'm not sure how I would actually get the information from the PDF file to download into the db?"
I would focus on that source for the details you need--unless it is proven/found to be unworkable. My feeling is that you have too many dependent attributes to manually create, maintain and use the database.
It may be worth the purchase of whatever Adobe product allows you to dissect a pdf file, then convert those pieces to be programmatically entered into your proposed system.

Again, I don't know what you need to get your stats/inventory/marketing info. And I don't know how to break out the pdf into the components of interest to you. And I don't know the car/vehicle industry in any detail. But I have felt throughout this thread that there is one massive amount of data entry and maintenance to design, develop, test and maintain the type of database you envision. As mentioned earlier there may be a much smaller set of data necessary to get you the stats you need, but we don't know what that minimum set is/may be.

My experience suggests you identify what you really need (you can work backwards from desired output to necessary input); build a model; using some test data and test scenarios, test the model and adjust until it works as needed; add test data for new model year/new option packages etc and test again. Then build the database based on the model. Don't start with reams of data and use trial and error to develop tables, queries to get what you think is required.

There may not be a way to break down a pdf, but my gut feeling is that that is the approach that uses Ford terminology; is a reflection of the vehicle details; is current and most of all it is mandated so won't be dropped on a whim.

Interesting project. Good luck.

[Topflite66]

What info exactly do you require that isn't on the sticker

?

The window sticker for each vehicle does provide 99% of what would be necessary to build my reports. The only info missing would be the actual Body Code and invoice price (price we pay for the vehicle). The Body Code is step 3 or 4 in the build process to determine what else is available for, Pep Code, engine, Transmission, Wheelbase, Axle, Groups & Packages, Wheels, Exterior color and Trim.

I think the question for me would be ---Is there a way to process a pdf to get the info/data in some usable format for database?

I don't have any real experience with pdf or Adobe products that might apply. I'm sure there are other readers who have.
My line of thinking is--Since you have
"I have a Ford site that I can go back approximately 10 years. However, I'm not sure how I would actually get the information from the PDF file to download into the db?"
I would focus on that source for the details you need--unless it is proven/found to be unworkable. My feeling is that you have too many dependent attributes to manually create, maintain and use the database.
It may be worth the purchase of whatever Adobe product allows you to dissect a pdf file, then convert those pieces to be programmatically entered into your proposed system.

Unfortunately it took me about 1 1/2 months to convince our IT department to allow me to add Access to my computer so I'm guessing that an additional purchase of software would be out of the question. They are very tight fisted when it comes to spending money on software that they think we don't need. They will not allow us to install any software that they have not pre-approved and they have to install it themselves. Which in retrospect, makes sense, we have 180+ dealerships that they are responsible for doing updates for all the software. It makes it extremely difficult to make sure everyone has the latest updates if everyone has different software installed on their computer.

Some of the stats that I have envisioned are %'s of sales on all the different equipment groupings in the tables that I have created. If you look at the spreadsheet that I uploaded, on the second page, labeled Unit Counts, might give you an idea of what I am currently tracking. That however, doesn't cover what else I was trying to accomplish. I created a separate workbook for each vehicle line that I would copy data to for determining % of sales for Exterior Color, Interior, Drive (4x4, 4x2, AWD, FWD), Pep Code and Engine. Because I was constantly copy and pasting the information to each sheet, and trying to take into account options that were discontinued, it turned into a Excel nightmare. I know that this sounds like a massive undertaking, from the data entry and maintenance aspect, but it can't be any worse than trying to do what I was working on in Excel. As it stands now I have all that information in Excel spreadsheets (for the most part) but no way to tie all the years together. I could just copy and paste all the data from each workbook to one workbook, 4 years and counting of 800-1200 separate records, and build Pivot Tables, however that doesn't take into account typo errors. That's where I thought Access and cascading combo boxes would come into play. The problem is trying to build a normalized db that contains dynamic data for each year. The first version of the db I uploaded, V2, worked perfectly but there was a lot of redundancy and not normalized. I'm trying to do it right way but It's becoming increasingly more difficult to find an answer to what I am looking for.

If you haven't figured it out already I'm kind of a stats nut. LOL

Thank you.

[ssanfu]

<snip>
It may be worth the purchase of whatever Adobe product allows you to dissect a pdf file, then convert those pieces to be programmatically entered into your proposed system.
<snip>

<snip>
Unfortunately it took me about 1 1/2 months to convince our IT department to allow me to add Access to my computer so I'm guessing that an additional purchase of software would be out of the question.
<snip>

FWIW, here are two free PDF to Text converters
http://www.majorgeeks.com/files/deta...4_creator.html
https://pdf-to-plain-text.en.softonic.com/

I have not tested them and I would download "pdf-to-plain-text.en.softonic.com" on a separate computer (virus risk).
But they are advertised as FREE.

[Topflite66]

FWIW, here are two free PDF to Text converters
http://www.majorgeeks.com/files/deta...4_creator.html
https://pdf-to-plain-text.en.softonic.com/

I have not tested them and I would download "pdf-to-plain-text.en.softonic.com" on a separate computer (virus risk).
But they are advertised as FREE.

I actually found a free online PDF to Excel converter that may work. It saves the file to OneDrive so that may take some familiarization but it could possibly be an option. Thank you.

[orange]

As I mentioned in #54 I used the converter at https://convertio.co/

It would take considerable effort to use the excel or csv or txt conversions. There may be better tools, but there is not a direct need to use Onedrive.
My conversions were placed on my PC.

You would have to parse a few window stickers to get some understanding of the converted file format (which ever one(s) you choose).
You may have to use multiple window stickers to get the gamut of options and packages.
You may also find that some window sticker conversions have variations in field/value positioning, so you could require multiple templates.

I see the test approach to be

1-- using VIN
2--get a window sticker in pdf format
3--convert the pdf to a type of your choice
4--attempt to design a process/functions/?? to get the values you need in a format you can read and repeat
5--test your "process" with several VINs to ensure you have a template/process/mechanism to deal with a variety of models/years etc.
6--now build a data model based on your needs
7--populate your tables based on your template(s)/process(es)

I am attaching the csv that resulted from the conversion of windowsticker.pdf based on your supplied VIN. Note It is in zip format since the forum doesn't allow .csv extension. You may see patterns that are not obvious to me, but with some analysis and design and considerable testing/vetting, I'm sure the information could be extracted to a usable format (may not be cost effective, but in my view much better than manual data entry of all the options and data you envision).

It will take some patience and trials to extract appropriate data from the converted file. But if the conversion is consistent, and your process is well tested and validated, this may be the quickest way to get the data you want/need from Ford's own information. As I mentioned earlier, it will take considerable effort to design and test some code to extract info from the converted file into a format you can load/use with Access.

Note: It may be possible to use some Adobe software to get the format and data from the pdf. I don't know and have no experience with Adobe products/procedures other than the free Adobe reader.

Good luck.