Weekly Test of Devices Tracking (1 Viewer)

[Malcolm17]

Hello,

Part of my database is for tracking testing of devices, we test one device each week and record the test date. I am looking to do 2 things, but I cannot think how to do them:

Device 1 16/04/2022
Device 2 23/04/2022
Device 3
Device 4

1. I want to show on my form the next device to be tested - so in the example above Device 3 would show as it has not been tested yet. (After we run the test then a button on a form updates the date using RunSQL Update command.)

2. Once all devices are tested then I want to delete all dates and start from the beginning again.

Can anyone think how I can do this please?

Thank you,

Malcolm

 

[June7]

Could use an unbound form with 4 sets of controls and VBA would save inputs to table then clear the form for next set.

You have only 4 devices? What if you need to add more?

 

[Gasman]

I would not be deleting the records, just add more with more recent dates.

 

[Malcolm17]

I would not be deleting the records, just add more with more recent dates.

Hi Gasman,

Yeh that would make sense, I keep the record of testing with details etc in another table, the table I am speaking about is the table which holds the device names and is really just to show what is left to test, the idea is the next blank date record is the next to test, but I cannot work out how to identify this. I have 22 devices and will need to add more.

Malcolm

 

[Malcolm17]

Could use an unbound form with 4 sets of controls and VBA would save inputs to table then clear the form for next set.

You have only 4 devices? What if you need to add more?

Hi June7,

I have 22 devices just now and adding more, I just used 4 as a quick example. Do you have an example that might help me or can you point me in the direction that you are thinking please?

Thank you,

Malcolm

 

[jdraw]

It might help communications/readers if you showed us your tables and relationships.

 

[Malcolm17]

Hi,

Yeh that would make more sense. I will extract part of it and upload it.

Malcolm

 

[CJ_London]

assuming your example is realistic - and deviceX is the order of testing you could try something like

SELECT TOP 1 *
FROM myTable
WHERE testDate is Null
ORDER BY DeviceName

 

[The_Doc_Man]

You probably would do best to have a "device" table with a device ID number as primary key, plus a separate "test" table for which the device ID is a foreign key and the date of that device's most recent test is a data item. Then you could do a query sequence similar to

Query1:
SELECT DeviceID, Max( DevTestDate ) As LatestTest FROM DevTest GROUP BY DeviceID

Query2:
SELECT DV.DeviceID, DV.DeviceName, DV,DeviceLocation, ...other device info, DT.LatestTest
FROM DeviceTable DV INNER JOIN Query1 DT ON DeviceTable.DeviceID = DevTest.DeviceID ;

Of course, query2 is building on query1. There is also the assumption that the first time you add a new device, you test it (as a performance baseline if nothing else) so that the INNER JOIN for new devices in query2 will have something to go on.

Using query 2 you can build a report showing each device's lastest test. By modifying the queries you can select only those devices with tests less recent than 1 month (or whatever number of days you want). By keeping a pass/fail item in the DevTest table you can show only those devices that failed their last test. In other words, that combination of queries becomes the starting point for any of several possible directions.

EDIT: Fixed a bad typo.

 

[Gasman]

I would probably have a device table with the device name and sequence indicator with testing interval. Then a table that records the testing.
Then check when a device need testing and advise a day or so before? Up to you if you forewarn?

Edit: Doc got there whilst I was typing on my phone

 

[June7]

I don't think the unbound form approach is best.

It sounds like you want to batch create a set of records. Then simply enter the test results without have to select each device one at a time to create record.

INSERT INTO TestData(DeviceID) SELECT DeviceID FROM Devices;

Then when you want to enter data, open a form that retrieves records where TestDate Is Null.

DoCmd.OpenForm "Tests" , , , "TestDate Is Null"

Form can have RecordSource like:

SELECT TestData.*, DeviceName FROM TestData INNER JOIN Devices ORDER BY DeviceID;

Bind textboxes to DeviceID and DeviceName fields and set TabStop No and Locked Yes.

 

[Malcolm17]

Hi All,

Thanks for your responses, I have attached a mockup of the part of my database that I am seeking help on - hopefully this will give a better idea of what I am looking to do.

Malcolm

 

[June7]

Database does not seem to incorporate suggestions already provided.

There is no table for test results.

There are no primary key fields defined in Devices and Employees. The Order field could serve for that purpose in Devices. Employees has StaffID field but it is text type and should be number or autonumber field.

HealthSafetyKeyDates table does not have relationship to Devices.

Or perhaps need more info on business model. Are these devices repeatedly tested (one per week) or is each device tested only once and never again?

 

[Pat Hartman]

You need a device table and a test results table. Then for simplicity, you can have a to be tested table. To start the process, run an append query that selects all the unique devices and appends a row to the to be tested table. Then the process each week is to run a query to select the top record from the to be tested table with a null date. That is the record to be tested. When the test results are reported, delete the tested record from the to be tested table. When no records are selected from the "top" query, run the append query again to populate the to be tested table.

This method is a bit clunky. I'm sure someone can build you a clever query but my brain is too foggy this morning.