More numbering / best approach

[amberkei]

I'm working on a database that my scientists can use to submit samples for analysis. I've got the piece working where they can say the number of samples, have it insert the correct number of lines (into a temporary table), and the pieces are automatically named (sequentially). I also have it set up that they can add samples from a different experiment before sending it to the report. I can build the piece that eventually appends the information to the master table.

This is the conundrum that I've been presented with:

Each time a set of samples is submitted, they are numbered sequentially, from 1 to the number of samples, then assigned boxes (96 per box, excepting every fourth box which gets only 92). The final report must list the box contents. There is possibly some room to move in the way the samples and boxes are numbered, but not the overall set up, because that's how our robot intakes samples.

Currently, the team is doing this manually, and it both takes a great deal of time and is error prone, which is why I'm trying to automate it. (Yes, there are off the shelf programs that we could buy which would do this for them, but that's off the table for now.) I don't want to have the box and sample numbers assigned at the same time that the lines are inserted because they need to be able to insert more samples. (And maybe there is a way to do this, and I just can't see it.)

My question is this: What approach would you take? What's the best approach? Am I over-complicating this??? Recordsets puzzle me more than they probably should, so I've not explored that option much.

I've thought about having the query behind the report assign the numbers. It would be nice if they were saved in that final table, but it isn't absolutely necessary.

I'm currently displaying that temporary table information in a continuous form, and I feel like that might be causing some of the problems. Would it be better to display as a datasheet form sub-form?

 

[amberkei]

It's currently done manually in Excel, to the tune of 10k or more samples a week. The Excel files (and there are hundreds of them) barely open on most of our computers, which is why I've been tasked with getting it into Access. (The hope is that eventually the information in Access will port better into a LIMS system once we bring one online, budget pending.)

 

[Steve R.]

It's currently done manually in Excel, to the tune of 10k or more samples a week.

An implication here is that more than one person would be involved in data entry. Based on that, I would encourage the use of MS Access as only the front-end. Use either MySQl or SQL Server Express as the back-end. You may wish to look into other SQL based databases that are open-source and free to use.

Each time a set of samples is submitted, they are numbered sequentially, from 1 to the number of samples, then assigned boxes (96 per box, excepting every fourth box which gets only 92).

This raises a couple of issues. One, do not use a primary key as your sample number. Create a separate field for the sample numbers. Second, I would expect that you will need a second table that handles the disposition of each box. The two tables, of course, would be linked.

 

[amberkei]

An implication here is that more than one person would be involved in data entry. Based on that, I would encourage the use of MS Access as only the front-end. Use either MySQl or SQL Server Express as the back-end. You may wish to look into other SQL based databases that are open-source and free to use.

I'll have to look into that. I suspect that the current task is above my current skillset, so I've been looking into more learning resources. Fortunately, this is the kind of project where it's okay if I hit a wall and tell the bosses that it's beyond what I can cobble together. (I started out telling them that they had a "hammer and nail problem." In other words, the only tool they have is the Access hammer, so every computer problem starts to look like a nail.)

This raises a couple of issues. One, do not use a primary key as your sample number. Create a separate field for the sample numbers. Second, I would expect that you will need a second table that handles the disposition of each box. The two tables, of course, would be linked.

Done. My primary key is generally an autonumber that doesn't get used for anything other than being the key. What you're suggesting is a second table that lays out the boxes (Box 1, 1-96, Box 2 97-192, etc), then aligning my samples to that layout?

A question; prompted when reading how everything works; it seems pointless to send items off for analysis if they are incorrectly labelled?

I suspect that I'm not explaining the process clearly, and for that I apologize.

The scientist samples specimen and places the samples into tubes that are arranged in a 96 well format box (8x12), in every fourth box, the last four spaces are set aside for controls. So the box and well are the location, which is what I'm trying to generate. Those four boxes are fed to the robot which extracts raw data, linked to a location. A human at the end makes the judgment call on the data and (currently) inputs it into the excel file. The data is the final "product.

