Filtering Dynamic Combo Boxes

[orange]

Just curious. How much info can you get re Options etc that you need from the (ford) VIN number?
Have you considered it as part of your set up?

[Topflite66]

A group of 5 digits indicate vehicle type or Model, body style and Engine. Other than that, the rest of the digits designate where in the world the unit was built, the plant it was built in, the model year, a digit used in an Algorithm to validate the whole VIN, and then the last 6 digits are used to specifically identify each vehicle.

That's a good thought, but I would then have to also keep track of all the different codes for each unit, which would, in my opinion add more confusion.

Thank you.

[orange]

I would then have to also keep track of all the different codes for each unit

But you have to record/load the various model, axle, transmission codes etc.anyway. You are going to use the Ford nomenclature/codification, right?

Well it was a thought. And if you are in North America/US, I think you could remove/not load details for other Countries/regions. You need some "junction" between ModelYear and all other "entities" you are dealing with. VIN may not be helpful at time of Order, but could be the link to information once the vehicle is delivered. Before loading all info, I think you need some sample test data to test against your requirements and evolving data model. Do this with pencil and paper and get a thorough handle on requirements-- it will reduce the trial and error(especially if you are working with a physical database).

Somewhere in your model and database you will have a table(s) similar in concept to VIN/junction table.
The value or code in specific positions with the VIN is a link to another table(s) from which you get information you can read and discuss (eg. 2016 Mustang..)

Good luck. Interesting project.

?? How do other dealerships accomplish the sales info/forecasting/statistics etc that you are trying to do??

[Topflite66]

How do other dealerships accomplish the sales info/forecasting/statistics etc that you are trying to do??

I'm guessing they just wing it. We are one of the larger dealers in our area so for the smaller dealers they don't keep as much inventory on hand. With less inventory it's easier to keep a handle on what sells and what doesn't.

I maintain the inventory fairly well with the Excel workbooks that I currently am using. I have filters set up on the sheets to filter out specific items. However, because we order between 700-1000 units a year I have a separate workbook for each year. Basically what I am trying to do is replace the spreadsheets with Access so I can generate the reports that I want. T

he person that was in this position before me had a sheet of paper that he wrote the Order #, Year and Model and that was it. They, at the time, had a problem with having good inventory, i.e. having 10 of the same truck in the same color. So when I designed the spreadsheets it really helped me get a better grasp of what to order. I am just trying to take it a step further so I can build the reports to be more efficient at my job. Sometimes though, like I mentioned to Micron, I feel like I've bitten off more than I can chew.

Thank you for your suggestions.

[orange]

I think most of us have been in that position. And as they say "How could you eat a whole elephant?"

1 bite at a time. So it's a matter of identifying the issue (what is really required) and then taking an approach that confirms the requirement and then a series of logical steps to "consume the elephant".

If you had to write down in point form the 10 most popular requests the Boss/management/banker wants answers to, do you have that list? If you said (hypothetically), I can give you 3, not 10, so which 3 are most important. (Or you can play that game by yourself). Get something working and see if it is extensible (easily). What is missing? Do you have the info to gather/produce/calculate what is missing? Would some adjustment get you answers to 4 or 5 of the 10 things?

I remember working on a project where the gov't announced a new Grants program that would encompass the currently exist 30+ such existing programs across several gov't departments. We heard the announcement on TV --no warning other than a new gov't department was being announced. There were no forms, no computer programs, no details and yet the powers to be said it was open for business in a month or two. The reality was that no monies would be involved for say 6 months; groups were running around trying to create the logistics of forms, applicants, review techniques, qualification.... There must have been 12 working groups focused on specific aspects of such Programs --legal, financial, conditions, rules and regulations... and this was to be done online in multiple offices across the country.
1 piece at a time-- the first stuff was done on paper, then a spreadsheet, ... finally it was all tied in to a corporate database. 1 piece at a time.

You may have some options, especially if you can deliver something necessary, but not necessarily urgent. Or something urgent that my not be long term necessary.

