Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filling in fields on a form (A2k SR1)

    Hi All,

    I have bumped my head on this particular problem before trying to make a 'flexible' database. I hope I can explain it properly by giving a particular example and get some help please. Maybe my table design is faulty ? Any comments on the design are welcome as I am new to this game [img]/forums/images/smilies/smile.gif[/img] I have done some reading, but am still floundering. I have attached a screen shot of a portion of my relationships window to hopefully help my explanation. (Read "Assay" = "Analysis" in the screen shot).

    I am trying to develop a system for our laboratory to enter sample analyses. My original (inflexible) design would have had a table for each sample type and it's associated analyses e.g. Furnace Slag, which would have contained fields for identifying the sample, the timestamp info as well as a field for each analysis e.g. Fe, Co, Cu, etc (the "Components").

    In my newer and hopefully more flexible design, I want to maintain the list of sample types in a lookup table. For each sample type, there are several analyses that may be required to be stored on an hourly (or sometimes an ad-hoc time) basis. Because the analyses required on some of the samples varies from time to time, I chose to store the current set of required analyses in a lookup table as well. The problem now is to present the user with a table-like form that allows him to select the sample type in the first column on the form and then at the same time, the rest of the column headings on the form have automatically been populated with the analysis identifier e.g. Fe, Co, Cu, etc. For reasons already mentioned, I don't want to hard-code the anlaysis types in labels on the form. The next problem is to make the form populate the relevant records in the "Assays" table.

    I am at a loss as to how to proceed.

    Does my underlying design look wrong and un-necessarily complicated ? Am I missing something obvious here ? Any help would be gratefully received as I have encountered this problem before of wanting to use lookup lists to allow flexibility, but have always ended up falling back on multiple tables with repetitive field names for the different sample types. That is non-normal as far as I can make out ?

    TIA,

    Adrian

    Chambishi Metals plc
    Kitwe, Zambia
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filling in fields on a form (A2k SR1)

    I'm sorry, but it's a little hard to figure out which tables are the "lookup" tables and which tables actually accept data entry in your diagram. The join tables are obvious, but that's about all, and I don't see why you have both an Assays table and a Standard Assays table. Could you explain more specifically how these tables are supposed to be used and populated?
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling in fields on a form (A2k SR1)

    I thought my explanation might not be adequate - the tables are :

    1. "Samples" - The samples actually taken with a link through AssaySetID to the assays. Sample names are looked up in the "Sample Names" table.

    2. "Sample Names" - lookup table for sample names

    3. "Assay Sets" - Sets of assays - analyses are done in batches of up to 10 samples at a time. One of the added complications which I didn't mention was that I need to do some corrective calculations on batches of these values. Each batch of samples through the instrument may have had different standards used. Some of the inputs for these calculations come from the standards table. For the purposes of solving my problem, I think you can completely ignore the "Standards" and the "Standard Assays" tables and the field StdID in the "Assays" table.

    4. "Assays" - will hold the actual assays per compound per sample - The fields are : AssaysID field; AssaySetID linking back to the relevant set of assays; the index (also used in the calculation mentioned above, so ignore it); the ComponentID used to lookup the component name e.g. Fe, Co, Cu in the "Assay Compounds" table and the actual Value read from the analytical instrument for the analysis of the relevant compound.

    5. "Assay Compounds" - the lookup table where all the different compounds for which analysis is required are stored.

    I don't know whether it is good or bad practice, but I usually use an autonumber xxxxID field as the primary key for all of my tables. Not strictly necessary I am sure.

    A sample of the input form that I would like to create is attached.

    Thanks again,

    Adrian
    Attached Images Attached Images

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filling in fields on a form (A2k SR1)

    If I understand you correctly, the compounds can be different for each sample name. That would mean that the column headings change as the user moves from row to row. That in itself seems confusing to me, but it also means that while the user can see several rows at a time, the column headers are only valid for the current (selected) row.

    Something looking like your design could be accomplished by using a crosstab query. Unfortunately, crosstab queries are not updatable; in you first post you explicitly stated that the form is meant for data entry.

    So I don't think that entering data in one table-like design is a good idea. A continuous subform with each relevant compound in its own row is probably better.

    But perhaps somebody else on the lounge has a brilliant idea.

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling in fields on a form (A2k SR1)

    The headings will stay the same for all samples. There might be some that will be left empty for a particular sample type, but that doesn't matter. The reason for wanting to look the compounds up in a table is that when different feed material is processed (it changes every few months), then we may want to display (and enter) a couple of new compounds and drop a couple of the existing ones for all samples at the same time. I would probably add a flag field to denote active/inactive compounds, because it wouldn't make sense to actually delete a particular compound and have orphaned data.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Filling in fields on a form (A2k SR1)

    Your additional explanation is very helpful. Hopefully several people will comment and we can get some sort of concensus on what the best design is. For starters, my observations are:
    <UL><LI>It's not clear to me that the "Assay Sets" table is of significant value - it seems to me you could save the SampleID in the "Assays" record along with the Date, and with that info you could group the data into Assay Sets if you need to. Even if it is necessary, I think I would design the relationships so you have a One to Many from "Samples" to "Assays" and then a Many to One from "Samples" to "Assay Sets".
    <LI>How many different Assay Compounds are used in the assay process? Your sample form shows 4 - if that's all there are, your earlier solution of using columns for each compound may be the best solution, even if it isn't truly normalized. In that case, designing a form such as you suggest is fairly easy using a continuous form that displays a series of record vertically.
    <LI>Assuming the answer to the above question is many, then the design you suggest seems appropriate. However it does pose problems in form design. The easiest solution to form design problems like this is usually to use a subform - however subforms work in a vertical structure, not horizontal as you portray in your sample form. In that case, you would display one sample at a time, with a series of records for each of the components displayed below it. Another solution is to write a smarter form that displays a custom set of Compound Names for each record, which would presumably involve figuring out the number of compounds for the displayed sample. Such a form would probably involve more and more complex code.
    <LI>Your practice of using an autonumber field as the primary key is a good one in my view. Often people make the mistake of trying to use a numbering scheme that has some significance, and it nearly always ends up causing problems.[/list]Hope this is useful, and also generates some other opinions.
    Wendell

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filling in fields on a form (A2k SR1)

    If the total number of compounds used in a sample is constant, it should be possible - in principle. But it means that not only the labels acting as column headings must change, but also that the record source (and probably the name) of the text boxes corresponding to the compounds must be changed.

    The data displayed for older assays would be incomplete if some compounds are not shown. Wouldn't that confuse users?

    If you do this by hand, maintenance becomes a nightmare.

    If you add an active/inactive field, it is possible to write code that changes the design of the form based on the settings of those flags, but it will be a lot of work.

    But it seems to me that this is making things more instead of less complicated.

    One suggestion for a somewhat simpler approach:

    If you can determine the total set of all possible compounds, you could assign a column for each compound. In the OnLoad event of the form, you could enable/disable the columns according to the active/inactive flag in the Compounds table (or make them visible/invisible).

    That way, you wouldn't have to change captions and record sources. Less confusing, I think.

  8. #8
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling in fields on a form (A2k SR1)

    To address Wendell's comments first :

    The "Assay Sets" table was included because there is additional information that needs to be captured per set of assays. I apologise that this was not reflected in the information presented to date, but I had/have still not determined which data goes in which table, but intuitively I felt that this intermediate table was required.

    To clarify this, let me explain the analysis procedure. At most, 10 plant samples are processed through the instrument at a time. In addition to those 10 samples, there are a further two samples that are read at the beginning and end of the set - an initial standard and a final standard (physically derived from the same standard sample), making a maximum of 12 readings per assay set. Per sample data point (being the intersection of sample name and compound type in the second screenshot I presented) there are therefore 6 distinct pieces of information required to complete the calculation that I need to do to correct the raw assay to the corrected assay : initial standard value read from the instrument, the actual raw data value read from the instrument, the final standard value read from the instrument, the index (or sequence number) of the sample in the batch of (up to) 10 samples, the total number of raw samples in the batch (from 1 to 10) and the name of the standard sample used for this sample and compound (from which the standard value is derived through the link to the "Standard Assays" table). Having said all the above, Wendell's comment on doing away with this table is probably still valid, but I am not sure.

    There are presently 19 different compounds for which analyses are required in each sample. To include all the required information in the "Assays" table would then require at least 19*6 = 114 fields (plus a few others for sample ID and timestamp info) to cater for all the information in a set of samples. Clumsy as it may be, this is probably the best solution - the compounds don't change that frequently, so I will probably build a "monster" table. As more compounds are required, they can be added to the table and the forms modified accordingly.

    The crux of the problem is exactly as Wendell states in his third point - that sub-forms work in a vertical structure, not horizontal.

    -------------
    Hans' comments :

    I agree that things are getting more complicated rather than simpler. Your simpler suggestion looks like the way to go. I will then end up with a 'conventional' form/sub-form structure although the underlying table is not what one would have wanted.

    ----------------
    Many thanks for all your help

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Filling in fields on a form (A2k SR1)

    Going that way will end up with quite a deal of work, you would be better off (in my humble opinion) with a sub-form and entering the samples vertically. In this way you can add and delete as required from a table with no programming changes.
    Tell me, are you inputting spetrometre results for Steel making?
    If so, does the spectrometre generate any of these results?
    Pat

  10. #10
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling in fields on a form (A2k SR1)

    Thanks Pat.

    These are results from an ICP- Optical Emission Spectrometer in our laboratory at a Cobalt Metal refinery.

    Although I haven't gone into it in detail yet, I am sure the instrument will provide the data on-line to third party applications such as off-the-shelf LIMS (Lab Info Mngmt Sys), or even in ASCII format. The major problem is that we apply a proprietary "drift correction" algorithm to the raw data which will not be catered for in commercial software packages (I stand to be corrected here), so either we take the raw data directly into a LIMS and then correct the results when they are extracted from there, or I correct the data myself before it is stored in my own home-grown LIMS which is what I am trying to develop.

    Thanks again to everyone for their suggestions - I'll see what I can come up with <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Filling in fields on a form (A2k SR1)

    The reason I asked is that I have worked for the past 2.5 years at a Steel Mill here in the land of Oz.
    The spectrometer here in fact outputs an ascii file of readings which can be imported directly into Access.

    Let us know what you do come up with.

    Cheers,
    Pat

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Filling in fields on a form (A2k SR1)

    With 19 compunds and the possibility of some growth, I agree with Patt and HansV. I think I would definitely go to a subform approach rather than the fixed column approach. For one thing, there is a limit of 255 fields to a table. In addition, that sort of structure gets really difficult to deal with when you want to print things. It may take users a while to adjust to entering things in a vertical structure, but computer forms in particular do not lend themselves to things that have to scroll horizontially. If you hit roadblocks, post again and we will try to help.
    Wendell

  13. #13
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling in fields on a form (A2k SR1)

    Yes - I am sure sub-forms is the way to go now - thanks. The users will have to get used to scrolling down instead of entering everything into a compact table-type form.

    One big problem which is a bit like the blind leading the blind because I am so inexperienced at this is that I am only here in Zambia for a limited time, so I need to leave behind applications that will hopefully be usable for quite some time to come.

    At some stage I must look at splitting the UI from the tables so that somebody can send me the UI part for updating/modification/fixing if required and then I can e-mail it back. I have briefly tried using it as a split DB, but I ran into problems when I tried taking the application home to work on it, so the linked tables were no longer available on the network drive. I think I used the old DOS SUBST command at one stage to create a phantom network drive, but it is a while since I looked at that. Anyway that is another problem to be overcome !

    Thanks,

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Filling in fields on a form (A2k SR1)

    What you can do is to relink to the Database at home for any changes, testing you need to do.
    When you take it back to work it is just a matter of relinking the tables to the database on the Network.
    All quite simple really.

    You should be able to get the sub-form to really steam along as there is only one field to input (am I right there?) from what I can make out.

    If you have any problems just post your problem and we'll solve it.
    Pat

  15. #15
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling in fields on a form (A2k SR1)

    Thanks Pat.

    I split two of my .mdb's - the one worked with the built-in splitter, but the other failed so I used the method described by Mark Liquorman in a post from February - quite a simple process it seems.

    What about the relationships and permissions ?

    It seems as though the relationships were correctly recreated in the _be. When deleting the tables from the front end before linking in the _be ones, I was prompted to delete the relationships, but they seem to have been recreated after linking the _be tables. Is this correct ? Should the relationships be reflected in both pieces of the database ?

    The permissions seem to be a different story though - none of these have been saved with the _be. The only user with any permissions is myself. Group permissions were assigned - both the Admins and Users groups have full permissions which is not what I want - the whole point of the permissions is to stop users modifying designs or in some cases preventing them from deleting data. Does one have to manually go and re-assign permisions for all the tables in the _be ?

    TIA

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •