AutoNumber with constraints and interfield dependencies?

[Accessed]

So - that task is finished for the day. I do realize now though that I will also need to refresh all the fields after the entries are complete. If this doesn't happen and a user sees that WT is already in the allele box from the previous selection, then they are compelled to use this entry, which of course leads to the wrong output.



So complicated this coding stuff is.

[orange]

I'll read these in more detail later.
Can you post your latest database --zip?

[Accessed]

Hope this works fine - has my latest attempts. Kinda worn out...too many voices.

[orange]

A few comments:

AllelePK is number ---why not autonumber??

I would not use Hyperlink datatype. I'd use text and record the hyperlink as a string. When you want to use it as a real link, then use Application.FollowHyperlink txtURL . Others may have more info/suggestions.

No relationships for some tables??

Have you tested the model with sample scenarios?

[ssanfu]

I am still following (lurking) this thread and have a couple of comments also.

I do realize now though that I will also need to refresh all the fields after the entries are complete. If this doesn't happen and a user sees that WT is already in the allele box from the previous selection, then they are compelled to use this entry, which of course leads to the wrong output.

I added code to the existing after update code to handle changes in the combo boxes. You have to clear the dependent combo boxes, then re-query them.
Make selections in all of the combo boxes, then go to combo box "Kinase" and make a different selection......



The big problem I see is that in your form, the combo boxes are displaying text. That is OK, but at some point, you will have to store the values in a table. With all of the voices talking, did you hear the little voice that was warning you that the text in the combo boxes could not be stored in the table?? You need to store the PK value (a long), but the combo boxes are TEXT!!



So I modified your query and named it "Query1" (unique... right? ). Then I created a new form (you guessed it) "Form1".
If you open both forms side by side and make the same selections, you will see that "Form1" displays the text, but will store the PK value.
(For both forms, compare the "Kinase" combo box properties for row source (data tab), column count and column width (format tab).)

[Accessed]

The big problem I see is that in your form, the combo boxes are displaying text. That is OK, but at some point, you will have to store the values in a table. With all of the voices talking, did you hear the little voice that was warning you that the text in the combo boxes could not be stored in the table?? You need to store the PK value (a long), but the combo boxes are TEXT!!



So I modified your query and named it "Query1" (unique... right? ). Then I created a new form (you guessed it) "Form1".
If you open both forms side by side and make the same selections, you will see that "Form1" displays the text, but will store the PK value.
(For both forms, compare the "Kinase" combo box properties for row source (data tab), column count and column width (format tab).)

Welcome back to the fun house that is called my madness (or is it my madness that is called a fun house?).

Ah yes, indeed I knew I was storing text and was doing so for what I perceived to be all the right reasons (but are probably all the wrong ones). Namely, those text values are intended to be exported to an Excel workbook that serves as an intermediate for generating client reports. I don't actually want to store any of that information (at least not at this point) in our database. Rather I want to reference the database to extract the correct associations that either exist now - or soon will. Then once I have the information transferred to Excel, I want to wipe the information from the database.


...but I realize that's pretty stoopid and there certainly is a better solution. Just need to put my thinking cap back on so I can listen more carefully to the voices. For a side foray into my insanity, I've also attached my Excel project so you can have a little taste of the other side of things. Be careful though - it's likely to not make any sense at all.

The other part of the story is now I'm wandering into more complex areas that I really have no clue yet how to handle - or even how to describe it to ask for help. But in the latest installment you'll notice little hints of where I'm starting to head, and hopefully also notice that I am making more progress and starting to understand the importance of both syntax and event order.

Baby (seal) steps...don't club me bro' ;-)

Thanks for the new database to enjoy (and plagiarize) - as you can see I'm rather fond of the code I can find in those back rooms of your examples and it really helps me learn and explore.

Gracias Amigo!

[Accessed]

A few comments:

AllelePK is number ---why not autonumber??

Simply oversight - thanks for catching that. Much of the data were uploaded into Access using excel files where I generated the primary keys using arrayed index:match lookups. Probably really silly way to do it, but I couldn't figure out any other way. No doubt I forgot to properly set that field. So far though I have yet to find an anomaly - so perhaps I got lucky. At least now all the key stuff is in there so I can start playing with my....oh wait, I'm getting ahead of myself.

I would not use Hyperlink datatype. I'd use text and record the hyperlink as a string. When you want to use it as a real link, then use Application.FollowHyperlink txtURL . Others may have more info/suggestions.

Sounds like sage advice from someone who has been there done dat. Thanks - I will do so

No relationships for some tables??

Yeah - not yet. They're still pieces of an evolving puzzle. I can't yet see in my mind's eye how to fit everything together, so the tables are merely holding tanks while I try out different ideas

