Expanding subform (1 Viewer)

[PeterWieland]

I have searched for ages and can't find quite what I am looking for.

I want a subform, in table format, that expands depending on how many records there are. I will try to explain.

The parent form brings up the inventory record for a particular piece of equipment e.g. a desk lamp which has all the basic details (make, model, serial no, inventory number, purchase date etc.)

I then want a subform that will display all of the test results over time in a table. The rows will be the test results e.g. earth continuity, insulation resistance, leakage etc. Each column will be headed by the test date, with a final column for creating a new test record. This way I can see at a glance if a piece of equipment is starting to deteriorate.

	Mar-17	Mar-18	Mar-19	NEW TEST DATE
Visual (Pass/Fail)	Pass	Pass	Pass
Fuse (A)	13​	13​	13​
Earth Continuity (Ω)	0.054​	0.057​	0.058​
Insulation Resitance (MΩ)	>300	>300	>300
Leakage (mA)	<0.30	<0.30	<0.30

This is something like what I am trying to acheive.

The built in tabular and datasheet layouts don't do exactly what I am looking for.

Thanks in advance for your help.

 

[Minty]

You are want to be looking at a crosstab query and make that the object in your subform.

Personally I would do it the other way around your tests are fixed, so have them as the column headings, then a simple continuous subform with the date as the first column would work.

TestDate	Visual (Pass/Fail)	Fuse (A)	Earth Continuity (Ω)	Insulation Resitance (MΩ)	Leakage (mA)
01/03/2017​	Pass	13	0.054	>300	<0.30
01/03/2018​	Pass	13	0.057	>300	<0.30
01/03/2019​	Pass	13	0.058	>300	<0.30

If you reversed the sort order the last test would be on top to save you scrolling to the end...

 

[PeterWieland]

Thanks for the reply.

Unfortunately it does need to be the other way round. The set of fields is only an example, there are several more and don't fit easily on a screen width. The number of dates (i.e columns) would rarely go above 5 (5 year write-off of electrical equipment) and never more than 10.

 

[Minty]

For ease of creation and programming, I would still go the "vertical" route and simply have a scroll bar, but it's your data so...

Investigate creating a crosstab query to get you results.
You can then simply set that as you subform source.

Assuming you have a unique EquipmentRef, put that in the criteria as a named Parameter and it should pull in the data you want for the equipment id in the main form