Primary key problem

[chieflx]

Hi everybody,

I am fairly new to Access and I wonder if anyone can possible help with what I think might be a strange problem.

I have created a very simple database for various species of animals and the scientific literature that goes with each species. It consists of 2 tables and a junction table and it all seemed to be working well. I have used an Autonumber for my primary key all tables with one being called species ID and the other being refId and the junction table being called primary.

My problem is that although a new new primary key is automatically created when I enter new data (on the species table) it doesn't show up on a query. It works for the first 6 entries but nothing after that.

I am not sure how to test and 'fault' find the primary key to understand why it doesn't work with the later numbers.

Any help would be greatly appreciated,

Regards
Rob

 

[rzw0wr]

I am not sure you need a junction table here.
Your relationship should be 1 to many.
Animals =Many, species = 1
Animals are part of species, correct
1 Species has many animals.

Dale

 

[Galaxiom]

Use an Outer Join in the query.

The default join is an Inner Join which only shows results where matching records are in all tables.

Right click on the join in the query designer and change the Join Type.

 

[CJ_London]

and the junction table being called primary

Primary is a reserved word and its use for a table or field name can have unexpected results. I recommend you change this name to something else

Here is a link to Access reserved words

http://office.microsoft.com/en-gb/a...7-reserved-words-and-symbols-HA010030643.aspx

 

[chieflx]

Hi All,

Sorry I didn't make myself very clear, rzw0wr the first table is a species list which includes several aspects of behaviour and the second table is a list of scientific papers and their authors that give evidence for the behaviour. Some of the papers cover more than one species and some of the authors have written separate papers for several different species. From the little I understand I thought this meant I had a many to many relationship so needed a junction table to assign authors to species.

Galaxiom, I'm sorry but I don't know what you mean by outer and inner joins

CJ London, again sorry I didn't make myself clear, the table isn't called primary just the Primary key field. the table is called junction.

the species table has the following fields

SpeciesID (the primary Key)
Class
Scientific Name
Common Name
Population (whether the behaviour is at a population or individual level)
Left or Right (whether the animal is left or right handed)
Sex Bias
Age Bias
Notes

The Author table has the following fields

RefID (primary key)
Author
Date
Title
Journal
Volume Number
Issue Number
Page Number

and the Junction table has

Primary (primary key)
SpeciesID
RefID


I think that is all I need to do at this stage and as I say it works for the first 6 entries but then doesn't find anything in either query wizard or query design.


Regards
Rob

 

[CJ_London]

Primary is still a reserved word and you are using it for the field name.

According to your table descriptions, you have a number of other reserved words - Date, Left and Right (appreciate the latter two are one field name, but I have come across it causing problems)

Having said that, your table structure looks OK so it is may be down to data - have you checked that the Junction table is being populated?

 

[chieflx]

CJ London,

Thank you for the reply, I'll rename the fields and try that.

Just to clarify a little about what I am trying to do. I am attempting to create a database that shows whether a particular species is left or right 'handed' and what may affect that Behavioural aspect i.e. is it sex based (it is in cats) or possible age based (Ring-tailed Lemurs) and link the species to academic papers.

If I don't have a paper for a particular species (which means the junction table is not used) will the species still show up in a query? there are some species which I have observed directly and so can confidently add them to the species table but can not find academic papers to support the inclusion.

Once again thank you for your help and patience,

Regards
Rob

 

[CJ_London]

If I don't have a paper for a particular species (which means the junction table is not used) will the species still show up in a query?

To do this you need to have a left join between the species table and the junction table.

A left join means 'include everything from species and only those records from junction where there is a match'.

To make a left join in the query designer, make sure the line is drawn from the species table to the junction table then double click on the line and select option 2 which equates to the above para

 

[jdraw]

It's been a long time since I studied biology, but you are measuring/recording results of an individual. I can see how you could do some analysis and make some comments regarding the population you have recorded, but it seems a bit of a leap to make a statement on the species( I guess with some degree of confidence would make it OK).

You may need an individual table with a relationship to Species.

Perhaps if you gave us a brief scenario with some of your data...

Individual X, a cat is left handed. Articles about left-handed cats is noted have been written by John CatLover in CatMagazine vol 61 Sept 2010 ....

it may help readers understand/clarify your table structure.

 

[chieflx]

CJ London, thanks again I'll give that a go and see how it works.

Jdraw, Many species show some degree of 'hand preference' which is commonly known as lateralization. I am attempting to compile a simple database of all the ones I know about and to list the academic papers that support or refute the lateralization.

For example the domestic cat has be well studied and documented, so my species table contains information about cats (Felis silvestris). There is evidence that their lateralization is sex based i.e. males have a right preference and females left but there is no apparent age bias.

I have 2 papers that explore these issues and they are in the author table. By using a junction table the correct papers can link to the appropriate species.

I do need to do a bit more work and try to understand the query function a lot better because at the moment I get the species appearing several times (a different line for each paper) what I would really like is for the species to appear once and all the associated papers to appear alongside.

If you would like to see the database I am happy to send a copy to you (if I can work out how)

hope this helps,

Regards
Rob

 

[way2bord]

I do need to do a bit more work and try to understand the query function a lot better because at the moment I get the species appearing several times (a different line for each paper) what I would really like is for the species to appear once and all the associated papers to appear alongside.

This is a good thing. It's proper for normalization.

Once you are sure you're query lists all appropriate data, normalized - it's very easy to go to the query - change the view to "Pivot Table" then just Drop the PaperID in the columns section. FYI: If you have 50 papers to a single species, you're going to have a very ugly (and slow) pivot table when you do this...

 

[chieflx]

way2bord, thanks for the advice I've not even explored Pivot tables yet so that'll be a fun thing to do I hope. Most animals only have between 5 - 10 papers associated with them so hopefully it won't be too bad