Relationship?? (1 Viewer)

[sbooder]

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.

 

[Sergeant]

You need a primary key in your birds table.
tblBirds:
BirdID(PK)
CommonName
LatinName

tblSightings:
SightingID(PK)
BirdID(FK)
SightingDate
etc...

Make a relationship between the BirdID fields in the two tables.

The record source for your sighting form need only contain the fields from tblSIghtings.
Use the control wizard to create two combo boxes...
1. Turn on the control wizard at the top of your control toolbox.
2. Select a combo box and put it on the form.
3. In the dialog, select the top radio button (look up values in table/query).
4. Click Next and select tblBirds. Click Next.
5. Select CommonName and click the > button. Click Next. Click Next.
6. Select 'store that value in this field'. Select BirdID and click finish.

Do the same thing again but pick the LatinName in step 5.

Voile' You can pick the bird via either naming convention and the other one shows the corresponding value.

You said that you download the bird list. Make the table I described above and then use an append query to fill it from your existing bird table. This way, the PK fills in to identify each bird type.

 

[Andromeda]

Hello and welcome aboard!

 

[sbooder]

You need a primary key in your birds table.
tblBirds:
BirdID(PK)
CommonName
LatinName

tblSightings:
SightingID(PK)
BirdID(FK)
SightingDate
etc...

Make a relationship between the BirdID fields in the two tables.

The record source for your sighting form need only contain the fields from tblSIghtings.
Use the control wizard to create two combo boxes...
1. Turn on the control wizard at the top of your control toolbox.
2. Select a combo box and put it on the form.
3. In the dialog, select the top radio button (look up values in table/query).
4. Click Next and select tblBirds. Click Next.
5. Select CommonName and click the > button. Click Next. Click Next.
6. Select 'store that value in this field'. Select BirdID and click finish.

Do the same thing again but pick the LatinName in step 5.

Voile' You can pick the bird via either naming convention and the other one shows the corresponding value.

You said that you download the bird list. Make the table I described above and then use an append query to fill it from your existing bird table. This way, the PK fills in to identify each bird type.

Hi Sergeant,

Thanks for the instructions, but I can not get it to work. I may be being a bit thick here but what ever I do it will not auto fill the Latin name and visa versa. In one instance I thought I had it but when I viewed the Form, it showed the first bird in both combo boxes Common and Latin, but when I used the drop down to choose another bird it would not let me.

Also, Do you mean in the table I have called BBList (which is the table that only contains two fields, the first field is the list of British birds Common Names and the second Field is the Latin Names) that I should make the Common Name field the PK or do you mean that I should create a new field before the other two called BirdID and make that the PK?

If you mean the latter, then it will not let me unless I make the field an Auto Number field!

And last… what does FK stand for? I told you I was new to Access... and a little thick!

 

[Sergeant]

You need a primary key. You can't add one once the table is populated, so...
1. Rename your BBList table to BBList2
2. Make a new table (design view) with three fields (BirdID, LatinName, CommonName)...note the field size property in the grid at the bottom...default is 50...if names may go over this many characters, adjust accordingly now.
3. Right click the BirdID field and select 'Primary Key'...for Data Type, select Autonumber.
4. Close and save the table as BBList
5. Make a new query...select the BBList2 table and click add/close.
6. In the toolbar there is a drop-down for query type...select 'Append Query'.
7. In the popup, select BBList and press OK.
8. From the BBList2 table at the top of the query grid, double-click LatinName and CommonName (or whatever you're calling those fields).
9. Down in the grid, use the 'Append To' dropdown to select the appropriate destination field for each of the two fields on your query.
10. Click the exclamation mark up in the toolbar. A popup will advise you that you are about to append records, allow this.

Now you have all your bird records in a new table and each has an autonumber primary key.

You refer to birds behind the scenes by BirdID...this allows you to access the common and latin names for any BirdID.

Feel free to post more if you run into problems.

 

[Sergeant]

BTW, FK is Foreign Key...in this case it is BirdID (foreign key on tblBirds.BirdID)

 

[sbooder]

Blank Fields!!!

Hi Sergeant
Thanks for the info. It is now working, the error on my part I was making the PK in my tblNewSightings BirdID, the same as in tblBBList, this caused the dropdown/combo boxes not to work. I have now made the PK in tblNewSightings RecordID, and it now works fine.

There is a but though. All the entries I enter in frmNewSighting go where they should in tblNewSightings, but not the CommonName or Species (I have changed Latin to Species) they just leave the fields blank in tblNewSightings, what is going on there??

I have created a new Table called ObserverList, so I can add new observers, and created a combo box in the NewSightings form which show all the observers from tblOberverList and this works, it enters the record in the correct field in tblNewSightings, so why not the bird names combo boxes??

Any further help would be most appreciated.

Many thanks for the advice so far.

Simon.

 

[Sergeant]

The idea of a relational database (well, one idea of it) is to have a numbered list of things in one table, and in another table...just store the number (not the thing...just it's corresponding record number PK). When you use the control wizard to build a combobox, that is what it does for you...even if you don't select the PK field from the lookup table.
It might help if you list your tables and fields, or take a screen shot of your relationship window and attach that.

 

[sbooder]

Screenshots

Hi Sergeant,
attached is a pdf with some screenshots of the db including the requested Relationship window.