AutoNumber with constraints and interfield dependencies?

[ssanfu]

I looked at the attached Excel WB (Post # 39)......



Investigators.invFistName <<- spelling

Plasmids.plasmidMS (Y/N) <<-- Is the "(Y/N)" part of the field name? (should not be)

How does the table "Antibodies" fit in? No FK field...


Looking at "RelationshipMap.xlsx", lets say you have 100 records in table "CellAssays".
When adding/updating records in tables "CellStorage", "GoGoGlow" and/or "Westerns", how do you know which record in table "CellAssays" is the one you want?

Table "Plasmids" has identifier field "plasmidName", "DeriMethods" has "derimetName", "FusionPartners" has "plasmidName", "HostCells" has "hostcellName"....
Table "CellAssays" has no such identifier field.

[Accessed]

I looked at the attached Excel WB (Post # 39)......

Investigators.invFistName <<- spelling

FistName...you never heard of fist name? It's like, don't be mouthin' off to me son or I'll be showin' you my fist name.

No, not working for you? Gomenasai.

Good catch - fortunately didn't make it into the DB :-)

Plasmids.plasmidMS (Y/N) <<-- Is the "(Y/N)" part of the field name? (should not be)

How does the table "Antibodies" fit in? No FK field...

And this is where I start getting into bigger trouble and draw the ire of my peers. You see, my problem is that I'm never happy - as soon as I map something out, I see the possibilities and want more. That's in part why I can't do things in a linear fashion and I sort of suck when it comes to following rules. At some point I just want to start fiddling - and this is me fiddling at my evil best.

The plasmidMS is meant to be a question - does the plasmid include a MS sequence (trust me, the full word is a mouthful to say and type)? If the answer is yes then the investigator would be prompted with a text box into which they would have to enter teh appropriate sequence. If the answer is no, then move on - nothing to see here.

Antibodies - another one of those QC tools we use. It doesn't have any relationship to the assays per se, but I need to incorporate the data they produce into the database so that the data are associated with a specific assay

Looking at "RelationshipMap.xlsx", lets say you have 100 records in table "CellAssays".
When adding/updating records in tables "CellStorage", "GoGoGlow" and/or "Westerns", how do you know which record in table "CellAssays" is the one you want?

Yup - you hit the proverbial head on the nail...err, yeah. So, I have made some progress and I now find myself at precisely this junction. Here is the current Relationship map:

Attachment 24833

And here you see where I realize there's a new challenge. You see, "assays" are a rather nebulous term. The word itself really incorporates several fields. Minimally an assay is composed of:

1. The Host Cell
2. The Fusion Partner
3. The Kinase
4. The Allele
5. The "Method" we use to produce the assay

In the lab, a cell line would typically be named "BaF3/BCR-ABL [T315I] (B)" which you might notice is a conjugation of all the fields listed above. But then throw in the MS sequence, the Epitope Tag, and the list goes on and on.

So I've come to realize that I somehow need to embrace the concept of a relational database and make it work for me. What I need is a query form that says:

"Good Morning Herr Dr. Knucklehead, what would you like to do today (can I interest you in some Bunnies)?"

The answer would be entered into a number of fields, just like the Freezer Log we/you have already created. So I would enter in the list of items 1-5 and then I would have choices like:

1. Do you want to thaw cells?
2. Do you wan to know which plasmid was used to make that assay?
3. Would you like to see the Western Data?
4. Would you like to see the GoGoGlow data?

So, in the relationship map above, I have the FreezerLog table on the right, and it appears to have everything I need. But in reality, the freezer log should simply use the assayFK to link it to the CellAssays table, because that table has all the information and makes all the associations that are really important to us (and there is the normalization I believe that has been stressed - and I get it). But in order to really see how all this might fit together, I had to start doing some work so that I could really understand what Access can do for me, and what in turn I have to do for it.

A true marriage :-)

Table "Plasmids" has identifier field "plasmidName", "DeriMethods" has "derimetName", "FusionPartners" has "plasmidName", "HostCells" has "hostcellName"....
Table "CellAssays" has no such identifier field.

Indeed - guilty as charged for all the reasons listed above.

I suspect you folks had no idea what madness I was about to unleash upon your tidy community. My honest and most sincere apologies. I'll do my best to behave, but will no doubt aggrevate everyone as I continue to toil away on this in my spare time.

For now, I need to fiddle some more and try some ideas to see how to tie these things (Freezer Log Table and CellAssay Table) together. That's not to say I won't gladly accept suggestions, and I am reading everything you direct me to as I have time.

Sincerely,
The Trouble Maker

[Accessed]

Okay, I do believe I've had a fun-filled evening. I think this is closer...and I think is much more "normalized". Thanks for poking and prodding me in the right direction - I hope you can see the product of your efforts:

Attachment 24834

Time for bed :-)

[orange]

Is it a Cellassay that gets put in a vial and stored in " freezer storage"?

If so, then it seems all of you tables up to the point of storage, are leading to CellAssay.
Each Cellassay (in a vial(uniquely barcoded) is placed in a storage Location)

When you put that vial into storage, you have:
-the barcode that can trace back to your other tables (cellAssay info)
-the storage location into which the vial was placed
-the Date and Time that the vial was placed into storage
-the name of the Person/tech that placed the vial into storage

To me, based on the evolving models and comments, the barcode is a major identifier that can be used to relate all of the constituent parts of a Cellassay. The barcode is one unique identifier, but you also have foreign keys or a composite unique index to relate the other table entries to the "vial"/CellAssay.

I have no idea what Westerns are (I'll disregard any comments re Gene Autry...), they may be a reference, as antibodies may be, to resolve/lookup/categorize some aspects of collection/analysis/study. Also, it isn't yet clear to me what an Investigator does" Or how they are assigned to "vials"? Perhaps there is a field of expertise or other attribute that associates and investigator with "whatever is investigated".(Then again it may be arbitrary/no rules?)

I would have an overall Audit log that records changes to any/all tables with who, when, did what to what type of capability. Again, I would create a table that spans the "Cryoarea" and use a series of update based queries to modify contents as appropriate.

But I would like to see a series of statements regarding each of the "things" that are represented by your evolving tables, and a statement (in business terms) of how these things relate to one another.

You have made some comments re Forms etc, and that will come in due time. All user interaction would take place via forms. The forms would contain controls to invoke "procedures/processes" to achieve things like searches, queries, reports.... Getting the business described sufficiently well, so that you can develop a data model and test it against your business rules, is key to a successful database.

Good luck

[Accessed]

Is it a Cellassay that gets put in a vial and stored in " freezer storage"?

That is correct

Each Cellassay (in a vial(uniquely barcoded) is placed in a storage Location)

When you put that vial into storage, you have:
-the barcode that can trace back to your other tables (cellAssay info)
-the storage location into which the vial was placed
-the Date and Time that the vial was placed into storage
-the name of the Person/tech that placed the vial into storage

To me, based on the evolving models and comments, the barcode is a major identifier that can be used to relate all of the constituent parts of a Cellassay. The barcode is one unique identifier, but you also have foreign keys or a composite unique index to relate the other table entries to the "vial"/CellAssay.

This is true - but there is a complicating twist. Each CellAssay is actually stored in 30 tubes/vials, and these are constantly replenished. Let me address some other questions and then comment further.

I have no idea what Westerns are (I'll disregard any comments re Gene Autry...), they may be a reference, as antibodies may be, to resolve/lookup/categorize some aspects of collection/analysis/study. Also, it isn't yet clear to me what an Investigator does" Or how they are assigned to "vials"? Perhaps there is a field of expertise or other attribute that associates and investigator with "whatever is investigated".(Then again it may be arbitrary/no rules?)

I would have an overall Audit log that records changes to any/all tables with who, when, did what to what type of capability. Again, I would create a table that spans the "Cryoarea" and use a series of update based queries to modify contents as appropriate.

I'm singing in my brain, just singing in my brain...

As you have noticed, Investigators are a common theme. Any given assay may be touched by several Investigators over the course of its life. Knowing who and when (Notebook & Page) creates an audit trail in case something goes wrong. But perhaps a built in audit log would be even better - particularly if it could be set up to alert me when key events take place (like someone looks up the location of an a frozen CellAssay but doesn't delete/extract it from the Freezer Log - yet it has disappeared).

But I would like to see a series of statements regarding each of the "things" that are represented by your evolving tables, and a statement (in business terms) of how these things relate to one another.

I think this would really help. I've come to realize that there are still aspects of the design that aren't right and I'm really struggling with them. But please let me make this important point: there are really three critical aspects to the operation that are all tied together yet must somehow "exist" somewhat autonomously. They are:

1. Production of the Plasmid

2. Production of the CellAssay (using the plasmid)

3. Cryostorage of the CellAssay (only after it has been created - and this doesn't always work) <-- Here's the key. Because this doesn't always work, we cannot use the freezer vial barcode as the key identifier in the workflow.

These are sequential events that absolutely depend on the prior event being completed first. So my database somehow needs to capture this aspect of the workflow. I think one area that I must improve is the CellAssay - it really needs to have a good unique identifier. In the past, we have strung together the various field attributes - for instance BaF3/BCR-ABL [T315I] (B) T060616 <-- this last text string, which is for all practical purposes the creation date, has always served as the unique identifier (and I think still should). We need this, because it is absolutely possible to have these two CellAssays:

1. BaF3/BCR-ABL [T315I] (B) T060616

2. BaF3/BCR-ABL [T315I] (B) T040314

Even though everything is the same, we must treat them as two different entities because they are created on different dates. So my thinking is that assayPK is the primary and critical unique key in the database, and the Txxxxxx creation date is the unique identifier that we can use to distinguish CellAssays from each other when these assays are being logged into the database (something that can even be assigned dynamically when an investigator first documents an attempt to create the assay).

Good luck

Thank you - yes, I need a whole bunch of this thing you call luck :-)

[Accessed]

Greeting again, we're back!

Progress is fun and this is starting to make sense. Much road lie ahead in this tunnel, but there is now a tiny pinhole of light. We have our relationships completed (thanks AgentOrange, mapping it out really helped identify and plug up the holes). Now we're at the form stage, and we have begun by using the template Steve provided as a foundation for storing our tubes/vials in the freezer. Here is the form in its present form:

Attachment 24845

The control boxes highlighted in blue text pull their field values from the shown Constructs query (this table used to be named plasmids), while those in red text are derived from the CellAssays table. The Construct control box is purple text because the field value derives from the Construct query, but is also used in the CellAssays table as the unique identifier of the construct that is introduced into the cells.

As you can see in this image, the Host Cell and Kinase have already been defined. Now what I want to do is learn how to program the control boxes that follow with the specific fields that are associated with each entry. For example, once EGFR is selected as the kinase, the only fusion partner with which it is associated is BCR. So when I select the fusion partner from the control box, rather than seeing this (which lists all available fusion partners):

Attachment 24846

I simply want the field to display only those fusion partners associated with EGFR - in this case BCR (fusparFK #3). The same rules would then apply to the subsequent fields, all looking up there choices in the Constructs Query. Once the blue and red entry fields have been completed, the freezer Log table can be properly populated with all of the correct values. I know this should be easy as it is a central feature of a relational database, but I just haven't figured out how to do it yet. Once I get this part figured out, then I think I'll be able to handle most of the rest without having to pester you poor folks any further.

David and The Voices (coming to a venue near you soon :-) )

[orange]

Can you post a copy of the database zip format? I'm interested in the relationships.
I would suggest using autonumber PKs on all tables, if you haven't done so. I see alphanumerics in your DepositVials form?

Now we're at the form stage,

Possibly, but I have a few suggestions for consideration.
If you have your relationships and tables established, have you tested your model with some test data?

Map out your processes -- these

These are sequential events that absolutely depend on the prior event being completed first.

Identify the sequence of these events, the event name and a brief (1 liner or phrase) what happens at that process step. If, at the end of a process step, you have an option to go to one or more steps, please identify same.

This will help you with forms and logic and interface design. Depending on your needs, you could set up a "standard set of messages" to assist users/developers/communications/documentation.

You can mock up your process flow/interface logic with a few forms and buttons, and some messages.' It may seem to be overkill, but you'll do more analysis and learn more about your requirements. We used to call this stub processing. You basically have a Start form--with a message of what this system is about and button(s) indicating next step in process. And these button(s) do the same for the next process...... Once you get this set up and tested, you can take each of the buttons and expand on the click event to incorporate any code you create.

You can sit with user and run thru the start form, click the buttons, see the logic and get feedback/comments.

Just some comments to consider.

[Accessed]

Can you post a copy of the database zip format?

Ask and ye shall receive. Sorry - rather long day and I had to strip sensitive/IP information that had already been added. This is G rated - nothing here to harm the children :-)

I'm interested in bunny relationships.

Really! Me TOO! We should talk ;-)

I would suggest using autonumber PKs on all tables, if you haven't done so. I see alphanumerics in your DepositVials form?

Nope, all autoNumbers. But in the form it is shown as names - because I have a Medndellian pea brain and I cannot keep track of all them numbers. They make me numb (see how I did that...numb...numbers....numbest!!)

Possibly, but I have a few suggestions for consideration.
If you have bunny relationships and bunny tables established, have you tested your "model" with some test bunnies?

That's a pretty private question me thinks. What I do with my "model" I only do behind closed doors ;-)

But yes, many different versions and models have been tested with them test data. Me getting better but still be caveman.

Map out your processes -- these Identify the sequence of these events, the event name and a brief (1 liner or phrase) what happens at that process step. If, at the end of a process step, you have an option to go to one or more steps, please identify same.

This will help you with forms and logic and interface design. Depending on your needs, you could set up a "standard set of messages" to assist users/developers/communications/documentation.

The bulk of that is in my head. Anytime I write these things down on paper I simply - lose the paper. But here's the readers digest version:

1. Take the puzzle pieces called "Vectors", "Kinases" and "Fusion Proteins" and assemble them into "Contructs".

2. Put the "Constructs" into "Host Cells" and use special "Derivation Methods" to produce "Cell Assays".

3. Test the "Cell Assays" using "Westerns" and collect "GloData". If those make you happy, put them in the "Freezer" using the "Freezer Log" as your road map to find the "Cane", "Box", "Column" and "Row" locations.

How you like them apples?

You can mock your bunnies with flow/interface logic and use good forms to push their buttons. Don't forget some massages.'

I'm thinking them bunnies might like this. I'll let you know tomorrow :-)

