Hello,
I have become the de-facto database "expert" in my office as my small amount of knowledge trumps everyone else's. I took a couple of introductory community college courses in MS access and have played around with VBA in excel and to a lesser degree in access. We have a database to collect and manage data collected from farms that we work with. The database was developed before my time and the person who designed and implemented it has since passed away. Based on my knowledge of database design, I might have designed it differently if it was up to me, but we have to work with what we have in the short to medium term. The database was designed to handle a set number of farm visits or "interviews" prior to the farm being "finished". Of course our program has evolved and we now have additional interviews that need to take place. I would like to modify the database to allow for the possibility of additional interviews. Having looked at the code, I feel a certain amount of optimism that I can do this. My initial question is this (something tells me I might be asking a few other questions ):
I need to add additional "interviewType" records in tblInterviewType to accommodate additional interviews. The primary keys currently in tblInterviewType are sequential short integers (see image below) and they need to stay that way because they are related to the "interviewType" field in the tblInterview. When I attempt to add additional records to tblInterviewType, it auto generates a long random integer for the primary key. I attempted to delete relationships between this table and the others and change the primary key data type but access would not allow me to do so. I'm guessing there might be a work around for this. Does anyone have any suggestions? Sorry for the long winded description. Thanks.