Inventory Database Help

[changedsoul]

Hi, Im new with access and databases. I was hoping for some guidance on creating my tables. Im trying to create an inventory database that for starters only keeps track of what I have and the quantities. My problem is if I should have one table for everything, or one table each for each type of item. I think it comes down to what information I am storing for each item too. For example, I work at a machine shop. I want to track insert levels, screw levels, drill levels, etc. Each item I want to track has drastic difference in item information that identifies it. An insert for example has an ISO number as well as a Grade that uniquely identifies it. A Screw has a nominal size, a pitch, and a length that uniquely identifies it. To make sure an item is not duplicated, i think I need to have multiple primary keys, because a screw will be a different item if the nominal size and pitch are the same, but the length is different. Can someone help point me in the right directions? I am leaning towards a separate table for every item I have. When I think of this, I cant help but think there is a better way and less confusion, but this is why I am asking here.

[rzw0wr]

The following link may be a good stater for you.

http://symplebyte.com/microsoft_offi...ta_access.html

Dale

[changedsoul]

Thank you for posting this link. This brings up a question that has been eating at me and never knew how to ask. Going through this example, all the way up to the end in part 4, he mentions about when entering data, you get an error if there is not a corresponding album already in its table., and so you have to enter the Album name first in its table, then the song, etc.

This to me seems to not be seamless. If I set this database up, I would like to be able to enter all the info at one time, whether its in a table or a form. It seems counter productive if I had to enter a CD I had, to have to first goto the album table and enter the album. Then goto the artist table and enter the artist, then goto the song table and enter all the info there.

This has been my main hangup when trying to understand database design, it wasnt untill now going through this example I am able to understand what I am having an issue with.

I thought relationships allowed you to link other tables to one table so you could enter new info into that one table and the data would be placed into its corresponding relationship tables. Thats why I had such a hard time with relationships, because i would create a relationship and thought I could now update both tables from within one and always got errors such that a record could not be found in a table.

So....
Using this Music database as an example, linked to above, How do I go about entering data seamlessly. One place to enter Song, Artist, Album, etc, and if the album doesnt exist in its album table, it would add it to the album table, without me having to have to manually add the album first and then go back to add my data.

[orange]

changedsoul,
I'm going to suggest a different tutorial in the hope of helping you with tables and relationships. Only because it's different than the one Dale suggested-- another sample so to speak.
But first, an inventory database is not trivial. If you do some searching on various forums, you'll see this sort of comment.

In the tutorial I'm recommending, you start with a business definition/description; work through the statements and follow a process to identify potential tables and associated attributes(fields); go through a process to see how the tables may be related based on the business description; and continue until you have designed the tables and relationships.

All before you start to worry about Access forms and queries etc.

You'll have to work through the tutorial to get an understanding/familiarity with the process. But it works and the author shows interim steps and the solution.

see http://www.rogersaccesslibrary.com/T...lationship.zip

Good luck.

[changedsoul]

Thank you also for this link. I came across this a few days ago in my search for knowledge. Im not trying to say I fully understand normalization, far from it, but with a very simple two table database that has been broken down into its correct entities, I am lost at this point how to pull it together into a seamless way to enter data into both tables.

All these tutorials I read seem to stop short and stop at the relationship stage where I think I need a step further to help it all sink in. Something like: So now you have your two tables and a one-to-many relationship between them, now you have to " knowledge i don't have here" to make entering your data seamless. From an end user point of view where they dont need to know what table holds what data. I need a complete very small walk through I guess.

I know I need to apply all these techniques and fully intend to. But reading all these tutorials seem to suggest to design your database as a whole first. I cant work that way. Maybe if I was well versed in database design. I need to start small with two tables in a relationship and learn how to bring it together into a seamless way to enter data into both tables.....If this makes any sense.

Dont get me wrong here, I really value all the info you and everyone else gives. Its just that I am reading and re-reading about table design and I am missing a vital piece of information that will make it all click in my head. So far I haven't found it.

[orange]

Note: The links I have included are for reference, I'm not suggesting you need these to do what you are trying to do. You can review these links at your leisure. Read through my example and see if it helps.

Suppose you had a Student Table and a Course table.

tblStudent
Studentid PK
Studentfirstname
StudentlastName
other student specific fields

tblCourse
Courseid PK
Coursename

If you have many Students and many Courses you have the Many to many condition.
To resolve this you use a junction table, and create 2 one to many relationships.

tblStudenttakesCourse
StudTakesCourseID PK (autonumber)
StudentId FK to tblStudent
CourseId FK to tblCourse

....1....................>Many<................... ....1
tblStudent--->tblStudenttakesCourse<---tblCourse

??A Student takes 1 or More Courses (otherwise he/she is NOT a student)
A Course is taken by 1 or more Students

John takes Chemistry, Calculus and French
This is the classic 1 to many.

The above info is about database design -tables and relationships --not data entry/queries.
Below is how you might use the design to show Students and their Courses in Access

Best handled in Access by Form/Subform. The Form (main) is used for the 1 side of the relationship (John), the many side is handled in the subform. The form and the subform are "connected" by the LinkChild/MasterFields -- in this case StudentId is the link between tblStudent and tblStudenttakesCourse.

So when we see the Form/subform you see John and the Courses he is taking.
If you wanted to add a new Student, that student would have to be recorded in the tblStudent before you could identify
which courses that student takes in the tblStudentTakesCourse. Similarly, if you wanted to indicate that John also takes
Botany, you must have a record for Botany in the tblCourse, before you can have it in tblStudenttakesCourse.

The message here is You must add the record to the One table, before you can use it in the Many table when dealing with a 1 to many relationship.

One to many is often referred to as Parent/Child. You can not have Child before you have Parent. So you don't just add something to a table and it magically/seamlessly is in a second table.

I hope this helps some. If not, use this example and post question or scenario that's unclear.

There is a lot to learn and it requires time and practice. Your database design is intended to support your "business". You can design the tables and relationships without involving the database management system. Too many people want to jump into queries and forms... without understanding the business and business needs.

-----------------------------------------------------------------
Here are a couple of videos:
Resolving many to many https://www.youtube.com/watch?v=7XstSSyG8fw
Form/subform https://www.youtube.com/watch?v=YZKN_-P6wck

Here is a standard set of links I often recommend:
Principles of relational Design http://forums.aspfree.com/attachment...2&d=1201055452
Entity Relationship Diagramming http://www.rogersaccesslibrary.com/T...lationship.zip

Video tutorials:
These are quite good for learning by Watching/Listening rather than reading.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

Steve Bishops Series on VBA Programming for MSAccess

[changedsoul]

Thank you very much "orange", that video on forms was exactly what I needed to see it all come together. I at least have an idea now how the data entry can go. Without that I was really lost when it came to trying to enter data into tables when two have a relationship. The subforms cleared it up. I know there is much more to know, but this helped me over come my first learning hurdle. I do have my more questions, so Im sure Ill be back.

I find the hardest thing when asking for help is not knowing what to ask because your really not sure what you don't know
I would like to ask for some guidance though. With the task I have in front of me and not fully understanding everything yet, when I try and tackle my normalization, it would probably be wise to not try and include every detail of every item I am trying to track.

I am getting overwhelmed just thinking about it before I even get to paper. There are so many different items, each with their own unique way of being identified, and most needing at least two PK's to protect from data duplication, I need a direction to get started in.

[orange]

We've all been there. Ask away.
Good luck with your learning.

[rzw0wr]

orange, thanks of the zip file.
I have been looking for more and better tutorials.
Thanks also for helping out on the is post.


Dale