AutoNumber with constraints and interfield dependencies?

[Accessed]

So, last night I spent some quality time reading about DB "Normalization" and have come to realize that mine...is not. I think I can say I did okay for a novice, but now understanding the complications I realize that there's work to be done. My plan right now is to in many ways start from scratch - particularly since I haven't done that much yet. I did come to realize that there may be information that I'm not accessing properly. For instance, when I view the property sheet in design view, the drop down list does not reflect the name changes to row (now boxrow) and column (boxcolumn) that I applied to the actual table in design view. No doubt there is a name field lurking somewhere that needs to be modified?



I'll post significant milestones as there achieved in honor of those that have assisted so far. The first step will be to properly lay out the database and figure out where the relationships should lie.

[ssanfu]

For instance, when I view the property sheet in design view, the drop down list does not reflect the name changes to row (now boxrow) and column (boxcolumn) that I applied to the actual table in design view.

This sounds suspiciously like you have look up fields in tables. Would advise AGAINST using look up fields (in tables), multi-valued fields, and calculated fields.

In (re)designing your tables, I would suggest you use pencil & paper, whiteboard, sticky notes, the window, etc to do the design work before you dive back into typing in Access.

[orange]

Re: your post #10.
I participated in the discussion, and some others involving "freezing replicated samples".
I was surprised to see as many database forum participants who actually work in research/industry or labs with samples and testing.

I used to work in a lab many years ago, but remembered some forum discussions the helped me with "Googling".

I agree with Steve --don't use lookups at table field level. Also, stay clear of multivalued fields.

In my view, your requirements are critical to your table and relationship design.
Placement of individual samples with specific barcodes in a multilevel structure. Jeez - it sounds a bit like a 3 dimensional crossword. Pencil and paper and scenarios with lots of testing-- and once you get the design and approach clear, then to Access and development.

Good luck.

[Accessed]

Steve and Orange - you are both correct. I do (believe) I was using lookup fields at the table level - and I'm probably making it (far?) more complicated than it needs to be. I'm also a true neophyte - never afraid to foolishly jump in with both feet cast in cement and then madly thrash to keep my head above water. I really started to see trouble when I looked at the existing relationships between my tables and saw this (you are welcome to laugh at my expense):

Attachment 24790

I even chose to display my defiant disregard for intelligence by not enforcing referential integrity (I can hear you snickering).

So I am indeed back at square one now. If not too much of an imposition, I hope you will entertain my various steps to get this right. I might be dumb, but I promise a good show if you want to ride the see-saw with me.

Sorry- have to go now...the voices are calling me.

[ssanfu]

I don't think you are at step 1.... maybe 1.7 or possible 2. You have learned a lot.


I have been playing around with a dB..... the picture of the relationship window allowed me to see field names. My field types are not correct, but you can get the idea......

Creating the dB and reading the post orange posted (post #9) raised a lot more questions for me.....

I didn't use the code from Allen Browne - I used unbound controls and wrote some code. Remember, this is demo, only a demo!
Selecting the freezer finds the first open Cane, Box, Row & Column. Very simple code - no smarts... the code - not me. (well, maybe me)
After saving, the next open row & column is found.


Remember, this is a quick and dirty demo

[orange]

Good stuff Steve.
When I read the older thread, I had the understanding that they created a table with all of the storage locations, and they included an IsOccupied/IsNotOccupied flag that could be used to show "next storage/available storage". I haven't studied this OPs data in detail, and have just scanned the older thread.

They seemed to deal with all possible storage locations, and another set up to deal with the smallest unit of sample/sample size. Working with smallest items and storage units if you will.

It will be important to understand the details of What gets stored, for how long... When something is removed, the storage location becomes unoccupied etc.
The occupied/unoccupied allows the use of the listbox "trick" from Datapig which may be a very intuitive interface. Its the details for mocking up a prototype that are still a little sketchy (to me anyway).
It would be good if the OP could create some clear scenarios along the line:
--I have to store 14 samples, I have to remove samples from TestX, For some reason, 10 samples in TestK have to be moved to another location... What samples are in FreezerY,Cane 3,....
just some queries that will require logic and processes to keep things organized.

As I said, good material that should help the OP. I like that you don't have embedded spaces in names. Perhaps the OP can work with your evolving tables and we can see some progress to a demo prototype.

The OP on the older post hasn't visited since Aug 2015, so is unlikely available for comments and advice. I did put in a PM but I don't put much faith in getting an answer.

[Accessed]

Okay - hard to ask for help without showing everyone where I'm trying to go. Hopefully this attachment will help you see my inner madness...and also shed some more light on my greater goals. I don't expect to get there in a day and this is simply meant to be a reference (entertainment?) map so you'll understand where all my strange questions are coming from (well, at least some of them I hope). While you read I will catch up on all of the information you've posted for me - thanks in advance for everything!

Dazed and Confused

[Accessed]

Wow - starstruck and speechless. The latter is rare - trust me. I'm eternally grateful and forever in your debt Steve. Now I'm off to study the treats you have placed in my Halloween basket :-)

