Show/hide columns with specific text (1 Viewer)

In Access 2016 I have a table with results from a checklist. With a query I select those records where in one of 48 fields the text “NOT OK” is present. The output of the query is shown in a datasheet. 48 columns means a very wide datasheet, so I want to hide all columns where “NOT OK” is not present.

I know queries cannot hide/unhide columns so I probably have to use VBA. I was thinking of looping through each column, check column for “NOT OK” and hide if not found. But how do I check values in a column. Do I need to loop through each record as well?

In my search I found some topics about hiding empty columns, but it refers to reports which does not have the ‘on print’ event. Maybe using a recordset?

Or is there another way to accomplish this?

Thanks.

48 columns in a normalized table is very rare.
Perhaps there is a table design issue?
What exactly are the conditions for hiding?
Can you show us some samples? Raw record and desired output.

Thanks for your reply jdraw. Because I don’t have 10 posts yet I am not able to add screenshots or links yet. But I will try to explain in clear text.

First some background information. I have digitalized a paper checklist which our operators have to fill in. This checklist consists of 48 fields where they have to check “OK”, “NOT OK” or “N/A”. Besides those fields there are other text fields to describe found issues and how they solved it. There are also fields for project info, machine info, date/time, operator name and signature.

Project info, machine info and operator are separate tables. The huge table has all the check-fields (text), other text fields for descriptions, date/time field and signature (attachment) field.

This "tblChecklists" has a total of 65 fields. I tried to normalize it by creating separate tables: tblProjects, tblMachines and tblUsers. Would it be better to break tblChecklists up into multiple tables? Would it better perform when there is a lot more data imported? It only contains 50 records for now.

The conditions for hiding a column is as follows. Let's say I have a query with the following result, which is 'printed' on a form in datasheet view.

Col1 | Col2 | Col3
--------------------
OK | NO | OK
OK | OK | NO
OK | OK | NO

With this datasheet I would like to hide Col1, because everything is OK. Only Col2 and Col3 need to be visible.

Hope this clarify your questions.

Thanks.

You can post an attachment/file if you have < 10 posts
IF YOU USE ZIP FORMAT
see this

Here's the zip file.

Edit: zip file contains screenshots of the relationships, output of query and how I would like the output.

From the description you should have one table that holds "Machines", a second that holds "Check list items", and a third that would link a given "Machine" to a given"Check List Item" that holds the "OK/NOT OK" flag as well as the date of the inspection.

Going forward it will aid greatly in solving some of these issues.

Hi Mark,

Thanks for your reply. I redesigned my tables based on your recommendation. Is this what you meant? (see zip file)

With 60 checklist items and 45 machines which are daily checked, tblRegistrations will have 2700 records a day. In one month we already have 81.000 records. How will a query perform after 3 months? Or after 6 months with 500.000 records?

I have no experience with large Access databases so just curious. Or is 500.000 records still small?

I also keep in mind that we will expand this database with maintenance reports (in another table, I assume).

How will a query perform after 3 months? Or after 6 months with 500.000 records?

Click to expand...

Providing your data is properly indexed it should not be a problem

Consider also that text takes up a lot of space, so you could use byte numeric fields 0 meaning n/a,1 1 OK and 2 not OK

And you probably don't need to record the N/A records. The absence of a record can also have meaning - i.e. N/A

You could possibly even go one stage further and only record the not OK's, the absence of a records then meaning N/A or OK, and the N/A's can be identified in a separate list.

tblMachines
MachinePK autonumber
MachineName text (E9513, etc)
...
...

tblTests
TestPK autonumber
TestName text (ucTrackLinks etc)
...
...

tblTestsMachines
TestMachinePK autonumber
MachineFK long link to tblMachines
TestFK long link to tbltests
PassCriteria text (perhaps it varies from machine to machine)

tblTestFails
TestFailPK autonumber
TestDate date
TestMachineFK long link to tbltestsmachines

