How to maintain keys & create records in a 1-to-1 relationship

[dkmort]

First off, let me say that I've read several threads that strongly discourage from using 1-1 relationships. I will give an explanation of my situation in this post, and you all can tell me if there would be something better for me.

I am linking to several MS SQL 2005 tables from Microsoft Office Accounting. It is the system we use for orders, time tracking, invoicing, a/r, etc. In order to extend its functionality, I'm using Access to create more reports & forms.

In my case, I basically just need to add a few fields to one table in the MS SQL database. But I am worried that doing so may cause problems down the road (or maybe even now). So my plan is to just create my own Access tables for any additional fields that I want to track, and tie them to the applicable SQL table via 1-many or 1-1 (where appropriate) relationships. To keep with my plan to not touch Microsoft's tables, I'd like to just use a 1-1 between the SQL table, and an Access table that I create. This way the Access table is essentially an extension of the SQL table. At least, that's what I'm thinking.

If you know of a better way to accomplish what I'm wanting, please let me know.

I guess I would be best to dig into their SDK, and hang out in their developer forums to get their recommendation. However, I think that all of the concepts coming from DB design & MS Access are just as applicable, and that I should be able to come up w/ a good solution utilizing the access-programers forum.

One of the main problems that I see with this would be auto-record creation in the 2nd table. I can go into the 2nd table and create records w/ matching primary keys to the first table. I can even create a form that let's me pull the key from a drop-down to make this quicker. But this is really inconvenient, when all that I really need is for a record to exists in the 2nd table, any time that it exists in the first. Or if I could do something fancy w/ a form that would auto-create the record in the 2nd table & match its key up w/ the first as soon as I try to manipulate the other fields that the 2nd table contains.

I really appreciate any ideas with this situation.

Thanks much!

-
Doug

 

[KathyL]

I see no problem with you having an Access table with a 1-to-1 relationship with an external/linked table. I’m assuming you’re going to use ODBC linking to bring the table into Access? From what you describe, I’d do exactly what you’re thinking. 1-to-1 related tables do not require that you have the same number of records in each table. For example, you might have 10,000 records in the MS SQL/ODBC linked table, and only 10% of those might have secondary records in your second table. Maybe your second table is just a memo table, and maybe there are only memo notes on 10% of the records. In this example, it will save data storage because the other 90% of the records don’t need records in the secondary table.

It should be relatively simple to have a form which calculates the needed key field in the second table record, by equating to the value of the primary record. That is not inconvenient.

No, I don’t think you’re going to have problems down the road.