How To Create Family Relationships?

Aug 23, 2005

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.

View Replies

Relationships? Anyway To Create These Using SQL?

Dec 20, 2005

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

View 2 Replies View Related

Adding Payments Due For Family Members In Query.

Sep 28, 2005

Hello,

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;

Any assistance would be greatly appreciated.

Regards,
Chris

View 4 Replies View Related

How To Create Multiple Table Relationships

Mar 30, 2012

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.

View 1 Replies View Related

Modules & VBA :: Create Sequence From Table Of Relationships

Jan 14, 2015

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.

View 12 Replies View Related

Create Relationships Within Document Distribution List

Sep 9, 2011

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.

View 1 Replies View Related

Forms :: DCount / 2x Criteria - Counting Family Members Under 16 Years

Jan 13, 2014

Using MS Access 2000

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.....

=DCount("MemberID","tblHHMembers","CustID = [CustID]")+1

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!

View 2 Replies View Related

Newbie Trying To Create A Dependency Diagram And Determine Relationships

Feb 9, 2006

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

View 2 Replies View Related

Tables :: Creating PK With Calculated Autonumber To Create Relationships

Sep 24, 2012

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

View 1 Replies View Related

Forms :: How To Create A Form That Can Edit Or Create A Record

May 13, 2014

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 Related

General :: Create A Button That Will Create A New Empty Backend

Oct 31, 2013

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.

View 7 Replies View Related

Help With Relationships

Jul 18, 2005

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

View 5 Replies View Related

Many-to-Many Relationships

Aug 2, 2005

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.

View 10 Replies View Related

Relationships - One-to-many?

Aug 16, 2005

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

View 4 Replies View Related

One-to-Many Relationships

Mar 7, 2006

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.

View 6 Replies View Related

Relationships

Mar 16, 2006

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.

View 4 Replies View Related

Relationships

Aug 7, 2006

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

View 5 Replies View Related

Help With Relationships Plz

Aug 18, 2006

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

View 1 Replies View Related

No Relationships!

Oct 10, 2006

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

View 3 Replies View Related

Relationships

Nov 9, 2006

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 Related

Relationships

Nov 23, 2006

Please 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?

View 8 Replies View Related

Relationships

Jan 1, 2007

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.

View 3 Replies View Related

Help With My Db Please? - Relationships

Jun 19, 2007

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

View 4 Replies View Related

Relationships

Jul 4, 2007

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

View 6 Replies View Related

Where Are My Relationships?

Aug 10, 2007

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:

View 4 Replies View Related

Help With Relationships

Jan 31, 2008

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

View 14 Replies View Related