(Ultimately, this is an interesting project because you're tracking the genetic information for breeding information through many generations.)

 

[gemma-the-husky]

I have to say that at some point databases become commercial tools. I assume this will save your company a lot of money in time alone - in addition to the gains you don't yet know about from the improved information handling.

Access is nothing like as, well, accessible, as excel. you can design spreadsheets without using any macros or VBA code. You just cannot possibly design any half decent database without a good deal of code - a vast amount of which is actually used to limit what users can do in access. (equivalent in excel to protecting every cell, and allowing access to selected cells only, by adding suitable code) if you consider access in those terms, you will realise why it becomes rather difficult.

My advice would be not to try and get it done cheaply, and get some professional help in at the start. You will save a lot more than trying to do it all yourself.


couple of things

continuous form/datasheet are basically the same thing. I prefer the former, as you can add buttons etc - but occasionally I use datasheets. Changing from one to the other will not have a great effect on the project.

numbering items sequentially may be useful, but equally it may not be necessary. you don't have to slavishly copy a manual system. often there are better solutions.

finally - you say you do not want a ready made solution - but unless it is priced ridiculously it will almost always be the most economical solution. Having said that, your initial requirements do not seem very demanding, so there may well be reason enough for you to develop your own solution.

 

[amberkei]

finally - you say you do not want a ready made solution - but unless it is priced ridiculously it will almost always be the most economical solution. Having said that, your initial requirements do not seem very demanding, so there may well be reason enough for you to develop your own solution.

I agree. I do not, however, hold the purse-strings. The use of my time alone is a non-zero cost, and I've found software that is less than that. I do not always understand why certain decisions are made, which, I'm sure, is a problem we've all experienced at one point or another.

numbering items sequentially may be useful, but equally it may not be necessary. you don't have to slavishly copy a manual system. often there are better solutions.

I find this an intriguing thought, and the reason why I wondered what approach other people would consider taking. There are some constraints - the robot intakes tubes laid out in a specific fashion. The coordinates are usually expressed as A1-A12, B1 -B12, etc. and each box numbered. I felt that sequential numbering would be easier for me to handle, but perhaps I was wrong?

It's important to keep in mind that each of these samples has a history - perhaps 10 generations or more of crosses - that the database contains. I'm just describing a small piece of what I've already built. There has been some interest in implementing barcoding systems, and I'm wondering if the answer lies in there somewhere.

 

[amberkei]

>>> There has been some interest in implementing barcoding systems, <<<

Barcoding in MS Access is relatively easy and I have had some experience with it.

Perhaps this is better shuttled to another thread, but did you build out your own system or purchase one of the varies add-on packages that's out there?

(Those samples are of plants that will go on to be the next generation. So many things that need labeled, and labeled in a way that can withstand the weather, that a Sharpie just doesn't cut it sometimes.)

 

[Steve R.]

What you're suggesting is a second table that lays out the boxes (Box 1, 1-96, Box 2 97-192, etc), then aligning my samples to that layout?

It depends on the quantity/quality of the data you are collecting. If you are not (currently) collecting box information, then having a second table may be considered unnecessary. If you wish to expand you data recording to tracking boxes, then you will need a second table. The box table, if created, would contain only information related to the box. For example: date packed, date shipped, date received at the destination etc.

While collecting/recording more information is always considered "good", it adds complexity to your effort. For now you may wish to keep it simple.

 

[amberkei]

It depends on the quantity/quality of the data you are collecting. If you are not (currently) collecting box information, then having a second table may be considered unnecessary. If you wish to expand you data recording to tracking boxes, then you will need a second table. The box table, if created, would contain only information related to the box. For example: date packed, date shipped, date received at the destination etc.

While collecting/recording more information is always considered "good", it adds complexity to your effort. For now you may wish to keep it simple.

*pondering* I'll have to talk to the project lead that requested the database. I suspect that we care more about the individual samples and how they score than we do where they are located during the screening.

I can kind of see a set up in my head, though, where, when sampling, rather than use the approach that I've taken, you "fill" boxes (which more closely mimics what they're doing by hand). It might, in some ways, be easier than this strange Box, Well system that I'm using that mimics the Excel sheet set-up.

 

[Steve R.]

The fun of getting to a common understanding. My perception of a box, was something that was packaged and mailed.

 

[amberkei]

The fun of getting to a common understanding. My perception of a box, was something that was packaged and mailed.

Technically, when I talk about the "Box," it's one of these: