New to access db

[Brent]

I am trying to develop a db to track people that go to conferences, where,
when, how much etc. I have three tables, one called employees, one called
events and one called funding centres. I have tried to form relationships
between tables and I think this is where the problems start. In the employee
table I have employee ID, first name, last name and employee number. In the
events table I have event ID, conf name, location, date of travel, date
returned, reg costs, travel costs, accommodation costs, refund deadline and
employee ID. In the funding center table I have payment ID, accounts,
functional centre, other funding sources and employee ID.
I have a one to many relationship from the employee table employee ID to
funding centre employee ID. I have a one to many from the employee ID in the
employee table to the events table employee ID.
First of all can someone tell me if these relationships are OK or are there
other fields I need to have for this to work? I set up a form with the
employee fields on top and a subform with an event and a funding subform but
it does not work.
Sorry for the length of the note but I wanted people to have the whole
picture. Any help would be appreciated.

 

[Mr B]

Brent,

This sounds like one of those situations where you need a many to many
relationship, not a one to many.

Because you can have many employees attending many conferences with funding
for each attendee at each conference you need to be able to link all of these
together.

Creating a many to many relationship is accomplished by creating yet another
table. In this table you have the Employee Id, the Conference Id and the
Funding Id. A record is created in this table by storing a value identifying
the employee, the conference and the funding.

Then when you need to see the information, you simply link the corresponding
tables to this linking table to produce the data.

Creating and working with many to many relationships sounds complicated at
first, but if you can just think through the real world senerio you will find
that it simply follows the logic for the real world situations.

You will need to use some VBA code to write the records to the linking table
to make this happen.

As you work through this, don't hesitate to post back here for more help.

 

[Brent]

Thanks Mr B for your help with this. I have created the table however I'm
still unsure how I create the relationships and what record do I create
storing what value?
Sorry to sound so lost on this but that is what I am.

 

[Evi]

Naughty Brent! smacked legs for crossposting

You would be better off adding both newsgroups to the Newsgroup heading -
it'll be easier for you to keep track of replies too.

My reply is in tablesdbdesign.

Evi

 

[Mr B]

Brent,

There is no actual requirement for you to have to create the relationships
between the tables because you will be creating a record in the linking table
that will provide all of the links between the other tables.

You will add records to the employees table. Then at some point you will
have a record for a conference. While you are on the record for the
conference, you could have a form that would open with a list box of
employees with multi select enabled. Then the user would simply select as
many employees from the list and when finished selecting, click a button and
your code would add a record to your linking table for each employee selected
from the list. Each record would have the EmployeeID and the ConferenceID
value, thus the link.

This same process could be accomplished from the record for any employee and
have the user simply select the conference they are about to attend from a
list or combo box. Your code would then add a single record to the linking
table with the EmployeeID and the ConferenceID.

The funding information could be added when viewing the employee record by
simply creating a record of the data for the funding and adding the FundingID
for that record to the record in the linking table for the specific employee
attending the specific conference. So when you are finished with the record
in the linking table, you should have an EmployeeID, a ConferenceID and a
FundingID in each record in the linking table.

 

[Brent]

Thanks for the help Mr B. I have a few things to try and hopefully i'm on my
way. I did post to the table newsgroup after i posted here first as I thought
I might be in the wrong newsgroup. I apologized to Evi and you for the no no.

 

[Mr B]

Brent,

If you still need help with this, don't hesitate to post back. Someone will
be glad to try to help.