One Form for multiple devices vs single record (Test Event)

[zkrucz]

My company inspects backflow (BF) preventers (potable water devices that prevent water from siphoning back to the source).


These devices have unique serial numbers. We have 60 customers, each having from 1 to 25 such devices. I have a Table which lists each of these devices by customer and BF Serial #.

I also have another Table with records showing the Test Day of each device which are all tested at the same time (Test Day) event.
I can link both tables by the Unique BF Serial#.

My problem is: how to create a single form in which I can show all Test Day records (Fields) for each customer (the number of pages in the Form would vary from 1 to 25) dependent on how many devices each customer has.

I want the results for each BF device to appear in the same space (“window”) as that of the remaining devices (the number of pages would vary from 1 to 25). This would be to duplicate manual forms used at present time.

Otherwise, the only solution that I see is to create individual Forms (similar to the current manual forms), one for each BF device and filter the Records for each customer by for each Test Day record for each BF device (1 through 25), which is doable but unwieldy.

[RuralGuy]

Can you create a query that displays the records you desire?

[ranman256]

you can have a single record master form, CUSTOMER
subform to show their devices,
another subform to show the tests on a selected device

be sure to bind the LINKED MASTER FIELD & LINKED CHILD FIELD.

[Micron]

You should have tblCustomers with all the customer data; name, address, etc - nothing about valves. Use autonumber ID as first field.
Same for tblValves - autonumber id, customer id as a foreign key (fk), then serial, maybe physical characteristics, maybe a descriptive field re location (notes)
You'd also need a table for inspections - autonumber inspection ID, valve IDfk; not sure about the rest of that or the need for any other tables without knowing more background. You would not usually have a field for every inspection parameter as that is a spreadsheet approach.

Use a single main form for customer and a subform for valves. The 2 are linked by the customer id and when you navigate from one customer to another, the subform will list their valves. You might need a 2nd subform for inspection details.

[zkrucz]

@ranman256 and @Micron

Would a crosstab query work in this case as the source for my single Form?

[Micron]

Not if you want to be able to edit data. A ct query is not updatable IIRC. Certain that you cannot add records through it though. It seems you don't like the suggestions given?
BTW, I was trying to listen to someone on the phone and type my response at the same time, so it took too long and I didn't see that there was already a response. Interesting that they are basically the same. You might want to give that some weight.

[zkrucz]

@Micron


It’s not that I don’t like the idea. But, I have about 40 fields (each having 1 to 25 values) that I need to link to its own “box” (as in manual form). So, I think I would need to create 40 individual Sub-forms to do that, correct?

[Micron]

each having 1 to 25 values

What does that mean? A field might not have anything in it in 1 or more records? Or any given field might have 25 values in one record? The former should not be an issue. The latter most likely an issue. I think part of the problem is your terminology. F'rinstance, forms don't have pages but reports do. Tab controls have pages though. I am also quite certain that your basic problem is that your table schema is not normalized -

all Test Day records (Fields)

Records should be kept in a vertical fashion, not horizontal like a spreadsheet. This fact isn't to be taken lightly. If that is what you have done, you will always find there is another issue as soon as you solve one, and some will be difficult if not impossible to solve. One thing you could do is post something that gives a clue as to your schema. Maybe a spreadsheet dump of your tables (it's an easy way to create an html table here); maybe a pic of your relationships if you have created relationships. Or post a zipped db copy.

[zkrucz]

@ Micron,
Yes, I agree with you regarding “vertical” record keeping. However, what I did not tell you prior, is that the Table with the BF data is generated via import of data from Technician IPads. Unfortunately, the provider of the iPad service utilizes the spreadsheet format : data comes in “horizontally”.
I had been trying to create the subforms as you recommended above and my effort is not coming out as intended precisely because of the Table spreadsheet format.
I have been scratching my head and reading up on queries and came across a Union Query. My thought is to created 25 Queries for each BF_ID (Serial #) and then use the Union Query to aggregate all 25 under general fields representing the unique fields (shown in the 25 Queries). And, then create one form using the Union Query. However, I have never created a Union Query and would welcome your input how to get that done.
Finally, another potential complication is that part of the tablet-initiated Table (iPad service), we collect signatures. Those images are also imported into the Table as Attachments. Not sure if a Union Query would aggregate those attachments, at all.
We are working to move those images to a Table outside of the Access DB but this is the situation with which I am dealing with now.

[Micron]

My thought is to created 25 Queries for each BF_ID (Serial #)

I probably would look for a better way. If you need to edit, you could end up having to modify 25 queries each time there's a change. There is also a union limit, although I suspect it's higher than 25.

One way would be to create a table designed like the spreadsheet to accept the data, then use it in however many queries are required to append the records to the appropriate tables First time this would possibly be make table queries, thereafter, appends and/or updates. Surely this would not be 25 queries? That would mean you'd have 25 tables. You could bypass the first table stage by linking to the spreadsheet as a linked table, unless users are adding a workbook file each time they upload. That would be harder to manage.

Another is to use automation whereby code copies spreadsheet column data to the appropriate tables and fields.

[ArviLaanemets]

An example how to display test results with normalized table structures

[zkrucz]

@ Micron

Looks like the Union Query will solve the problem for me in this situation. I tested it out without including the Attachments (images of signatures) and I am almost there except I ran into issues issues with my Attachments. Typically the Union Query uses square brackets to enclose the [field name]. How do I include the attachments?

[Micron]

I have never done that, so this is a guess. Create and save your union query but don't include attachment fields. Then in a new query, add the union query and the table with the attachments. Not sure if your table relationships would be such that you'd use an equal or outer join. Easy enough to try both of course. If that doesn't work, then I don't know - unless the answer is that you have no common field between the union and the table with the attachments. I would think that would be a requirement.

[zkrucz]

@Micron

OK, I compromised and came up with a workaround to my image issue. I am using only the first signature in a series (the first of up to 25 devices) to authenticate the Form. Not the best solution but it provides the level of endorsement that is needed. Thank you for your help on this.

[Micron]

NP. Glad you found a solution of sorts.