Table design

[CoachBarkerOJPW]

I have a data base for a business. Say the business offers five different
services serv1, serv2, serv3, serv4, serv5. I want to be able to manage these
services (A, U, D, V, S). I don't want them all in one table because I dont'
want the services mixed as I manage them. so I have (and yes I need to use
autonumber as the primary key) the following

tblServ1
tblServ2
tblServ3
tblServ4
tblServ5

In each table I have 4 fields

PrimaryKey ServType Cost SomeText

I want to have another table, tblServices that lists all of the services, so
when a selection is made from a combo box on a form, either a form or a data
grid fill with the appropiate data, depending on which service is selected.
The problem is I can not remember how to set up the tblServices. Any help
would be greatly appreciated.
TIA
CoachBarkerOJPW

 

[John Vinson]

I want to have another table, tblServices that lists all of the services, so
when a selection is made from a combo box on a form, either a form or a data
grid fill with the appropiate data, depending on which service is selected.
The problem is I can not remember how to set up the tblServices. Any help
would be greatly appreciated.

So far as I know, you can't.

What is Service 8?

There are five tables, each with an autonumber value 8.

Proper table design is NOT "mixing your data". It's handling it
correctly. If you insist on using multiple tables, and insist on using
autonumbers, you're painting yourself into a corner.

John W. Vinson[MVP]

 

[CoachBarkerOJPW]

So the next question would be how to set this up correctly.

 

[Larry Daugherty]

You haven't fully described the real-world problem your intended
application will solve. You have posted an issue about the planned
organization of your data. As given, that plan is clearly wrong.

You really need to think through and describe what you're trying to
achieve. Even if you're sure you know, write it down or put it into a
Word document. (I favor Word's Outline view when I'm starting a
project).

My guess is that you need to do some reading on the fundamentals of
databases. Microsoft publishes some books for newbies and there are
always the "Dummies" books. No slight intended

Within a database all instances of the same thing are recorded in a
single table. Therefore ALL of your services would be in the same
table. When you finally get your application done you might be
surprised to find that it isn't even one of the "major" tables.

If you were an automobile dealer you wouldn't have a different table
for each color car. You'd have a single table that would hold
information on all cars and color is just an attribute of a car.
Likewise, what ever else you're doing that your application supports
will probably come to the fore and when you need to enter the type of
service you'd select it from a list.

You can get lots of support from these Access newsgroups.
microsoft.public.access.gettingstarted and
microsoft.public.access.tablesdesign are a couple of great ones for
people just starting with Access.

Get a book or two or more. Explore the resources on the web. You can
do some great things with Access if you put in the time and effort to
learn it.

HTH

 

[CoachBarkerOJPW]

I realized what my mistake was after my second post and worked out the
issues. Was trying to make something harder than it had to be. I just got my
logic all twisted up.LOL
No slight taken, I am not a newbie but then I am not quite an expert yet.
This is a project for a senior level programming class in VB.net using an
SQLand an Access back end. Got some CRDs back from other teammates and they
made no sense, so I was thinking along the lines of what they needed to do to
straighten them out as far as the tables went.
Appreciate the advice, have never turned a blind eye to good advice when it
is offered.
Thanks

 

[J. Goddard]

If you want all the services in one box, you could use a union query and
base the combo box in that. But you would still have to figure out
which table each row in the query came from....

Best way (as others point out) is to use one table, with another field
containing A, U, D, V or S. It's not difficult to keep them separated
when you need to.

John