Of course, it may not be safe to assume the absence of a record is OK, so tblTestFails could be renamed tblTestOK so the absence of a record means a fail

@Stephan,

Take a look at the attached, it may be what you need.

Not the same industry but same concept. Add a record ONLY for what is needed. Show ALL available but let the user specify which one by entering a date. Still playing with this as I'll be doing some additions for how long vaccines are good for and when we are looking for them, plus making sure the date entered meets the cycles we go off of. For your application its pretty straight forward though; For any check that isn't OK, enter a date and hit the tab key. History shows which ones have failures.

Let me know if this is a solution for you!

Thank you both CJ and Mark for your recommendations and examples. I have changed my database design as you can see in NewRelations.jpg.

I added an extra table for item values. For now we have only OK, NOT OK and N/A. We have more paper checklists with other options. Some checks have 4 or 5 options. CJs solution will not work in that case, but this way I can easily expand the list with values.

One other challange I have is this. Some of the checklist items have an extra text field which is only used when the item is NOT OK. So that text field is bound to that specific item. Because text fields take a lot of space (like CJ mentioned) I thought it would be best to create an extra table. In screenshot option1 I have linked this table to tblRegistrations and tblItems. I'm wondering if this is a design issue, because I create a loop here? Should I just create a text field in tblRegistration like I showed in option2?

Now I write this down I'm thinking of a third option: using tblItemRemarks and link this only to tblRegistrations?

I also have some general text fields which are not bound to a checklist item. These fields will not be used often. Should I create an extra table for that too to avoid unneeded space usage?

You can use a query to add some normalisation to begin with.


Then you can either filter that query


or create a new normalised table from it.

Some of the checklist items have an extra text field which is only used when the item is NOT OK. So that text field is bound to that specific item. Because text fields take a lot of space (like CJ mentioned) I thought it would be best to create an extra table.

Click to expand...

empty text fields do not take up space, only the characters used so no need to have a separate table, just leave blank if not required

Some checks have 4 or 5 options. CJs solution will not work in that case, but this way I can easily expand the list with values.

Click to expand...

don't see why not, values could be 0,1,2,3,4... with a byte field type, up to 254 options

@Stephan,

Take a look at what I've attached. While the diagram may look to be a little... unusual... this is because part of what you think of as the "T_Machine" record is actually broken out through other files. For a given machine there is a Checklist of items that need to be checked. This is accomplished by having a "T_MachineType", so you can create a checklist for a type of machine. You also have general checklist items held in a "T_ChecklistItem" table surprisingly enough. These get put together in "Tl_MachineTypeCheckList". From a logical perspective this file really holds what would be the individual fields in your existing lay out.

From there, each day you would add an entry into "T_MachineInspection" with the current date. If you look at how I have the many to many example, it is currently asking for a date. For your use you would be looking for the inspection result. These are child entries under T_MachineInspection and use a link back to Tl_MachineTypeChecklist to identify which checklist item they are the response for.

For your use, you would want to build a query that returns ONLY those Tl_MachineTypeChecklist records that match the MachineType_ID in the parent machine to T_MachineInspection. If you use a continuous form, each would then look up the entry in Tc_InspectionChecklist for the current date and allow a user to update it OR would have a black that they can fill in to create an entry in Tc_InspectionChecklist.

It is a bit complex, but it will allow you to enter the items that are normally checked, assign them to a type of machine, and have those machines then have only the matching checklist that is appropriate displayed. This avoids the whole "N/A" issue. You would ONLY be entering values that are relevant.

It would probably take you a couple days to get this part working, but once you do you will be able to report for each machine what is wrong/right by simply going through the linking records.

NOTES on my naming conventions; I normally preface dates with Dt because I am far more likely to note anything ... odd ... happening if I can quickly pick out that its a date/time rather than any other type of variable. I also use _ID for autonumber ID's I am planning to use for linking. once more it helps me avoid using the wrong value. Likewise T is for table, Tl is a Linking table, and Tc is a child table. I've used rather expressive names to hopefully make it more clear what is connected to what.

