Best way to reuse items?

[perrymans]

So, I am making a database that is going to use a database of checks to generate point-in-time tests. The checks change frequently (as in they are updated) but the original check used for the tesst must be preserved.

The solution I am thinking are separate Project tables. So a project would be created and when submitted, a separate table based on the ID field in Tbl_Project would be created. So Project XYZ with a Tbl_Project.ProjectID of 4 would create a new table called Project_4 with all the checks.

I want to be able to use the same forms, queries, reports, etc. for the database. So a user would select the desired project to work on, I would store this temporary value, and all queries would filter on that value to define the appropriate Project table.

The problem is, I know this would work for filtering records, but I don't know if it will work for defining source tables in queries?

Or even if this is the best way?

Just looking for thoughts, feedback, or ideas.

Thanks. Sean.

 

[PHV]

a separate table based on the ID field in Tbl_Project would be created
Have a look here:

http://r937.com/relational.html

The ProjectID should be a FK in the checks table instead of a part of the name of the table.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

Howdy perrymans . . .

All is a little vague here. Could you expand on how the [blue]point-in-time tests[/blue] change? ... I'm assuming you have columns of [blue]checkboxes[/blue] and each time a test is made the checkboxes are awarded a different combination of true/false.

[blue]Your Thoughts?[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[perrymans]

i.e.

Today, the test is to execute:

TESTNUM: LNX002200
ls -la `find / -name equiv.hosts`

in a Linux 4.5 environment.

So I build a test "package" based on this test. In 6 months from now, a new test set comes out from the government and the test has changed:

TESTNUM: LNX002200
ls -lL `find / -name equiv.hosts`

I can't link to the tests in the checks table because once the new test is loaded (which will apply to packages from then on) it would link that if I pulled the record of my old package (and I need to preserve the original tests used).

I could just append the checks to the Tbl_Checks, but I worry about always having to do Max queries which are touchy and slow in access.

Well hell, maybe that is the way I should go.

I might have just talked myself into it. Do you concur doctor?

Thanks. Sean.

 

[TheAceMan1]

perrymans . . .

Everywhere I turn with this I can think of too many questions to ask. This keeps me unsure and prevents me from giving you worthy replies.

I'll brainstorm a little more and see what happens.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[perrymans]

To keep a relational structure, my only option seems to be appending the updated checks and using Max queries when building the 'test package'. This way the first test package is still linked to the appropriate checks in the table, and when new packages are built, it will use the latest uploaded check.

My other option is to simply import all checks from May's release and associate them with that release's version number. Then import all from September when that comes out and associate all with that release's version number. I would then always build packages based on the latest release.

However, this will cause duplicate values in the table since not all checks change.

Option 1 seems to make the most sense and shouldn't be that hard. It certainly solves all of my problems presented by my "separate table for each project" idea.

Thanks. Sean.

 

[TheAceMan1]

perrymans . . .

My last post wasn't just an out, I've been genuinely bainstorming your secnario since you 1st posted. Perhaps answering some qestions will open a door!:

When you make a new table whats causing you to do so? ... different fieldnames . . . what? ... I already believe I know ... but it doesn't hurt to ask.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[perrymans]

All field names are the same. The reasoning for the new table is 1) point in time capture of the checks (because they change, but what was used for THIS test needs to be maintained) and 2) allow customization of the checks without affecting other checks to be uploaded.

I thought about it today too, and had another thought. Have a Tbl_PureChecks which holds the most current versions of the checks. When a Project is created, load the checks into Tbl_ProjectChecks and have this act like the main table of all checks. But it would allow customization of the checks if needed and keep the appropriate versions associated to the right test.

I also thought about having the main Tbl_Checks, and simply include customizable fields in the Tbl_ProjectChecks (with ProjectID, CheckID, and more fields that would allow augmenting the check). The problem with this approach is that it makes the checks longer on the reports and won't allow true customization.

those are the thoughts I have been ahving so far. Sean.

 

[TheAceMan1]

perrymans . . .

A look at the table structure & fields would be a big help, and when you say [blue]checks[/blue] ... what do you mean and how are the checks assigned in the table?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]