Form for entering animal cages in laboratory

[shiphtfour]

Hello, I am completely new to Access, so please excuse my total lack of knowledge. I recently started working at a lab that does mice breeding for research and my boss would like me to work on the access database they have setup. What I am trying to do is create a form that will be used for entering new cages into the database. As of now, the database is setup with a backend containing a database for each room with tables for each strain in that room. The frontend database is then used by the researches when they want to order mice.



There is a table for each strain and within these tables are 9 fields: ID, Strain, Cage#, Sex, Genotype, Animal#, Birthdate, User, and Sent/Retire date.

I want to create a from that makes inputting all this information much easier. I want the top of the form to have a drop-down list of the different tables (or strains) that the data will be put into. Below that I would like a textbox for inputing the cage number. Below that a dropdown box for either male or female, followed by a textbox for the number of mice. Because the mice have different genotypes in the same cage, after inputting the the number of mice is it possible for the same number of fields to appear where the user can input the genotype for each mouse? Then a field to input the birthdate.

Right now, you have to just open the table and input this information manually. But, the way it is setup, each mouse in each cage is given its own row. So for example:

Cage 001 Mouse #1 Female Born 0233
Cage 001 Mouse #2 Female Born 0233
Cage 001 Mouse #3 Female Born 0233

Is this a bad way to be inputting data? I know that ideally you shouldn't be inputting the same data multiple times... Anyways, any help is greatly appreciated!

[pkstormy]

It's not the end of the world to have a non-normalize table and have duplicate values (it just means users will have to update those fields for each record and you may have to consider duplicates within your query counts). I would store duplicate values in my main table for 'grouping' type fields when I was working with 5+ million records (so I wouldn't have to join multiple tables in my totalling queries and make them run faster.)

Ideally though for your situation, you'd want to set up your structure relationally and utilize a subform or popup form to enter the relational records versus duplicating the values in the main table.

But you want to keep duplicated fields to a minimum. For example, duplicating customer name, address, etc... type info in a table wouldn't be a good idea (or for you, duplicating a whole bunch of descriptive fields for the mouse.) When you do this, you either end up risking the chance a user will not enter the data correctly each time or you'll need to write a bunch of 'work-around' type coding to update the appropriate fields on the form. Consider users adding new records and the chances that they will enter one of the values incorrectly which will make any data cleaning type work more difficult. It's also more difficult designing forms around a non-normalize structure where values are duplicated such as your example. Consider that you a user would need to update multiple records versus one record if changes to a record are needed. Chances are the user will miss updating one or more of the records they need to update (or again, you end up writing a bunch of 'work-around' coding to update all the other records.) It's just not a good idea for accurate recordkeeping and your form design actually becomes more complex keeping the appropriate fields updated instead of less complex with a simple subform or popup form.

[shiphtfour]

Is there an easy way for me to convert the current database into a relational structure or would that just involve a lot of copying and pasting?

[pkstormy]

Easy depends on your experience working with relational structures (this usually requires a moderate skill level). It can be a time-consuming process where it's easy to make a mistake. If you only have a dozen or so records, I'd recommend just manually entering them in the new structure after you have your form design setup. Otherwise...

You want to 1st make sure you have an autonumber field in the main table of your new relational setup. This field will be key for linking to the relational tables (you'd have a matching field name in your relational table as well which would just be a number field type in those tables - ie. MouseID might be a good field name). This would uniquely identify each record in the main table and this value would be duplicated in your relational tables. You'd then need to run a few queries to append your current unique data to the main table in your relational structure. The hard part then becomes grabbing that autonumber value in your new relational structure to then append that value to the relational records.

Thus for your relational structure: MouseID = autonumber field type in the main table and MouseID = number field type in your relational tables (you can have an autonumber field in the relational tables as well (ie. RecID) but this value doesn't really play a role.) The MouseID field would be the field you'd join between the tables.

Again, it's not a simple process and you have to be careful that you don't grab the wrong autonumber value being populated in your relational tables - that's where it becomes a little bit difficult since you'd need to somehow get the correct autonumber in your main table new relational structure to then use when appending the relational table data.

If you've never converted a non-relational structure to a relational one, plan on a bit of a learning curve. If you're converting a few hundred records or you have a lot of fields (more than the 5 or 6 fields in your post), you may want to touch base with a 'good' local consultant to help you.

1st step though would be to get your new relational structure setup, design the forms, and then work on converting the data.