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.
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?
I have a query that lists members who have outstanding payments. The results of this query are being used to populate a winword mail merge document.
The problem with this query is that if there are multiple members from the same family who owe then I will get an entry for each of them in my query results. What I would like to do is add the amounts due so that I only have 1 entry for that family. Additionally, since it is possible that more than 1 person can have the same last name and not be related I would also like to have the query check the address field and if it's the same than add the amounts due. The following is the sql statement for the current query.
SELECT Members.FirstName, Members.LastName, Members.ParentsNames, Members.HomePhone, Members.PaymentDue, Members.Active, Members.MemberDues, Members.FamilyAmount FROM Members INNER JOIN MemberTypes ON Members.MemberTypeID = MemberTypes.MemberTypeID WHERE (((Members.PaymentDue)=Yes) AND ((Members.Active)=Yes)) ORDER BY Members.LastName;
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.
I'm building a customer management database. Part of which is a table to record details of all members of the main customers family or the household compliment.
I'm currently using a DCount to work out how many people on the table are related to my client, to work out the size of the family.....
Could I also then count the members of the household that are under 16 years old by looking at the DoB from the same household members table?
I realise that I'd need to ask access to calculate age at the same time as working out if they're under 16 and then count them if they have the same Customer ID - which might need a little more than this single function!
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
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?
I 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.
Hi 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.
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?
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?
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.
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?
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"
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?
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?
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. :)
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?
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.
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?
