JSON converter for Access (1 Viewer)

Zydeceltico

Registered User.
Local time
Today, 10:27
Joined
Dec 5, 2017
Messages
843
Hi All,

I'm designing a DB for the quality assurance department of a steel manufacturing plant. The primary data collection is from inspections of materials and products as they are delivered, rolled, welded, painted, fabricated, etc.

The challenge is that all of the various TYPES of inspections share some common fields - a few - but all inspections have far more non-common fields. So...when thinking about table structure, there is quite a bit of redundancy if I went with the traditional RDBMS approach to table structure. I will have a huge table with hundreds of fields defining various inspection criteria and the vast majority of the cells will be blank. I personally don't like that design. It doesn't feel normalized.

It was suggested to me that I look at an EAV design which I'll admit, I could conceptualize, but found too complicated to implement - primarily because 60% of the DB lends itself strongly to RDBMS.

My current workaround is that I have one "umbrella" table (tblInspectionEvent) that collects all of the shared data and a separate table for every TYPE of inspection (e.g., tblWelding, tblPainting, etc). It works a charm for data entry. There are two challenges though with this approach. The queries for reports are extremely complicated (at least for me) with joins and such and if the VP of Engineering wants a report on ALL TYPES of inspections that were made on any given job, given that each type of inspection has all of its own inspection criteria, it is a real bugger to aggregate all of that into a single report.

So I've been toying with the idea of utilizing the JSON datatype (which is not natively supported by Access) for a variety of reasons including moving the front end to a browser-based solution (for a variety of reasons that I won't go in to here). My primary curiosity about JSON datatype is that appears that it can be utilized more robustly at the point of data entry than a typical RDBMS style approach. We do a lot of spot check inspections where we do not do full-blown inspections and only collect a few pieces of data (maybe only one) versus a full blown piece inspection which may have twenty or more fields that have to be entered.

While I was exploring this idea, it was suggested to me on this forum by isladogs, that I look at this piece of software that he developed: JSON converter for Access

I've only briefly perused the manual but it looks very promising. One immediate question: how does the converter handle updates to a table? Simply adding more rows instead of deleting existing? I'm sure the info is either in the video or in the manual but I thought I would ask more immediately.

It looks very promising and worth a look for anybody else who may be considering the JSON datatype.

Thanks,

Tim
 

isladogs

MVP / VIP
Local time
Today, 14:27
Joined
Jan 14, 2017
Messages
18,186
The most common use is to import downloaded JSON data from the internet or a saved file.
Therefore, by default, the JATFA app prevents duplication by deleting existing records before appending new records.
This is handled automatically by the transform function generated by the app.

However, the app is supplied as an ACCDB file with full access to the code.
That means anyone using the app can change that behaviour if preferred to append new records whilst retaining all existing records.
Duplicates can be prevented in the usual way using indexing.

For anyone interested, I'll attach a zipped copy of the user guide.

NOTE: JSON Analyse & Transform for Access (JATFA) is a commercial app which does far more than convert JSON files.
A free cut down evaluation version is also available from the same web page but isn't as fully featured


However, please send me a PM or email if there are any specific questions about this app rather than JSON files in general,
 

Attachments

  • JSON Analyse & Transform User Guide.zip
    2.4 MB · Views: 310

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:27
Joined
Sep 12, 2006
Messages
15,613
I struggle to see how JSON or XML can be used as a replacement for a database, to be honest.
 

Zydeceltico

Registered User.
Local time
Today, 10:27
Joined
Dec 5, 2017
Messages
843
I struggle to see how JSON or XML can be used as a replacement for a database, to be honest.
It wouldn't be a replacement. I'm just exploring options for designing a front end for an EAV backend - I'm ADHD and find that I function best by ....oh...I don't know - exploring. :)
 

LarryE

Active member
Local time
Today, 07:27
Joined
Aug 18, 2021
Messages
562
You can still use ACCESS for this. I just completed a project for an aerospace firm that had very similar requirements. They had various Equipment Types instead of manufacturing inspection types, but the actual table design is similar to what you are describing. You have:
  1. Multiple manufacturing inspection types (performed at various stages).
  2. Each with its own set of inspection requirements.
  3. Each individual inspection requirement could be performed by various inspectors on various dates
  4. Each with an inspection result.