It may seem to be overkill, but you'll do more analysis and learn more about your requirements. We used to call this stub processing. You basically have a Start form--with a message of what this system is about and button(s) indicating next step in process. And these button(s) do the same for the next process...... Once you get this set up and tested, you can take each of the buttons and expand on the click event to incorporate any code you create.

Does dreaming count? I do lots of that and I'm pretty sure my forms have been chasing me around with a small hatchet. Seriously though, that's where I too want to go too. But before going there I want to understand this little lookup function. You see, I fixate. And when fixated, there's no fix for that fixation except asphyxiation. <-- see how I did that one, fix...fixed....Jim Fixylated

You can sit with user and run thru the start form, click the buttons, see the logic and get feedback/comments.

There is no logic to my madness, but I'm touched that you think there might be some lurking somewhere. If I find it I'll let you know.

Just some comments to consider.

They were great. Sorry for playing around - we're tired.

The Voices of Failed Logic

[orange]

Good stuff!

I have been away, but just opened my copy of your database. I have a small routine to help manage tables, fields etc. I ran it with your tables and offer the following comments. Keep in mind that I have been out of school several decades so some of these comments may be irrelevant.

Some of your fields seem like they might get their values from reference tables. (also known as lookup tables. these tables typically have an ID and a name, and perhaps a description. These are often used in combo/drop-down boxes from which the user selects the appropriate value. It reduces naming and spelling variations --provides consistency.)

