Possible revamp of tables - ideas?

[Carrie]

Hi Again, I'm going out on a limb here to see if anyone can help me with a
design problem as I am pulling my hair out! I have a db with:

Tbl_MAIN (well info)
Subtbl_SCVF_MAIN (an issue at the well)
Subtbl_SCVF_Test (an annual govt reqt)

These are also in a form with subforms. The SCVF_Test is a subtable of
SCVF_MAIN. I was originally told that with the annual test reqt the user
would in January want to merge all records that were "open" (Status field in
SCVF_MAIN) into a Word doc. which then gets sent to staff, they do the annual
tests and send in the completed forms. The User would then add this data
into the db, creating a new SCVF_Test record. I planned to write a query to
export off of everything with a status of Open.

I have now been told that while this will happen in Jan, the User will also
periodically throughout the year, re-run everything that still requires a
test. I can't figure out how to do this because the SCVF_MAIN record will
still be open in most cases even if they have completed the test and the
SCVF_Test record won't have been created yet.

The User currently records all this in an Excel spreadsheet with columns for
each year (2005, 2006, 2007) and puts an X in the column once the test is
done. She then filters for merging based on everything that doesn't have an
X. I know that I should not duplicate this format in the database.

I'm wondering if there is some way to make the db create a new test record
for each year although that will create a number of empty records because the
SCVF_Main Record could get closed at any time before the test is done.

Does anyone see a way of dealing with this that I am not noticing?

 

[Amy Blankenship]

Hi Again, I'm going out on a limb here to see if anyone can help me with a
design problem as I am pulling my hair out! I have a db with:

Tbl_MAIN (well info)
Subtbl_SCVF_MAIN (an issue at the well)
Subtbl_SCVF_Test (an annual govt reqt)

These are also in a form with subforms. The SCVF_Test is a subtable of
SCVF_MAIN. I was originally told that with the annual test reqt the user
would in January want to merge all records that were "open" (Status field
in
SCVF_MAIN) into a Word doc. which then gets sent to staff, they do the
annual
tests and send in the completed forms. The User would then add this data
into the db, creating a new SCVF_Test record. I planned to write a query
to
export off of everything with a status of Open.

I am not sure what you mean by "subtable." I can guess that possibly you
mean that SCVF_Test is related to SCVF_Main in a one to many relationship
with SCVF_Main as the one side.

I have now been told that while this will happen in Jan, the User will
also
periodically throughout the year, re-run everything that still requires a
test. I can't figure out how to do this because the SCVF_MAIN record will
still be open in most cases even if they have completed the test and the
SCVF_Test record won't have been created yet.

What is your work flow between the two tables? Why would a test have been
completed, yet no record has been created? You can't pull data that doesn't
exist, so I suggest you find a way to record the test when it happens if you
want to be able to query what tests have happened.

The User currently records all this in an Excel spreadsheet with columns
for
each year (2005, 2006, 2007) and puts an X in the column once the test is
done. She then filters for merging based on everything that doesn't have
an
X. I know that I should not duplicate this format in the database.

You need to duplicate at least one part of this process... at some point
between the time that the test is run and when you run the query, someone
needs to record that a test has happened. Otherwise, there is nothing to
query.

I'm wondering if there is some way to make the db create a new test record
for each year although that will create a number of empty records because
the
SCVF_Main Record could get closed at any time before the test is done.

You don't need to create those records. You just need to have a query that
shows what you need to see.

Does anyone see a way of dealing with this that I am not noticing?

I'm not 100% clear on what it is that you're saying, but let me make a guess
before I wind up typing a long winded solution:

What you want is to have (or appear to have) a record in Test for every
*year* starting at some point (2005?) and continuing to the present for
every record in Main. Right now, perhaps, records are only created in that
table when a test is actually performed, so no records exist in Test for
some years (or only the current year?) for some records in Main...?

Does that about sum it up? If so, let me know and I'll give it a stab.
Otherwise, please clarify.

HTH;

Amy

 

[Carrie]

Hi Amy - thanks for your response - I obviously haven't been clear enough!

You are right about the relationships (that's what I meant by subtable)

Basically the record in the Main Table may or may not have a SCVF issue. If
it does a new SCVF_MAIN record will be added (there can be multiples of this
over the years too: one-to-many). If the SCVF_MAIN record has a status of
"Open" then it requires an annual test.

Once this test is completed and the form submitted, the User will enter the
data off of the form, creating a new SCVF_Test record. This will happen
every year until the SCVF_MAIN record is Closed however, as I have it built
right now there is no test record for the current year until after it happens.

Since the User wants to be able to merge to the form at any time based on
the fact that the test for the current year has not been done, I cannot
create the export function on the SCVF_MAIN status "open". Even if the test
has been done the status will still be open but now a test won't be required
until the following year.

I somehow need to be able to indicate that for the current year, a test is
still required on this particular SCVF_MAIN record.

The only way I can think of to do this is somehow make every open SCVF_MAIN
record generate a new SCVF_Test record.

Does that make more sense?

 

[Amy Blankenship]

Hi Amy - thanks for your response - I obviously haven't been clear enough!

You are right about the relationships (that's what I meant by subtable)

Basically the record in the Main Table may or may not have a SCVF issue.
If
it does a new SCVF_MAIN record will be added (there can be multiples of
this
over the years too: one-to-many). If the SCVF_MAIN record has a status of
"Open" then it requires an annual test.

Once this test is completed and the form submitted, the User will enter
the
data off of the form, creating a new SCVF_Test record. This will happen
every year until the SCVF_MAIN record is Closed however, as I have it
built
right now there is no test record for the current year until after it
happens.

Since the User wants to be able to merge to the form at any time based on
the fact that the test for the current year has not been done, I cannot
create the export function on the SCVF_MAIN status "open". Even if the
test
has been done the status will still be open but now a test won't be
required
until the following year.

I somehow need to be able to indicate that for the current year, a test is
still required on this particular SCVF_MAIN record.

The only way I can think of to do this is somehow make every open
SCVF_MAIN
record generate a new SCVF_Test record.

Does that make more sense?

The way I would do it is create a new table, something like this:

SCVF_Years
YearID (PK/Autonumber)
YearDesc (Text)
YearStart (Date/Time) (optional)
YearEnd (DateTime) (optional)

Ideally, you would relate this to SCVF_Test on YearID, but given that the
year information is already in that table, you may have to relate to
YearDesc.

Now, you need to create a query that simulates a table that contains the
missing records. Open the query builder and show the SCVF_Years and
SCVF_Test tables. double-click on the join line and change the join type to
"Show all records in SCVF_Years and only records in SVCF_Test where the
joined fields are equal." Select the fields you need (especially the one
where SCVF_Test relates to SCVF_Main) Save the query as qryYearTests.

Next, you need to create a query where you join SCVF_Main to qryYearTests.
Show both tables in the query builder, and then drag the PK field from
SCVF_Main to SCVF_Test to allow it to join properly. Select your fields as
normal, setting the criteria on Status to "open". You should be able to run
your merge off this query.

HTH;

Amy