This is the kind of thing ACCESS is designed for. Let me adapt the database I designed before and see if you can use it. I will attach the example and table design example.
 

isladogs

MVP / VIP
Local time
Today, 14:27
Joined
Jan 14, 2017
Messages
18,186
Just for clarification, JSON data is analysed then imported into Access tables.
From that point onwards, it becomes like any other standard data in Access.

The main use is for handling the wide variety of data available now provided online in JSON format.
The JSON format is more versatile for complex data than using a CSV flat file and more concise than XML
 

Zydeceltico

Registered User.
Local time
Today, 10:27
Joined
Dec 5, 2017
Messages
843
You can still use ACCESS for this. I just completed a project for an aerospace firm that had very similar requirements. They had various Equipment Types instead of manufacturing inspection types, but the actual table design is similar to what you are describing. You have:
  1. Multiple manufacturing inspection types (performed at various stages).
  2. Each with its own set of inspection requirements.
  3. Each individual inspection requirement could be performed by various inspectors on various dates
  4. Each with an inspection result.
This is the kind of thing ACCESS is designed for. Let me adapt the database I designed before and see if you can use it. I will attach the example and table design example.
Thanks Larry! I would love to review what you've done. What you describe is definitely in the my ballpark.
 

Zydeceltico

Registered User.
Local time
Today, 10:27
Joined
Dec 5, 2017
Messages
843
Just for clarification, JSON data is analysed then imported into Access tables.
From that point onwards, it becomes like any other standard data in Access.

The main use is for handling the wide variety of data available now provided online in JSON format.
The JSON format is more versatile for complex data than using a CSV flat file and more concise than XML
Yep - and that is exactly how I am envisioning utilizing it. The idea is still in its infancy but that clarifies what I was thinking. I'm thinking more about how to collect the data rather than how to store it - which isn't exactly correct either - but I'm wondering if I can't revisit the EAV-type design (in Access) with a single table if the collected data is in a different format initially (JSON) giving the user more flexibility and latitude in what is collected. Of course, this would mean using a browser-based GUI FE but that's OK. Reports can be exported on a weekly basis from the Access BE via pdf which would be totally acceptable. Certainly a step in the right direction. ....or I'm delusional.....lol....either way I'm enjoying the Journey of Discovery.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:27
Joined
Oct 29, 2018
Messages
21,357
...anybody else who may be considering the JSON datatype.
Just a little nit pick... JSON is not a data type, just like XML or HTML is not a data type either. Rather, it's just another way to mark up or notate an otherwise plain text information into some sort of a structure.

You may also have the wrong idea about how JSON is used on the web. Rather than store data in JSON format, websites actually store the data in a database table or tables and simply use JSON to "share" that data with other systems or users.

So, if I was going to integrate with another (external) system that accepts JSON data, I would probably store that information in tables and then export them into a JSON file. Similarly, when I receive JSON data from outside sources, I would probably parse them and store them in my normalized tables.

Just my 2 cents...
 

Zydeceltico

Registered User.
Local time
Today, 10:27
Joined
Dec 5, 2017
Messages
843
Just a little nit pick... JSON is not a data type, just like XML or HTML is not a data type either. Rather, it's just another way to mark up or notate an otherwise plain text information into some sort of a structure.

You may also have the wrong idea about how JSON is used on the web. Rather than store data in JSON format, websites actually store the data in a database table or tables and simply use JSON to "share" that data with other systems or users.

So, if I was going to integrate with another (external) system that accepts JSON data, I would probably store that information in tables and then export them into a JSON file. Similarly, when I receive JSON data from outside sources, I would probably parse them and store them in my normalized tables.

Just my 2 cents...
two cents is worth a dollar to me right now :) THX

I don't mind be incorrect or wrong. I do mind not knowing. :)
 

LarryE

Active member
Local time
Today, 07:27
Joined
Aug 18, 2021
Messages
562
I was envisioning something like this:
1646763143366.png
So that each inspection type:
  1. may have multiple inspection dates and inspectors and
  2. has its own set of inspection requirements based upon inspection type and
  3. each inspection date and inspector has their own inspection result.
 

Zydeceltico

