Electronic Parts and Project Database

[MadTom]

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

[ssanfu]

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

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):




Good luck with your project....

[MadTom]

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!

[Minty]

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.

[MadTom]

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.

[ssanfu]

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

[MadTom]

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

[ssanfu]

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

[Minty]

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.

[MadTom]

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!

[MadTom]

Is there a way to edit or add new file to the original post? or can this thread be deleted and start over?

[Minty]

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.

[MadTom]

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

[rpeare]

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:

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

[MadTom]

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