Page 2 of 5 FirstFirst 12345 LastLast
Results 16 to 30 of 63
  1. #16
    Topflite66 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    87

    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?

  2. #17
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Probably would be best.

  3. #18
    Topflite66 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    87
    Great. Thanks again.
    Attached Files Attached Files

  4. #19
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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??

  5. #20
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.

  6. #21
    Topflite66 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    87
    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
    Thank you for those suggestions. I can easily make those changes.

    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 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.

    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'm not sure why Keyless entry is part of orderDetail??
    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 also think you would have a table such as vehicleOrdered that would have all necessary attribute IDs.
    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.

    Perhaps you need separate Invoice and Delivery.
    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.

    Thank you again for the suggestions. I will make those adjustments.

  7. #22
    Topflite66 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    87
    Here is what I have so far. I forgot to mention, I worked on this at home with Access 2007. Thank you
    Attached Files Attached Files

  8. #23
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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"?

  9. #24
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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:
    AxleID Axle Model
    1 3.15 Regular Edge
    2 3.15 Limited Slip Edge
    3 3.15 E-Locking Edge
    Otherwise, the junction tables could look like (series considerations can be handled by the BdyCdSeriesID)
    AxleID Axle Model
    1 3 2
    2 2 2
    3 1 2
    - 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.

    Other observations:
    I think this should be split: E1C - 150 Van - Med Roof RWB Sliding into
    BodyCode Series
    E1C 150 Van - Med Roof RWB Sliding
    or
    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.

  10. #25
    Topflite66 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    87
    That looks better. Can you take a few sample records and test your proposed set up?
    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.

    Is there anything in the Packages that is also part of the other "options/features"?
    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.

    Thank you.

  11. #26
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    This is what I'm driving at with respect to my post part about junction tables.

  12. #27
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.

  13. #28
    Topflite66 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    87
    Quote Originally Posted by Micron View Post
    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?
    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.

    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.
    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 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.
    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.

    - there are no relationships between any aspects such as model series and options (like colour, trans, engine, etc.)
    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.

    I'll try working on the junction tables though.

    3.15 Limited_Slip vs 3.31 Limited-Slip; one underscore character has crept in (unless that was intentional).
    That was a typo.


    Other observations:
    I think this should be split: E1C - 150 Van - Med Roof RWB Sliding into
    This reference is for the Transit Van which has several combinations. For example:

    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.

  14. #29
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    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.
    I've been working on this for about 4 months now and I'm really no further along than I was at the beginning.
    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.

    Try one or two junction tables and post back with your approach.

  15. #30
    Topflite66 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Posts
    87
    Here's an update. Not sure if I'm on the right track or not? Thank you
    Attached Files Attached Files

Page 2 of 5 FirstFirst 12345 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multiple combo boxes for aggregated filtering
    By Mohibullah in forum Access
    Replies: 23
    Last Post: 02-22-2017, 08:26 AM
  2. Filtering Using Combo Boxes
    By Beanie_d83 in forum Access
    Replies: 4
    Last Post: 05-19-2016, 06:34 AM
  3. Combo Boxes Filtering
    By gatsby in forum Forms
    Replies: 1
    Last Post: 07-22-2014, 12:46 AM
  4. Replies: 3
    Last Post: 09-22-2013, 11:29 AM
  5. dynamic combo boxes
    By brad in forum Forms
    Replies: 1
    Last Post: 04-15-2013, 01:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums