Expected vs Actual

  • Thread starter Thread starter Claire
  • Start date Start date
C

Claire

Hi all,
I have a query (qryExpectedDevices) that will produce a list of devices that
are supposed to be tested at a specified site. I know I am able to create a
one to many form bound to this query with the site information on the top and
the devices listed below.

However, I have a separate table (tblTestResults) that lists each device and
its test information (ie date, pass or fail, and comments). I would like to
create another (or a related or the same?) one to many form that lists the
site information on the top (including testing date and inspector to be
entered), and lists the devices that were supposed to be tested along with
test results. The one part of the form would be bound to a table tblTests (I
haven't designed this part yet, but I think I know how to work it), and the
many part will be bound to the tblTestResults. I would like this to be
populated with the devices from the qryExpectedDevices.

How can I populate the tblTestResults portion of the form (the subform) with
the devices from qryExpectedDevices when I enter the site in the main form?

Note: It's possible that additional devices will be tested at this time
that were not expected. I'm fine entering these on a pop up form or the
like, but it would be nice to enter them on this form. I don't think I'll
ever want to delete an expected device from the actual list, but that's not
out of the realm of possibility (I think I'll have a box to check that says
missed, instead of removing it).

Thanks for any suggestions you have,
Claire
 
Claire said:
Hi all,
I have a query (qryExpectedDevices) that will produce a list of
devices that are supposed to be tested at a specified site. I know I
am able to create a one to many form bound to this query with the
site information on the top and the devices listed below.

However, I have a separate table (tblTestResults) that lists each
device and its test information (ie date, pass or fail, and
comments). I would like to create another (or a related or the
same?) one to many form that lists the site information on the top
(including testing date and inspector to be entered), and lists the
devices that were supposed to be tested along with test results. The
one part of the form would be bound to a table tblTests (I haven't
designed this part yet, but I think I know how to work it), and the
many part will be bound to the tblTestResults. I would like this to
be populated with the devices from the qryExpectedDevices.

How can I populate the tblTestResults portion of the form (the
subform) with the devices from qryExpectedDevices when I enter the
site in the main form?

Note: It's possible that additional devices will be tested at this
time that were not expected. I'm fine entering these on a pop up
form or the like, but it would be nice to enter them on this form. I
don't think I'll ever want to delete an expected device from the
actual list, but that's not out of the realm of possibility (I think
I'll have a box to check that says missed, instead of removing it).

Thanks for any suggestions you have,
Claire
Click to expand...
Test results should be linked to the device table.
I would probably have a form with two subforms.
The main form would be the location.
The first subform would be the items to be tested.
The third subform would be related to the second.
Pick a place and all the items show.
Pick an item and all the tests for that item show.
If the third form is based on a continuos form that looks like a datasheet,
then you can use conditional formatting to show Passed, Failed, or not
tested.
 
Claire wrote:

Darn.
There are several places on the web where such related subform examples can
be found and I think the Northwind database has one.
 
These tables are linked in a many to one relationship, but they are not in
the same table. One device may get tested four times a year, and we need to
keep track of that over the course of about 5 years. I like the multipart
form with two subforms for looking how a site or device tested on the last
test, but I don't see how it works for the data entry part.

If I have a subform that has the devices to be tested, and I click on one,
can the next subform populate the tblTestResults with the device information
and allow for entry of the actual test results?
 
Claire said:
These tables are linked in a many to one relationship, but they are
not in the same table. One device may get tested four times a year,
and we need to keep track of that over the course of about 5 years.
I like the multipart form with two subforms for looking how a site or
device tested on the last test, but I don't see how it works for the
data entry part.
Click to expand...

I'm not sure what that first sentence means but if you have different tables
holding the same information they should all be rolled into one table.
ONE table for the machines.
ONE table for the locations.
ONE table for the tests on the machines.
Anything else will cause you problems in design sooner than later.

There may be other tables, such as test type, or who tested.
 
It looks like we're agreeing here, I just misunderstood what you had written
earlier. I do indeed have one table for the devices, one for the sites, and
one for the tests. I ALSO have one for the results of the tests. ie, John
Doe performed a test at Wallyworld on 7/10/09 is on the test table, but the
results are in a separate table. This contains a primary key for the test
with each device tested as a record.
Test Device Result
123 A pass
123 B pass
124 C fail
124 A pass

I would like to prefill this table with a form that gets the devices from a
query of devices that were expected to be tested. At this point I'm thinking
I have to run an append query that will enter the devices, and then add the
results to those records. The question now, is if there's a place for
ANOTHER table for expected devices, and then the one for test results. Hmmm.
I'll have to think on that one.

Does the append query seem like the sane way to do this to you?

Thanks,
Claire


:
 
Claire said:
Hi all,
I have a query (qryExpectedDevices) that will produce a list of devices
that sava
are supposed to be tested at a specified site. I know I am able to create
a
one to many form bound to this query with the site information on the top
and
the devices listed below.

However, I have a separate table (tblTestResults) that lists each device
and
its test information (ie date, pass or fail, and comments). I would like
to
create another (or a related or the same?) one to many form that lists the
site information on the top (including testing date and inspector to be
entered), and lists the devices that were supposed to be tested along with
test results. The one part of the form would be bound to a table tblTests
(I
haven't designed this part yet, but I think I know how to work it), and
the
many part will be bound to the tblTestResults. I would like this to be
populated with the devices from the qryExpectedDevices.

How can I populate the tblTestResults portion of the form (the subform)
with
the devices from qryExpectedDevices when I enter the site in the main
form?

Note: It's possible that additional devices will be tested at this time
that were not expected. I'm fine entering these on a pop up form or the
like, but it would be nice to enter them on this form. I don't think I'll
ever want to delete an expected device from the actual list, but that's
not
out of the realm of possibility (I think I'll have a box to check that
says
missed, instead of removing it).

Thanks for any suggestions you have,
Claire
Click to expand...
 
Back
Top