Results 1 to 9 of 9
  1. #1
    rbiggs is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    23

    Help with Design

    I am new to Access and trying to create a DB for a client that is in the trucking industry. Before I get ahead of my self I was wondering if you could view my relationships and make sure they are going to accomplish what I am after.



    First I created this with a template so the error on open 'The form name 'Project List' etc... how can I find this and delete the reference.

    Rules of the DB:
    An Invoice can only have 1 pickup location and 1 customer
    An Invoice can have multiple drop off locations and AccessCharges
    A dropoff location can have only have 1 driver and 1 truck

    I am sure there are other aspects I have not thought of and your comments are greatly appreciated.

  2. #2
    Rod is offline Expert
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    One question at a time! Here it is.

    Attachment 3697

    So I guess you want to know how to get rid of it.

    Hold on - I can't find out what version you are using from here. I will post this and then post again.

  3. #3
    Rod is offline Expert
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Top left-hand corner of the Access window is the circular Office Button. Click that and you get the usual Open, Save, Print, ... list but notice there is a button in the footer saying Access Options. Click Access Options and then select Current Database. Simply delete the entry in the combo box.

  4. #4
    Rod is offline Expert
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Your relationships are compliant with the rules you quote. So no problem there. I do have some comments about naming that I'm willing to share and a couple of other things. I tend to pontificate but if you want my input then post back.

  5. #5
    rbiggs is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    23
    I would greatly appreciate your input thank you.

  6. #6
    Rod is offline Expert
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    I have already warned you that I pontificate. I’m also somewhat of a pedant. What I say here may be, and often is, contested by other persons; we all have our own personal styles. That’s what this is mainly about: style. As far as I can tell your project is valid in its current state and will achieve your requirements as you describe them. If you decide to make changes, Access does a pretty good job in propagating changes throughout the database but does not necessarily find all occurrences – particularly true for name changes and VBA code.

    You have a mixture of singular and plural for your table names. I am of the school that believes table names should be singular. This convention is inherited from data modelling where all entity names are singular. Plurals are reserved for collections as demonstrated by those Access designers: Property (value), Properties (collection); Control (value), Controls (collection); etc.

    There is a debate over choosing the primary key for a table. I think the majority (myself included) believe it is best to include a ‘meaningless’ key whose only purpose is to provide a unique key for each row. This neatly avoids compound keys. The autoincrement field type is ideal for this purpose and all of your tables have a ‘meaningless’ key.

    tblInvoice however contains a concern with Invoice_Number. The name suggests an external value – something generated by the Accounts Department? – but the definition is a computer generated autoincrement. Which is it? You can have both. I suggest you add a ‘meaningless’ primary key to tblInvoice. You can still declare Invoice_Number to be unique but it is no longer the primary key.

    An even greater debate rages over whether table field names should have tags. The majority (myself included) do not use tags because it makes the code so much more readable. Your design does not use tags and I mention this just in case someone advises you to include them.

    How to name foreign keys? Personally I use a similar name as the field in the parent table: for example ‘ID’ on the product table becomes ‘ProductID’ as a foreign key. Some add a suffix of ‘FK’ or a prefix of ‘_’ whereas others (like yourself) use a name that is different from the field in the parent table. (e.g. Invoice_Number > DropOffLocation_Invoice) Again it is purely a matter of style. I find that if I have used the convention of naming the primary key of a table ‘ID’ (uppercase, indicates an autoincrement) then to see a field named ProductID amongst the attributes of another table is sufficient indication of a foreign key. Anyway, of what use is knowing a field is a foreign key?

    While on the subject: what is the purpose of the field named Customer_Invoice in tblCustomers? It looks suspiciously like a foreign key from its type but there is no relationship. Similar comment about Rte_Access_AccessorialID. Differing spellings on that table anyway: Acces and Access. ‘Access’ is a reserved word so be careful!

    Now for the field names themselves. From the first Access tried to be overly user-friendly in allowing special characters in names, especially spaces, and for that reason encouraged one and all to enclose names in brackets (when coding). Some still follow this convention strictly and enclose every name in brackets whether it’s necessary or not. You have no spaces in your names and brackets will not be necessary. However you do have lots of underscores.

    There is nothing wrong with including underscores. You are using them to separate portions of the field name; the first portion indicates the table and the second the value. I suggest that this use of underscores becomes somewhat tedious, unwieldy and unnecessary. I advise (but do not always follow my own advice ) that variables should always be explicitly referenced. Using an example from your db you would write:

    tblDropOffLocation!DropOffLocation_TruckClass

    I contend it is more comprehensive and easier all round to write/read without any loss of meaning:

    tblDropOffLocation!TruckClass

    Another reason I no longer use underscores is that Access adds underscores when concatenating your names with events. So I believe TruckClass_Click is more comprehensive than DropOffLocation_TruckClass_Click.

    Incidentally, DropOffLocation_DriverName and DropOffLocation_TruckClass do not belong on tblDropOffLocations; when needed you obtain the values from tblDrivers and tblTrucks respectively via the ID foreign keys. Delete them!

    A word of warning: if you follow my suggestion then be careful of calling a table field, ‘Name.’ It can and does cause problems because Name is a property in just about every Access object. Use ‘FullName’ and you are home and dry.

    When I flip through your table designs there is only one field description. Yes you remember what it’s all about now but if you’re like me then six months down the road any aide memoire is welcome. Unfortunately Access uses the description as a user prompt so the wording of the description is critical.

    I have not spotted any ‘Lookup’ definitions in your table designs. I guess you love them or hate them. If used I would expect to see a lookup on each and every foreign key.

    That’s enough for now. As I said it’s mainly about style but there are a few concerns mixed in with the foregoing.

    PS I'm already tired from typing 'DropOffLocation;' could you not rename it, 'Delivery' or 'DropOff?' Likewise PickUpLocation becomes PickUp.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Wow, Rod, that really covers a lot of ground. Agree with all. I do wonder if you had a typo in: "do not use tags because it makes the code so much more readable"
    I've never used tags but if the code is more readable, should I?

    I want to reinforce advice to be careful about naming and avoid using spaces, non-alpha characters (@ # $ % & * + - = ? / underscore can be exception but I don't use it) in any names. Nor reserved words as full names. I think I saw # character in query name when I took a quick look at the project couple days ago.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Rod is offline Expert
    Windows Vista Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Thanks, I feel I've only scratched the surface.

    I meant that the absense of field name tags makes the code more readable; tags just clutter and get in the way. Also I use all tricks to make names as short as possible such that the need to scroll horizontally is minimised. You well know how long some of those VBA lines become without using the continuation character - which is an underscore, no less!

    I missed the query name - well done.

  9. #9
    rbiggs is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    23
    Rod

    Thank you for your advise I believe I have incorporated most of it into my database. Thank you again.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Form design
    By pgdeval in forum Access
    Replies: 2
    Last Post: 05-27-2011, 01:12 AM
  2. Help please in design!
    By Sleepymum in forum Access
    Replies: 1
    Last Post: 01-25-2011, 11:12 AM
  3. Design help
    By jacko311 in forum Database Design
    Replies: 0
    Last Post: 11-12-2009, 05:57 AM
  4. DB Design
    By Merkava in forum Database Design
    Replies: 2
    Last Post: 11-10-2009, 05:51 PM
  5. Design help
    By marix in forum Database Design
    Replies: 1
    Last Post: 04-18-2007, 07:54 AM

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