Question Lots of Database Questions.

[travisdh]

Hi All,

I have attached my database to this forum in the help some people might be able to help me play around with the relationships so that I can get this to work.

The table Structure goes like this:

Jobs
|
Samples ----------------
| |
Test Groups - Results
|
Test Analytes

From the Jobs page you can pick the company the job is for, and the contact for that company, but I can't seam to get the drop down box to only show the contacts that work for the selected company, could someone help with that one.

The second part is Where you pick the test groups for a sample, is it possible to have a table automatically generated that has space for the results for all the test analytes that would be there for that sample. What i mean is say the sample 1 has test group A, which has analytes A1, A2, A3, A4 when I click on the sample, how can I have a table open that has all of those when there is no table simple like that.

I know the design having test groups and test analytes seams quite convoluted, as having a results table that has test analyte and a test result, but the reason I did that was because I want to be able to dynamically generate new tests in the system and new test groups so that it can be used by any section.

The only other thing, is If i have a parent sample, and child samples under that (all in the sample table) how can I have the system calculate a result based on the values of the parent and sample fields.

The example i use is TVOC, in this case there are a few calculations if the parent sample has testing for ACCS, then in that test group I have selected things like flow rate, sample size, chamber entry date, chamber exit date, and then there are lots of sub samples which might be a TVOC sample which would have the analyte TVOC, as well as the flow rate for that sample, what I want to be able to do is have a results table entry which takes the values from the calculations (eg Parent sample Flow Rate * Parent Sample Surface Area * Child Sample Flow Rate / Child Sample TVOC)

And is there any way in the test analytes table, I could have a setting to allow that to be a calculation in the results page, and create new calculations in a field and put it in the parent test group to use the child values for a parent calculation.

Sorry if it seams very round about way of explaining it, it seams quite a bad explaination but I hope someone can help

The Summary Points:

1. Jobs field shows client company, but does not limit the client contact to the client company staff members.

2. How to get a table to be generated for sample's analyte results, that is a sample has group a set, and group a has 12345 analytes, so a results table (which has sampleID, Result Name, ResultValue) is generated.

3. How to use test result values for samples (and child samples) to generate a semi-complex calculation. For example take parent sample's surface area, and chamber flow, and then use those with the child samples flow rate, and TVOC values to generate a final result for the parent sample and be able to put calculations as results, as linked through test value.

I am no means set on the design, rather than a lot of the values are important, and I want to keep test groups and analytes, and have a table that lets me easily enter a value to a sample and test type.

Hope that makes sense.

Thanks for any help.

 

[DCrake]

When is the assignment due?

 

[travisdh]

It's not actually an assignment, I am trying to streamline the way results and data is handled at work, at present everything is manually recorded and calculated and I felt a database could make day to day more efficient.

 

[Pyro]

Hi Travis,

First I want to give you a high five for wanting to use a database. I have worked in a bunch of labs (biology and chemistry), and omg, how those people love their spreadsheets. Holy crap, spreadsheets for logging patient data, sample data, equipment data, staff data, consumable data, quality control data... I could go on! IMO, it should be mandatory for science students to graduate with at least some basic understanding of information systems. Right, well, before this turns into a major rant, let me try to help you out here .

I think that in your OP, you might be asking a bit much to get useful responses to your questions. If you don't get a lot of help, i suggest you break it down a bit.

A couple of quick points:

is it possible to have a table automatically generated that has space for the results for all the test analytes that would be there for that sample.

This is not the right approach. You want one or several linked results tables (dependant upon the data), within which you will record the results of all of your tests, linked by their sample id.

what I want to be able to do is have a results table entry which takes the values from the calculations (eg Parent sample Flow Rate * Parent Sample Surface Area * Child Sample Flow Rate / Child Sample TVOC)

Storing of calculated fields in tables is generally never done. If all of the variables used to calculate your results are stored in the database, you would recreate that calculation wherever it is required, for example on forms or in reports, either directly in the controls or in the underlying queries.

To help you with one of your questions:

From the Jobs page you can pick the company the job is for, and the contact for that company, but I can't seam to get the drop down box to only show the contacts that work for the selected company, could someone help with that one.

You need to adjust the query in that combo box to show records only associated with the selected company:

SELECT Staff.[ID], Staff.[First Name], Staff.[Last Name], Staff.Company_ID 
FROM Staff 
WHERE (((Staff.Company_ID)=[Forms]![Lab-Jobs]![Company])) 
ORDER BY Staff.[Last Name];

