Table Fields (Name / number) dependent on another Tables data.

[Kelemit]

I have an ... interesting issue. I am wondering if others have dealt with this. I have a solution, but am not the happiest with it.

I basically have 2 databases here. (Or I will when I'm done making them) and one is dependent upon the other. In fact, the 2nd's tables are dependent upon the fields in the first database's table.

This database is for quality control checking purposes.

First off,

Database 1:
Basically a list of standardized audits and what needs to be checked for those audits. What needs to be checked can change as things progress, but stays pretty standard.

Database 2:
This will hold the checks that the QC branch will do, based on what type of Quality Check they are doing, they have an audit standard. This audit standard exists in database 1.

What basically ends up happening is that the data in Database1 become Table fields in database2.

Example:

Database1:
Radio Check Audit Standard.
Needs to check following:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.

Another Random Check Audit Standard
Needs to check the following
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Did something specific for this Audit
Didn't do something else, also specific for this Audit.
Etc.

Continue on with other Audits.


Database2: Will keep track of all the quality checks that have been preformed.

Today we will check Radio Protocol
Did they:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.

Yes, No as necessary.

2 Days later, we need to do another QC on the Radio Protocol
Need to check if they did:
Process Specs
EDMS Drawings
Manuals
SoP / Policy
Used Radio callback
Used radio during emergency
Etc.


Etc.


But what will be checked for in Database2 depends on what the standard is for the "Radio Check Protocol," or etc.

Right now, i see 2 options:

Option1
Make the 2 databases, and when a user "Picks" the Radio Check protocol, a linked table is filled with the Name / object that needs to be checked with a field in the same row to hold the data if the object was done / not done etc.
The fields in Database1 are automatically put into this specific instance of Quality Check, etc.

Only problem with this: Requires coding to auto update. Easy to do, but if errors occur, things will screw up.

Option2:
When the user picks the Radio Check protocol, simply allow a subform with a refreshed drop down box. Allow user to pick items they need (Limit it to only this Audit list, etc) to set to true. Assume false for other items not specifically chosen by user.

Problem with this: If an Audit standard changes to remove an item, or add item. Any quality checks done will also change for the assumed false items. If another Item added in, then all old QCs will now also have that item added in / reported assumed false. And if it is deleted, all old QCs will loose the item that was reported false.


I'm seen the first option as the only viable one.

Am I missing this, is there some other way to do this that would be better / wiser? Etc?

Kelemit

 

[Uncle Gizmo]

I am not sure I understand what you want, however I would say that what you propose to do in this statement:

What basically ends up happening is that the data in Database1 become Table fields in database2

Goes against any sort of sensible database design.

You must never think in terms of adding extra fields to tables.

Just to be clear :

You must never think in terms of adding extra fields to tables.

This sort of thinking is "spreadsheet thinking", and that is one of the biggest mistakes people make when they come to the MS Access from XL. MS Access is not a spreadsheet, the tables may look like spreadsheets, but they are not. It is a completely different discipline to get your data to work in a database.

Information that would quite easily and properly be displayed in a spreadsheet would probably need three or four tables to store it in MS Access.

First of all you must look at your data and identify the "entities" for example an address is an entity.

A typical address table would be:
ID:
address 1:
address 2:
address 3:
town:
county:
post code:
country:

from your question I would guess that one of your entity's will be an "Audit"

Audit ID:
Audit Date:
Audit name:
Audit description:
Audit supervisor:

the above defines the layout of a table, each entry in the table would represent one Audit.

You may want to include an extra field: Audit Type.


If you are having difficulty in identifying your "entitys" then maybe you need to start study an Audit in more detail so that you can identify the entity's. it is quite difficult at first, but as you progress you will find the knack of identifing the entities becomes easier and easier.

 

[Kelemit]

Thank you for your reply. And I appreciate the help, but I do know that.

I do NOT want to add fields to a table, that is the essence of the entire problem.

That is the point I am attempting to make.

I have a situation where the items in database1 will inform what database2 will be. IE: That data that will fill the fields in database2 are of type ABC which will be recieved from database1 AS DATA!

That is the struggle. Did you see the options / solutions that I showed at the bottom?

I have Several different audits, all with different "options" These audits will become templates by which entries will be made.

Basically, as an example, a record in database1 should become a Table in database2, with the data listed in the record of database1 becoming the fields in Database2.

THIS does NOT work. I KNOW that... hence the reason I'm asking for help.

If it could be setup this way, then it would be great, but Access does NOT do this. Hence the reason I'm asking for how to accomplish this.

I need to have a way to take that data in Database1 apply it to Database2 still as data (Probably a one 2 many relationship so the data can be fluctuating and different sizes) but apply it AS IF it were a field.

The only option I see right now is to use database1 to create a series of 1 to many relationship tables. Then use the data in those tables to create the data in Database2, again as a 1 to many table with a second field as the "data" field to the firsts fields "name of field, field." The first field (name of field, field) would be data recieved from Database1. The second field, the "data" field would be the field that the user creates a record with.

It would almost be like a nested table.

I'm unsure how it would work though.

Hence why I'm asking for help

Kelemit

 

[Kelemit]

Anyone have any ideas?

I'll try to explain again a little more. Hope it helps.

Basically, Standards for accomplishing a Quality Check can change, and are stored in a separate database / table. Those standards define what needs to be checked while doing a quality check. Those standards have two categories. Ones specific to audit protocols, and ones that apply to all audit protocols. So 3 tables, One with audit, one with specific standards (1 to many) and third table with standards to apply to all protocols.

Now, In doing a quality check, what needs to be checked is defined in the audit protocols ( those 3 tables ). Those items to be checked will vary over time and over protocols.

Any ideas?

Kelemit

 

[Pat Hartman]

I'm going to second uncle gizmo's comments. I think you are thinking about table 2 as if it were a spreadsheet. Your problem is the typical survey/questionnaire question we see (and answer) here frequently. You can search for posts on surveys to get more details but basically, when you start an audit, your application will copy the set of checks in a particular protocol and insert them into the "answer" table. Keep in mind that they will be ROWS not columns in the answer table. Then using a subform, the auditor will check yes or no for each item.

 

[Kelemit]

I agree Phil... that is the answer I came up with as well.... If you read my "Option 1," you will notice that is exactly what I spelled out.

But as you can see from my first post, that is not the question I am asking.

I am asking if there is a better way.

I know what you are both saying by the "spreadsheet mentality," but I do not have that. I have been working and creating databases now going on 10+ years.

I can always learn more, and that is what I am attempting to do now.

The way I am explaining it is to show how the 2 databases would function / work if it were a perfect world. But it is not, and I have access to deal with.

The point is IF, (PLEASE note the if....) it were possible, this is how it should be built (in a PERFECT world).

Database one is a collection of standardized audits.

Database two is a collection of the Database one standardized audits completed at specific moments / times.

Therefore, Database two's tables should be created "on the fly", as it were, (AGAIN IF ... IF it were a perfect world / possible) whereby the Fields of database two's tables would be the current record entry of database one.

This ISN"T the way to do it. I understand that.

The answer I came up with is basically to have VBA copy whatever entries are in DB 1 into DB 2 as a record in a table on the many side of a 1-M relationship.

The only thing I don't like about this is that:

1. If my script screws up, we have an incomplete entry and need to delete it. This simply leaves the data open for corruption.

2. The need to define each entry type is now needed, and now a table is needed for each entry type for each section. IE: Need a text table, a Yes/No table, and a Number table (minimum I would think). So that you could put in those types of entries if needed.

Either that, or have one table with 5 fields, 1 field for the "NameofField", one field for "TypeofField", and the other three fields being a yes/no, a number, and text. But 2 of those 3 fields would ALWAYS not be used, because it can only be one thing. A yes no, a number, OR a text. At that point, you have one table, but 2/5's of your table is completely unused / wasted.

Or a final optoin is to just have one table and 3 fields, the third field being just text. And putting in code to interpret the field. If field2 (which is field type) in this record is number, then i would have to convert the text to number. If it is yes/no, then I would simply enter yes, or no, or true / false into field3. But then, also, i would need to reinterpret that data as well, thus I would need to put in code/change things/etc.

Anyway, thats the only solution I see at the moment.

Let me repeat. THIS is the SOLUTION that I see, at the moment. I WOULD LIKE another one.

I do NOT want to generate a table automatically, I do NOT want to make a spreadsheet / excel -esque database. I do NOT want to add more fields into a database. I do NOT want to make a badly designed / stupid database.

I DO want a better solution if mine is short sighted / etc. This seems to be about the only viable way, but that is why I am asking, I am wondering if others have encountered this and have a better solution.

I hope this clears up the confusion.

Kelemit

 

[Uncle Gizmo]

>>>Let me repeat. THIS is the SOLUTION that I see, at the moment. I WOULD LIKE another one<<<

Sometimes you can get to close to a problem, locked into a particular process, unable to see your way clear.

Take a holiday, somewhere nice, Spain, Barcelona, tappas, San maguil larger.... I'm off!!!!! see ya.....lol

 

[Kelemit]

?? um....

Did I offend you in some way?

That response didn't really help.

anyway, maybe someone else can help.

Kelemit

 

[Uncle Gizmo]

No offense intended! It was supposed to be humorous.

I can't see a solution other than identifying the entities, (tables) and starting from scratch.

If a way of arriving at a solution eludes you with what you have already got, then sometimes you need to go back to basics and redesign the elements and put them together again in a different way if you can. You will then Hopefully arrive at a different solution. Just an idea really, just a suggestion as you seem to be stuck.

 

[Kelemit]

I'm not really stuck... i haven't even used this idea yet, to be honest.

I'm just trying to find out if it is the best way. The idea I came up with is basically identical to what you and phil both spoke of. And i know that this is just about the only solution there is. But I dont' know if there are any ways of doing this that are better, or if there are any ways of doing this that I REALLY should avoid.

I haven't created a database like this yet, and see it as a challenge and something fun to do. I just wanna make sure that I'm on the right path. Its not the best solution thats out there, but its not bad either.

I just don't like having to use code to append / add records into a table in order to create a complete / correct record because i don't want a pure record to be based upon my code. This allows the possibility of corruption to occur.

Anyway... let me see if I can do this. I'll run through with this and see what I get.

Kelemit

 

[Pat Hartman]

I just don't like having to use code to append / add records into a table in order to create a complete / correct record because i don't want a pure record to be based upon my code. This allows the possibility of corruption to occur.

There is no need to write code to do this. Use an append query that selects the questions from tblA and inserts them (with the correct foreign key) to tblB. If you have different field types, that complicates the issue slightly. I would use a single text field.

In one of my survey applications, I created a table that held subtables that were used to populate combos. This allowed me to use different scales for individual questions - Yes/No, 1-5, 1-10, A-D, etc. That ment that I used two fields in the answer table. One for the foreign key to the combo table and the second was a memo for long text answers.

PK, Group, Value
1, 1, Yes
2, 1, No
3, 2, 1
4, 2, 2
5, 2, 3
6, 2, 4
7, 2, 5
8, 3, A
9, 3, B
10, 3, C
11, 3, D
etc.

 

[The_Doc_Man]

This seems, at first glance, to violate normalization. At least, that is my gut instinct. So then the question I ask myself is "WHY does it appear to violate normalization." Because that will be my in-road to analysis.

OK, there is nothing wrong with having tables in multiple DBs and having (at least implied) relationships between them. But you are using the two DBs like they were single-table DBs in your explanation, or at least that is how it comes across. That is a bit confusing but not insurmountable. So your issue of having data in table/DB 2 be dependent on table/DB 1 could be supported, I guess.

However, you said the audits (table 1 data) change over time. So unless there is a date field in the audit tables, you have information in one of the tables (table/DB 2) that doesn't depend on primary information in the other table/DB. Yet that date field is necessary to identify the validity range of the table/DB 1 entries. That isn't good.

Next, there is the issue that you insist that the number of tables and fields in the second DB will change. It isn't impossible, but it will be infernally difficult with this design to maintain meaningful relationships because you are not on a constant-size (or shape) playing field.

Relationships in the TRUE sense cannot be maintained when you have non-constant schemas on one side of the relationships. Therefore I am not surprised you are having trouble doing manually what I don't think is possible automatically. (Unless I, too, am misreading your concept description.)

These relationships are, as you said, meta-relationships. I.e. nested or hierarchical. Access is still not a "true" object-oriented DB (though it has many of the features thereof.) Normally, a relationship is field-to-field, not field-to-table. But that is what you are describing. I'm not sure that Access will EVER directly support what you are doing. Because remember, Access is a SMALL BUSINESS product, not a major corporate support product. What you seek is a hierarchical database, and those are rare these days.

Thinking more about it, I question which is the chicken and which is the egg? Do more fields appear in Table / DB 2 because of changes to Table DB 1? Or do fields appear in DB1 because of changes to DB 2? That isn't clear, either, and without that kind of information, you question might literally be unanswerable.

 

[Kelemit]

Thanks much for the responses.

My wife's grandmother died about a week ago, and I had to take off some time to be with her and head out of country. So I haven't been here for about a week or so. Apologies for taking so long to get back.

@Pat Hartman:: Your idea was essentially identical to the idea I had, the only exception was the append sql. This is excellent. Something that I don't usually think of. (I came from a programming background.) I'm still learning to use SQL effectively. That still requires some code, but not near as much, and greatly reduces corruption issues. Thanks much.

@The Doc Man:: First off, I do agree that there appears to be some violation of normalization, only in the fact that there will be duplicate information. But that duplicate information is something that will be required mainly because this database will require changes over time, IE something that Access does NOT inherently support. I have run into this problem before with Access and "time dependent" primary keys. These things are a hassle. But not impossible to overcome, and I believe that I have achieved that without destroying normalization.

2nd, the two databases are actually multiple Table databases, I only refered to one table because the issue is the same across all the tables that make up the record. I have several dependent tables with 1 to many relationships to the primary table, thus creating an unknown amount of entries per record. This is the crux of the problem, those entries, being unknown, as well as there number being unknown, require creating records, instead of fields in a table to accomadate future growth / change over time.

The fact that audits change over time is, I agree, difficult, because, as I said before, Access does not effectively allow for changes over time while maintaining a single record. Either the record must be recreated in its entirety when it is created "new," then a date applied so that one can know which "version" to use, or make the records for the two databases independent of each other. Only use the first database to create the new record to be applied in the second database for qualtiy check purposes. (IE, in access speak, just copy what the current record is in database one, into database two.) This means no normalization, but allows for change over time without destroying the needed information for the quality check.

The second method (duplicating records into the 2nd database) works only because the information it stores is, in actuality, completely separate from the first database. The first informs the second, but is completely separate from the first.

Basically, the first holds examples of HOW to do a quality check, and the second holds the data of ALL the quality checks ACTUALLY DONE. Therefore, the data is different and the databases are accomplishing completely separate / unique purposes.

As for the chicken / egg issue. Database1 IS the chicken, but it is possible that issues raised while doing quality checks (IE database2) might inform changes that will make there way into database1. IE: We need to check A, B, and C. Lets call this checking procedure standard9. This standard9 of checking A, B, and C is put into database1 as a standard9. When users then begin to do quality checks based on this standard9, users begin taking down information about what there checking of A, B, and C have revealed. Possibly A, B, and C are yes / no's, or texts, maybe numbers, etc. So when the user has accomplished a standard9 quality check, he opens database2 to report his findings.

I this instance, A was true, B was 2, and C was false. He then picks standard9 as his standard of checking, and a new record is created (like Pat showed) with the names "A, B, C" in one field, and the values in the other field in a separate table linked by PKs, etc.... This is the dangerous part. If the record is not created properly, it is possible that only fields A and B make it, and C doesn't because of an error. We now have a bad record. But on with the example.

The record populates correctly, A, B and C appear, and faithfully wait for the user to input the data True, 2, and False respectfully.

It is possible, that as time goes by, they realize that they don't have ENOUGH information, and go back to database1 and put in D for more information. ALL records created under standard9 standard in Database2 should NOW have A, B, C, D. All old records in database2 should still only have A, B, and C. The CURRENT record for standard9 (ONLY 1) in database1 should show that current procedure dictates checks of A, B, C, and D.

Maybe, they realize C was redundant at a later date. Database1 now has Standard9 record modified to have C deleted. All NEW standard9 audits will now be created in database2 with 3 records of A, B, and D with appropriate data as well. But all older versions of standard9 audits will STILL show A, B, C, and D; or A, B, C whichever version of Standard9 they were created under.

This is the crux of what is going on, and the best option seems to be as Pat Hartman has described.

The issue of normalization between the two tables is a non-issue, i believe, because the both store completely different information, and have completely different functions. But they are not completely independant of each other.

Think of the example of an employee database and a tools listing / checkout database. The tools that are checked out should ONLY be checked out by an employee, and therefore the tools database can derive info / "who should check out" info from the employee's database, but it stores completely different data and has a different puprose.

The examply of standards and audits just also happens to add in the complexity of other derived information as well.

Any other comments on this would be greatly appreciated. I will attempt to report what I discover.

Kelemit.

 

[The_Doc_Man]

The issue of normalization between the two tables is a non-issue, i believe, because the both store completely different information, and have completely different functions. But they are not completely independant of each other.

I think less of the duplication of information as a normalization violation. Normalization for tables that are not COMPLETELY independent of each other is not a non-issue. Normalization in your "employee checks out tools" case is not a good example because normalization is important for that case. You store information in the check-out tables that incudes both employee and tool data as well as time data. They normalize at the point of overlap.

Your explanation of the problem with audits simply tells me that you haven't fully grasped your own problem. The situation you described cannot be stored correctly without either normalization or unwanted duplication of data. If an audit standard is recorded in table 1 and a set of results are recorded in table 2, they must be normalized, either to each other OR to a (currently obscured) third table that is the junction table between the two.

BECAUSE - the audit standard (as you described) changes over time AND the results of an audit occur on a date. It is only through that date AND a test number or standards number than you can decide whether the data in the second table is a valid response to the tests in table 1. I suggest some reading into the topic of Interrogative Logic (the companion to Declarative Logic) that decides issues of "A" being a valid response to "B." Relationships between A and B can exist even if they don't really appear to be related in form or style.

I most strongly urge you to back off and do some more in-depth analysis. It seems to me that you are missing something that would link these apparently disparate items together. Without identifying that "something" you are doomed to forever flounder around. Stop NOW and re-think your problem. Either your descriptive skill or your problem model is deficient. (Please do not take that as personal, it is intended as a constructive criticism.)

Your paragraph that starts with "The fact that audits change over time is, " shows me that you aren't thinking of this in the "right" way. You are essentially denying the existence of the time factor because you don't want to truly normalize your DB. But in the very same paragraph you admit it exists. In essence, you are looking for a short-cut out of a more massive problem. I won't tell you I've never done that, but your problem that results is that you want time-independent properties yet you are finding that because of them, you have to change things in a cooperative manner.

To be honest, if you aren't willing to more closely embrace time in your design, I don't think this problem HAS a solution in any automated way. Just one man's opinion, take it for what it is worth (i.e. how much you paid for it...)

 

[Pat Hartman]

I this instance, A was true, B was 2, and C was false. He then picks standard9 as his standard of checking, and a new record is created (like Pat showed) with the names "A, B, C" in one field, and the values in the other field in a separate table linked by PKs, etc.... This is the dangerous part. If the record is not created properly, it is possible that only fields A and B make it, and C doesn't because of an error. We now have a bad record.

You are making way too much of an issue with this. The query is simply copying a set of records from A to B and assigning a foreign key to the B table so that the records in B relate to records in table C. There are no conditional statements involved and so the SQL is unlikely to "break" in the future. Also keep in mind that the records in B include a foreign key that relates back to their parent record in A. So table B is really a relation table that connects A to C.

Keeping history in A complicates the problem but not that much. I would suggest including an effective start date and an effective end date. This will allow the SQL that copies from A to B to only pick up A records that have null ending dates. In addition, you should not allow changes or deletes to A records. The effectivity end date should be set when the procedure needs to be modified or deleted. If the procedure is still needed, an entirely new record should be inserted. With this method, records in B will ALWAYS point to the A record that was effective when the B record was created.

 

[Kelemit]

@Doc_Man:: As for my defeciency, I believe it is more my description abilities, not the way I am thinking of the problem. I honestly understand what both of you are saying, I guess I have difficulty in explaining though. I was really forced to use and learn Access on my own, and I know that I do not have the proper terms / explanations of databases, let alone Access.

In fact, I didn't even know that normalization techniques had been defined and named until I found this site and began learning / exploring. I ended up teaching myself normalization levels 1 through 6 on my own through trial and error. The only normalization that I wasn't able to figure out on my own was the 5th one, I think. That opened my eyes and helped alot. (Actually ran into that problem myself a couple years back and couldn't figure that one out. Was good though. I recognized the problem, but didn't find the "right" solution. Jerry rigged something.)

Anyway, I know that the tool check out / employees wasn't the best example, but it was to point out that the data between the two are independent. The normalization is still important to retrieve the data of who "checked out" the tool.

I was attempting to use this example only to show how the the data is completely separate in its purpose from each other.

In a similar situation, the data from the two are separate "types" of data. The data from database1 would only set up / create the record for database2. After that, it would be unnecessary. Almost like the way word / excel templates work. Database1 would be a listing of templates, and database2 would hold the data inputted into the "templates" that were generated from the current / correct template from database1.

Hope the explanation is clearer.

@Pat:: I agree, but I'm trying to figure out what purpose would be to make database2 data dependent on database1 information. Mainly because the data that would be duplicated in database2 would ONLY be the name of the derived "field," as it were. The only other thing that would be kept in there would be the "data," IE either they yes / no or a number / text. And this is NEEDED.

By the time that this thing gets into use, with the number of audits existing, updates, field parts to those audits, etc, the PK field could be as long or even longer than the actual name of the field that the PK field identifies. At this point, just storing the actual field name in Database2 would be wiser. Regardless of how one looks at it, there must be atleast two fields in the table. One to store the data, and one to return the name, either via PK or just store the name in the table.

@ the time issue:: I'm not ignoring the time issue, but I do not need to keep a record of changes made to the standards in the audits (database1) database. All that is needed is to maintain the current standard that is needed. At this point, I either need to duplicate the information in Database2 so that the fields are not lost (IE don't use PKs but put in the "field" names) or keep a record of all changes over time to standards in database1 and the date them and return the names of the fields from database1 based on the date that the quality check occured found in database2. See above @Pat to understand why I think it would just be wiser / better to store the names in the quality check and not use PKs with links to return the name of the derived "fields."

@ Query issue:: The query is a simple issue, and I agree. But when the standard / audit is chosen, code STILL must be run to create the records needed for that standard. I'm not worried about the query failing or becoming obsolete. That won't happen. I am worried about the (thankfully now only 1 or 2 lines) of code that could fail needed to CALL the append query and run it.

Kelemit