Can a form change common fields on different tables/

[PATRICKPBME]

If I have a DB with 6 tables that are related to each other but "connected by 1 field (primary key/foreign key) Is there a way to format the form so that when it adds or changes a primary key on the form it makes that change in all of the tables? When I add fields to my form it is no problem changing the data in a field unique to a specific table. But what about the data that is common to all the tables?

For example, if all fields have a contract number an a new number is entered on the form, can I design the form in some way so that that new number is entered on all tables and not just on the table where the field containing the data was used on the form. I guess I am looking for something similar to what you can do in Excel, telling all cells in several sheets to incorporate the data in a specific sheet.



Is this where referential integrity comes in, etc. OR do I have to manually enter the new contract number on every table?

Thank you!

[CJ_London]

not quite - creating a record in one table will not automatically create related records in other tables. But you can do it with code.

referential integrity is about preventing child records being create when there is no parent record.

It also means if a form based on a parent table and the child tables are in subforms (rule is one form, one table) on that form, when you create a new record in one of the child tables it will automatically create the relationship for you. But that is not what you a saying you want to do.

[June7]

I think I understand the structure you describe. I might have a similar arrangement. I have a db that tracks lab samples. When a sample is submitted a 'master' record is entered by user and various tests are selected that the sample will be run through. Each test has its own table. These test tables each have a 1-to-1 relationship with the 'master' table but not with each other. When a test is selected, code creates a record in that test table with the 'master' PK as a FK. The code commits master record to table and uses SQL INSERT action to create the test tables records.

My db has a table for each test because there are too many tests and too many fields to fit in 1 table and this structure actually makes it easier to store data and produce reports in the desired formats.

Why do you have 6 tables?

[PATRICKPBME]

Thanks to all for your input. June7 I'll start with your last question regarding 6 tables,

I am a self taught access user. One of the things I was told/read was that it was not good to have tables with too many fields. Admittedly, "too many" was never really defined but the suggestion was that I break up data/fields into different tables.

To be specific, I am dealing with medical contracts (600 of them) and adding to the database weekly with new physicians. In addition to name, address, position, phone, etc. I have production numbers, salary, director in charge, business unit, dept # and so on. Probably a total 50 different fields. I have separated this information into 6 tables. If you think 1 table could handle them all, I am all for that. The bigger "problem" I was told was with one of the other remaining fields which contains all of the documents relating to a physician. The PDF files are large and I was told having so much info on 1 table would create a problem. I would often get the "Access not responding" popup which, I was told, probably was because of so much info in one table. That is why I started breaking up the info into separate tables. Plus, I have seen examples of DBs with a dozen or more tables where, from my perspective, all of the fields could have just been on one. Sure, there would have been probably 100 fields but if they were being populated through one form, who cares?

Much like your DB, I have a master with one-to-one to the others. The primary and foreign key are the only similar fields "linking" the tables. That is the contract #. Of course, when I create a new record on the form, all of the information will be updated on every table...except the contract number. That only updates of course on the master table as that is the table from which the field was placed on the form.

If could could put all of this information on one table, that would certainly be the easiest solution. Next, I guess, would be to get the db down to maybe 2-3 tables and manually insert the contract number in the two not linked to the form I think I followed you until you mentioned SQL insert. Above my knowledge base at the moment. Interested among other things in your and anyone's perspective on the "don't make you table with too many fields" thing.

Thanks again!!

[June7]

Yes, that is getting to be a lot of fields but not unmanageable for a single table. Would have to know more about your business model.

Each contract is for a single physician? Can a physician have only one active contract? Do you want to maintain history of contracts? Depending on answers, all fields in 1 table with exception of PDF documents may be optimal normalization. As you discovered, embedding files in table can degrade performance and then they chew up Access 2GB file size limit. This document info should be in a related 'child' table, a record for each document. And instead of embedding in table, store files externally and save filename in a text field.

Attachment field is a multi-value type of field. I recommend not using multi-value fields for normal data and rarely for attaching files - such as an employee (or your physician) photo (very small jpg/bmp/png image).

A multi-value field actually stores data in a hidden 'child' table. They are difficult to work with and require special handling in queries and VBA.

If there are any other fields where multiple values are stored, then consider a related child table. What are 'production numbers'?

[PATRICKPBME]

Thanks. You have already helped expand my database knowledge. For example, I didn't know about the 2GB limit. Does that refer to a table or to the whole database? I assume the latter Still a bit foggy on the attachment field for the records. What I have seems to work very well but if there is a way to "protect" me from the 2GB limit I am certainly open to that. I am just below 1 gig now. Production numbers are simply numbers tracking procedures the doctors do for purposes of compensation. Nothing fancy, just a number

A physician can have more than one contract which has a different # to a record I have now. I can search the DB to show all of their contracts or search it by a specific contract #. From what you have said, it seems that I could have everything in one table except for the documents. Now, when I bring up a record the attachment field appears as a paperclip with a number indicating the # of documents. Clicking on that field allows you to to open any of the documents. As to that, I think I need to do some reading on a better way to have the type if instant access to a document that I have now.

Having said that, lets say I get really lucky and bring this down to two tables with the primary/foreign key being the link. I sense you are saying there is no way to add/change the key in the primary and have it change in the foreign. Thanks!

[June7]

2GB limit is for the database file.

