Access :: What Relationship To Use

Relationship??

Jan 14, 2006

Hi All,
I am new to access and this forum, so hello all.

I am trying to build a database to keep records of my bird sightings. As you might have surmised; I am a birder.

I have come to a holt on creating a complex relationship between the Bird Entry From and the table that contains a list of British birds.

In Bird Entry From two of the fields are for entry of the bird name, one field being the Common Name and the other being the Latin Name. They are both drop down lists that contain all the birds from the table, this being done by import data. The table that holds the bird list has two fields One is called Common Name and the other is called Latin Name, this being the same as the filed names in question in the Bird Entry From

This is where I am coming unstuck. What I would like to happen is that when I enter a bird in the Common Name field (either by typing until the bird is auto entered from the drop down or using the drop down to find the bird) it automatically enters the Latin name in the Latin Name field and visa-versa, but I have become very frustrated with trying to do this.

Am I wasting my time? Or is it possible? And if so would anyone be kind enough to tell me how?

Many thanks,

Simon.

View 8 Replies View Related

Relationship Help Need

Feb 28, 2007

Hi i need help with one of my relationships

I need help with linking Member 1: N Reservations

the entites are

Video (Vcode, VTitle, Date Made, Director, Genre)
Copy (CopyNum, Vcode, Due Date)
Loan (LoanMemNum, CopyNum, Ldate, In, Out)
Member (MemNum, MName, MAddress)
Reservation (VCode, resMemNum, Resdate, CollectDate. Collected Not Collected)

The keys are underlined

Cheers

View 1 Replies View Related

Relationship

May 16, 2007

Hi there!

I've three tables in my database, tblInfo, tblSavedInfo & tblCountry.
for tblInfo I have this fields - Ref: Cost: RefCountry:
for tblSavedInfo I have this fields - Ref: Cost: Country:
for tblcountry I have this fields - RefCountry: Country:

My problem is when I save from tblinfo to tblSavedInfo I want it from RefCountry on tblInfo check wich country is on tblCountry and save on tblSavedRef the country instead the refCountry.

As tblInfo has about 200.000 rows I need something to make it quick. Is there anyone that can help me please.

Thanks

View 2 Replies View Related

Many-to-many-to-many Relationship

Jul 25, 2005

Any advice? I've tried some things and no success with linkage

Rooms Table
RoomID
Room No
Type
Desc

Project Table
Project ID
Project
Desc
Faculty Assignment
Project Grant No
Sponsor
Project End Date

Student Table
Student ID
Students

Many rooms with many projects, many of those projects in many of those room, with many students working on those projects. Many rooms, many projects, many students. Argh...
I've already tried two junctions between rooms and projects // and projects and rooms

What is making this so difficult?

View 3 Replies View Related

Relationship Help Please

Aug 14, 2005

As you all know Im not a expert on databases but work in a volunteer basis in a small community area where we have no money for people to do anything for us.
Im proud because I did the other database and its up and working but this one has got me beat.
Im setting up one for the volunteers, which has computer knowledge, any basic training they need, when they are available and what tasks have been set for them.
I need to have a one form which has just their personal details but to be able to put in data on the other forms and it will link back to the volunteers name.
Ive attached what Ive done and Im sure your all going to laugh - but any help would be appreciated

Barnesy

View 5 Replies View Related

Many To One Relationship (I Think...)

Aug 19, 2005

Trying to set up a fairly simple DB. Here is the layout:

tblRoom(roomID, roomNumber, rackOne, rackTwo, rackThree)
tblStudent(studentID, name)

Each student will be assigned to one, and only one, rack. There will be 3 students to one room. One student can only have one room but a room can have many students... I store studentID in the rackOne, Two and Three fields. My problem is when I try and querry for the information so as to display actual names rather than ID's. I'm not sure If my querry is the problem or if I've set the tables up incorrectly. Any insight is much appreciated.

View 2 Replies View Related

Relationship Help

Aug 30, 2005

Hello,

I'm having some problems setting this up right.

I have 3 databases.

Volunteers

FIRST
LAST NAME both primary

Movie Data

MOVIE
DATEboth primary

Attendance

MOVIE
DATE

USHER 1 First
Usher 1 LAST

View 1 Replies View Related

Relationship Help

Aug 30, 2005

Hello,

I'm having some problems setting this up right.

