Results 1 to 9 of 9

Electronic Parts and Project Database

  1. #1
    MadTom's Avatar
    MadTom is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT
    Posts
    70

    Electronic Parts and Project Database

    This is my first database project using Access. Been working on it for some time to get it the way I wanted it. May have some bugs and need some tweaking! Please make Suggestions and comments to make this project better. Many thanks to all that helped in this forum! Next I need to add reports.


    Mad-Tom
    Attached Files Attached Files

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,144
    I finally was able to get your dB converted to A2010.

    I am confused on where this is headed. Here are your tables. I do not see any relationships between the tables - well, "CapacitorType.CapType_Name" may be the source for "Capacitors.Cap_Type".
    Click image for larger version. 

Name:	Relationship1.png 
Views:	30 
Size:	50.8 KB 
ID:	37607
    And setting the format for the PK field (an autonumber) is not a good idea.


    AutoNumber
    ----------------
    Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.

    Also see Microsoft Access Tables: Primary Key Tips and Techniques


    This is closer to what I would expect to see (I added a table and changed some fields in existing tables. I set RI between a couple of tables):
    Click image for larger version. 

Name:	NewRelationship1.png 
Views:	32 
Size:	53.9 KB 
ID:	37608



    Good luck with your project....
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  3. #3
    MadTom's Avatar
    MadTom is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT
    Posts
    70
    Thanks Steve, still work in progress. I will look at the AutoNumber field. I do have some
    relationships between the tables, but working on it, not sure about
    relationships yet.
    Thanks for your reply!

    Click image for larger version. 

Name:	Screenshot (13).jpg 
Views:	26 
Size:	96.5 KB 
ID:	37631


  4. #4
    Minty is online now Hacker
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,179
    I'll put a small conundrum in for you here, about quantities.
    A quantity of things isn't normally a single entity of that thing.

    Lets say you have 300 of capacitor ID 54. Your Bin locations can only hold 100 of each.
    Therefore you have to have three bins with 100 of that part in them.

    Can your data model handle that type of information? It doesn't immediately look like it.

    What you will probably need is a partID, LocationID and Qty sub table.
    Then your parts can live in as many locations as required, and your total parts qty is a calculated sum of the grouped part IDs and Qty's.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    MadTom's Avatar
    MadTom is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT
    Posts
    70
    Minty, Not a problem in my data base, just add Part info. with another ID# and a new Parts location. If you do a search you will see two parts with same info. with two locations. If you wanted, you could update location to a bigger one and put all your parts in that one.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,144
    Referring to the image in Post #3.

    Why are the fields
    P_ProjectQty,
    P_ProjectComp,
    P_Project,
    P_Date
    (and maybe P_Notes)
    in the table in tblParts? Are they an attribute of a "Part"? These fields are describing things of a PROJECT, not Parts, so they should not be in the Parts table.

    You would not need/have "tblProjects_1".
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  7. #7
    MadTom's Avatar
    MadTom is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT
    Posts
    70
    It would seem that way, but I'm using them as Temp data to add to projects and Subtract Qty from Parts Table in a Query. Look deeper! P_Date when entered, P_Notes as needed.
    Thanks,
    Tom

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,144
    So the dB is opened in Exclusive Mode - only one user at a time??

    Not the way I would design it...... but, it is your dB.



    Good luck with your project.....

    I'll back out........
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  9. #9
    Minty is online now Hacker
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,179
    I have to say it's not making sense to me, and I have built a couple of rudimentary parts / inventory systems.

    If you have a PartID as a primary key, I would assume you have another field which is a PartNumber (maybe a manufactures part number), this is the part number the end user sees and is familiar with.
    Why would you add the same part number again with a different PartID?

    Is your tblParts actually more of a parts used table, not the actual "Parts List" for lack of a better description. In other words you would normally have a MasterPartsList consisting of

    PartID, PartNumber, Description, OtherFixedData, etc , etc

    And you have a PartsUsage table something like

    UsageID, PartID_FK, UsedOnProjectID_FK, Qty, DateUsed, UsageType, etc etc

    Does this make sense or are we all barking up the wrong tree.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Electronic Parts Database
    By MadTom in forum Database Design
    Replies: 7
    Last Post: 07-23-2018, 05:10 AM
  2. Machine Parts Database
    By johnomar in forum Access
    Replies: 1
    Last Post: 04-24-2017, 03:04 AM
  3. Products and Parts Database
    By aesp533262 in forum Database Design
    Replies: 13
    Last Post: 11-11-2012, 07:07 AM
  4. Replies: 3
    Last Post: 08-02-2012, 10:37 AM
  5. Electronic ledger database design
    By conjoa in forum Database Design
    Replies: 1
    Last Post: 07-31-2012, 04:49 PM

Tags for this Thread

Posting Permissions

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