One-to-Many between tables

[Timothy Andrepont]

I am in the process of cleaning up/normalizing databases that were built on the same system. The conversion database I am working with has little or no data in a few of the tables.

I have come up with a simple challenge that has me stump. I have 13 data tables that are structured the same: Each starts with table ID (AutoNumber), followed by unique AssInscecID (number), and then _Weight_ID ( text).

I have not succeeded in setting up the relationship in last 3 tables (11, 12, and 13) with tblCatWeight which should be a one-to-many relationship as is the case for the first 10 tables. I cannot find how these last tables differ from the first tables. You will note that there are tables within the initial 10 that have no data as is the case with one of the tables in the last three.

I have attached a zipped fill with the database and pdf file showing pictorially what I have written above.

What do I need to do to modify the table structure of tables 11, 12, and 13 to generate the 0ne-to-many relationship needed?

Your help will be greatly appreciated.
Timothy

 

[jdraw]

Timothy,

What is the underlying business? What do the tables represent?
You could look at this tutorial - it may give you some ideas.

 

[Timothy Andrepont]

JDraw,

I am getting ready to read through "Entity - Relationship" tutorial to verify I understand these relationships. Thanks for sending it to me.

The underling business of this database is Condition Assessment of physical assets. This particular database is looking at all components of a canal system.

If you will study the information I attached to originating thread, you will see that all 13 of the tables with numbers in their name are in the same relationship with the joining table tAssInspec and to the tblCatWeight. The present interest is with the relationship between tblCatWeight.CategoryID_Txt and tbl_##_Discription of the 13 tables. As is shown in the Relationship diagram all CategoryIDs of tblCatWeight are in a 1-to-Many relationship with weightIDs (????_Weight_ID) in each of the 13 tables. However, in the last 3 tables (11, 12, and 13) the relationship as shown in each corresponding query does NOT show a 1-to-Many relationship. See "Qry_11_OC_Count_Sum" design's view. It shows the relationship to only when each is equal. I am only looking for what causes the relationship of "Category_ID_Txt" Not to be 1 to Many with "Risk_Weight_ID_Txt".

I am hoping you will help.

Thanks for you continued interest in this little basic challenge.

Timothy

 

[Timothy Andrepont]

JDraw,

"What do the tables represent?"

Each of the 13 numbered tables represent component condition. tblCatWeight carries component weighting factors.

Let me know if you still need more clarity.

Timothy

 

[jdraw]

I have looked briefly at your database. I have no idea of you business details.
Who created the database? Who maintains this database? Where does this database fit with the key business of your organization ("the Company")?

Getting an outside person to look at the table designs and the existing database relationships window, does not tell What the business and related processes are/should be.

As it stands, it appears to be a lot of jargon related to the business. Terms like BS_ID, PSL_ID, WWS_ID don't seem to be well named for those outside the organization.

Overall it seems to align with your comments of Asset review based on a number of criteria. It seems to take these criteria/factors apply a weighting factor to arrive at some calculation related to overall
"goodness/acceptability" (my terms).

In T11 - I would not start out with multiple memo fields in a table. I would certainly look at having a table of comments and a comment type and probably a Date and CreatedBy field.

In T12 I see comment/descriptions for table fields

eg Type station comment,
PS scoreed by Dennis and Mike.

which isn't helpful to me, but may mean something to you. Who are Dennis and Mike?
What can they tell you about the table/database?

These same comments appear for T13??

In a system/application of this nature, I would expect some sort of documentation. It may be old, and out of date, but it would be a starting point. Whenever anyone has adjusted anything in this database, they had to have some knowledge of what they had and what/how would change and tested.
There may even be an Operations Manual as to how it works, who does what and when.

What is tblSum_01_05? It seems missing if you attempt to Open report rptQryCalTotal_By_PS

I haven't checked others, I just did some random executes to see the result.

I did see reference to a SCADA report. I googled to find this

SCADA


