Hope someone can help.
I have a database with 15 tables which revolve around one core table [tbItemNo]. There is a one/many relationship between it and tbAccession in one direction and several others in the opposite direction, eg tbEvent.
tbAccession 1-M tbItem 1-M tbEvent
The primary key in tbItem is an automatic number, but this seems to be preventing me from sorting several of my reports from coming out in the order I want - it sorts by the key field rather than dates (text fields) or alphabetically on other fields. I've tried inumerable combinations in sorting and grouping, but there's always something wrong.
tbItem has a foreign key field to link to tbAccession (AccessionNo) and a field called ItemNo. It is the combination of these two that identify every item in my collection (an archive), and in combination they are unique. Might it help if I made these two a multiple field primary key? Could I do away with the auto number field altogether and link my other tables (eg tbEvent) to the multiple field key? Could this possibly help with my reports?
Another option might be to combine the data from AccessionNo and ItemNo into one field in tbItem, but I'd still need the foreign key field, and that seems to break the normalisation rules.
I'd be grateful for your advice, Robyn
I have a database with 15 tables which revolve around one core table [tbItemNo]. There is a one/many relationship between it and tbAccession in one direction and several others in the opposite direction, eg tbEvent.
tbAccession 1-M tbItem 1-M tbEvent
The primary key in tbItem is an automatic number, but this seems to be preventing me from sorting several of my reports from coming out in the order I want - it sorts by the key field rather than dates (text fields) or alphabetically on other fields. I've tried inumerable combinations in sorting and grouping, but there's always something wrong.
tbItem has a foreign key field to link to tbAccession (AccessionNo) and a field called ItemNo. It is the combination of these two that identify every item in my collection (an archive), and in combination they are unique. Might it help if I made these two a multiple field primary key? Could I do away with the auto number field altogether and link my other tables (eg tbEvent) to the multiple field key? Could this possibly help with my reports?
Another option might be to combine the data from AccessionNo and ItemNo into one field in tbItem, but I'd still need the foreign key field, and that seems to break the normalisation rules.
I'd be grateful for your advice, Robyn