Then once you make a company selection, you will need to requery the contact combo. In the after update event of the company combo, select code builder, then code it so it looks like below:

Private Sub Company_AfterUpdate()
Me.Contact.Requery
End Sub

Hopefully you get some more responses. I am kind of busy today, so i can't commit much more time to helping out, but if i can, i will.

 

[travisdh]

Thanks for the reply, most of it makes sense but can I get you to elaborate on the following comment you made.

A couple of quick points:
is it possible to have a table automatically generated that has space for the results for all the test analytes that would be there for that sample.
This is not the right approach. You want one or several linked results tables (dependant upon the data), within which you will record the results of all of your tests, linked by their sample id.

So in my database at the moment there is the jobs, which links to the samples table, and the samples links to the test groups table, which links to the test analytes table which links to the results table, and in the results table it links back to the sample. The idea being that as the database grows, there are minimal fields in the table, that is instead of re-creating the table each time, I can just add a new field in test analytes and add those to groups, and when they get selected in the sample (test group required) it would allow you to see those test results.

Is that the right approach, to have that, but what I ment was then to be able to generate almost a SQL table which is updatable, so that it might query by test and job to show all samples that have that test and let you enter results, or to be able to query by sample and return a sample that has all the tests assigned to it so that in the table when you fill it out, it updates or inserts values in the results page. Like a results by sample, or results by test data entry page.

I am trying to make it as simple for others to use, and I just think that if they have to manually select everything and enter everything in including samples then it won't happen, but if I allow it so that if you click on the samples you want, then click on a "Enter Sample Results" button, and up pops a table that has samples along the top (horosontally) and tests vertically with the ones that are not applicable greyed out, then it would be much easier to enter. Even just per sample to show all tests that require results would be more than fine .

Part of the results should be easy, that is I have a speadsheet that has all of the data in it, including the JobID, and the SampleID, and the TestName, and the Result, so i will create a macro to automatically import that into the table, so that the GC stuff goes in fine, its more the other stuff like weights, and surface areas and stuff.

In terms of controls, Does that mean that I can create a control that makes a new value, or fills out a results field based on the data in all of the other tables. I was wondering how I could get around adding new calculations in later. For example I may only have two calculations at the moment, but as time goes on there may be new calculations, and I want to try and avoid having to re-build a new control in the database each time there is a new test with a new calculation, also how would I limit a control to running when only a test group is created. Do i create a results test analyte within the parent group, and have the control check to see that if there is a parent sample, and it has the test required "X" to run the control, could I run this control or query within a report, so at the very end it generates the result, or would it better to have the control just update the results field test analyte with the result, and then use that in the report. I just don't want people to be able to generate reports if there are no results. And i guess I could have a sample results completed checkbox that gets ticked automatically when all results fields are filled out, but not sure on the best approach.

Sorry its a massive question, I do have some IT background, and have some SQL experience, I am just unsure of the best way to go and any guidance is great as it helps me to learn, and I can pass onto others, or make other complex databases.

Thanks!

 

[Lightwave]

In terms of controls, Does that mean that I can create a control that makes a new value, or fills out a results field based on the data in all of the other tables. I was wondering how I could get around adding new calculations in later. For example I may only have two calculations at the moment, but as time goes on there may be new calculations, and I want to try and avoid having to re-build a new control in the database each time there is a new test with a new calculation

Ok lots and lots of questions I simply don't have time to answer all of them but I can help you with this one and one in particular.

There are many ways of making calculations.

If you have a new calculation however you will need to define a new formula everytime you want a new calculated somewhere. I just don't see anyway round that.

As above it is general best practice NOT to store calculated fields inevitably there are exceptions to this. For example in cases where you have a legal duty to record something like VAT or taxes and those taxes can vary over periods but are fixed at time of calculation. Hopefully in a scientific setting unless the science changes that might not be an issue. You yourself will know better as to the appropriaptness of this.

Regards calculation of values there are a number of different standard ways.
1. Creating box on form and using other fields in the form as source variables for a forumla which is automatically calculated
2. Creating text box on repory and using other fields in the report as the source variables for a formula which is automatically calculated
3. Creating a new query and using the fields selected in the query to create a new field that uses these fields to calculate a new column or field
4. Writing a function or method that can be referred to through a query or from an event to calculate something.

Of these 4. is probably the more complicated and more advanced.

What you might want to do is have a go at adding two fields in a form and returning it to a third field. That should start getting you seeing the possibilities.