If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
New to access db
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. -- Thanks Brent |
#2
|
|||
|
|||
New to access db
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. -- HTH Mr B askdoctoraccess dot com "Brent" wrote: 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. -- Thanks Brent |
#3
|
|||
|
|||
New to access db
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. -- Thanks Brent "Mr B" wrote: 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. -- HTH Mr B askdoctoraccess dot com "Brent" wrote: 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. -- Thanks Brent |
#4
|
|||
|
|||
New to access db
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 "Brent" wrote in message ... 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. -- Thanks Brent |
#5
|
|||
|
|||
New to access db
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. -- HTH Mr B askdoctoraccess dot com "Brent" wrote: 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. -- Thanks Brent "Mr B" wrote: 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. -- HTH Mr B askdoctoraccess dot com "Brent" wrote: 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. -- Thanks Brent |
#6
|
|||
|
|||
New to access db
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. -- Thanks Brent "Evi" wrote: 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 "Brent" wrote in message ... 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. -- Thanks Brent |
#7
|
|||
|
|||
New to access db
Brent,
If you still need help with this, don't hesitate to post back. Someone will be glad to try to help. -- HTH Mr B askdoctoraccess dot com "Brent" wrote: 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. -- Thanks Brent "Evi" wrote: 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 "Brent" wrote in message ... 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. -- Thanks Brent |
Thread Tools | |
Display Modes | |
|
|