I've started working on a database some weeks ago, and some of it works great, yet some doesn't...
The database is meant to gather data on archaeological objects from excavations - specifically objects made of bone, antler, horn etc. It already has a lot of tables... maybe too many? And with the many tables come many questions. I'll start with one or two and we'll see how it goes.
At the end of the post (in the code box) I added a link to an image of the relationships view. Just add the http thing as prefix
So the "central" table in the database is:
OBJECT_BASIC_DATA
ID - (Autonumber, primary key)
ObjectID - (number, another primary key) = this is a number given to an item by me for the research. I'm not sure I need it, since I have the ID (autonumber) field, but with the mistakes and deletes I did over the past weeks the autonumber is already at 100+, while the table has maybe 8 rows. I also use this field in 1-to-1 relationships to other tables (see below).
other fields in this table are connected (as child fields) to other parent tables (Sites table; References table; Archeo_context table; Archit_context table) and they seem to work perfectly.
Now, OBJECT_BASIC_DATA (OBD for short...) had many many fields and it was getting too big, with many of the fields empty, so I decided to split it into many smaller tables (OBJECT_MEASURMENTS; OBJECT_TYPOLOGY; OBJECT_RAW_MATERIAL; OBJECT_TECHNOLOGY; OBJECT_CHRONOLOGY; OBJECT_TAPHONOMY; OBJECT_PICTURES; OBJECT_PARALLELS). I tried to make 1 to 1 relationships between these tables and OBJECT_BASIC_DATA, since every record in OBD could only have 1 record in these tables and vise-versa. I used the ObjectID field, which all of them have, as the relationship field, although it is a primary key only in OBD (where it primary keying with ID [autonumber]). I'm not sure I made the right decision splitting the big table into many small ones (about 5-10 fields in each). I'm also not sure about using both ID and ObjectID as primary keys in OBJECT_BASIC_DATA, but ObjectID is the only sure connection I have between OBD and the other OBJECT_TABLES above.
Next issue... I've created a form to enter new records. It looks really nice and organized and colorful. But it doesn't work. How do I make Access understand that when I type a number in the OBD.ObjectID control,it needs to put that value in all the ObjectID fields of the other tables related to it? I tried screaming at it, but it didn't work.
I'm not sure I can post pictures, so I add a link to an image of the tables relationships. Its a mess, but its as clear as I could make it. Just add http at the prefix.
Hope to hear some opinions and suggestions
Sorry for the extremely long post
Ariel
The database is meant to gather data on archaeological objects from excavations - specifically objects made of bone, antler, horn etc. It already has a lot of tables... maybe too many? And with the many tables come many questions. I'll start with one or two and we'll see how it goes.
At the end of the post (in the code box) I added a link to an image of the relationships view. Just add the http thing as prefix
So the "central" table in the database is:
OBJECT_BASIC_DATA
ID - (Autonumber, primary key)
ObjectID - (number, another primary key) = this is a number given to an item by me for the research. I'm not sure I need it, since I have the ID (autonumber) field, but with the mistakes and deletes I did over the past weeks the autonumber is already at 100+, while the table has maybe 8 rows. I also use this field in 1-to-1 relationships to other tables (see below).
other fields in this table are connected (as child fields) to other parent tables (Sites table; References table; Archeo_context table; Archit_context table) and they seem to work perfectly.
Now, OBJECT_BASIC_DATA (OBD for short...) had many many fields and it was getting too big, with many of the fields empty, so I decided to split it into many smaller tables (OBJECT_MEASURMENTS; OBJECT_TYPOLOGY; OBJECT_RAW_MATERIAL; OBJECT_TECHNOLOGY; OBJECT_CHRONOLOGY; OBJECT_TAPHONOMY; OBJECT_PICTURES; OBJECT_PARALLELS). I tried to make 1 to 1 relationships between these tables and OBJECT_BASIC_DATA, since every record in OBD could only have 1 record in these tables and vise-versa. I used the ObjectID field, which all of them have, as the relationship field, although it is a primary key only in OBD (where it primary keying with ID [autonumber]). I'm not sure I made the right decision splitting the big table into many small ones (about 5-10 fields in each). I'm also not sure about using both ID and ObjectID as primary keys in OBJECT_BASIC_DATA, but ObjectID is the only sure connection I have between OBD and the other OBJECT_TABLES above.
Next issue... I've created a form to enter new records. It looks really nice and organized and colorful. But it doesn't work. How do I make Access understand that when I type a number in the OBD.ObjectID control,it needs to put that value in all the ObjectID fields of the other tables related to it? I tried screaming at it, but it didn't work.
I'm not sure I can post pictures, so I add a link to an image of the tables relationships. Its a mess, but its as clear as I could make it. Just add http at the prefix.
Code:
prntscr.com/a48pcl
Sorry for the extremely long post
Ariel