Relationships In General...
Apr 20, 2005:confused:
Could Someone point me in the direction of some info on relationships.
I have a basic understanding but I start to get lost on when to use certin types of joins.
ADVERTISEMENT
General Understanding Of Relationships
Dec 22, 2006Please see the attached two files.
I have tried to figure out what the difference in functionality would be between 1 and 2. In the latter I have just created a series of relations between the last two tables (but that structure could be used throughout the whole DB instead of hanging it together with one relation between tables as is the case for the rest of the DB in this picture).
So there can be three different situations with this table structure:
- linking all tables together via multiple relations
- linking all tables together via a single relation
- a combination of both, such as in the images.
Why would someone choose one versus the other? "Which is better in what situation?"
I've been fiddling about for a while now, but in a structure such as this it doesn't seem to make a difference. Or maybe there are differences in use/design, but on a level that I have not explored yet.
Could anyone explain this to me a bit? I have multiple books, all of which handle basic relations but I have not yet seen an explanation for this question. I think understanding the whole relationship topic is critical to be able to see other pieces of the puzzle fall into place later on. And as of yet, this is really fuzzy to me.
General :: Relationships Between 9 Tables Are Broken
Aug 16, 2013I have been asked to update at my organisations access 2003 based database that has been in operation for about 6 years.
I find that there are no relations between more than 10 tables and wondered how reports are still being produced.
General :: Displaying Data In Many To Many Relationships
Aug 26, 2014My problem is displaying data in many-to-many relationships.
There are three main tables: tblShops, tblOwners and tblMarketingEvents.
These are linked through two junction tables creating two many-to-many relationships:
1) Each Shop may have more than one Owner and each Owner may have more than one Shop.
2) Each Marketing Event may reach more than one Owner and each Owner may be reached by more than one Event.
Now I want to display for each Marketing Event:
1) the data of that event, (tblMarketingEvents)
2) plus the owners reached in that event, (tblOwners)
3) plus all the shops owned by the owners reached in that event. (tblShops)
Preferably, I want to display more than one record at a time without repeating any information.
Is there a way to display the data like this?
I tried a query: it gives the right data but does not display it as I need. It repeats the data from tblMarketingEvents and tblOwners for each shop.
I tried a form with subform: it can only display one record at a time and is hard to work with (printing etc)
I tried a report with grouping levels based on the query: Again, right information, wrong display. I need to group according to the records of the MarketingEvents table and not just according to a single field.
General :: Creating Multiple Relationships Between Two Tables
Aug 2, 2013I'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,
General :: Access 2007 Many-to-many Relationships Export To Excel?
Jul 18, 2014how to export Access' many-to-many relationships in excel.
My database is a Project portfolio management tool. One project has many different fields, some of which can only be single values (one-to-one, easy to export to excel) and many others are multiple values fields, built as many-to-many relations (through junction tables).
One usage that was not specified at database creation time was the ability to export the portfolio to excel, so non-access-savvy users can browse, filter, sort and play with the portfolio however they want.
If I build a report, it will contain as many sub-reports as there are junction tables, rendering it un-exportable to excel. A Form would have to be continuous, barring the use of sub-forms for the m2m relations.
Building a query will generate many lines per project (as many as the most populous multiple field), making the excel sheet nearly unusable (in my users opinion, and here, the client's the king).
The best route I have been exploring so far involves "transforming" the multiple fields, so a the different "rows" become additional "columns".
Help With Relationships
Jul 18, 2005Hi all. I really need help with this. Does anyone see a problem with my relationships , i have attached a copy.
Any help will be greatly appreciated
ClaireB
Many-to-Many Relationships
Aug 2, 2005I am new to database design and curious about relationships. In a hypothetical situation, if I have four tables: tblProducts, tblOrders, tblEmployees, and tblCustomers, how could the relationship be set up?
The Products are stored to keep track of the quantity on hand and the employees will also need to be tracked as to how many products they sell. Customers are recorded and Orders wrap up all three tables with the fields: OrderID, ProductID, EmployeeID, and CustomerID.
Would it make sense to say that many products can be bought by many customers and/or many employees can place many orders.
Relationships - One-to-many?
Aug 16, 2005Hi
I've selected a number of tables to the relationship window. When I drag the primary key on one table to the corresponding foreign key on another table, the link created is one-to-one. I want one-to-many. Why did it create one-to-one? How do I change this to one-to-many?
Thanks
One-to-Many Relationships
Mar 7, 2006Hi all. I am new to the use of access and have been told that I need to create a one-to-many relationship to be able to have several records from a drop down list saved to one unique number (my primary key).
Is this correct and if so how do I do this? If it is not correct what is the best way to do what I require?
Thanks for helping.
Relationships
Mar 16, 2006Hi
I have created two tables which are linked with a one-to-many relationship
Using a form the user enters a date. Then in VB I add a value to the date and store this in the second table.
I have simplified this to try to find out what was going wrong and found that when the database adds a value to the second table which only has two fields it first of all adds it to the begining of the record set and the id field does not update to that of the first table where the relationship is joined.
Do I just captutre the value of the id field and add this to the recordset.
How to I get the database to look foir the end of record set before I appent the new generated record.
Relationships
Aug 7, 2006Hi,
I have looked for this on the help section; however, it does not seem to be covered. How would you change a one-to-many relationship to one-to-one? Access seems to automatically guess that the many side is where the foreign key.
Can it be changed from Access or is entirely on how you design your tables?
Thanks,
Bee
Help With Relationships Plz
Aug 18, 2006Hi !!
I am doing a school project on a school database. In my DB I have a table with all students records (eg. Student ID, Name, Surname, DOB, class etc) and I have another table where the student fees payment slips will be printed.
The fee table will only have the ID, name, surname and class fields and fee will be input for every student for every term.
I want the fee table to get all the info except the fee from the first table automatically when I give a student ID.
I have created a one to many with only Student ID fields linked, NOTHING.
Then I tried to link many fields on each side (eg Name to Name, Surname to Surname etc) but I got a msg "No unique index found for the referenced field in the primary table"
Plz help me !!
Thanks
No Relationships!
Oct 10, 2006would having a db with several tables and NOT setting any relationships up in the relationships window affect the db in any way?
i have such a db, which originally had all the relationships set etc. however, as i encountered errors and had to replace tables and delete entries, i must have at some point deleted all the relationships.
i just relaised that the db now has no relatioships set up (all the primary keys are still in the tables) so i'm wondering if this matters?
cheers
Jose
Relationships
Nov 9, 2006I'm trying to define relationships between a few tables in Access 2003 and It's only doing a one to one. How do I get it to do a one to many?
View 5 Replies View RelatedRelationships
Nov 23, 2006Please someone help! This is very urgent!
I am designing a database for a clinic
basically,
One Form is a Demographic Form, which was been designed. Each person was given an identifier code.
However, they all did 3 questionnaires. Each one of them has their ID code on it.
Is it possible for me to make a query so that when I search ID code, it actually brings up all the information on every questionnaire?
Relationships
Jan 1, 2007If I have a table "tblContinents" with just two fields ContinentID and Continent and I have another table called "tblCountries" with three fields CountryID, Country and Continent.
After reading several articles, i decided not to make the Continent field in the tblCountries as a lookup field but rather I will do that at form level via a combo box. In such situation, since i am not creating a lookup field at table level, do you think that it is necessary to create the relationship between the two tables in the "relationship window", or shall i just leave it as it is, and if the need arises i will create such "relationships" in queries?
Thanks very much.
Help With My Db Please? - Relationships
Jun 19, 2007hi, im new here and i dont really know much about databases with access. im currently doing an assignment at school requiring me to do a entity relationship diagram for all my tables, which i do not know how to do. Can anyone please assist me asap? thank you very much in advance
below is the link where i uploaded it because it was too big to be uploaded onto this post. :)
http://www.megaupload.com/?d=E6AU28CL
Relationships
Jul 4, 2007In my Db I have TblQuotes & TblQuoteItems
I want the one-to-many relationship to be based on my own generated quote reference rather than the PK/FK.
My quote reference looks something like this IN123/10
Does the character '/' cause a problem with creating the relationship as I get the error message "No unique index found" even though I know that they exist?
Thanks
Where Are My Relationships?
Aug 10, 2007When I view the relationships window, not one of the relationship connector lines (whatever the technical term is) do not show up! I click view all relationships, view direct relationships. I restarted my computer, restarted Access. Nothing!
I know I have relationships because when I am table design view I try to change something that I know has a relationship and it doesn't let me because it says I have a relationship.
What is going on!?:mad:
Help With Relationships
Jan 31, 2008Hello..
Here is what I have. The database is made up of three tables (see below and see attachment). I have it set as one event to many ethnicity's and many personnel with the event ID as the PK in all three. Did I do this correctly? I thought I did and I had a form with each one of these running correclty but now it is not working.
tblEvent
EventID(PK)
tbleventEthnicity
EventID (FK)
tblPersonnel
EventID(FK)
See attachment for the other fields.
Can someone just check to see if I set this up correctly or what I should do differently?
Thanks
Relationships, 2 In One?
Nov 2, 2004I have already made a relationship 1 to many, i want to add another relationship. I put the name of the feilds i want to join up in the drop down box. I put "enforce in..." and "Casgade update related fields" on and join type 2. Then when i press ok, it comes up with "file title " to "film title" and comes up with "no unique index found with the referenced field of the primarytable".
Does anyone know what this meens?
Relationships
Nov 22, 2004I am writing a access database for work(fire dept). It is a database for the vehicle checks. Most of the fields are Boolean fields we just need to know if the inventory is on the unit. I am kind of new to this. My tables so far are as follows:
TCardiacMonitors
TFirstInBags
TGlucometerKits
TIntubationKits
TUnitNumbers
TCompartmentNumberOne
thru
TCompartmentNumberEight
TSealNumbers
TEngineChecks
all list the inventory that is supposed to be in that table
Everything is dependant on the Vehicle it is on so my question is Do I relate everything to a look up field in TUnitNumbers(intUnitNumberID) or is there a better way to do this? I need to relate everything to the date it was checked
Thank you for your help Bill
Relationships
Jan 7, 2005OK. I'm not real sure how close I am on this. I have a feeling I'm not too close so I was hoping I could get some help. I have a database with 11 tables. These can be seen in the screen capture attached. For the 'Action Taken' table there are 10 fields for 'Departments Involved', 15 fields for 'Memo Sent To', and 15 fields for 'Job Title'. The 'Departments' table is linked to 'Departments Involved1', the 'Memo Sent To' table is linked to 'Memo Sent To1', and the 'Job Title' table is linked to 'Job Title1'. All the other tables are showing the fields that are in that table. When I enter a record and try to go to a new record it gives me the following error message.
"You cannot add or change a record because a related record is required
in table "Background Information'."
I'm not sure what to do about this. I'm sure there is a simple solution but I don't know what it is. I am pretty new to Access (this is my third week on the job) and a little confused on some things. I would appreciate any help you could provide.
Relationships
Feb 21, 2005Hello. I'm new to databases and anything I know I've read up on. I know that relationships are essential so I'm trying to sort a database in ms access that I have inherited at work. It contains health service staff and their record of training - various training that they do regularly. I've managed to put the data in 1nf but am having difficulty after that.
A couple of questions:
If I give each staff member a unique id do I need to link their job title to it? Job titles chang quite a lot.
Other information about courses, dates of training, trainers etc. - should they be linked to staff name via separate tables?
I'm using a spreadsheet example from microsoft that shows me how to go from raw data to 3nf but I'm not sure how to split the information, so if you have any suggestions or know any good guides I'd be grateful for your help.
Thanks.
Help With Relationships
Feb 23, 2005I have 2 tables. tblcontact and tblboat.
tblcontact
CustomerID-PK
Customer
Contact
tblboat
CustomerID-FK
boat
Serial
i have made a form to enter the data, all works ok. I built a form to search by customer and it works fine. I can not find a way to build a form to search by boat name or serial. Can some one please help?
Thanks in advance!
Tim