Designing a Database

Christina · Dec 16, 2003

I'm having some trouble determining how to set up some tables, queries, and forms. I've tried a few things and nothing has worked...so I will give my situation and maybe someone will be able to help figure out how to set this up. Tried setting up 2 tables, a query with both, and then a form based on the query. But what I'm trying to do hasn't really worked this way. So here goes...

We hold training sessions, and must have a form that compiles all of the clients with some totals as to total clients, how many are minorities, how many have disabilities, fees collected, etc... But we also would like to know who was at that training. Each client fills out an intake (information form) which is already set up with a separate form and table. Each client is assigned a unique ID and after they are uploaded to the gov't database they are assigned a government id number which is then also entered into our database. Each training session also receives an id number when uploaded from access, and we must have our own id number to upload (which is auto generated). The clients that attend the training, we would like to link to their client information table. But many clients may attend more than one training (thus they have replicated client numbers and gov't id numbers). I would like a list of the clients by their client number and last name, by the training number we assign and the gov't id training number. I tried putting this all on 1 table with the other training sheet info (but listing client1id client 1lastname, client2id client2lastname, etc...) But that didn't work. I also tried to set up a separate table with their info, query which combines the 2, and then have 1 form. But that also didn't work right. I really want to keep away from having to enter each trained client individually on to a separate form into a separate table. But rather would like 1 form where I just list them (in separate fields) and they enter as separate records on to the table that is set to list them by client id, last name, and the 2 training numbers. I just don't know how to set this up. Everything I've tried has brought me errors and bugs. I've only been working with access since the beginning of Oct so I am learning as I go. So any help anyone can give me would be greatly appreciated in how to link this all together. Also, if you have any more questions you can ask.

Thanks to anyone who is willing to help me tackle this.
Christina

Maxine · Dec 16, 2003

Christina,

Create at least 2 tables. One to contain the Client ID,
Government ID and other client info. The other has the
Training ID and the Client IDs that attended. Use a form
and subform to display the clients that attended a
specific training or the trainings taken by a given client.

Create controls on the form that use the DCount function
to count how many minorities, etc.

I design databases and would be willing to work with you
on yours. You can contact me at [email protected]

Regards,
Maxine

-----Original Message-----
I'm having some trouble determining how to set up some

tables, queries, and forms. I've tried a few things and
nothing has worked...so I will give my situation and maybe
someone will be able to help figure out how to set this
up. Tried setting up 2 tables, a query with both, and
then a form based on the query. But what I'm trying to do
hasn't really worked this way. So here goes...

We hold training sessions, and must have a form that

compiles all of the clients with some totals as to total
clients, how many are minorities, how many have
disabilities, fees collected, etc... But we also would
like to know who was at that training. Each client fills
out an intake (information form) which is already set up
with a separate form and table. Each client is assigned a
unique ID and after they are uploaded to the gov't
database they are assigned a government id number which is
then also entered into our database. Each training
session also receives an id number when uploaded from
access, and we must have our own id number to upload
(which is auto generated). The clients that attend the
training, we would like to link to their client
information table. But many clients may attend more than
one training (thus they have replicated client numbers and
gov't id numbers). I would like a list of the clients by
their client number and last name, by the training number
we assign and the gov't id training number. I tried
putting this all on 1 table with the other training sheet
info (but listing client1id client 1lastname, client2id
client2lastname, etc...) But that didn't work. I also
tried to set up a separate table with their info, query
which combines the 2, and then have 1 form. But that also
didn't work right. I really want to keep away from having
to enter each trained client individually on to a separate
form into a separate table. But rather would like 1 form
where I just list them (in separate fields) and they enter
as separate records on to the table that is set to list
them by client id, last name, and the 2 training numbers.
I just don't know how to set this up. Everything I've
tried has brought me errors and bugs. I've only been
working with access since the beginning of Oct so I am
learning as I go. So any help anyone can give me would be
greatly appreciated in how to link this all together.
Also, if you have any more questions you can ask.

Jason · Dec 16, 2003

Hi,

I have a database that saves Orders. There are many products that an order
can have. With this in mind there a 3 main catagories of products.
Currently, I have one product table for each catagory because each product
has different options. What is the best way to map to each product table.
I was thinking of adding the ProductCatagoryID in the orders table which
would map to Products Catagory table. This table would have the id and
table name in which to save the products table to. Is this the best design?
Basically, I am looking for a design that will tell my front end which table
to save to based on
catagory to save the product data for that order.
Any ideas?

Maxine · Dec 17, 2003

Jason,

Yes, that will work. You can add a ProductCategoryID
directly to the Orders table.

Alternatively, you can put the OrderID and ProductID in
one table and the ProductID and ProductCategoryID in a
second table and relate these two tables.

I design databases and would be willing to work with you
on yours. You can contact me at [email protected]

Regards,
Maxine