Hi,
I have two tables i would like to create a relationship with but the two fields that i want to join are not exactly the same e.g
Table 1
0000111111
Table 2
111111 ABC
The key piece of information that i would like to link together is the 111111, in SQL i would just type "WHERE Code LIKE '*111111*'" or something like that.
Is there anyway i can create a relationship based on the data above?
Many thanks
Tim
I am building a DB for a nonprofit, and trying to figure out how to create a family relationship for contacts. Basically, it will be for mailing- so they receive only one, and to record donations.
At times, John and Mary Smith will donate together, and sometimes John Smith will donate in conjuction with an organization.
So, I would like the family relationship to be with John and Mary Smith, but also would like to be able to track that John Smith, but not Mary, is a member of that particular organization.
I would like the donations that John & Mary make together to show up in both their records, but the donations that John makes with an organization to show only in his record and the organization record.
Is this possible to create in access? I'm a bit stumped at this point.
HR.zip. I'm trying to create database for HR and all the tables has to be linked with on table "PFEmployees (one employee has to be linked with all other tables "beneficiary,disciplinary,caruser,otherinfo etc...). Is it possible to do it.
Look into attachment relationship. I am really stuck with this.
I am writing a deposit recording database for archaeological layers. My units are recorded in one table, and then the relationships are recorded in a separate table. I have limited the recording of relationships to be either "same_as" or "below".
So in tblContexts is have say: 1,2,3,4,5,6
In tblcontextrels I have say : 2 is below 1; 3 is below 1; 4 is below 3; 1 is below 5; 6 is the same as 5
I want to view the deposits on screen in their stratigraphic sequence. So I think I need to write a piece of code to run through "tblContextrels" which will then create a hierarchy attribute. This attribute can then be used to sequence the context deposits I am looking at.
I am brand new to using access databases but I am in the midst of trying to set one up for controlled distributions of controlled documents.Essentially, I have three tables:
1) Listing of all our controlled documents and their properties (doc number, doc title, doc revision, doc type, doc status, effective date),
2) Listing of all our copyholders (name, location, phone number),
3) Document Distribution List which lists copy numbers, quantities, and medium to deliverable (paper, electronic, size, etc)
What I want to do is create relationships within the Document Distribution List (using 'add from existing fields') from the other two tables so when a request for a new controlled copy comes along, I don't have so much data redundancy.
For example, in Table 1, I have listed Document XYZ and copyholder Jane Doe from Table 2 wants to receive 1 copy of Document XYZ. I would like to have Table 3 have an added field from Table 1 where I can pull down the applicable document number, which when selected auto fills out it's corresponding details of title, revision, doct type, status and effective date in this Table 3. In addition, after I select the document Jane wants, I want to have another field added to Table 3 from Table 2 which lets me pull down Jane's name to assign her a copy number.
Hello : )
I am currently taking a database design class using Access 2003 and am looking for some help. I am trying to draw a dependency diagram before I create the tables based on the following attributes / fields in one table named Student:
StudentNumber, StudentName, StudentMajor, student's AdvisorNumber, AdvisorName, AdvisorOfficeNumber, AdvisorPhone, student's NumberCredits and student's Class (freshman, sophomore, etc.)
So far I have identified the Primary Keys as StudentNumber and AdvisorNumber and this is what I have:
Table #1 - Student = Student Number functionally determines Student Name, StudentMajor (?) NumberCredits and Class
Table #2 - Advisor = Advisor number functionally determines Advisor name, office number and phone
The problem I run into is with the the StudentMajor / Advisor / StudentNumber relationship. Since one student can have many majors and therefore many advisors since there is only one advisor per major, and each advisor has many students I am assuming it is a many-to-many relationship.
The problem I am having is determining the third table (and fourth if there will be one) and the relationship between StudentMajor / Advisor / StudentNumber.
Should StudentMajor be included as functionally dependent on StudentNumber? If it is there would be redundancy due to multiple entries in that field for each row so I guess I can't include it actually. :confused:
Wouldn't there have to be another attribute named AdvisorDept for this to work properly....that way there is a relationship between Advisor and their department they work in so I can link the student's major to the advisor :confused:
Any pointers and suggestions would be greatly appreciated
Thanks
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 am trying to create a form to enter data in a table. I would like to make it pull in info from a switchboard. If the record already exists I would like it to find it and allow me to edit the info. If the record doesn't exist I would like to be able to add a new record with the data input. What is the best way to accomplish this?
View 1 Replies View RelatedI created a database and I manage to split it into front end and backend. now I'm going to make an EXE of the front end.My question is when I open the front end, I need the database to be empty, and them create a button that will make the user select which project he wants to open (backend).I also need to create a button that will create a new empty backend and save it as a new project.
View 7 Replies View RelatedHi 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
I 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.
Hi
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
Hi 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.
Hi
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.
Hi,
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
Hi !!
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
would 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
I'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 RelatedPlease 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?
If 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.
hi, 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
In 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
When 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:
Hello..
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
I 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?
I 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