I have 3 databases.

Volunteers

FIRST
LAST NAME both primary

Movie Data

MOVIE
DATEboth primary

Attendance

MOVIE
DATE

USHER 1 First
Usher 1 LAST

USHER 2 FIRST
USHER 2 LAST

AUTOKEY PRIMARY

I have a one to many relationship between the movie and date between the attendance and movie data table. I have tried linking the name to the usher fields but I"m not getting the result I want. I want to be able to open up the volunteer table and have it show me the movies they have worked on. The movie data table will list who worked it, but the volunteer tables are not.

Any ideas?

Thanks,
Cody

View 11 Replies View Related

Relationship Help

Sep 11, 2005

I have to tbls which have indeterminate relationships and I'm told that ther is no unique index found why is this?

i am using emplyID on both tblCommission and tblSales both are number type.

I might not need the commission table as I'm using this is calculate commission as said im my previous post

View 1 Replies View Related

1 Or 0 Relationship

Nov 4, 2005

at one point in my database I have two tables and for everyone record in one table I may have either 1 or 0 records in the other. How do I express this in access design? Since it's an optional 1 to 1 relationship I guess?

View 1 Replies View Related

Relationship Help!

Mar 3, 2006

I was wondering if anyone could help me with a query. Basically I am doing (or trying to do) a small database to track childrens progress in my mums primary school. I'm doing this for free as the budget is rubbish for this type of thing and i dont have enough knowledge to ask for payment anyway! I want to get it right from the start and I think if I get the relationships right initially then I can complete the rest of it on my own. I did this at university (normalisation etc) but ive not used it since i left in 2002 and so have practically forgotten everything i ever did.

Basically, the child comes into the school and is predicted a level in maths, reading and writing. Then in the october, feb and july of each of the 2 years they are there, they are given actual assessment levels. They are also given a prediction level at the beginning of year 2.

So far I have one table with student no, surname, first name, gender, ethnicity, year group and SEN(special educational needs) - with the primary key being student no.

This is where I get stuck - do I go for a maths, reading and writing tables and split it that way - or on an assessment basis, so prediction yr1, october yr1, feb yr 1 etc etc. Or is this completely wrong? Am sooooo confused, so any help would be greatly appreciated.

The outcome I want is to be able to query a childs progress, so for example: show children who achieved a 1a in yr1 maths, and then out of those children, who achieved a 2a in y2 maths (as this is the required shift in progress set by government). I really hope that makes sense.

Thanks in advance
Nicola

View 3 Replies View Related

One-to-One And Many-to-Many Relationship

May 10, 2006

I'm rebuilding my database from scratch now that I've learned a bit from the forums about developing a proper database and I want to investigate whether I have the relationships set up correctly, as I seem to be using lot of one-to-one relationship, which most articles say is very rare, and whether I am formulating many to many relationship correctly. Here is a screenshot of my relationship.

If I've understood this correctly, one-on-one is desired if you have a subset of data that is applicable to the main table only occasionally and is dependent on the main table's data. Have I used that correctly?

As for many to many relationship, articles say this is formed by having two one to many relationship linked together in a table. In my screenshot, you can see that tblAdvocacyVisit is the linking table. However, I've set GuestID as a one-one relationship whereas ClientID is one to many relationship, reasoning that since I do need to relate the Guestl Logbook data to Advocacy table for some queries I will be performing later, this will save me some work. I'm afraid that this may compromise RI or something like that. I can simply add a PK autonumber to tblAdvocacy to revert the GuestID as a one-many relationship, but can't see how that would not create more work in queries or so...

Oh, are the lookup tables supposed to be related at all?

TIA.

Edit- Updated the relationship screenshot.

View 14 Replies View Related

Many-to-many Relationship

Jul 27, 2006

I�m building a database of protein mutants we use in the lab. A lot of analysis is done on these proteins by various people and it�s becoming a bit of a nightmare keeping track of all the information per protein. What we need is a database. I�m getting a reasonable grip on it all I think with the excellent examples I have found on the forum but there are obviously areas that I have great difficulty with. The main problem for now is that many-to-many relationships are involved and I just cannot get them to work properly.