I can picture at least 3 tables:

Physicians - last name, first name, address, phone, email

Contracts - number, physician ID, amount, date awarded

Documents - filename, contract number

I still don't understand production numbers and how and when they are used.

Run Compact & Repair on the db and see if the size reduces.

In spite of my example fields, advise no spaces or punctuation/special characters (underscore only exception) in names. Don't use reserved words (date is one) as names.

As far as adding key in dependent tables - that's what form/subform arrangements are for. Don't know why you would need to change a key but that is possible.

[PATRICKPBME]

Thanks, I'll give it a whirl. I have used Compact and Repair in the past and believe this can be set to run at every closing of the DB.

The production numbers are just to show what a doctor has done. If I am asked, for example, how many by pass surgeries has Dr X done this quarter, the number for that is in the DB.

Again, I guess I am correct that the common (primary/foreign) key will have to be added/changed manually in an subsequent tables where I have established a relationship. If I add contract 1234 to the "main" table and that is the primary key in table 1 and foreign key in tables 2 and 3, it won't automatically be added to tables 2 and 3.

Thanks for all of your help.

[June7]

I see a fourth table for Accomplishments, linked to Contracts.

Data entities of your structure appear to be physicians, contracts, accomplishments, documents.

The foreign key will automatically populate in a form/subform arrangement. As soon as a value is entered into any field of the subform, the primary key from main form will populate to the foreign key field of subform. Forms can be nested 7 deep.

[PATRICKPBME]

Okay, thanks. I will look into forms and subforms and see what I can learn. Thanks again!

[CJ_London]

I have used Compact and Repair in the past and believe this can be set to run at every closing of the DB.

you can, but it has little benefit in real life. In the context of a live environment (as opposed to development or testing) if it is multi user then each user will have their own copy of the front end - which should not change (it is adding/deleting data which causes the db to bloat and that is in the backend) so compacting will have no effect.

And if you are trying to compact the backend, you can only do this by opening (either manually or with VBA) the backend and compacting - which will only work if no one else is in the db at the time, otherwise you will get an error.

If it is single user then OK you can leave tables in the front end - but it is not recommended practice because if you do get an issue (compacting will not solve all issues) you run the risk of losing both data and forms/reports etc. And if you do compact on close, there is nothing worse than the user jumping up and down desperate to leave the office, but can't because the app is taking its time to compact.

Compact and repair should be undertaken as part of a database management process which would include other activities such as taking a regular backup.

[orange]

Patrick,

Tables are not simply an arbitrary grouping of fields. As June pointed out, tables are based on the business model --that is, the business facts and processes. A business model can be derived from a description of the business. The thread suggests you are dealing with physicians, contracts, accomplishments, and documents. However, if you could describe clearly in simple terms--a day at the office telling us what a physician does; how it relates to contract(s); where do accomplishments and documents fit.... then a model of the business can be created and tested to ensure
your model is complete and consistent. Once you have such a model, you have a blue print for your database structure/design.
Getting your tables and relationships designed to support the business is a critical step in any database application.

Here is a link to some tutorials re database design/tables and relationships.
For practical experience / learning spend 45-60 minutes and work through one or two of these tutorials.
Class info system
Catering Business
Widgets

Good luck with your project.

[June7]

Ajax, I have observed db bloat with design changes but very little bloat due to adding/deleting data. I just ran C&R on split db. Backend showed no change in size, frontend went from 32MB to 27MB.

[CJ_London]

I was referring to a production scenario. Yes you will get db bloat for design changes. With design changes it is also worthwhile doing a decompile once in a while as well. Backend - I was referring to adding/deleting data - the db grows as you add data, but if you delete/change data it does not shrink it unless you do a compact/repair. Some db's you only add data, it is never deleted/changed

[PATRICKPBME]

Thanks again to all. Maybe I am making this sound more complicated than it is (I hope).

This database, for the moment, is accessed only by me. I have developed it and I run any queries when anyone needs information. At some point, the idea is to have several people be able to retrieve the data but they would be unable to add, alter or delete anything. Orange asked for a "day in the life" scenario and I think doing that might be helpful.

Each day, I open my email to see if I have been sent any information that I would need to enter into my DB. For example, I may get an email stating that Dr. X is going to extend his contract through 12-31-18. I open the form, go to Dr. X and note that the new expiration date on his agreement is 12-31-18. If a new agreement or amendment has been supplied with the email, I add that document to Dr X's records in the attachment field. The same procedure would apply concerning compensation, location, director responsible, etc.

If I get an email that new doctor has come on board with the draft of his contract, I open the contract and from the same insert the information on the form as a new record. This is an instance where the new contract number on the the form would only appear on primary table and I would have to insert is manually on any other tables. Considering subforms now for that although I do want to look more into what the "nesting" process referenced earlier involves.

Now, how do I use this database? Well, I may get a call from Finance and be asked something like "What is the Cap on Dr. X's compensation for the year" or "Can you tell me how may doctors we have who have contracts expiring within the next 18 months", or "I need to know anyone who has a contract effective since 5-1-17." As to the first, I just bring up the doctor's record that has been populated via the form. As to the latter two inquiries, I can just run a query and provide the information.

Here is what I am thinking at the moment to solve my original problem. If I do get the DB down to 3 tables, I guess I could just do subforms and type the contract number in three times?

Thanks again!!