BTW - Probably would have been better served reading your post before making my PDF. Hope you enjoy my amateur hour ;-)

[orange]

Your pdf description helps with communication.

Do you have a fixed number of freezers? And therefore a fixed (maximum) number of storage locations?
I visualize a "condo complex" where there are B buildings, each with W wings, and each wing has F floors and each floor has A apartments. So with that structure, the apartments can be identified by
B,W,F,Aa---Azz where the total apartments per floor is zz. Since there is a physical value for zz, you have a maximum number of physical apartments. Initially these apartments could be labeled "unoccupied". You can assign tenants to apartments and label each assigned apartment as "occupied". As tenants move from /leave the complex, the associated apartment is labeled "unoccupied". As new tenants arrive, the "unoccupied" apartments are available for assignment.
Record keeping would allow identification of who is assigned/occupies which apartment (on which floor, in which wing of which Building.); when they were assigned to the apartment and how long they have been there; whether they have moved from one building(or wing or floor or apartment) to another. New tenants would not necessarily be located next to each other, but every tenant could be uniquely identified and their current apartment uniquely located.

If you build/create new buildings, with same structure, your storage increases. I have used the analogy of similar buildings, but it could be that the buildings have different structures --it just gets a little more complex, but the concept is that there is a physical structure and the apartments are either occupied or not occupied. This model doesn't preclude a building, wing,floor or apartment(s) being unavailable for "tenancy temporarily" because of repair/maintenance...

Just some thoughts for consideration.

Basic storage of a sample:

UnitSample---->SampleStoredAtLocation<-----UnitLocation

[Accessed]

Okay Steve - starting to understand what you did, though not necessarily why or how at this point. I had yet to grasp the concept of combo box versus text box, but I think I'm starting to see how they can be used. Where I still have much to learn; how/why you choose to use Queries versus Forms to access and store data in Tables - really interesting. What I can say is that what you have done is really slick - difference between what an amateur can do and what someone experienced can do.

I wanna be experienced.

[Accessed]

Your pdf description helps with communication.

Do you have a fixed number of freezers? And therefore a fixed (maximum) number of storage locations?

In practice - yes. We have two (Hansel and Gretel). Fortunately they never get lost because we give them plenty of crumbs. In reality we can have as many as we need. When Hansel and Gretel can eat no more, we simply look for more lost wanderers (to translate - we buy more freezers).

I visualize a "condo complex" where there are B buildings, each with....

I think so, but the analogy isn't working for me too well - yet (though I understand what you're saying). If I may borrow...

Freezer: Land purchased by a developer upon which ten story buildings (Condos) may be constructed. These "condos" are our "canes".

Canes: The land purchased has sufficient space to build nine Condos (Canes).