My gut feeling is that you will spend an inordinate amount of effort and time loading reams of data that has to be kept current and maintained. Some of this may be easier if you can get table/extracts from Ford--eg. standard coding tables for modelyear (not sure of the name but I'm thinking of an extract from some of their reference table(s)). You might en be able to glean info from Build sheets and use OCR/scanner to get the data (with less effort).....

[ssanfu]

So the title for this thread is Filtering Dynamic Combo Boxes

I've been following this thread (ie lurking ) and have been a little confused. I can see where the OP is headed, but it seems like he keeps getting side tracked.

To me, it seems like there are 3 parts of the dB:
1) inventory control/tracking
2) tracking dealer orders
3) building custom customer requests/orders


For the last posted version of the dB (Vehicle DB V3), the table designs/linking seem to be only partially complete.
If I select a year from the model year, every model is available for every year. Every option is available for every model and year.
This is wrong... IMHO.


With regards to 1) inventory control/tracking....

A short fairy tale:
Long, long ago in a far away job, I started learning about databases (and was hooked). I didn't really know what I was doing (so please excuse naming conventions). I named tables starting with numbers (a big no-no I have since learned). But the numbers helped me load the tables in the correct order with the related data.


I worked for weeks on trying to set up the tables and probably used up a tablet of legal paper drawing pictures and relationships. (remember, I was in the beginning of my Access journey - being mostly self taught).

Attached is the dB (mine) for cascading combo boxes that I created. But by the time I had it (mostly) working, the need had changed and was headed to a web page. (but I learned tons!!)


There are two forms - one with combo boxes and one with list boxes. Try the combo box form first.
I created the list box form to help me follow what was happening with the selections I made. BTW, there is a lot of code....


Because the data is/was extremely stable, I didn't make forms to add/edit data.


Maybe/hopefully this will shed some light....





Good luck with your project.........

[orange]

Hi Steve,

If I select a year from the model year, every model is available for every year. Every option is available for every model and year.
This is wrong... IMHO

I agree that modelyear is needed for all options/feature tables. I think the OP will recognize this when he attempts to test his model with a representative scenario. I tried to make that point in #33 re junction.

[Topflite66]

Thank you for your response ssanfu. Initially I had built a version which had cascading combo boxes that worked correctly, which actually was where the question came in how do you apply additional filtering to Dynamic (or Cascading) Combo Boxes. However, both Micron and Orange pointed out that because of the redundancy it was not a sound db. So I had to start from scratch and they steered me towards a more normalized db. Because of my inexperience, with Access i have been trying to take baby steps, with their guidance to get a functional db that is correct with as few steps as possible. I really don't want to spend a week getting everything set up only to find out that what I did on the 2nd day was wrong. They have been great with suggestions and comments.

To me, it seems like there are 3 parts of the dB:
1) inventory control/tracking
2) tracking dealer orders
3) building custom customer requests/orders

That pretty much sums it up.

For the last posted version of the dB (Vehicle DB V3), the table designs/linking seem to be only partially complete.
If I select a year from the model year, every model is available for every year. Every option is available for every model and year.
This is wrong... IMHO.

At this point, I haven't gotten to building any queries or any adding any code yet as I want to make sure I have the table structure correct. I haven't worked with Junction Tables before, to be more accurate, I should say I've never built a "Normalized" db before. As I said baby steps.

Thank you for the attachments. I'm sure they will come in to play at some point.

[Topflite66]

I worked for weeks on trying to set up the tables and probably used up a tablet of legal paper drawing pictures and relationships. (remember, I was in the beginning of my Access journey - being mostly self taught).

I feel your pain. I'm there now. I've self taught everything regarding computers. Mostly trial and error though.

Attached is the dB (mine) for cascading combo boxes that I created. But by the time I had it (mostly) working, the need had changed and was headed to a web page. (but I learned tons!!)

I looked at your db and I think it might fit the bill. I will be working on V4 of the db now. I'm only going to, at this point set up 4 tables, the query and a simple form to see if it works. If it does I will upload it and see what you all think. Thank you very much. I really appreciate it.

[Micron]

Consider attaching workbooks to a post if you get stuck with ssanfu's db. You could copy them and Find/Replace names & other sensitive info quite easily. It might help us understand the complexity of model/pep/trans/etc.

[Topflite66]

Consider attaching workbooks to a post if you get stuck with ssanfu's db. You could copy them and Find/Replace names & other sensitive info quite easily. It might help us understand the complexity of model/pep/trans/etc.