Have you tested the model with sample scenarios?

What, and not involve my bunny friends? C'mon Agent Orange, what kind of sicko do you think I am? {snickers in the background}

[Accessed]

Okay Steve - went over your code and as usual pretty slick. I understand most of what you did, but you've snuck some stuff in there that doesn't yet make sense to me. For instance, you've referenced my tables as "tb", yet I have them named as "tbl". This is not the first time that there's been "things" that leave me scratching my head. Good stuff though and certainly more fun study material :-)

Edit - upon waking this morning he realized that he was looking at the label fields rather than at the actual combo box field. He now completely understands everything that was done. He can really be kind of stoopid sometimes this bald-knuckle headed freak we call master.

With Boisterous Sincerity - The Voices

[Accessed]

One last thing before I retire. At one point I had a very nice SQL View tab on the bottom right of the application. It has very suddenly disappeared on both my computers (haven't looked at other files yet). What in the world did I do?

Never mind - old fart moment. Forgot that SQL only shows up when viewing queries.

Silly me :-)

[Accessed]

Okay, another busy few days find the database growing into a monster (complete with a name change to reflect its gargantuan proportions). There is still plenty of work to do, but I don't think there will be too many more tables going in for a while. So I'm going to post the latest version so that it can serve as a source for further discussion since - once populated with the next round of data - it will have too much proprietary stuff to methodically cleanse.

I do have a BIG question though. I'm about to populate the table titled "tblValidation16Q2" with a massive amount of data. In it's prime, it will contain (at least) 15,183 unique entries in each of the three fields titled CellAvgIC, CellSD and CellN. I also expect this number to continue to grow with time, but plan to "recycle" the table once per quarter with a new table containing updated information. But I'm at a really important inflection point - I have an alternative choice of making ~ 65 different tables, each with 241 entries into each of the above three fields. The downside of the latter approach of course is having to "recycle" 65 tables four times per year (though likely could be handled by some sort of macro?).

So, what are the opinions of those that have traveled this road before?

[orange]

I do have a BIG question though

Before we answer,

-please provide a description of "tblValidation16Q2" what it is? what it's for? (as simple English as possible)
-ditto for each of CellAvgIC, CellSD and CellN

Why do you think there is an option for design of the tables? You know the subject, we don't, so please explain/describe your thoughts/rationale.

What is the Event (inflection point) that occurs quarterly?

These all seem to be factors in your "business", and your database should support your business. So we need clarification on these aspects of the business.

I would NOT be adding "(at least) 15,183 unique entries" at this point. While testing and evaluation the current model, add 10---20 records. Do some testing. Decide which structure you will go with, add a few records, then test. When convinced of structure and vetted with the model, then add the data.

I have just opened your latest database.
For your own benefit, when working with your relationships, try to reduce/minimize the number of crossed lines. It will become a reference for maintenance and training. It will also help you understand the database structure.

Good luck.

[ssanfu]

Wow... this has really exploded from "How do I store vials"!!


So drink a couple of your favorite ice cold beverages to silence the voices and listen to me....

About the new table:

<snip>... I'm about to populate the table titled "tblValidation16Q2" <snip> ...but plan to "recycle" the table once per quarter

<snip>...a massive amount of data. In it's prime, it will contain (at least) 15,183 unique entries <snip? ...

<snip>...I have an alternative choice of making ~ 65 different tables,..<snip>..."recycle" 65 tables four times per year

First, the massive amount of data... This is a tiny amount of data. I have a table with 28 field and a little over 2 million records. So no worries there.

Second, the table name. It looks to me that "16Q2" is the year (2016) and the quarter. And you are planning on recycling the table every quarter. Does that mean you are going to delete the table and create a new table named "tblValidation16Q3" and another table later named "tblValidation16Q4"?
Now expand that to 65 tables......
Congratulations!! You are now a full time database support programmer!

Yessss, this could be done in code, but...... What about the queries? They will need to be edited for the new table name(s). Then there are the forms.... and reports. And don't forget having to modify the VBA code. Yikes!!!!