1. Here is a list of tables and fields that you/your application may use reference /lookup table values for populating. You know your situation better than any reader, so you can determine relevance.

table_name	field_name
Antibodies	antiSource
Antibodies	antiCatNum**
Antibodies	antiLot
Antibodies	antiFrag
Antibodies	antiSpecies
Antibodies	antiIsotype
Antibodies	antiClonality
HostCells	hostcellCatNum**
HostCells	hostcellMedia
Investigators	invStatus
Kinases	kinaseClass
Kinases	kinaseGBR
Kinases	kinaseEL
Westerns	Lysis Method

2.** The CatNum... seems to be a "category-like" concept???


3. These fields have spaces embedded in their names. Should not have embedded spaces.

table_name	field_name
Westerns	Harvest Density
Westerns	Lysis Method
Westerns	Preparation Date
Westerns	Exposure Time

4. Instead of Birthday, you might consider DOB or DateOfBirth

table_name	field_name
Investigators	invBirthday

5. Not sure where antibodies-1 fits or what these fields are??
Also, what is table Antibodies_1?
It seems Westerns are tests.

table_name	field_name
Westerns	antiFK_IP
Westerns	antiFK_Primary
Westerns	antiFK_Second

6.The use of these fields in table Constructs is not clear.
Constructs fusparStart ---These tend to imply length
Constructs fusparEnd
Constructs kinaseStart ---These tend to imply time/duration
Constructs kinaseStop

