General :: Creating Multiple Relationships Between Two Tables
Aug 2, 2013
I'm trying to create multiple relationships between the same two tables, but I run into problems every time I try. I'm using Access 2007.
Specifics:
I have a table with information on meetings and there are two hosts. So I have fields Host 1 and Host 2. I have a second table that lists possible hosts and their personal information. I have a relationship between table 1 "Host 1" and the Host in table 2; I cannot create another working relationship between table 1 "Host 2" and the Host in table 2.
how I can get that to work? With just the one relationship, I can go to table 2 of the hosts, click on their name, and see all their meetings.But if I add another relationship, it takes out all of the information. I've been working on this for over an hour,
I'm thinking about creating temporary (while db open) relationships between tables, since transfering tables is possible only if no linked relationships are present. However it's important to preserve in some cases referential integrity.Looking online, I found this code:
Code: Public Function CreateRelation(primaryTableName As String, _ primaryFieldName As String, foreignTableName As String, _ foreignFieldName As String) As Boolean
[code]...
Even with the comments I don't really understand what exactly this code does or doesn't do.I tried the code. It seems (?) to create a relationship. Since the relationship doesn't show up in the relationships table, I'm not really sure if it's there.
- why it's not visible among other relashionships (or is it me?? maybe, it wasn't the code that was working but the query...) - how to implement referential integrity - what's the behaviour of this supposed relationship. Is it permanent or not? If not when is it deleted?
I am trying to create four tables: Company, Contact, Activities, and Opportunities.
I want them to relate hierarchically. A Company can have many contacts, contacts can have multiple Activities and Opportunities. But you can't have contacts without a company and you can't have Activities and Opportunities without having a contact. I want all PK's in all tables to link to one another, that you cannot create one without the other.
How I can do this in Access 2010?
YYMM00000-000000-A0000 CompanyID-ContactID-ActivityID or YYMM00000-000000-O0000 CompanyID-ContactID-OpportunityID
I have 3 tables made up of ID and NAME and other parameters. NAME is unique field in each table, whilst ID is not unique. the three tables share the same fields, but can't be combined due to the NAME field not being unique throughout.
Now I have a single table that has a unique ID so I want to make a 1-inf relationship between this table and all three. the problem is how do I look at all three tables at the same time instead of having to insert subdatasheet on only one table.
and then I need the last SQL query to create Table1 with column id related to the column id in table2 and table3. As I understand the relationship must to set when creating the table... so this is what I got: CREATE TABLE [Table1] (ID INTEGER PRIMARY KEY CONSTRAINT Table1ID REFERENCES [Table2](ID) REFERENCES [Table3](ID));
This produces no errors but when checking in MS Access there are no relationships between them.
I have 5 tables and 2 forms. The primary form is what I input all the information into (Tracking) and the other form is to update employee information (update form).
The "Tracking" form is where I add information to 4 of the 5 tables. Here is where I'm stumbling. Would it be more practical to just have 1 table and just expand the fields, or have the form put the information into the separate tables. Those 4 tables are Employee, phone, spotter and radio.
I'm wanting to keep a running tally of who doesn't turn in what equipment on what day.
Maybe a noob question, but I'm still learning access (I know a little late at the age of 37, but better late then never). I have a form with several tabs, linked to a table with employee information, works like a charm. Now, my last tab is called settings, maybe no need to say that the information from this tab needs to be called from a different table called settings. Certain information, like version number, department, etc I want to display all the time in the header and I want to be able to manipulate that information through the settings tab. I definately don't want any relationships with the employee table. Is there a simple way to achieve this, I tried drag and drop but this leads to errors, so definately I do something wrong here. Much appreciated,
Hey all, i would appreciate some help with Access here.
I am an attorney, and i am trying to set up an access database for all of my cases.
I have one table in which i keep the names, contact information, etc of all the witnesses who i have used or will be using (especially expert or law enforcement witnesses who i will be using in multiple cases).
I also have a main table in which i have a record for each case. Because I have multiple witnesses for most of my cases, I have had to create multiple relationships between my main cases table and my witnesses table.
My question is this: How do I specify when setting up a report (or a form/query/etc) that when i ask for the witness's address i am looking for the address i made with a particular witness, specify that relationship between the two tables, as opposed to the other relationships between the same two tables.
I hope that makes sense, and i would very much appreciate if someone could give me instructions.
I'm building a database about languages and the segments (sounds) they contain. So far it's a many-to-many relationship between languages and segments, and I've set it up as follows.
tblLangInfo: LangID (PK), language name, language family, etc.
tblSegments: SegmentID (PK), Segment -- this table has only one field, with 24 records, each one a type of sound I'm interested in
tblSegmentLangJoin: LangID (PK), SegmentID(PK)
I have another table, tblProcesses, with an (exhaustive for my purposes) list of the "processes" (a linguistic term) a language might have, which also has a many-to-many relationship with tblLangInfo. Thus two more tables:
tblProcesses: ProcessID (PK), Process name
tblProcessLangJoin: LangID (PK), ProcessID (PK)
Here's the fun part...
I'm interested in documenting which segments can participate in which processes, as either a trigger or target. I think this necessitates yet another table, tblProcessParts, with an exhaustive list of the decomposed processes, by which I mean:
This table is thus also two fields, ProcessPartID (PK) and ProcessName_trigger/target.
The relationship I need to capture is: *given* a language, relate each segment to 0 or more ProcessParts. This relationship is many-to-many, and this on top of the other many-to-many relationships described above.
E.g., "t" in Lang1 might be a trigger and a target for Process1, but "t" in Lang2 might be just a trigger for Process1, while "t" in Lang3 might be neither a trigger nor a target for Process1 (though Lang3 does have Process1), and finally "t" might be neither a trigger nor a target for Process 1 in Lang4 because Lang4 doesn't have Process1, etc.
I've attached a picture to illustrate the relationship I need, since that's likely clearer.
One possibility I thought of was to change tblLangSegmentJoin to have a third field that is the primary key (LangSegID), and relate that to the ProcessPartID table. How to appropriately define my data tables and relationships.
I'm trying to create a database at work keep track of projects I'm working on and all the different events that happen during the project.
I want the main table to be customers, which will include the job number (as the primary key) name, address etc. Then I need other tables that have information about the building permit that will include dates city names etc and then another table that would include information on our sales people and so on, there would be maybe 5 tables that all connect back to the customer table.
I have tried this several times and keep running into problems, I'm sure it has something to do with the relationships. I'm missing something. I can create 2 tables and it works fine but once I add a 3rd it wont work.
I am creating a database of medieval labor contracts and have come across an issue.
I have a table of Contracts, and a second table of People. I want the table of People to show every contract in which that person appears. Each contract has multiple roles - there is always at least a Laborer and an Employer.
The same person might appear as a laborer in one contract, and an employer in a second contract and I want my People table to pull every contract in which that person appears, regardless of the role they play in the contract.
So far I have not been able to get this to work. I set up two different one-to-many relationships which link the People table primary key (personID) to two separate columns in the contract table. However, in the People table, instead of pulling contracts in which the person appears as either Laborer or Employer, it will only pull contracts in which the person appears as both Laborer AND employer (a situation which will never occur in my actual data but which I tried out as a test).
I'm new to Access but not SQL, and am having difficulty coming up with a solution to the following problem:
I have one table for all Strings used in all other tables (to make Internationalization easier). The fields are: zId, zType, and zString. zId is an AutoNumber field. zType is a number which defines what type of string it is ( Item Name, Person Name, etc. ). zString is a text field with the actual text string (can be empty).
I have another table for Items, which have (among other things) Names (field name= zName). zName is actually a lookup into the Strings table and stores just the ID.
I want to create a form for people to enter a new Item. When creating a new item using this form, they will have to give it a Name, which will need to add a new entry into the Strings table with the appropriate type (ie, Item Name).
So, my question is "How to do the following..." * on my form to enter Item Data, when a user goes to create a new Item, I must first create a new entry in the Strings table with the correct value for zType. Then, I must get the value for zId for this new entry and assign it to the zName field in the Items table for the new record that I am creating.
I'm working on a contact database for my company's sales efforts. It tracks interactions with prospective customers and assigns them to employees here. My question is this: For mass contact efforts (such as mailings), is there a way to create mass entries to my "Interactions" table so that no one has to enter a mailout interaction 1000+ times?
Structure of the relevant bits of the database is as follows:
Contacts Table Interaction Table
Contacts Table is linked with Interaction table via ContactID
Queries are run to narrow Contacts by various criteria (ex.: by the employee they're assigned to).
So if I run a query to get all the contacts assigned to me, and there are 450 of them, and I send a mailout to all of them, and I want to add an interaction to those 450 people, is there an automated way to do this?
I am trying to develop a DB that is multi user. i ahve not split the data from the forms into separate db's as the users are in different locations and our network can be very slow.
part of the requirerment is to produce a drawing approval form, my current sticking point is that the method i am currently using to create the forms only allows one person at a time to use the process.
currently i have a command button that runs a query and then creates a table from the results. the table is then used as the data source for a mail merged form in word.
what i ultimatly need is a process which allows multiple users to generate these forms at the same time and have the ability to save the form (with no code behind it) as a word document (either RTF etc)
i am thinking that if i create a query on the fly in VB and then use the results to use in a report this would allow me to do the above.....
So my questions are:
1) is my assumption correct 2) If so, how do i do it as i have never done it before 3) If the above is not the best solution/ does not work... what will/is
I am trying to create an input form for input to tables.There is a main TblContacts table with fields such as: LastName, FirstName, Address, etc. There are also ID fields for the Company and Country and joins to the Company and Country tables, each with just ID and name fields.I want to have all the fields from the TblContacts table and the Name fields from the Company and Country tables on the form. I want to be able to select the Name fields from the Company and Country tables from combo boxes and for these also to populate the Company and Country ID fields (from the TblContacts table).
To create the form, I selected Create Form whilst the TblContacts was open. Then, I added the combo boxes pulling the names from the Company and Country tables.My problem is that the selection of the Company and Country names above is NOT populating the Company and Country ID fields on the form (taken from the TblContacts table. I have checked in relationships that the joins have been created.
is there anyway you can create one form which updates all the information in multiple tables. I need to create one of these to make the navigation of the database more easy, so if anybody could help me it would be much appreciated.
I am creating a database of bird banding and breeding records for the refuge where I currently intern. To do this, I have created the following tables:
Adult Banding Records Nestling Banding Records Historical Banding Records
The specific issue:
I have already created forms for entering data into these tables, but now I would like to create a "search" form that will use the primary key (which is always the band number of the individual bird, across all tables) to search ALL the tables and pull up all the information on that record. This will be convenient when we recapture a bird that is already banded, so we can look up their information in our database. Also, since I plan to add many more tables, it would be nice to not have to search each table individually.
I have DB used for inventory for many different categories. I have a table and form for the following: Location, Printers, Pc's and many more.
What i am trying to accomplish is to have a advanced search form that will display how many pc's and there makes and model from selecting the location name or Cost center from a combo box.
So an example would be I want to select MPP-WDF from the combo box click a button and it will return the number of PC and there makes of model's and some other information in a list of records.
I am building a form to create a user record and at the same time i have some yes/no options which are located in other tables but when i want add a user i cannot select any yes/no options they seem locked?
I would like to know if I can create a password protection on specific tables. Within my database, there are certain tables and/or reports to be view by a selective amount of personnel. Can I password protect just that view?
I am using SQL 2000 and have two tables that I want to create a relations ship, so that when I add data to one it gets added to the other.
Table_Main QuoteID int PartNumber nvarchar (both make up key field)
Table_Secondary QuoteID int Item nvarchar (both make up key field)
When I use the database diagram in MS Access (ADP not MDB) I get: "The columns in Table 'Table_Main' do not match an existing primary key or UNIQUE contraint."
Othere then then actual name of the filed, they all match, what gives?
I'm a student working on a project in which I've inputted information from course evalution forms into new databases. I have a database for each semester and then tables for the individual workshops. Problem is, these are many to many relationships and there is no real way to link the tables. My friend suggested a redesign, which would essentially mean splitting up the information for an individual form. (i.e. course instructor in one table, comments on workshop in another table) I have a big problem with this because I would be eliminating peoples' ability to see all the information for one workshop in the same table, which in my mind is just as useless. I'm interested in hearing your thoughts if I'm taking the right approach. Thanks.