Acronym for supervisory control and data acquisition, a computer system for gathering and analyzing real time data. SCADA systems are used to monitor and control a plant or equipment in industries such as telecommunications, water and waste control, energy, oil and gas refining and transportation. A SCADA system gathers information, such as where a leak on a pipeline has occurred, transfers the information back to a central site, alerting the home station that the leak has occurred, carrying out necessary analysis and control, such as determining if the leak is critical, and displaying the information in a logical and organized fashion. SCADA systems can be relatively simple, such as one that monitors environmental conditions of a small office building, or incredibly complex, such as a system that monitors all the activity in a nuclear power plant or the activity of a municipal water system.

 

[Timothy Andrepont]

JDraw,

Thanks for your reply.

A novice constructed these databases. I am barely more than a novice myself.

These databases are not concerned with the business model. They are to be a starting point of tracking condition and ranking of capital assets. These databases score and textural comment on each component of each asset. Component scores are summed, averaged, weighted, and brought into the Asset entities final score for a specific date.
All comment/memo are external to any calculations or searches and are intentionally not part of any indexing. Additionally, comment/memo fields are attribute’s score justification. Almost each attribute has a unique memo field. I wonder if this would alter your recommendation for comment tables.
Inspection dates are found in tInspection. At this time there is no bywhom tracking.

Table12 and Table 13 comments about “Descriptions” are well taken and appreciated. They were descriptions of the score source for these two tables. They should not have been placed in “Descriptions”. It presently represents an area of database needing further designed for both tables.

Be assured there is not yet any documentation on any of the 3 database. (Future work.)

I can almost realize hard it must be to look into a novice’s database. There are presently, three databases using earlier variation of the current database structure. All databases are filled with reams of calculation on most non-memo fields. I loathed the thought of having to recreate all the calculations, i.e. start from scratch.

I have read your tutorial. I had a good grasp on “Entity - Relationship”. I understand, further, how important enforcing these relationships in the records of this database is. At this point in my effort, I am only asking why the queries off tables 11, 12, and 13 are not 1:Many? I have checked everything I know how to and cannot see how these last three tables are different than the first 10 tables. Would it be possible only do address this question in your next reply?

Again thanks for working with me.

Timothy

 

[jdraw]

I have read your tutorial. I had a good grasp on “Entity - Relationship”. I understand, further, how important enforcing these relationships in the records of this database is. At this point in my effort, I am only asking why the queries off tables 11, 12, and 13 are not 1:Many? I have checked everything I know how to and cannot see how these last three tables are different than the first 10 tables. Would it be possible only do address this question in your next reply?

I am only asking why the queries off tables 11, 12, and 13 are not 1:Many? I'm not sure what you mean by this. Aren't you asking about the relationship (not query)?

Quite honestly I don't know anything about the data or business detail, and anything I may say is pure guessing. It seems that each AssInspecID represents a unique asset inspection so I could see the relationship being 1 -1 between tAssInspec and all the tbl-01 thru tbl_13 and in fact they are. I see the relationship between tbl_CatWeight and all the tbl_01 thru tbl_13 as 1 to Many in all cases. I must be missing something based on your request.

The more I look at this the more I'm thinking this is likely set up specifically for summary reporting.
(All queries are Sum/Count related)??

I will comment though that if you are designing this database based on 3 existing databases and you don't know what the fields mean nor how the calculations are done or what they mean, you are working somewhat blind -- in my view.

Hope this is helpful to you.

 

[Timothy Andrepont]

JDraw,

Thanks for you help.

Let me try to give you a little history flow on this and the other databases.

First the data base we are presently working on is full of non-working queries and reports. The current database was generated without any date reference. I am trying to re-doing its structure without loosing data from the 13 tables. (Let me add here, the current database does not have many records. However, the other 2 database have thousands. I am hoping implement update into all database.)

Pre-modified version of the database we are working currently
did not have the following tables: tAsset, tAssInspec, and tInspection.

Tables: tblPS_GenData, tblPS_Master, and tblVisitDate will be deleted once all connection to them is severed. New tAsset has the data from both tblPS_GenData and tblPS_Master. Most of the queries are still setup to use the being deleted tables.

​

Maybe because I am so weak in design, I thought I could back into this rework by first setting up the structure around the new tAsset, tAssInspec, and tInspection tables and establish new relationship to existing entity data of tables 01 through 13.