Hope this helps!

Thanks for your replies.

@static: Normalisation with queries is an option, but I think it's better to redesign my database. But your examples will be usefull when I move the data to the new design.

@Mark: Your database is a bit complex indeed, but it makes a lot of sense. I will take closer look at it and expand the database with other info like users and projects. I also keep your naming conventions in mind.

In the meantime I started documenting the our business description and a functional description of the database. I will post that later.

Here are the business and database descriptions. It is probably not complete yet, but it describes most of it.

Business description
Snijder is a company that specializes in hiring out earthmoving equipment and concomitant technical services, operating around the globe. Equipment can be made suitable to the clients wishes or items can be custom made by our design and construction department. The projects are prepared and managed from our head office in the Netherlands.


Processes
Operators need to inspect the machine before they start to work. Technicians are onsite to do periodic maintenance and to resolve issues. On most projects a mobile stockroom and workshop is onsite. Changes in stock need to be synchronized with the head office by using forms as well (future feature).


Form workflow
• Forms are created on a third-party online platform.
• Via a mobile app the user downloads, fills in and sends the form.
• Forms are saved on the third-parties platform.
• Via API calls the forms are downloaded in JSON format to the head office.
• The JSON file is ready to process the data into the Access database.


Database business rules

A. Scope
Tracking machine and project history

B. The THINGS OF INTEREST include
1. Checklists
2. Maintenance reports
3. Damage reports
4. Machines
5. Projects
6. Users
7. Report issues by machine
8. Problem descriptions

C. These THINGS are related as follows
1. Operators fill in checklists before they start to work with the machine.
2. Technicians use more extensive checklists, also called maintenance reports.
3. Damage reports are used after accidents (for example when a machine is flooded).
4. Each type of machine has its own list of checklist items.
5. Each checklist will have a picture attached (signature, png file). Additional photo’s may be attached as well.
6. Each checklist item has an text field which is used only when an problem on that item is found.
7. Each report will include the user and project information.
8. Reports can be based on:
a. Machine – list all forms for given machine where errors occurred on one of the checklist items.
b. Project – same like a, but for all machines in the project location.

Attached is Marks database which I expanded with extra fields in T_MachineInspection and Tc_InspectionChecklist and I linked some extra tables. I also played with queries and reports to see what info I can get with this design.

I added data to T_MachineInspection manually, but it's a bit complicated to add matching data to Tc_InspectionChecklist. In SQL it would probably a lot easier? Do I use JOIN on every table link? I need to follow some tutorials on this.

Inspection data will not be added via forms, but is read from a JSON file. So I use VBA to get the data and SQL to import this into the database.

Question:
I'm not sure how Tc_InspectionChecklist get the checklist items. Or actually the other way around. When I have a list of checklist items and its results (JSON file), how would I add that to the miscellaneous tables?

Post 15 was moderated, I'm posting to trigger email notifications.

@OP,

Equipment can be made suitable to the clients wishes or items can be custom made by our design and construction department.

Click to expand...

For custom equipment, do you use custom check lists? If so, is the check list based off of the machines components?

@Mark

For custom equipment, do you use custom check lists? If so, is the check list based off of the machines components?

Click to expand...

We do not use a custom checklist for custom equipment. If the item is not on the machine they check N/A.

This afternoon I was discussing the database with a colleague. We have several 'versions' of checklists like these:

• The operator uses a daily checklist.

• The technician uses an extended checklist for, lets say, every 1000 working hours.

• There is a more extended checklist for every 5000 working hours.

• And when the machine comes back from the project it is completely overhauled, that's the most extensive checklist.

I was thinking of creating a new table T_ChecklistType and link it to Tl_MachineTypeChecklist. That way we can also define checklists for the special equipment. I think...