I am new to access and have been given a large task. I have been at it heavily for the past two months and have read the dummies book and have searched relentlessly on the web. So without further ado here's the trouble. I will state my problem or what I think my problem is and then I will tell you what I am trying to do so if there is a better way, I can go that route instead. I have several linked tables that I want to have a matching record created in the detail table every time a new record is created in the master table. the relationship is one to one. I have clicked on cascade delete which will delete the associated record when the master record is deleted.
Here's the background. I am in the military and our company will be going from using Excel like a database to a real database. My first step was to create one large table that everyone can see, access and update regularly. The problem quickly came that we collect more than 255 (access' field limits per table) bits of information on everyone. The next step is more logical and I created several related tables. Here is where the first problem begins. A few of my tables are as followed. admin table (collects all basic information on people such as rank, date of rank, date of birth marital status etc., MT table that collects all of the data involved in what license they poses, expiration dates and when there explosive certification expires, Training Table that covered what badge qualification they have, schools and date completed etc. SCP table that covers everyone’s status of a security clearance, official passports, and a dozen dates that deal with that section. There is even a table that will house sensitive data like SSNs Passport numbers etc. For most of these tables I NEED a record created even if there is no data to input at that time so we can show the state of readiness. I have set the primary key to auto number on the master table and a long integer for the detail tables. B/c the name and rank is only stored in the master table I would like you view and search based on these fields so I can identify the person before adjusting the motor transport section. This is for the human factor as I know access can keep it straight based on the keys an relationship. I think the answer may be a macro but I haven’t found the answer. Thanks for whatever help comes. I can also post/ email the database or forms if it will help as there is no personal data in it.