It was while moving through this conversion process I noticed that I had inconsistency between the structure I require as shown in "Relationship View" and some queries (11, 12, and 13) that did not follow the designed relationship. This raised a red flag to me. After checking and re-checking all I could discover to check, I could not find my error. I thought it most be a simple overlook of some kind on my part, so I proposed the question.

I have reattached the "RelationShipDiscrip.pdf" in the Zip file. Page 1 shows the relationships I determined required. ( I fully appreciate it is likely this base relationship can be improved but that is another issue.) Pages 5 shows design view of "Qry_01_BS_Count_Sum" that maintains the desired relationship. This relationship is consistent for all Qry_01* through Qry_10*. But this relationship is lost in queries "Qry_11*" through "Qry_13*" as show in Page 7 for example. Why?

​

Thank for your patience in working with me.

Timothy

 

[jdraw]

Sorry --- I did just saw the relationship diagram in the pdf (pg 1), and not see the other pages.
I've looked at the query designs -- I get an error (data type mismatch) in Qry_11????
I've looked at the others and don't have an answer for you.

Do you have any description of what these queries represent that may reveal the 1 vs many?

tblCatWeight has the names of the Category_ID_txt and their associated weight factor Category_Weight.

Is it possible that the other 2 databases have all the detail info, and this latest database has only the summary info?

Where do you fit in the overall process? Who was responsible for developing, or ordering the development of, the other 2 databases? Who uses/used the other 2 databases?
My feeling is you must get some info about what you are working with before you can confidently move forward.

 

[Timothy Andrepont]

JDraw,

Thanks for your help.

I've looked at the query designs -- I get an error (data type mismatch) in Qry_11????
I've looked at the others and don't have an answer for you.

I have correct the data mismatch and now the Qry_11??? has the 1:Many relationship as expected. The remaining 12 and 13 queries are still out of sink.

Do you have any description of what these queries represent that may reveal the 1 vs many?

Each entity has up to 13 attributes. Each of query, "Qry_##_??*_CountSum",: counts, sums, averages, weights condition score found in corresponding numbered table.

Is it possible that the other 2 databases have all the detail info, and this latest database has only the summary info?

No, all three databases are independent within themselves. Only keyed in information is placed in each database.

Where do you fit in the overall process? Who was responsible for developing, or ordering the development of, the other 2 databases? Who uses/used the other 2 databases?

Presently, I am responsible for all activities concerning all three databases. I also enter the information into the data bases. That is why there is not yet any user interface. I am also responsible for providing reports.

My feeling is you must get some info about what you are working with before you can confidently move forward.

I have attached three working reports form one of the other databases. These reports represent a non-date reports for one time condition assessment. I need to provide similar reports by site visit date for existing databases as well as template for future Asset categories.

I hope this helps in someway.

I am off until Monday, have a great weekend.

Timothy

 

[pr2-eugin]

Is there a reason why you have in the Query 11, join between Category_ID_Txt and AssInspecID. Should the JOIN not be between Category_ID_Txt and OC_Weight_ID?

 

[Timothy Andrepont]

pr2-eugin and JDraw,

Thank you for looking into my simple issue. Eugin you are correct as far as the connection Query_11. I had caught that error somewhere along the way. One of JDraw's suggestions caused me to see this problem.

Now for the good news. I could find nothing wrong with the queries 12 and 13. I finally resorted to building new queries from scratch using the exact fields and formulas copied for each respective existing query. Access recognized I both new queries as having a 1:Many relationship as expected and desired with the Weight_ID fields. Problem solved, even thought I do not understand why it could not be corrected with code.

Until the next time, thanks for your help.

Timothy

 

[pr2-eugin]

Very glad you have sorted the issue.

The best thing of using Relationships defined.in the Relationship window is that when you create Queries between the table, the relationship/JOINS are auto inserted for you. When you manually try to change it that's where you come in trouble. I think that's what happened in your previous queries, you would have made joins on some other fields and when you actually try to create the true relationship it might not recognize it.

So as you have figured out creating a new query from scratch is the best solution

Good Luck !

 

[Timothy Andrepont]

pr2-eugin,

Thanks for the insight advice. It is helpful and seem to make since to me, a novice.

Thanks.

Timothy