Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206

    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
    9,664
    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:	214 
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:	213 
Size:	53.9 KB 
ID:	37608



    Good luck with your project....

  3. #3
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    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:	209 
Size:	96.5 KB 
ID:	37631


  4. #4
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    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 Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    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
    9,664
    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".

  7. #7
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    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
    9,664
    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........

  9. #9
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    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 ↓↓

  10. #10
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Sorry for the delay! There maybe a time where the same PartNumber is in two locations, also many different parts all have different ways of naming and numbering, I'm offering many options. This database is for Parts inventory and usage, also keeping track of projects and parts used in each. I'm learning as I go. Thanks for your comments!

  11. #11
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Is there a way to edit or add new file to the original post? or can this thread be deleted and start over?

  12. #12
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Add a the latest copy to a new post in this thread if it's related to the original question.
    Keeps the suggestions and your project evolution in one place.
    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 ↓↓

  13. #13
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206

    My updated Parts and Projects dadabase

    This is a sample database. This database was designed for my personal use to keep track of all my small parts and boards so I can find them when needed and know what I have on hand. I wanted to learn Access and no better way then to have a goal and a project for the real world.
    I'm always looking for suggestions and upgrades. I would love Comments!
    Thanks to all that helped!
    Tom
    Attached Files Attached Files

  14. #14
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    There's no need to separate your data entry and 'search'. They can both be done on the same form. Especially if you use unbound forms.

    My personal preference here is not to use bound forms unless you have a very strong understanding of how to prevent data getting into your database that you don't want. For instance. On one of your records you have no part name. I would assume a part name is a required element, in a bound form you would have to have a 'before update' and a 'before insert' event to cycle through your fields and determine whether they are necessary or not. I usually do this using the TAG property and doing something like:

    Code:
    dim ctl
    for each ctl in me.controls
        if instr(ctl.tag, "REQ") and isnull(ctl) then
            smsg = smsg & ctl.name
       endif
    next ctl
    
    if len(smsg) > 0 then
        msgbox "The following fields are required" & vbcrlf & vbcrlf & smsg & vbcrlf & "Please try again", vbokonly, "ERROR ADDING RECORD"
        exit sub
    else
    
       'go on to whatever check you're doing next
    endif
    Secondly, You have a ton of space chewed up unnecessarily, and this form will only work for people who have their screen resolution set to what you did your design with or a higher resolution. Anyone using a lower resolution will have to use the scroll bars on both the right and bottom to get to elements of your form. I would suggest trying to compact the UI as much as possible just to make it usable by a wider variety of users.

    Third, never allow data entry to occur on your tables directly. That is a recipe for disaster. All your 'X table' buttons will allow anyone using the database to add anything they want to these tables or even accidentally alter existing records (or delete them) which will fry your data over time.

    Fourth, if this database will be used by anyone but you, I would hide the tables, it's really a very small thing but it will prevent any novice from accidentally opening this database (by holding down the shift key) and deleting/causing damage to/renaming your tables.

    Fifth, I would split this database into a front end and back end. So even if someone 'deletes' a table. They would actually have to go to the back end and delete the table as well which protects you from most new users doing something disastrous to your database.

    Sixth. I would have a part transaction table, where you add and remove parts from your supply rather than just updating a number (which appears to me how you're doing it right now). This way you can calculate what you have on hand and also audit your usage, who used the part for what project, etc. Right now you're relying on them knowing the project name on your parts screen which is the reverse of how I would set this up. I would have a parts table, a project table, a receipts table (this could potentially be part of the 'project' table depending on need), which had a common 'part transaction' table recording both additions and subtractions from your inventory.

    Your data structure looks ok though, field names appear to be relatively consistent, no special characters in the tables I looked at which is good, not using reserved words, also good.

    rp

  15. #15
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Thanks for your comments! I understand your concerns, but at this time only used by me, and some things added only for my convenience and could be deleted or disabled. So many different parts, the only required field I have is Part Type at this time, some Part Names are the same as Part Type. Same as some parts don't have a Part Number, but most do. Please remember I'm new to Access and this is my first database, leaning from my mistakes.
    MT

Page 1 of 2 12 LastLast
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, 06:10 AM
  2. Machine Parts Database
    By johnomar in forum Access
    Replies: 1
    Last Post: 04-24-2017, 04:04 AM
  3. Products and Parts Database
    By aesp533262 in forum Database Design
    Replies: 13
    Last Post: 11-11-2012, 08:07 AM
  4. Replies: 3
    Last Post: 08-02-2012, 11:37 AM
  5. Electronic ledger database design
    By conjoa in forum Database Design
    Replies: 1
    Last Post: 07-31-2012, 05: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
  •  
Other Forums: Microsoft Office Forums