Is there a reason, they aren't both End or Stop?

7. For your own benefit --for specifications, for documentation, for communication and for any training --it would be a good exercise to write a brief description/definition of each table and each field.

[Accessed]

Greetings Earthlings!

It is we, me and I - and we are back. Much to do about everything during our absence. To start off, the weakest link actually purchased a book and started reading (Access 2016 Bible by Alexander and Kusleika). 1089 pages of Access bliss - we thinks he mights have blown the cerebral cortex :-)

But seriously folks (that I rarely am) - if anything I have learned that this is a great place to...learn. I have been helped more here than anywhere else, or by anything else I have read. For that you earn my full respect and kudos for creating a warm environment to learn - even embracing the psychopaths such as me :-)

So I have managed to construct what I hope is at least a 2N - if not a 3N - database. I know pride is one of the 7even deadly sins, but I'm feeling rather naughty today - so I will say I'm quite pleased with what I've accomplished so far. But now I am embarking on my first efforts to create some forms and I come again seeking assistance with two favors to ask:

1. In attempting to generate forms, I've discovered that I can actually overwrite values in some of my tables. This is not good and I want to lock down those entries I have made. Is there a simple way to do so on a global scale, yet leave those fields that have not yet received entries (such as the many incomplete entries in tblConstructs) so that the entries can be made at a later date?

2. I want to make a form that is based off my first query. In my mind it would consist of dependent control boxes. The first would list all of the unique kinases (without duplicates). Once one of the kinases has been selected, you could use a second control box to select from the alleles available for that particular kinase, then select from the available fusion partners and finally the available host cells. Not sure what the proper term would be - maybe cascading dependencies?

The reason for #2 is I would like to prepare a form where clients could select a specific cell assay using the various options available in qryCellAssays. The form would be more of a running list so that they can sequentially choose the various assays without limit, i.e.

1. ALK/WT/EML4/BaF3
2. ALK/WT/TPM3/BaF3
3. ALK/L1196M/BCR/BAF3

and so on. A simple nudge in the right direction should help. The zipped database is attached for your amusement. I know there's still much work to do (like assigning indexes), but I'm getting there.

[orange]

Invalid attachment shows when attempting to open your file.

Once one of the kinases has been selected, you could use a second control box to select from the alleles available for that particular kinase, then select from the available fusion partners and finally the available host cells. Not sure what the proper term would be - maybe cascading dependencies?

This is known as Cascading comboboxes.

Sample free videos of the concept
http://www.datapigtechnologies.com/f...combobox1.html
http://www.datapigtechnologies.com/f...combobox2.html

Have you made a list of "questions" you want to be able to answer with this proposed database?
Have you worked through your current model with sample data?
Does your model support/answer the questions?

If by using a form, you can change existing data -and you don't want that to happen - you may want to avoid a bound form. However, more info about what you want to accomplish under what circumstances will help with design. Clear specifications and requirements always facilitate development.

Good luck with your new book.

You may also find this 70+ free video series by Steve Bishop a great reference.