Attached is the 2017 spreadsheet I am using. I have removed sensitive info. Some codes that you might wonder about:
Order #
D/T - Dealer Traded Units - Units that we traded in from another dealership
H*** - Our Order Number
B***, Z***, S*** - MFR Generated Orders
Action
DTO - Dealer traded out - Which means another dealer called us for that unit
DTI - Dealer traded in - Which means that unit was the return vehicle to the other dealer for a trade we initiated
Sold - Units that are considered sold but have not been posted by the office yet.

Green Highlighting - Vehicles that are no longer in inventory because of being sold or dealer traded
Yellow Highlighting - Vehicles that are considered sold but not posted, Units ordered for a customer, On hold units, etc

I have separate workbooks for separate years. The problem with using the spreadsheet is that it is a flat design, meaning you can type anything in any field.

Thank you.

[Micron]

Going to throw out a curve here. Since the db is for reporting/collating/aggregating data that comes from another application, and this db is not to be used to create orders, then why get hung up on cascading combos with mega combinations? Surely order data comes across your desk in some form - paper records of ordered units, or you look at the ordering app data on your monitor, whatever. Transpose that data into your db and don't worry about cascading. Going to make up stuff now that may not exist, but it should serve my point nonetheless.
If I order a black 2018 Fusion SE with 6 speed auto, 2.0L engine and it has black cloth, pick those values from your combos, whose row sources are the "lookup" tables (not lookup fields) and reproduce the order. All you should need to do is exercise a little care, and if you pick a wrong value (i.e. it isn't possible given some other choice) no harm done (you'll spot it sooner or later). If orders were being placed using your db, I wouldn't take such a position, but they're not. This is only a tool for you to spot trends and see what's in stock and what's not there yet, plus report on what seems to be common trends.

The attached db is how I'm suggesting you set it up based on your prior information. Two date fields are for determining order status. You'll notice that with the 2nd record, the order has not arrived. I didn't include some fields from the details table (such as pass code or whatever it was called) and used only one orders table. The intent is to provide an example only, so the form layout won't even be close to what you want, plus combinations of options I used to build 2 records may be impossible. Look first at the orders table and you'll see that it stores the ID of each lookup table. The combos are 2 columns, with the first being bound. That's how you see Fusion but store a number.

MicronVO.zip

[Topflite66]

Thank you Micron. I'll take a look at it and get back to you.

[orange]

While I agree with most of what Micron has written regarding the use of your proposed database, I think there may be a major omission in the model.
It seems that the options in the combo boxes(lookup tables) have to be identified with the model year that the specific option was available. That is if the
3.55 E-Locking axle was only available in 2016, it should not be presented in the 2017 and 2018 axle list combo. Similar situation with all other option groupings.
Perhaps there is some other way to limit the choices based on the data, or the application or other means, but it seems (to me at least) that model year (and possibly some other field(s)) may be needed to effect this reality. Here I'm thinking that some option may only be available if the customer purchased some other option(s). For example, some engine and transmission combination is only available if you purchased the "off road package X", or the "police duty package" etc. It is this need to record all options and apply model year and/or other dependencies that I was referencing re the amount of "set up" required in my earlier post.

[Micron]

Normally I would agree with you. However, it has been stated that there is something on the order of a thousand possible combinations, and the difficulty for the OP seems to have been how to cover them all off. Since this db is not for ordering, then why worry about it? Apparently, the combinations are not so simple as to be limited by year or year and model. Admittedly, I haven't delved deep enough into the issue to verify if the combinations are that complex, but it seems akin to a many to many relationship while at the same time being intermixed. I suspect it can be done albeit the supporting lookup tables would be rather large; i.e. there would be many records in each to support the possible combinations.

I don't agree that the cascading feature is needed if it's as complex as the OP has stated. The db is solely for reporting on trends, and the orders are created elsewhere. What would be required is a reasonable amount of care in replicating the order. If it's far simpler than what I perceive, then by all means press your case. I admit I tend to focus on detail rather than the big picture, which can sometimes be a constraint. Yet I haven't studied the spreadsheets to see if I can grasp the spider web of relationships, but I did go to Ford.com in an attempt to figure it out, but they don't reveal enough of what goes on behind your choices. At this point, I figured in order to be able to assist to get this right requires more understanding of those relationships, but that might just mean building a limo when a Focus will do the job.