help setting up tables in Access

  • Thread starter Thread starter Vince
  • Start date Start date
V

Vince

I need some help! Here is my database project -

1 - Each ring has it's own item number.
2 - Each ring can be several different sizes.
3 - Each ring can be several different colors.
4 - Each color can be either white or yellow gold.
5 - All rings of the same item number are the same price,
regardless of size, color or gold type.

How would the tables be setup and the relationships
established for me to do the following:

1 - Easily enter each ring into the database.
2 - Select a ring and see what size, color, gold type and
price are in my inventory.

I dont seem to be able to get the tables and relationships
setup correctly. I would appreciate any help provided.

Vince
 
I need some help! Here is my database project -
Click to expand...

Sounds like a class assignment. Sorry, I finished taking all the
Access classes I plan to take some time ago. This is YOUR project.
I dont seem to be able to get the tables and relationships
setup correctly. I would appreciate any help provided.
Click to expand...

If you have a *specific problem* or question you want explained, by
all means post it. Folks aren't going to do your homework for you
though!
 
No John this is not homework or a class assignment, this
is a real world project. My wife has started a business,
and I thought that Access would be the best tool to use
for helping her keep track of her inventory and sales. I
program in VB, and use Excel with VBA all the time, but
have not had much experience with Access.

I originally created 1 table with all the information, but
after reading about Access I don't think that would be the
correct way to set it up, so I have gone back and tried to
setup the following tables -

1 - tblRingNumber
2 - tblColor
3 - tblGoldType
4 - tblSize
5 - tblPrice

I can setup table 1 and 2 above, set the reference as 1 to
many, enforce referential integrity, cascade updates and
deletes and it works fine. It is when I try to add the
additional tables and references that things don't seem to
work correctly. Maybe I don't need 5 tables, maybe I need
more, I don't know, but I do know the original design of
the database will save me a lot of problems later.

I would appreciate some help with this, or if you know a
good Access reference book that I could go to Amazon.com
and purchase that would be fine also. I did get a laugh
about your class assignment comment. I am 40 years old,
with a full time job and 4 children.....going to class is
the last thing I have time for right now!

Vince Williamson
 
No John this is not homework or a class assignment, this
is a real world project.
Click to expand...

My apologies for the lecture. It sounded like one - and I should have
checked before venting my suspicious.
I originally created 1 table with all the information, but
after reading about Access I don't think that would be the
correct way to set it up, so I have gone back and tried to
setup the following tables -

1 - tblRingNumber
2 - tblColor
3 - tblGoldType
4 - tblSize
5 - tblPrice
Click to expand...
I can setup table 1 and 2 above, set the reference as 1 to
many, enforce referential integrity, cascade updates and
deletes and it works fine. It is when I try to add the
additional tables and references that things don't seem to
work correctly. Maybe I don't need 5 tables, maybe I need
more, I don't know, but I do know the original design of
the database will save me a lot of problems later.
Click to expand...

Perhaps it would be clearer if you indicate a) how the tables are
related and b) what specific problems you're having! "don't seem to
work correctly" isn't very informative. Are you using a Form to enter
the data? If so, you should be able to use Combo Boxes based on
tblColor, tblGoldType and tblSize to select valid values. Or are you
another victim of Microsoft's "Lookup Wizard" misfeature? Are you
trying to do your data entry in Tables? If so, consider using a Form
instead.
I would appreciate some help with this, or if you know a
good Access reference book that I could go to Amazon.com
and purchase that would be fine also. I did get a laugh
about your class assignment comment. I am 40 years old,
with a full time job and 4 children.....going to class is
the last thing I have time for right now!
Click to expand...

Glad you could laugh about it, and again... my apologies. Though I was
in fact taking classes (not in Access) six years ago... at age 50!
When I stop learning, my wife has instructions to call the undertaker.

A book I like a lot is from my friend and colleague John Viescas -
"Running Microsoft Access <version>". He's got some good ground-up
examples of how to design a database. Another, unfortunately out of
print, is Rebecca Riordan's _Designing Relational Database Systems_.
There are a number of other good books as well; if you can make it to
a good bookstore with a technical section, pull a few off the shelf
and see which fit your learning style.
 
Back
Top