Results 1 to 8 of 8
  1. #1
    biederboat is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Location
    Portland, OR
    Posts
    12

    Newbie designing database - need basic structure advice on strategy


    Hey all,

    I didn't see a newbie area so I'll do a quick intro. Most of my database experience is old, really old as in R:Base. I have done a few single-table applications in Access so I do understand the basics of tables, forms, reports, etc. I'm now starting to design a database at my present employer which will print a "smart drawing" that will in essence be a one-page report showing data for one record (i.e. a part number). Now we have four different basic types of part numbers and we would like the report and the data to be different for each part family. Am I best off making different tables for the different types of part families or is there some way I can control the fields that are used based on part type.

    Just as an example, all part families would have the fields customer, part number, revision, base material, part type and a few others. But "Part type #1" would have Core Size and a few other fields that the other part types wouldn't have and so on.

    I'm leaning towards just making different tables for each one since they all have a unique report but just thought I'd check if that's really the right approach.

    Thanks!
    BB

  2. #2
    FMAlanbrooke is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2014
    Posts
    8
    Sounds to me like you only need one table that has all the fields for all part types plus an extra field "part type" with a drop-down list indicating which field it is part of. Then you create a query and/or a report based on only those fields with the search results you want. This is assuming that there are only a few differences between part types so adding extra fields to the table won't make it too big. You could also create a tabbed form based on the table that had tabs for each part type or have four different forms that only displayed the part types you want. Are there specific features of each part type that need to be recorded? In that case you will need a part number and a part type table, linked.

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by biederboat View Post
    ...I'm leaning towards just making different tables for each one since they all have a unique report but just thought I'd check if that's really the right approach.
    There are a couple approaches you can use. One might be to use a lookup field in the table. I do not like this option for various reasons. Another approach is to make an actual table that represents the four groups.


    tblGroups

    GroupID (PK)
    GroupDesc (Text)

    With this you can place a numeric value (type Long Integer) in your main table that represents the group. The Primary Key value will go in the Foreign Key field of the main table.

    Here are some tutorials on RDBMS
    https://www.accessforums.net/tutoria...ase-45451.html

  4. #4
    biederboat is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Location
    Portland, OR
    Posts
    12
    Quote Originally Posted by ItsMe View Post
    Another approach is to make an actual table that represents the four groups.
    I think this is the direction that might make the most sense, if I understand it correctly. We would make a main table of customer, part number, part type, etc. (fields that are common to all parts) and then the part type would indicate which table to use for the fields that are relatively unique to the different part types. Each of the part type tables would have a unique report based on the data used for that type of part. I guess that's sort of a hybrid approach that I didn't think about since I've been away from all this for so long.

    Does that approach make sense? There would be at least 5 fields in the main part number table (again, common to all part types) and then each part type table would have at least 6-7 fields just for that part type (in theory, some of these are common amongst some of the part types by none of them would be common against all part types (e.g. inside and out diameter is common among two of the part types but not all of them).

    Before I commit to this I'll make a list of all the fields for each part type and see if it might make sense to use your approach FMA. I'm assuming that there will be a great disparity once we get past the common fields amongst the part types but, if they tend to be just a few I can see where your approach might make sense.

    Hope that helps clarify it.

    Thanks,
    BB

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I would not have a table of parts, tblParts, that included a field, "Customer". An exception might be a distributer that uses the manufacturer number for the part number and inserts a VendorID into a Foreign Key field. This would allow for multiple records having the same Manufacturer Number and the VendorID would act as a Compound Key.

    Otherwise, a parts table would have

    MyKeyValue (PK)
    MyCompanyPartNum (Long Integer or Text)
    ManufacturerNum(Text)
    DescriptionField1
    DescriptionField2
    QtyField

    The above table would be for parts that would be representative of a whole case or unit. Child tables would represent pieces or case counts of a unit; in a circumstance where your company would divide, store, and distribute partial cases/units.

    I recommend you go over some tutorials for creating an entity relationship diagram, rules of normalization, and data types for Access.

  6. #6
    biederboat is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Location
    Portland, OR
    Posts
    12
    Quote Originally Posted by ItsMe View Post
    I would not have a table of parts, tblParts, that included a field, "Customer".
    I think I understand what you're saying but maybe I need to clarify. We are a "job shop" manufacturer, these part numbers are associated to a customer of ours (these are their part numbers, not ours). We do not have need to assign our own internal part #'s, nor is there ever a case of where the same part number is sold to two different customers (two different ship points perhaps, but that's a whole other issue). I think what you're implying is the case of where one part could be sold to multiple customers and we might be selling it as a different part number than what we purchased it as; which is not the case for us.

    Does that make sense?

    Thanks,
    BB

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Yes, it does. In a case like this, you could include a Customer field. It would be similar to the Vendor scenario I pointed out. Note that you would want the Customer field to be a Foreign Key field. So, rather than typing literal text that describes the customer into the Customer field, you would enter a Key value that represents a record in another table, tblCustomers.

  8. #8
    biederboat is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Location
    Portland, OR
    Posts
    12
    Quote Originally Posted by ItsMe View Post
    Yes, it does. In a case like this, you could include a Customer field. It would be similar to the Vendor scenario I pointed out. Note that you would want the Customer field to be a Foreign Key field. So, rather than typing literal text that describes the customer into the Customer field, you would enter a Key value that represents a record in another table, tblCustomers.
    Gottcha, I haven't thought all this out that far yet but it makes sense.

    BB

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

Similar Threads

  1. Advice on designing my 1st database
    By cmb in forum Database Design
    Replies: 2
    Last Post: 09-09-2014, 03:42 PM
  2. Need help and advice designing a call center database.
    By AbandonedRobot in forum Database Design
    Replies: 3
    Last Post: 07-20-2014, 01:49 PM
  3. structure advice?
    By akwormy in forum Forms
    Replies: 2
    Last Post: 06-11-2014, 06:43 AM
  4. Replies: 27
    Last Post: 10-08-2013, 03:20 PM
  5. Database Structure Advice
    By Douglasrac in forum Access
    Replies: 9
    Last Post: 07-16-2011, 07:14 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