Here is an older series of videos by Dr. Art Langer that deals with Analysis and Data Flows This series is older and the quality of the video is not great, BUT ( the concepts and discussion) is very worthwhile.

[Accessed]

Oops - let's try again. Thanks for the videos, exactly what I was seeking!

[Accessed]

I made progress, but hit a new hurdle. In the attached DB, I can enter the correct "Kinase" and it properly filters the appropriate "Allele" in the next box. But then I need to apply both the "Kinase" and "Allele" entries together. If I don't, then "WT" returns many incorrect "Alleles" since this parameter is shared among most of the entries in the "Allele" field and are in fact associated with every "Kinase". As you can see in the attached, I tried to use "AND" to combine the two criteria, but the result is a prompt for a value, which entered results in nothing.

No doubt I'm missing some key stuff and the Google prophet did not reveal the secret (yet). I'll keep looking but will be grateful for more assistance

[Accessed]

While giving my brain a rest, let me answer some questions

Some of your fields seem like they might get their values from reference tables. (also known as lookup tables. these tables typically have an ID and a name, and perhaps a description. These are often used in combo/drop-down boxes from which the user selects the appropriate value. It reduces naming and spelling variations --provides consistency.)

Indeed. I actually added "Alleles" because there are many that I expect we will be re-using in the future and I want to make certain the nomenclature we use remains consistent. Some of the other suggestions below are really useful and can be worked in downstream. Baby steps and all.

2.** The CatNum... seems to be a "category-like" concept???

Catalog Number is simply for consistency in ordering. A given manufacturer can actually have many different antibody "flavors" and the CN helps us remember which one was tastiest :-)

3. These fields have spaces embedded in their names. Should not have embedded spaces.

Gone with the wind...

4. Instead of Birthday, you might consider DOB or DateOfBirth

Indeed. Looking at it now the choice of "Birthday" is really odd. Must have been late.

5. Not sure where antibodies-1 fits or what these fields are??

This is going to be a real challenge for me. Antibodies are indeed used in Western tests, and for any given antibody it can be used as either AntibodyIP, AntibodyPrimary or AntibodySecondary. But a single antibody cannot be used for all three at the same time (only in different experiments). So in essence I would have three different foreign keys all relating back (at different times) to the same primary key. I'll need to do a lot more reading to understand how to deal with this challenge.

Also, what is table Antibodies_1?

It was an accidental click that took me forever to get rid of. But it's finally gone :-)

6.The use of these fields in table Constructs is not clear.
Constructs fusparStart ---These tend to imply length
Constructs fusparEnd
Constructs kinaseStart ---These tend to imply time/duration
Constructs kinaseStop

Is there a reason, they aren't both End or Stop?

You are very correct. Before internal beta testing I'll need to take some time to add descriptions for many of the more obscure fields. kinase and fusion proteins are a great example, but were carefully chosen. The answer is rather theoretical and I don't want to make (more) enemies. Suffice to say that there is still method to the madness, but the madness isn't quite done yet.

​

7. For your own benefit --for specifications, for documentation, for communication and for any training --it would be a good exercise to write a brief description/definition of each table and each field.

Yup, we both agree on that - it's grown much more complex than originally intended. Unfortunately now that I understand what it can do, it will absolutely continue to grow in complexity commensurate with my skill level (and patience).

[Accessed]

Okay, still chugging away and making progress. Figured out how to get the correct values to populate Fusion Protein:

SELECT qryCellAssays.FusionPartnerNameFROM qryCellAssays
WHERE (((qryCellAssays.KinaseName)=[forms]![frmAssayRequest].[cboKinase]) AND ((qryCellAssays.AlleleName)=[forms]![frmAssayRequest].[cboAllele]))
GROUP BY qryCellAssays.FusionPartnerName
ORDER BY qryCellAssays.FusionPartnerName;

But now I cannot figger out how to trigger two simultaneous "Requery" events of both kinase and allele combo boxes. Once I get that then I think I'll wrap up today's project.

And back again with the solution. Context (command order)/syntax is indeed everything:

Private Sub cboAllele_AfterUpdate()
Me.cboKinase.Requery
Me.cboFP.Requery
End Sub

One more combo box to go and then I shall rest (far more than seven days though ;-)