Registered User.
Local time
Today, 10:27
Joined
Dec 5, 2017
Messages
843
I was envisioning something like this:
View attachment 98944
So that each inspection type:
  1. may have multiple inspection dates and inspectors and
  2. has its own set of inspection requirements based upon inspection type and
  3. each inspection date and inspector has their own inspection result.
Where tblRequirements is an all-inclusive set of fields representing all possible requirements across all inspection types?

I like that. Especially now that I am almost two years into the current DB and have a very good understanding of what will and won't be inspected.
 

LarryE

Active member
Local time
Today, 07:27
Joined
Aug 18, 2021
Messages
562
Where tblRequirements is an all-inclusive set of fields representing all possible requirements across all inspection types?
The requirements table holds the various inspection requirements for each type: Painting, Welding etc. but the requirement is input into the Requirement field together with a Requirement Number that can be changed. Each requirement is NOT its own field. So Painting has its own set that has been input, Welding has its own set that has been input etc.

Painting might have a requirement that the paint fully covers everything, for example. Welding might have a requirement that welds are not cracking, for example. Then, when each inspection is completed, the Results table holds the results for each inspection date and inspector for each requirement. So you have a standard set of inspection requirements for each type that can be used over and over again. But you can easily add and delete requirements too. Maybe some inspection requirements need to change or be deleted from time to time. You don't want to be adding or deleting fields from the tables. You want to be adding or deleting RECORDS from tables, not FIELDS.

Does that make sense?
 

Zydeceltico

Registered User.
Local time
Today, 10:27
Joined
Dec 5, 2017
Messages
843
The requirements table holds the various inspection requirements for each type: Painting, Welding etc. but the requirement is input into the Requirement field together with a Requirement Number that can be changed. Each requirement is NOT its own field. So Painting has its own set that has been input, Welding has its own set that has been input etc.

Painting might have a requirement that the paint fully covers everything, for example. Welding might have a requirement that welds are not cracking, for example. Then, when each inspection is completed, the Results table holds the results for each inspection date and inspector for each requirement. So you have a standard set of inspection requirements for each type that can be used over and over again. But you can easily add and delete requirements too. Maybe some inspection requirements need to change or be deleted from time to time. You don't want to be adding or deleting fields from the tables. You want to be adding or deleting RECORDS from tables, not FIELDS.

Does that make sense?
It does to a degree. A little more explanation on RequirementSelection and RequirementNum?

I can see RequirementID, InspectionType_ID, and Requirement. But what are RequirementSelection and RequirementNum used for exactly? In my pea-brain, I can see getting away with the first three -except some Requirements with be on all Inspection Types
 

Zydeceltico

Registered User.
Local time
Today, 10:27
Joined
Dec 5, 2017
Messages
843
The requirements table holds the various inspection requirements for each type: Painting, Welding etc. but the requirement is input into the Requirement field together with a Requirement Number that can be changed.
??
Capture.JPG

Each requirement is NOT its own field. So Painting has its own set that has been input, Welding has its own set that has been input etc.
This part I understand.
 

LarryE

Active member
Local time
Today, 07:27
Joined
Aug 18, 2021
Messages
562
I used Requirement Number to give users a chance to number their requirements in the order they want. You may want certain inspection requirement to occur before another so you can order them any way you wish and they will be sorted in that order on the form.

I use the Selection field in each table so that if you use continuous forms where all records can be viewed at one time with scroll bars, then each record can be selected individually which is important when adding new records. If you use single forms where each record is displayed individually, then you would not use the Selection field. For example, using continuous forms, you can view all
??
View attachment 98947

This part I understand.
RequirementSelection and all Selection fields I use as Yes/No with a Checkbox control on the form. Requirement number is just a number field users can enter numbers to use to order their Requirements.

I am so sorry but my WiFi service failed this morning and the tech just repaired it. My internet connection obviously was disconnected. I am working on a model with forms so you can see what it look like. I am not using continuous forms so some of the Selection fields are not going to be used. I'll attach the file tomorrow so you can play with it. It's really not that complicated, since I've done it before.

