"Transpose" in the form

[Monardo]

Hello

Usually I "Google" the problems, but I could not find key words for my question.

I was told that if I have data in this shape:

Number ¦ Test1 ¦ Test2 ¦ Test3
------------------------------------
000001 ¦ Pos ¦ Neg ¦ Pos
000002 ¦ Pos ¦ Pos ¦ Neg

It is always better to make table in following way:

Number ¦ Test ¦ Result
---------------------------
000001 ¦ Test1 ¦ Pos
000001 ¦ Test2 ¦ Neg
000001 ¦ Test3 ¦ Pos
000002 ¦ Test1 ¦ Pos
000002 ¦ Test2 ¦ Pos
000002 ¦ Test3 ¦ Neg

All is fine, until I decide to make my form, which I want to look as follows:

Record1
-----------------------

¦ Number: 000001
¦ Test1: Pos
¦ Test2: Neg
¦ Test3: Pos
​

-----------------------

Record2
-----------------------

¦ Number: 000002
¦ Test1: Pos
¦ Test2: Pos
¦ Test3: Neg
​

--------------------------

Naturally, I put [Number] in "One" table, [Test] and [Result] in "many" table and made one-to-many relationship, but when it comes to subform, it is always datasheet format and [Test] must be entered for each record, while I want that it is Single Form with Test1, Test2, Test 3 pre-entered.

Basically I need the form to look like as if it was created from the first (wrong) table above.

Is it possible?

 

[VilaRestal]

Yes but with code: unbound textboxes for the tests and results.

If it's for display purposes it would reasonably easy. If it's for editing then getting the changes back into the database (and especially allowing users to enter more tests) makes it more complicated.

So these questions first:

Would you want the user to be able to edit Test Results?
If so would you want the user to be able to edit Test Names?
If so would you want the user to be able to add or delete Tests?
If so what's the maximum number of tests a Number could have? (Note: a subform would not have that limitation)

It's a lot of work.


A subform can be made to look like it isn't a subform if it is flat, has no border, has no dividing lines or record selectors or navigation buttons or scroll bars.

I'd think very carefully about using a subform. It would be trivial in comparison.

 

[Pat Hartman]

Are there always 3 tests? Are there sometimes fewer? What will you do if tomorror there are 4?

This is a typical 1-m situation. Once you have more than 1, you have many. Many requires a second table and is best depicted with subforms and subreports. As Vila said, you can make the subforms almost disappear so they are not intrusive.

 

[Monardo]

Thank you for your responses, here I will give more details as requested:

1. I want the user to add/edit test results, that is the whole purpose.
2. Test names and amount of tests per number (which is patient) are fixed and they do not change.
3. There are 168 tests per number (that is the reason why I want data vertical and not horizontal)

The idea is that these are laboratory test results, which are printed out by machine. Each printout contains 168 test results (plus some Patient ID data). My goal is that data entry form looks exactly as the printout, to make it easy for "enterer" and minimize entry errors. I would not mind that it is "wrong" and I would put everything in one table as one record, but 168 is a bit too long. Therefore I was wondering if there was more "elegant" way of putting the records vertical, but displaying for data entry as if it was one record

 

[VilaRestal]

OK, then definitely do it as a subform.

First thing is to have code that will add all 168 tests (without results) to a number when a new number is made.

The subform will be continuous form view, additions, deletions and filters not allowed, flat, no border, no record selectors or navigation buttons or dividing lines. Vertical scroll bar only.

And that's essentially it. You might want to create a report for printing.

Immensely easier than doing it the other way. That would involve a lot of laborious naming of controls and a lot of code that would be very easy to get wrong.

 

[Monardo]

OK, then definitely do it as a subform.

First thing is to have code that will add all 168 tests (without results) to a number when a new number is made.

The subform will be continuous form view, additions, deletions and filters not allowed, flat, no border, no record selectors or navigation buttons or dividing lines. Vertical scroll bar only.

And that's essentially it. You might want to create a report for printing.

Immensely easier than doing it the other way. That would involve a lot of laborious naming of controls and a lot of code that would be very easy to get wrong.

Well, I came to this point myself, but problem starts after... As you said the subform would be continuous, but I need to rearrange 168 textboxes in 12x14 grid so that it looks like the printout.

And more and more I am afraid it is not possible?!

 

[VilaRestal]

but I need to rearrange 168 textboxes in 12x14 grid

Now you didn't mention that before.

That certainly is not possible in a subform.

However, it could be done in 12 subforms. Each one filtered to show a certain range of 14 tests.

Get one working correctly and then copy and paste it 11 times and change the filters on each.

 

[Pat Hartman]

Hopefully the numbering is down and accross because if it's accross and down, the selection criteria will be a b**** I've used this technique myself several times and it is an excellent compromise to an unbound form when the user doesn't want to give up the "spreadsheet" look.

 

[Monardo]

Thanks everyone for effort.

I tried every idea, but finally decided to do it "wrong" way (everything in one record). That was the only way to achieve what I wanted.

 

[Pat Hartman]

168 tests in each record? You'll be sorrrrrry

 

[spikepl]

The idea is that these are laboratory test results, which are printed out by machine. Each printout contains 168 test results (plus some Patient ID data). My goal is that data entry form looks exactly as the printout, to make it easy for "enterer" and minimize entry errors.

HUH ? You want to have someone to LOOK at machine-generated output and then type 168 values in ?!

1. Can you get it from source?
2. Can you print to a file and then read that file?
3. If the thing prints to PDF there are ways to machine-read a PDF. If it doesn't then get a free PDF writer, and set the machine to print to that. Unless the thing is married to a printer.
4. You can always scan printed output and OCR it

 

[Monardo]

Well, I am already sorry for 193 fields in one record, but that was only way for me to do the following form.

I am still open to suggestions, if anyone can achieve the same shape of the form with more elegant table behind.

P.S. OCR and/or other options are out of question in this case.

 

[gemma-the-husky]

the structure should not/does not have to follow the form.

the structure should be normalised.


the problem with structuring a form in the way you have is two fold

a) i presume your table also has 193 fields, (or whatever) - which will make comparing similar data in diffierent columns a nightmare, becuase the structure does not appear to be normalised

b) and you are probably storing stacks of null/empty feilds

c) and if your data scheme changes you have a nightmare redesigning forms




now one way you achieve this layout from a normalised structure is by summarising the normalised data into a temporary table for presentation means.

 

[Pat Hartman]

Based on the visual structure, there is an easy compromise that works with only 4 tests per row. Still not normalizes but a lot closer.

FK (I can't determin this from the picture)
LeftColumn (I don't know what the name of this is)
TopRowHeader (?)
TestA
TestB
TestC
TestD

Your form would have 7 subforms. The query for the first would select TopRowHeader = "D3". The second would select "D7", the third, "D14", etc.
You would set the form to not allow additions because you don't want a "new" row at the bottom and then size it's height to fit 6 sets of data as you've shown.

This schema will still cause some problems with analysis but nothing like you'll face the current way.