On each protein up to 8 different types of test are performed. To keep things easy I�ll call them A1 (for Analysis1), A2, A3 etc. These are done by different people from a group of 7. I�ve called them O1 (Operator1), O2, O3 etc. Each of the operators can do multiple analysis and the operators overlap. So e.g. O1 might be particularly good at A1, A4 and A7, O2 might be good at A2, A4 and A5, and O3 predominantly does A1, A4 and A6. What it comes down to is that each operator can in principle do each of the analysis so I have to give that option in the database.
What does work (but is not the solution as it introduces 8x duplication of data) is if I have a separate table with operators for each analysis. The same tblOperators is linked 8 times. There must be a way to link each of the analyis to only 1 tblOperators, probably with the use of a linking but I just cannot work it out from the examples I've found here.

Can someone help me on my way please?

Thanks,
Walter

View 7 Replies View Related

Relationship Help

Nov 20, 2006

New guy on the block here.

I am having trouble associating the relationships within a database to accomplish what I need. I don't know if I am over thinking or I just plain old don't get it. So, I ask if someone can draft this database up rather easily and post it for me or send me in the right direction to learn quickly.

This is what I am looking for: A table with people�s first name, last name, title, job position. That is it. I can do that and have done that. No problems.

Another table that has amplifying data on that person such as: date of birth, SSN, date employed, comments, etc.

What I envision is, creating a form to add the names, title, and job position of the people to the table.

Then I want to create another form that puts the other data associated with that person. This other form should have the first and last name from the first record to "view" only so the correct data can be added to the correct person.

That is it; I hope someone can help me out. I have been working on this for sometime and just can't seem to get it.

Thanks, all, E-dub

View 8 Replies View Related

Many-to-one Relationship

Dec 7, 2006

Please pardon my inexperience.

I have two tables, call the tblSales and tblCustomers.

tblSales looks like this:

CustID FName LName ...
1 John Doe
2 Jane Doe
1 John Doe
1 John Doe
2 Jane Doe
2 Jane Doe

tblCustomers looks like this:

CustID FName LName
1 John Doe
2 Jane Doe

with CustID being the primary field.

Suppose that no data exists in the FName and LName columns of tblSales. How could I automatically populate those fields with data based on the information in tblCustomers? I would like to be able to simply type in the CustID on tblSales and have the other fields populate automatically.

I'm sure Access can do this, but how do I set it up!?

Thanks!

View 2 Replies View Related

Relationship Help

Mar 15, 2007

I have

ProductTBL
ProductID
Product
BuyerID
Buyer2ID

BuyerTBL
BuyerID
Name

Each product can have more then 1 buyer.
In my table when I need to assign buyerID and Buyer2ID to the same person in order for the subtable to show the buyer is associated with it.

I have also tried to put 2 buyerTBLS on the relation ship screan and link one to BuyerID and one to Buyer2ID

I have created another table that has
LinkedTBL
LinkID
ProductID
BuyerID

and done a Many-Many relationship and this has worked however I was wondering if it may be done without this table.

I would like to just bring up the productID in a form and assign 2 buyers to it.

Thanks for your time,

Derek L

View 2 Replies View Related

Relationship Help

Apr 6, 2007

I am doing DVD Collection. Let me visualise my table first.

I had a table call "DVD Titles" which include DvdID, Dvd Titles, etc.....
a second table call "Dvd Copies" which include DvdID, CopyID, etc.....

first table primary key are both DvdID and Dvd Titles, Second table PK is DvdID and CopyID.

