ACCESS help!

[jsanchez]

hey kids,

I am creating this Access database. I have a table that has Social Security Number for the primary key. it is linked to other tables that have SSN as the primary key. I want it so that when i input the SSN in the first table, it adds it to the other tables without having to type it in manually. How do you go about doing this? My main concern is for the user not to have to be typing each social security number in the form so that they could enter info. in the table conisdering that the primary key cannot contain a null value. Please let me know if you need further explanation. Thank you.

[doctorgonzo]

What you are doing sounds strange. You say that the SSN is the primary key for all of these tables. That means they can only be in a one to one relationship. If that is the case, why not put all that information in just one table? Duplicating information is unnecessary in a relational database.

[jsanchez]

this was the only solution we came up with...it didn't work. Earlier, we tried setting another primary key in the second table that was an autonumber. we tried to link it to the other tables, but the numbers were always off sync. how would u recommend going about doing this?

here is a pic of the relationships...can u provide access newbies (us) with a diagnosis?

[sdkfz]

J Sanchez,

If I read this right, I think what you want to do is go into the relationships window (from tools menu) and when you set up the relationships between the tables select 'enforce referencial integrity' 'cascading updating' and 'cascade delete' .

Then when setting up the data entry form, use the 'basic customer information' (name ssn address stuff) as the first form and then for the other tables (orders, billing etc) , make them sub forms in the above form (the tab control is good for organinzing the different sub forms.)

If the sub form(s) need mutiple records (orders per customer for example) then do not make the SSN a primary key in the subordinate (orders) table, else as the good Doctor says you can not have more than one order per customer) Even though the ssn is not a primary key, it will be populated in the subform and you can still have a reatlionship back to the main table, just instead of 'one to one' you have a 'one to many' type. You can have a separate primary key for order number so no two customers have the same order number)


Hope that helps

[doctorgonzo]

From your picture, it looks like you have a bnuch of tables that share the "Campus Wide ID" number, all of which are in a one to one relationship. In general, all of this information should be put in one table. The only excpetion is when the other tables won't have records for everybody: for example, if you have 1000 students in the database but you only need RN licensure information for 10 of them, then it makes sense to put it in another table and link them in a one to one relationship. In that case, you will probably need code on a form to ensure that the IDs stay synchronized (the risk of having the IDs get out of sync is why it usually isn't a good idea to have separate tables).

If that is the case (one main table with thousands of records, and other little tables with only a few records each) then your setup can work. Otherwise, you should put it all in one table.

[jsanchez]

do you by any chance know the code to keep it in sync? thanks again good doctor and thanks to you as well sdkfz...

[doctorgonzo]

Enforcing referential integrity will help (as sdkfz said), but it isn't a perfect solution. There really isn't any way to make it hard and fast.

For example, take the database I manage. It has an orders table that has tens of thousands of records, all with information that is necessary for all orders, like opening date, closing date, shipment type, and so on. A small subset (<10%) need additional information, and that additional information is stored in another table using the same OrderID as a primary key. Thus, it is in a one to one relationship.

When you enter an order, if you need the additional information you can click on a button and Access will insert a number into the new table with the same OrderID, and a form pops up to enter the additional info. However, it can easily become out of sync. Enforcing referential integrity will keep you from changing the OrderID in the additional table to an ID that doesn't exist in the order table, but it won't keep you from changing OrderID 15000 in the additional table to 10500, if those numbers exist in the order table. Now you have the additional information attached to OrderID 10500 instead of where it belongs, at 15000, and there really isn't any way of even knowing that you did it.

Unfortunately, there isn't any generic code that can improve on this situation.