Boxes: Each "Box" is a floor of the Condo and each floor contains 96 rooms (it's a really big condo). They're arranged in eight "rows" of 12 adjacent apartments (we call these "columns" by convention).

Rows x Columns: Intersection of Row and Column is a room, and each room holds a single bunny. Sunny Bunny and Honey Bunny are actually one schizophrenic hermaphrodite in one room - sad really. We'll call IT Sunny Honey Bunny Smith.

Assays: Each assay is a family of identical bunny hermaphrodites. They're cute, so we usually like to keep 24 on hand (notice how I already revised my number?). One floor can therefore house four families of 24 Sunny Honey Bunnies (The Smiths, Schwarzes, Petersons and McCoys). The Hatfields unfortunately must be housed on a separate floor.

Storage and Retrieval: Think of my rock stars (staff) as angry vultures that regularly raid the bunny dens to provide sustenance (they're poorly paid, but they LOVE bunnies and they have all they can - um - cuddle). When Hans has "cuddled" 16 Sunny Honey Bunny Smiths, he must encourage one of the remaining eight to...make more bunny Smiths (see how nicely I kept that all PG for the youngins?). Sunny Honey Bunny Smith #1 is really good, s/he always gives birth to exactly 17 babies. It's really remarkable to watch (if you're into that kind of thing). Once done, there is again a family of 24 SHBSs. The other Smiths have identical remarkable talents.

Creating Bunnies: It's not easy producing hermaphrodite bunnies that can produce litters that are exactly the same size every time. When we do so, there is inevitably differences that arise - primarily because we never start with the same DNA pieces. As a consequence, some jump up and down, some jump only to the left, others jump only to the right...and the Schwarzes? Well, they simply don't move...ever! Variety though is the spice of life, right? So we have to keep track of which family does what, otherwise we might forget that the Schwarzes must have their food sack tied around their neck if we don't want them to starve to death.

So, if you're still with me, I'm basically looking for some help with the database logistics so I don't lose my bunnies.

Glad I didn't use marbles for my analogies :-)

[orange]

Ok, I think you may be making it a little more complex than necessary, but I do like the narrative.

This

UnitSample---->SampleStoredAtLocation<-----UnitLocation

is specific to storing a single "bunny" in a specific "room".
The "bunny" has a unique identifier (barcode in your terms) and the room has a unique identifier composed of Building/freezer + Wing/Cane + Floor/Box + Row + Column; and info pertaining to an individual "bunny's" residence is stored in the junction table.

All related information about a "bunny" or "family of bunnies" is held in other related tables. I'm focusing on the storage and identification of locations at the lowest level of "bunny" and "room".


Multiple freezers

Multiple things in each freezer

and multiple things ( within each thing) repeated as necessary

Pictures didn't go as planned--oh well.
These pictures are from an automated tape library system where software and hardware and a robot do backups to tape(s), record the data and the location etc and can recall the required data and recreate files on various hardware on various networks....

Info about the files is in a database, the tape volumes and contents are in the database, Date of backup, size of file, status of the tape volume, original file location...... whatever you have that is related to the file is in the database.
The robot, and associated logic, loads, copies files and stores tapes and records all interaction.

[ssanfu]

@orange

I had the understanding that they created a table with all of the storage locations, and they included an IsOccupied/IsNotOccupied flag that could be used to show "next storage/available storage".

I read it that as samples were added, add the record. Hadn't thought of creating all of the records first, then having the occupied/unoccupied flag.

As I said, creating the demo caused a lot more questions. Deleting, moving, need history?

I like that you don't have embedded spaces in names.

I'm very rigid about no spaces, punctuation or special characters in object names. And no lookup fields, MVF or calculated fields.

--------------
@Accessed
I attached a new ver of the demo. I was puzzled by a comment by you and found that, in the forms with the "frm" prefix, I had left record source as a table instead of a query. Now a query is used as a form record source and a combo box row source. (also fixed spelling error)

Assays: Each assay is a family of identical bunny hermaphrodites. They're cute, so we usually like to keep 24 on hand (notice how I already revised my number?). One floor can therefore house four families of 24 Sunny Honey Bunnies (The Smiths, Schwarzes, Petersons and McCoys). The Hatfields unfortunately must be housed on a separate floor.

Whoops!! Assays??? Where/how to these fit in the grand scheme of things? The voices NEVER said anything about Assays!!!
This is why using pencil and paper (or whiteboard) to design the table structure is better than diving into Access and creating tables....


Since Freezers can be added, the number of Canes, Boxes, Rows,Columns in one could be different than the previous freezers.
I added 4 more fields to the Freezer table for the max values (Canes, Boxes, Rows, Columns)


You get to decide how to accomplish things like add records as needed or add all possible records (slots) and update the record - this affects adding, moving and deleting records.
Does a history need to be kept of every entry (bunny?)? Added, moved, deleted????

These decisions need happen in the table design phase......

[Accessed]

The voices NEVER said anything about Assays!!!

Hello Steve...

Yes, you guessed right. We are THE Voices. See, here's the thing. You're not supposed to hear us - that's what makes us so special :-)

Not to worry though, we tend to stay quiet and not muddle around with the gray matter - too much.

As for those "assays", you already know them. They're what is inside all those tubes being stored in the freezer. See, crazy guy took your advice and made that diagram - it's in his 470 KB manifesto posted earlier.

As for crazy guy himself, we sent him to bed. Seems he's been a bit busy lately and we decided he needed to go dream of bugs bunny - dressed in a skirt. We are a wicked set ;-)

Since Freezers can be added, the number of Canes, Boxes, Rows,Columns in one could be different than the previous freezers.
I added 4 more fields to the Freezer table for the max values (Canes, Boxes, Rows, Columns)


You get to decide how to accomplish things like add records as needed or add all possible records (slots) and update the record - this affects adding, moving and deleting records.
Does a history need to be kept of every entry (bunny?)? Added, moved, deleted????

These decisions need happen in the table design phase......

We take pleasure in speaking for our little nutcase directly. You should know just how thrilled he is with all these bytes he's been digesting. We haven't seen him smile this enthusiastically since Easter! No doubt he'll be back at it tomorrow and will have much to make him hop about like...well, you know.

Tschus!

[Accessed]

For Agent Orange:

Tape drives? Rock on!! I love 8-track tapes myself :-)

Seriously, this is good and I get it now. Let me rest my noggin a little and I'll get back at it :-)