First table DvdID i have 1,2,3,4,5,etc...... (It's make of Autonumber)
Second Table DvdID i have 1,1,1,2,2,3,3,etc (Manual Key in, Long integer)

Second Table DvdID depend on the numbers of copies i own for the paticular DVD.

Now the problem is that i can't create a Enforce Referential Integrity(one-to-many) relationship between both DvdID. it prompt me "No Unique index found for the referenced field of the primary table."

Any Help?

View 9 Replies View Related

ERD And Relationship

Oct 2, 2007

Hi, I am new to Microsoft Access and am having trouble with creating a data model for a specific problem. I have imported an Excel spreadsheet involving music and want to create tables and let Access assign primary keys. The spreadsheet has the following columns:
Song, Artist, Album, Duration, Genre, MyRating (from 1-5), PlayCount, Location (stored in 3 locations), Tempo, and Mood.
I have to normalize the tables to eliminate repeating information.

After setting up the relation, I have to create queries; for example:
Second-most popular artist based on total quantity of songs
Favorite genre based on MyRating
Top Songs based on PlayCount

Can someone help me with this complex questions?
Thank you,
Pete

1.Develop a data model (an entity relationship diagram or a relational model) for the above business problem. Import the provided Excel spreadsheet. Create the tables and let Access assign primary keys (see MIS 301 � Access Skills for instructions on importing Excel spreadsheet and creating tables using the Table Analyzer Wizard). Paste a screenshot of the relationship view of your database in the answer sheet.

View 1 Replies View Related

One To Three Relationship?

Dec 12, 2007

Hi all,

can you create a fixed one to three relationship between tables in Access 2007?

So far I have only seen one to many relationships...

View 5 Replies View Related

Help With One To Many Relationship

Dec 14, 2007

Hi,

I am trying to create a one to many relationship but all I get is an option for a "Indeterminate" relationship?

Can someone tell me why?

Thanks.

Fen How

View 3 Replies View Related

Relationship Help

Jan 24, 2008

Hi

I am wanting to create a query but i need my relationships working.

This is what i have so far but i think it might be wrong

http://i82.photobucket.com/albums/j261/peckitt99/Relationships.jpg

could anyone help me if it is wrong and point me in the right direction.

much appreciated for all your help

Thanks

View 1 Replies View Related

Many To Many Relationship

Feb 15, 2008

I am aware of these but never had to make one, what I have done at the
moment is create two tables and a third junction table. I have created the one to many relationships to the junction table.

I have created a form and a subform to view the data.

What happens is I get a list of products in my sub form but when I enter data against them and move to the next record on the main form ths sub form data appears the same for each main record. I expected to get a list of teh same items but without any data against them.

Any ideas what I am doing wrong?

View 8 Replies View Related

1-to-1 Relationship

Apr 22, 2008

i have a parent table and a few children table where I want every entry in the parent to have to have an entry in one of the child tables. Is there anyway to force this? The only way I know of is to create a query that finds any that are missing and from the child tables and add them, but if it could somehow 'automatically' do that when a new entry in the parent is added that would be best.

View 2 Replies View Related

1:n Relationship Changes To M:n

Sep 20, 2007

Dear All,

I have the following problem and I would be happy if somebody could help me with that: So far I had two tables. One contained customer orders, the other a description of each article. The connection between both tables was through a unique article name, which was the primary key of the ArticleDescription table. So far this worked.

But now it becomes more complicated, as there is no longer a n:1-relationship between the two tables Orders and ArticleDescription as each article now can have different specifications, which however to no relate to the items in the ArticleDescription table. That is, I have to include a table between those two tables.

Old situation:

tbOrders - Old
Order# // Article# // Date // Amount etc.
1 //A //09/18/07// 5
2 //B //09/19/07 // 3
3 //A //09/20/07 // 2

tbDescriptionArticle
Article# // Description etc.
A //Text1
B //Text2

An SQL query to combine information of both could for example look like the following...

Select tbOrders.Order#, tbOrders.Article#, tbDescriptionArticle.Description
From tbOrders Inner Join tbDescriptions On tbOrders.Article# = tbDescriptionArticle.Article#;

...in order to get this output:

Order# // Article# // Description
1 //A //Text1
2 //B //Text2
3 //A //Text1

This changes now to:

tbOrders - New
Order# // Article#New // Date // Amount etc.
1 //A1 //09/18/07 // 5
2 //B1 //09/19/07 // 3
3 //A2 //09/20/07 // 2

...and I have additionally a table, which includes the relationships

tbRelationships
Article#New // Article#
A1 //A
A2 //A
B1 //B

...while tbDescriptionArticles remains the same.

How does the SQL query change in order to achieve the same output as with the first query? :confused:

Thank you very much.
Diana

P.S. Does anybody know how to display tables in this forum more efficiently?

View 4 Replies View Related

Relationship Help

Jan 25, 2006

Im doing a hotel booking system and need some help with my relationships.
I have 3 tables

Bookings - First name, last name, date of arrival, length of stay, room, booking reference, customer ID

Rooms - room, type, facilities, floor, price, customer ID

Customers - first name, last name, street address, city, county, post code, telephone number, customer id, room.

Any reccomendations for what the relationships should be if im doing a booking form would be great.

View 5 Replies View Related