Of course you can add fields where you need them.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:27
Joined
Feb 19, 2002
Messages
42,970
Here's a picture of a form that uses an EAV design. This is an application that is used to produce all the documents required to issue an insurance policy. Each type of policy has different data requirements. Hence the EAV design. So the fixed properties are columns as you would normally expect but the other columns vary by policy type. When you create a new record for a customer, the policy type combo determines what fields are required and they are all appended in the afterUpdate event of the main form. This particular app is very flexible and is controlled by the user rather than the developer. I designed it so that if they started issuing a new type of policy and needed to add new data fields, they could. In your case, the data fields are fixed unless you get something new but it's probably something they want the developer to do for them than for them to do for themselves.

In order to handle the "required" aspect of data, it is the Word automation part that pulls all the EAV data for use in a word document and as each record is read, its properties are checked and if it is required for this document, then the value of the field must be not null.

If you want more details, I can go into the supporting tables if you want to go this way.
DGT_DataEntry.JPG
 

Zydeceltico

Registered User.
Local time
Today, 10:27
Joined
Dec 5, 2017
Messages
843
I used Requirement Number to give users a chance to number their requirements in the order they want. You may want certain inspection requirement to occur before another so you can order them any way you wish and they will be sorted in that order on the form.

I use the Selection field in each table so that if you use continuous forms where all records can be viewed at one time with scroll bars, then each record can be selected individually which is important when adding new records. If you use single forms where each record is displayed individually, then you would not use the Selection field. For example, using continuous forms, you can view all

RequirementSelection and all Selection fields I use as Yes/No with a Checkbox control on the form. Requirement number is just a number field users can enter numbers to use to order their Requirements.

I am so sorry but my WiFi service failed this morning and the tech just repaired it. My internet connection obviously was disconnected. I am working on a model with forms so you can see what it look like. I am not using continuous forms so some of the Selection fields are not going to be used. I'll attach the file tomorrow so you can play with it. It's really not that complicated, since I've done it before.

Of course you can add fields where you need them.
Thank you. I am looking forward to it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:27
Joined
May 7, 2009
Messages
19,169
if you are using "Standard form" to enter your inspection, you can even
have it put to a Fillable form and use it instead.
the filename can be the same as the inspection number, so
you can link the pdf file to inspection number and ready for
editing/viewing.
 

SHANEMAC51

Active member
Local time
Today, 17:27
Joined
Jan 28, 2022
Messages
310
if the VP of Engineering wants a report on ALL TYPES of inspections that were made on any given job, given that each type of inspection has all of its own inspection criteria, it is a real bugger to aggregate all of that into a single report.
In fact, there are two tables – they are enough for an overview NTM report for any object

The third table is EAV, so as not to depend on real checks

For the convenience of further reports, the lists of requirements are divided into parts, since many things are repeated in different requirements regulations

Event table- it can be displayed in the NTM in various sorts and selecting the necessary ones, when you click on MORE DETAILS go to table 2

When you click on join, all join checks are displayed

When clicking onSending an order to a customer showing all shipments

You can select events by period or those with welding control.

It's all quite simple

Event CodeДатаEvent dateobjectinspectorverification goalnotesaction
112/2/2022order 1/2022joinSending order to customerdetails
210/2/2022delivery 2/2022pitchecking availabilitydetails

Table of checks for event 1 – again, a choice is possible


Verification codeVerification code eventcode verification dateкод requirementcode name of the requirementnotes
1112/2/2022Welding 3.1Welding controlReturn to the main page/
learn more
more 2112/2/2022Packaging 3.1Checking the packageReturn to the main page/
learn
more 3112/2/2022Packaging 3.2FillingReturn to the main page/
learn
more 4112/2/2022Loading 3.1LoadingReturn to the main page/
learn more
Table of checks for event 2

Verification codeVerification code eventcode verification dateкод requirementcode name of the requirementNotes
5212/2/2022Input control 2.1External safety of packagingReturn to the main page/
learn
more 6212/2/2022Input control 2.22Checking sealsReturn to the main page/
learn
more 7212/2/2022Input control 2.33UnpackingReturn to the main page/
learn
more 8212/2/2022unpacking 4.1unpackingReturn to the main page/
learn
more 9213/2/2022Chemical controlTaking samplesReturn to the main page/
learn more
 

Users who are viewing this thread

Top Bottom