Sounds good. Thank you for looking at it. Also I have done some work to the form and added a couple tables, so would you like the latest version?
Sounds good. Thank you for looking at it. Also I have done some work to the form and added a couple tables, so would you like the latest version?
Probably would be best.
Great. Thanks again.
Suggest
-you not use # in field name
-you do not have spaces within field names
-clearly identify the ID field eg ModelID , BodyCodeID etc. it could/will get confusing if they are all ID
You appear to have all attributes of options, models etc but no vehicle or sale info?? perhaps you do but names don't reflect it. I see Customer in your OrderDetails.
I would have thought (again not familiar with your requirements) a Customer would buy (you would sell) a vehicle, and that vehicle and its total options would be related to vehicle. I'm not sure why Keyless entry is part of orderDetail??
I also think you would have a table such as vehicleOrdered that would have all necessary attribute IDs. That is a table linking Customer and vehicle.
Perhaps you need separate Invoice and Delivery.
I don't know about your set up, but sometimes there are option substitutions for one reason or another. That could affect your design??
When I got to the part before the asterisks, I decided to take a different tack.
unless there can be only one detail row for an order, the detail side should be many, not one. This is the usual scenario where an order may contain a line for 100 bolts and another line for 100 washers; they belong to the 1 side (order). If everything on the order (mudguards, cargo net, floor mats) is one line, that would require a field for each item, which would not be the proper design.
A table for years seems unnecessary. I don't see year being an entity and it certainly has no attributes in your case.
You are repeating data fields in several tables (notably Model and Model_Year but there are many other examples). This is a violation of standard design practices (i.e. data is not normalized properly).
***********
To be honest, what you should do is
- write out a clear and precise narrative of what the process will be insofar as using this db is concerned. Review it a day later to ensure you have the process details straight in your mind. Imagine you are hiring a db contractor to build your db based on what you tell them.
- take a good look at post #12
- review the following links, starting with normalization. Maybe search this forum on this word as others have posted good links on the subject, or Google db normalization. The principles are universal and not limited to Access.
- start a new post about db design, include your narrative and any further supporting information
- either after or before that post, review the other links below for related design principles.
The reason is that your issue with the type of combo box interaction you envision you need is based on unsound design, and if you continue on your current path, you will always be faced with road blocks of your own making. I suspect I think that others will agree with me when I say many of us will go to great lengths to help someone build their own db as long as the path isn't going to be fraught with difficulties due to incorrect design from the outset. You have to be willing to learn good design practices as I/we have neither the time or inclination to help anyone create a poorly designed db.
Normalization is paramount. Diagramming maybe not so much for some people.
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/
Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html
How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html
Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm
The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Thank you for those suggestions. I can easily make those changes.Suggest
-you not use # in field name
-you do not have spaces within field names
-clearly identify the ID field eg ModelID , BodyCodeID etc. it could/will get confusing if they are all ID
I have not gotten to the point of entering the vehicle info as of yet. As for sales info, this db will not be used for that. I will be using this to manage our levels of inventory for ordering vehicles only. All sales are done by the sales desk and they have a completely separate db called ADP that is connected with our corporate office. Our corporate office does have an online db that they maintain but it only keeps track of basic info about the vehicles. It contains no information in it regarding color, trim level, order number, etc.You appear to have all attributes of options, models etc but no vehicle or sale info?? perhaps you do but names don't reflect it. I see Customer in your OrderDetails.
The main reasons I have the date fields in there is because I like to keep an eye on what kind of builds tend to sell quicker than others. As for the Customer field, I use that for when customers want a specific build of a vehicle and we don't have one and are not able to find one at another dealership. So we order it exactly how they want it. I put their name in that field for reference purposes.
I use that to keep track of the Keyless entry codes because customers have a tendency to loose the cards that come with each unit. Mainly for convenience.I'm not sure why Keyless entry is part of orderDetail??
The tblVehOrdr and tblOrderDetails tables are the tables that will be storing the data pertaining to each individual vehicle ordered. I just haven't entered any orders in yet as I have been primarily focused on the cascading combo boxes and entering all the data for each combo box. As I mentioned I'm a novice at Access so it has taken me more time than most to get everything right.I also think you would have a table such as vehicleOrdered that would have all necessary attribute IDs.
No customer information will be maintained in this db. The main idea behind this db is so that I can be more effective at ordering vehicles that will move quicker for our store. I have Excel spreadsheets that have all the individual orders now, but because of the amount of data, I order between 700-1000 vehicles each year, I thought it would be easier to generate the reports through an Access db rather than trying to do it in Excel. I hope my responses clear up some of your comments and questions.Perhaps you need separate Invoice and Delivery.
Thank you again for the suggestions. I will make those adjustments.
Here is what I have so far. I forgot to mention, I worked on this at home with Access 2007. Thank you
That looks better. Can you take a few sample records and test your proposed set up?
Is there anything in the Packages that is also part of the other "options/features"?
what is DeliveryDate - the date delivered to customer or dealership?
what is OrderDate - the date purchased by a customer or ordered by dealer? Would you want to assess efficiencies by knowing how long between purchase and dealer order? Between dealer order and arrival?
I'm surprised you would retain keyless codes. Your data cannot be 100% secure, and should a customer experience a vehicle break in where there's no forced entry, I'd say that puts your dealership on the radar. Call me paranoid, but I'd say you're taking on liability where you shouldn't.
I don't think you're ready for data testing yet. What I think is missing:
- the ability to obsolete things like axle or package options. This is often done via date fields when knowing the obsolete date is also desirable. In your case, a simple Obsolete flag (field with Yes/No checkbox) might suffice.
- there are no relationships between any aspects such as model series and options (like colour, trans, engine, etc.)
e.g. if the S series Escape can have but two colour options (there may be other limitations as well) I see no way to prevent by design any invalid choices from what I assume will be your combo boxes. This would require code, which should not be the preferred way. Put another way, You may not be intending to combo select colour, but I think you will for at least some aspects. Keep in mind that while you may get around this by way of completely understanding how to use the db and careful data entry, should you ever want to delegate this task, the responsible person will not have its operation so well engrained as you. A problem with free form data entry when it supports relationships - a search will find all instances of Regular but leave out the "Reglar" typos, thus free form data entry has the potential to undermine your efforts. Obviously, data entry of customer names is the only way for you but that data isn't related in your db. Note: just noticed that you actually are guilty of at least one inconsistency:
3.15 Limited_Slip vs 3.31 Limited-Slip; one underscore character has crept in (unless that was intentional).
- if the above makes sense, then the existing options tables are fine for providing combos with static choices but you are missing junction tables. Such tables would allow you to associate a trans with only the vehicles it applies to. Another way might be to do this in your current tables, but that would require at least one field (Model?) but I don't know as I don't fully understand the process, and repetition of data such as:
Otherwise, the junction tables could look like (series considerations can be handled by the BdyCdSeriesID)
AxleID Axle Model 1 3.15 Regular Edge 2 3.15 Limited Slip Edge 3 3.15 E-Locking Edge
- at this point, I'm undecided on the orders side of things but will have to leave that for now. You definitely have a spreadsheet approach there. If an attribute of a vehicle needs to be added (an additional table field), you are facing redesign of tables, queries and forms - not good. The options should be presented in a subform datasheet as rows, which would not force design changes if an attribute gets added.
AxleID Axle Model 1 3 2 2 2 2 3 1 2
Other observations:
I think this should be split: E1C - 150 Van - Med Roof RWB Sliding into
or
BodyCode Series E1C 150 Van - Med Roof RWB Sliding
BodyCode Series Description E1C 150 Van Med Roof RWB Sliding
Here's another example of query criteria and support for splitting the body code info: if the table value versus criteria is
E1C - 150 Van - Med Roof RWB Sliding
E1C - 150 Van -Med Roof RWB Sliding
you will miss results. Better to filter on the code E1C itself or code and series. I say this because I doubt you will be creating queries that use criteria in ID fields. More likely you'll be using descriptive fields.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Thanks for taking a look at it for me. At this point I'm not ready for data testing as I'm still not sure how to limit the options in the cascading combo boxes. For example when you choose a 2015 in the first box and Fiesta second box I don't know how to limit only getting the options for the Body Code that are associated with those two selections. I don't want the whole list to display only the Options for the 2015 Fiesta. As I mentioned, not sure how to do that. Any suggestions would be appreciated.That looks better. Can you take a few sample records and test your proposed set up?
Not really. Some models have Groups & Packages that are listed as optional equipment but as you get into the higher level builds they are standard. So I would need to keep them as options for the lower level builds.Is there anything in the Packages that is also part of the other "options/features"?
Thank you.
This is what I'm driving at with respect to my post part about junction tables.I don't know how to limit only getting the options for the Body Code that are associated with those two selections. I don't want the whole list to display only the Options for the 2015 Fiesta. As I mentioned, not sure how to do that. Any suggestions would be appreciated.
I wasn't suggesting you start with cascading combos and a physical form. If you take a copy of your data model, make some boxes to represent your tables, add sample values for those boxes. Then take a scenario modelX YearY with options A, B,C...etc. Does it work? Is there something missing?
All with pencil and paper is sufficient. Try a scenario where you sell a few cars in different modelYears--can you properly record each a vehicle where the option has changed across modelYears??
As Micron points out your options are all based on modelYear, so every option has to have a means of identifying/isolating ---this option in this ModelYear.. so ModelYear is going to be a big player in your design.
Delivery Date is the date that we receive the unit. I could change it to ReceivedDate if that would make more sense.
OrderDate is the date that we actually order each unit. That is primarily informational, I have it on my spreadsheets, and was not planning on using it in any calculated fields. There would really only be three calculated fields which would be:
the difference between InvoiceDate and DeliveryDate (to keep an eye on how long it takes to deliver the vehicle to us once Ford Invoices us),
the difference between InvoiceDate, DeliveryDate, and SoldDate (to keep an eye on how long a vehicle has been considered OnLot based on the InvoiceDate after it has been delivered) and
the difference between SoldUnits by model year that were ordered by us and ones that have been Dealer Traded in from another dealership. This is a % that I use to convince our sales team that they need to sell our units instead of other dealerships. Which goes back to the second option if we have a lot of units that have been on our lot for an extended period of time.
The first two are more so Date counters that will stop once the vehicles have been Delivered to us and Sold.
The data will be 99% secure as it will be maintained locally on only my computer and the only other person that has my U/N and P/W, to access my computer, is the person that is my backup when I'm on vacation. He's a sales person so it wouldn't be in his best interest to spread that info around. The reason for maintaining it is because customers have a tendency to lose the Keyless Entry Code cards that come with each unit. If they do, then the sales people can come to me to find out what the code is instead of getting their customers charged $XX to have it reset.I'm surprised you would retain keyless codes. Your data cannot be 100% secure, and should a customer experience a vehicle break in where there's no forced entry, I'd say that puts your dealership on the radar. Call me paranoid, but I'd say you're taking on liability where you shouldn't.
I do like that idea, especially for the Groups & Packages and Exterior Colors. Sometimes Packages that are offered one year are added to a Pep Code as standard the next year and the colors seem to change about every other year. the Yes/No checkbox seems more appropriate as all of the options that would be obsolete are Model Year dependent not date dependent.I don't think you're ready for data testing yet. What I think is missing:
- the ability to obsolete things like axle or package options. This is often done via date fields when knowing the obsolete date is also desirable. In your case, a simple Obsolete flag (field with Yes/No checkbox) might suffice.
This is where I was running into a stumbling block as I wasn't sure how to achieve the static choice options without creating redundancy. As you go down the selection list, for example Axle, to get the proper selection of available Axles, it would depend on the previous selections for ModelYear, Model, BodyCode, PepCode, Engine, Transmission and Wheelbase. So for the most part, each group of selections, as you go down the list, for each combo box is dependent on all of the previous selected options in the other combo boxes. Other than Exterior Color, which is dependent on (ModelYear, Model, PepCode, and Groups & Packages) and Trim, which adds Exterior Color to that group.- there are no relationships between any aspects such as model series and options (like colour, trans, engine, etc.)
I'll try working on the junction tables though.
That was a typo.3.15 Limited_Slip vs 3.31 Limited-Slip; one underscore character has crept in (unless that was intentional).
This reference is for the Transit Van which has several combinations. For example:Other observations:
I think this should be split: E1C - 150 Van - Med Roof RWB Sliding into
ID Model_Year Model Body_Code 154 2015 Transit E1C - 150 Van - Med Roof RWB Sliding 155 2015 Transit E1Y - 150 Van - Low Roof RWB Sliding 156 2015 Transit E1Z - 150 Van - Low Roof RWB 60/40 157 2015 Transit E2C - 150 Van - Med Roof LWB Sliding 158 2015 Transit E2Y - 150 Van - Low Roof LWB Sliding
which are just the 150 Vans. They also have (250, 350, 350HD in the Vans), (150, 250, 350, 350HD in the Wagons), (Cutaways and Chassis Cabs which have 9 configurations each). That description was for my purpose to keep all of the different Body Codes for the Transits straight so I would prefer to keep those together.
I'm starting to get the feeling that I am biting off more than I can chew. I've been working on this for about 4 months now and I'm really no further along than I was at the beginning.
Your choice of course. I think you will find out later that searching on this combined data will be problematic. I see no reason to keep it all in one field when a form (or query) could concatenate the values so they look as you have them now, but it is your choice of course.That description was for my purpose to keep all of the different Body Codes for the Transits straight so I would prefer to keep those together.
Understandable, but the reality is that you're much further along than what you were even just a few days ago. Access and relational data modelling has a big learning curve. Just think of how great it will be when it's all working! I think I can speak for others when I say that we'll help as much as we can.I've been working on this for about 4 months now and I'm really no further along than I was at the beginning.
Try one or two junction tables and post back with your approach.
Here's an update. Not sure if I'm on the right track or not? Thank you