Third. Table "tblValidation16Q3" is in a 1 to many relationship with "tblAssayRequest" (with RI). What happens to the records in "tblAssayRequest"?? They are all orphaned. (I don't think you will be able to delete "tblValidation16Q3" because of RI.)

--------------------
Consider this:
Name the table "tblValidation". Add another field... maybe named "ValidationDate" (type Text). The data for this field would look like "16Q2", "16Q3", "16Q4".
You said 65 tables, so add one more field to define which table of the 65 tables the data "belongs" to.
You didn't give an example of those table names, so I'll use "TableName" (type Text) for now.

So...... 65 tables with (approx) 241 records per quarter is (approx) 62,660 records per year.
Looking ahead 10 years, that is only 626,600 records (approx)..... and still a small number of records!!


Benefits:
- require one table
- remove the hassle of deleting and re-creating table(s)
- no re-linking of table relationships
- remove the re-writing or re-designing of code, queries, forms and reports.
- no orphan records to try and deal with
- relieves you from being a full time database support programmer!


What do the voices say about this???? Hmmmmmm??

[Accessed]

Good Morning Fellow Access enthusiasts!

First - thanks to my two colleagues Agent Orange and Dr. Velcro for their recent contributions. Truth be told I didn't see the most recent post until just now and will need to review (thoroughly) when I'm done with this. As for the statement regarding the growth of the database - so very true. Obviously there is a significant amount of sensitive content. There is also a decades worth of knowledge that would be difficult to relay in words - and all of you would hate me for even trying. But let me summarize as simply as I can - with the aid of some images:

1. You are already familiar with the concept of the Cell Assays - we have a grand total of 243 of these and the list is growing monthly.

2. Next there are the reference compounds. These are drugs like Aspirin and Viagra (we currently have 63 that we use - and this list is also growing). These drugs for us are like a master key to a collection of 243 padlocks - that master key fits into all of them and will open all of them. However, some keys work really well and others you have to jiggle and wiggle while turning for them to open the lock. We quantify this using a value we call IC50 - a low number means it turns easily and a high value means it's really hard to turn. We then try this over and over again and each time make an entry into a separate database that calculates a rolling average that includes the number of times we've done it (N) and the standard deviation of all those measurements (SD).

3. Finally, there are customers and their experimental compounds (which might someday be promoted from "Cabin Boy" to "Cabin Man"). We test these for them and provide the same information for their compounds, and how those data compare to the reference standards described in number 2 above.

So that's our business in a nutshell - make assays and use those assays to help our customers make the perfect master key for a particular protein called a kinase that - when malfunctioning - makes you sick. Make sense?

Steve, absolutely spot on with your analysis. I'm sorry for getting my terms confused - in reality there are only a few fields and currently 7500 records. All of this information was stored in Excel and was slow as can be. Now it looks like this and is lightening quick :-)



As you can see, I already figured out that this can be done with a single table - with the added knowledge that I haven't yet challenged the capacity of a table (not even close) the recommendation of adding a field to track year and quarter is the missing link! A very simple and elegant solution that I will absolutely use.

So now I have a database that accurately reflects the current state of our business and I've been playing with it all weekend after completing record uploads on Friday night. Now my attention turns to forms so that I can provide my staff and colleagues access to this information - but prevent them from messing up a month's hard work. I'm getting pretty good at forms too, but still have a remaining head scratcher where my knowledge is lacking.

Again we come back to our beloved Freezer Log:



And herein lies the question. My (Steve's form properly) has combo boxes where the end user selects the properties that collectively make up a "Cell Assay" that has it's own primary key. So in the Cell Freezer Log table. Their entries for "kinase" and "allele" and "host cell" etc. need to be converted into this primary key. This is where I'm still a mental midget I suppose. My guess is that the foreign key (cellassayFK) needs to be included in my query. Do I then need to have a text box in the above form (like Steve showed in an earlier post) that is hidden so that the value can be inserted into the table? Or can I simply reference the field in the query (or better yet, not even include it in the query at all since it is ultimately associated with all the shown form fields through referential integrity).

I'll obviously be studying this, but as always happy to have your valuable insight.

Thanks again all!

David and the voices

[Accessed]

So I guess sometimes you simply have to put something down in "ink" (aka bytes) before you see the errors of your ways. I think I now see the light - I can simply list the desired field in my query without having to show it in my form (I learned this from Steve's second database demo2). So in this case I simply need to add the field "cellassayFK" to my query and then I can reference that field when I insert the record into "frmCellFreezerLog". Seems almost obvious now that I break it down.

Wouldn't be surprised if there were even more elegant ways to do this - but I suspect it will work once I modify the SQL code.

[Accessed]

Back again. I realize I can create a new combo box, but in the future I want to know how to simply copy and paste an existing combo box and then redirect it to the correct field to display in the drop down. I have spent the better part of an hour and realize I'm clueless. Eventually I suppose I'll figure it out, but what in the world is the secret and what am I missing? Specifically, I can redirect the combo box to the correct query (in this case "qryCellAssays"), but its showing me the wrong column from the query. I've tried changing the bound column, the column count and the column width, and I can affect some changes, but not what I'm looking for.

Help (again)....