yet another partial match question...

**Rabbit** · Mar 31 '12, 09:09 PM

You should normalize you data instead of storing it this way. It will make querying much easier.

**zmbd** · Mar 31 '12, 11:16 PM

Microsoft answer, technically correct, but missed the mark.

Originally posted by Rabbit

You should normalize you data instead of storing it this way. It will make querying much easier.

Rabbit,
Thank you for that suggestion; however, the overlying data is normalized.
ie:
This is much like a library or other inventory management database, as a matter of fact, that is the framework I started with - don't re-invent the wheel:
- Table with the unique manufacture details
- Table with the unique product details linked back to the [manufacture_id]. That way one manufacture has multiple products then I can simply query by manufacture_id and return all of their prod
- Table with the unique asset details (ie serial number), linked back to the [product_id].
- Table with just allowed locations (such as building-1, building-2). This is to standardize the location naming.
- Table with just the allowed user details
- Table with the allowed states (such as given in the question). This is to standardize the status naming.
- Table with event history that links back to the [asset_id], [user_id], [status_id] (aka:[ID] in this question), [location_id], [History_Date] of entry and a [comment]. I even use this table to determine the last status of the asset by querying for the asset as a record set and looking a the data in the last record (eof).

Note all of the [*_ID] are foreign_keys between tables.

The table in question is for the "status" of the inventory item so that the same wording is used every time (however, the [status_id] is actually the primary key that is used just-in-case someone decides to change "OutOfServi ce" to "Out of Order." An archive might flatten the information but that's for another time.

Now, when the user has the form open to create a new history event for the asset - the quandary is that it doesn't make sense to allow a record entry for an item that is "returned" ([ID]=2) when it was never in the "shipped" ([ID]=4) state or "repaired" if never "OutOfServi ce." So, it would be better to not have the illogical status in the available list of the combo-box.

For all of the following solutions: The asset has already been found, the history table has been queried, and the record set deals only with those records related to the asset of interest.

My solution 1:
Looking at the current status of the inventory item in question, then using a huge VBA SELECT CASE (condition)/Case/CaseElse in an attempt to handle every possible combination of current inventory status and the next logically allowed status. I would have to go into the code every time a new condition is added... this is not only time consuming, but requires a lot of test and retest and when I'm gone, a nightmare for the next programmer! And My time is very limited from day to day...

My solution 2:
Build a table where I have a record linking the status [ID] and the next allowed status [ID]; thus, having a record for each allowed status (i.e. in this example, for [ID]=1 there would be three record entries {1,3};{1,4};{1, 11}. Then I can build a join query between the two tables, yea. Now this may be the more "Normalized " approach; however, it seems about as clumsy as solution 1. Creating 3 data-records for one record, or maybe even 5 or 6 records per one record. I can automate the table entries; however, this just doesn't seem to be an elegant solution.

My solution 3:
As given in the question. 1 record for each of the allowed status with a field that has the next logically allowed states.

In this case, I am not concerned about being able to build a query based on the [str_nextallowed status] field. It is simply a field used to limit the choices provided in a combo-box from the status table for the next asset history record.

SO, now back to the orginal quesition.
Z

**Rabbit** · Apr 1 '12, 03:36 AM

It's not normalized because the next allowed status field contains more than one value.

Solution two is the correct approach because it normalizes the data and results in the most optimized and simplest query. Your characterizatio n that it is more complex is incorrect. It is actually simpler, it's just more records. But it makes everything else easier.

And you said it uses three records to represent one record. But that's incorrect. What you have currently is actually three records worth of information that is being crammed into one record. And that is what makes the query more complicated.

**zmbd** · Apr 1 '12, 05:34 AM

Originally posted by Rabbit

It's not normalized because the next allowed status field contains more than one value.

Solution two is the correct approach because it normalizes the data and results in the most optimized and simplest query. Your characterizatio n that it is more complex is incorrect. It is actually simpler, it's just more records. But it makes everything else easier.

And you said it uses three records to represent one record. But that's incorrect. What you have currently is actually three records worth of information that is being crammed into one record. And that is what makes the query more complicated.

Well... the main data base is normalized and solution 2 was the route I had (have) taken thus far; however, after creating over 60 records in "tbl_nextallowe dstatus" against just 15 records in "tbl_status " just to create a filter for a form's combo-box seems "CLUMSY," not more complex... this is a jack-hammer to remove a carpet tack.

It is just this one table, "tbl_satus" I was thinking on making the exception to the "normalize-everything" rule as it is basically a "Look-Up" table.
For example:
" http://www.sqlmag.com/article/perfor...-your-database "

The field [str_nextallowed status] will not be re-entered/used in any other table. I can't imagine EVER needing to generate/obtain asset records based on their "next logically allowed" status. Current status, yes, pull that data routinely.

Thus, is there a method of implementing solution 3?
I concede the point that this will partially de-normalize the database; however, it is ONLY on this one table for a specific combo-box record source.

**Rabbit** · Apr 1 '12, 10:10 PM

Well, if you want to keep it the way it is, you just need to jury rig the SQL a bit.

Code:

SELECT *
FROM sometable
WHERE ',' + nextallowed + ',' LIKE '%,3,%'

**NeoPa** · Apr 1 '12, 11:03 PM

Originally posted by zmbd

zmbd:
Well... the main data base is normalized and ...

You know, there's very little point in asking for help unless you're prepared to listen, and respond intelligently, to the comments provided. You surely must appreciate that, being the one asking for help, there's a chance that the one who's offering such help actually knows a little more on the subject than you may do. Rabbit is not likely to be posting to say that your explained structure is non-normalised unless, well, unless it is.

I suggest you familiarise yourself with some of those concepts (I'm suggesting you remind yourself of all that implies, rather than starting from scratch as you sound like you have some understanding of the matter if not a complete one) by checking out Database Normalisation and Table Structures.

**zmbd** · Apr 2 '12, 03:44 AM

@ Rabbit:
Thank you for your help!
More than likely, I'll stick with my "solution 2" - mainly because I already have the tables, query, and code constructed and, as you said, it is the more normalized approach. However, it just seemed overkill. Just to see if it could be done, after reading the article in the link I included in my reply, and several other related articles, I had tried "solution 3" and a SQL similar to what you offered before asking if "solution 3" was possible here; however, more than likely I made some typo in the SQL as it returned all of the records with at "3", "13", "33", and so forth. Just not worth either of our time to try and figure it out as the "Solution 2" works and I don't have to justify a non-normalized table to the work-group.

@ NeoPa:
- FIRST, and FORMOST! - I greatly appreciate the time and effort it takes to read all of these questions and even attempt an answer. What a Herculean task!!! And to do it for free, WOW!!!!!
Sincerely, THANK YOU! Would be nice if you could hear the truth in that person-2-person!

- Thank you for the link referring to DB-Normalization. It never hurts to refresh one's memory.

A) Rabbit's initial response was the "easy way out;" however, understandable given that Rabbit, at that point, had information only about one table out of some dozen or more tables in the database. His second response appeared to refer only the table in question and not the entire database - which I assure you is at least 3NF and more than likely BSNF... I haven't finished double checking for BSNF... given that the manufacturer's table has the address information in it that I haven't bothered to break the states, zip-codes, area-codes, etc... out of it, it more than likely isn't BSNF yet nor does it need to be.. 2NF would be enough for the project at hand.

B) Did you actually read my reply to Rabbit, or did you stop reading at the first sentence?

C) Did you read any of the rest of the thread before you made your post?

D) I believe that if you had read the entire thread you MIGHT have seen where I had already used a normalized approach ("Solution 2"), you would have had a link to a peer-reviewed journal concerning the absolute need to normalize every little detail (perhaps, maybe, I have read something that you haven't?), and hopefully, you would have posted something more helpful instead of implying that I am unintelligent.

NeoPa, for your information: I am a Chemist with a background both in mathematics and computer sciences; sub-disciplined in both the analytical and engineering fields. My normal data-sets are in the tens of thousands of data-points per sample analyzed, over two to five hundred samples a day across over a dozen different instrument systems and instrument types in over a dozen different laboratories from a very large customer group. A vast majority of the data is housed in either an Oracle or SQL backend databases which I can assure you have been tested by people with a lot more experience than either you or I in how to establish a normalized (3NF or BSNF) relational database. And these people have provided me with a great deal of "real-world" training - experience one doesn't get sitting in a college classroom. HOWEVER, that training costs a heavy price and everyone is cutting back costs in the current economic environment so I couldn't kibitz with them about a different approach without a little surcharge.

So, yes, I have a little experience in data-set theory and database design. So knowing that MS-Access has a few quirks, I asked a question based on a peer-reviewed article, and I am called "unintellig ent" by someone that can't even take the time to read the entire thread.

How Rude!

**NeoPa** · Apr 2 '12, 09:26 AM

Let me start by apologising if my response seemed to imply you were unintelligent. Clearly this is not the case, so I'm sure you appreciate the difference between indicating that an action is not an intelligent one, and the subject of that action not being so. That said, my wording was injudicious, and I probably should have said ill-considered.

I suspect it was the interpreting of that first sentence that led me astray. Now I read it again I realise you probably meant to say :
Well... the rest of my data base is normalized and ...

I'm a very literal, and often somewhat pedantic, worker and I interpreted what you did say as indicating a contradiction of Rabbit's statements, whereas now I appreciate you probably were not intending it to mean that at all.

You're right of course. Sometimes it's difficult to read every last word that's posted and still be able to be of assistance to as many as possible. I had come across some of your posts before though, and knew that you were generally of a higher intelligence than most (I noticed at least one helpful answer recently). I also read a bit of the linked article too. So, yes I read the thread. No, perhaps I didn't properly interpret the quoted comment. I'd blame it on the hour but I frequently work beyond midnight so that would be a feeble excuse.

However, my response was not as a sneering comentator, looking down on your intelligence, but as a moderator who appreciates that not every action or response from intelligent people can properly be described as intelligent. I think I misinterpreted on this occasion, but I feel very protective about the experts on this site who offer their time for free (Let me state clearly here that I no longer consider your behaviour indicates a need for such protection, but the explanation remains true nevertheless), and who are prohibited by our rules, essientially, from protecting themselves or arguing with members in the threads. It can be frustrating and off-putting for them when faced with such responses without that 'right to reply', and unfortunately many members don't seem to have the same respectful approach that you clearly have for the help that you're offered and need a little step-in to illustrate that there are boundaries beyond which they will not be allowed to stray.

Normally, if I were dealing with your response, I would have stripped out all the parts which would be considered to be argumentative or inflamatory from the thread, and responded via PM, where such an approach is allowed (I would have quoted accurately). However, an admin has already decided to allow your post, and I'm not about to override their decision, so I've responded in the thread. May I ask that, in future, if you have an issue with anything you see here, that you either report it or PM a moderator (or admin if, as in this case, you feel the unacceptable behavior is from a moderator). I can understand that you see red when you believe you were accused of being unintelligent, but to keep the technical forums clear of noise (Important probably, but not technical) we prefer such dealings are handled away from the forums. This rule has an unfortunate side-effect of allowing the idea that we are hiding from public criticism, which really isn't the reason at all, so I'm almost glad the Admin has chosen to allow this one to remain public.

May I just add, as a point of interest, that I still feel that de-normalising this particular data is counter-productive. I suspect your problem (for approach #3 that you describe in post #8) is due to omitting the commas in the search as Rabbit included in his suggestion. I'm sure, if you tried it exactly as he suggests, it would work for you. Why is the de-normalised approach a lesser approach? Here are a couple of thoughts I had on the matter. I'm sure there are more :

Checking normalised data can be done without de-atomising a field. The Like statement in SQL takes more overhead than a simple comparison. Essentially, you are taking database work and treating it like flat data - thereby losing the benefit of the many optimisations that a SQL back-end has built in.
If you ever need to link the table to others, then that is precluded if the data isn't normalised.
Precludes easy use of a ComboBox in order to control potential entries in linked tables.

**zmbd** · Apr 2 '12, 11:04 AM

@ NeoPA:

:)

How does one get the "quote message" to work... humbled by a webpage. ;-)

Anyway,

Checking normalised data can be done without de-atomising a field. The Like statement in SQL takes more overhead than a simple comparison. Essentially, you are taking database work and treating it like flat data - thereby losing the benefit of the many optimisations that a SQL back-end has built in.
If you ever need to link the table to others, then that is precluded if the data isn't normalised.
Precludes easy use of a ComboBox in order to control potential entries in linked tables.

Very valid points. Your last two points are however the crux of the question. The table is both the row-source for a combo-box and linked to in the the "tbl_histor y" (basically "tbl_histor y" is my join-table for assets, users, locations, etc...). The combo-box was just simply listing all of the available "status" and I was looking for an elegant means of filtering what was presented and the slight overhead for a 15 record table at first glance seemed a fair trade.

I'll certainly give Rabbit's suggestion a try... seems a shame not to do so! :)

=
Thank you!
I often feel very possessive of the people that work with me in the labs... so I can empathize with your position, I am in the same one at work. :)

Easy enough to misread something. When we write procedures for an analytical method in the lab, the document goes through at least two other people just for that reason :) ! I get really spoiled haveing that proof reading!!!!

Besides, without the body language and voice intonation - the intent of a sentence just becomes that much harder.

**NeoPa** · Apr 2 '12, 11:42 AM

I appreciate your response and I've replied more fully in a PM.

**zmbd** · Apr 2 '12, 06:26 PM

For anyone else following this thread…

“Solution 3” just had some VERY un-predictable results… even with the SQL Rabbit suggested. More than likely something quirky in MS-Access 2010.

And this is what I had done... sofar, appears to work.

“Solution 2 – the normalized approach”

tbl_status :
[Status_ID](pk-autonumber),[Status_Name](txt),[Status_LongName](txt)

tbl_StatusNextA llowed :
[StatusNextAllow ed_ID](pk-autonumber),[Status_ID](fk(tbl_status)-long),[AllowedStatus](long)

(Wherein: tbl_StatusNextA llowed has a record such that for each [Status_ID] in tbl_status, there is a record for each logically allowed next state… {PK1, Broken(3), Repaired(4)} ; {PK2, Broken(3), SentOutForRepai r(5)} ; {PK3, Broken(3), ReturnedFromRep air(6)}… etc.)

tbl_history:
[History_ID](pk-autonumber), [history_datetim e](#date#), [Asset_ID](fk(tbl_asset)-long), [Location_ID](fk(tbl_locatio ns)-long), [Instrument_ID](fk(tbl_instrum ents)-long), [Status_ID](fk(tbl_status)-long), [User_ID](fk(tbl_user)-long), [history_comment](txt).

NOTE: These are only three of the tables in the database… there are several more.

I start out with an unbound form (that way the user doesn’t need to wait for the record set to load - tbl_history is HUGE), using some simple combo-boxes the user can either enter the asset_id or find it using the manufacture, product, serial number to filter things… once that is accomplished, the query is built, the form is bound, and the related records from tbl_history are shown in the detail section of the form as a continuous form .

If the user clicks on the (New Record) button an “events” form opens, then, using VBA, I build the following query:

qry_combobox_fr mhistory_allowe dstatus :
strSQL = _
“SELECT Status_ID, Status_name
FROM tbl_status RIGHT JOIN tbl_StatusNextA llowed ON tbl_status.Stat us_ID = tbl_StatusNextA llowed.AllowedS tatus_ID
WHERE (((tbl_StatusNe xtAllowed.Statu s_ID)=” & lng_laststatus & “));”

lng_laststatus is set to the status recorded in the newest record, based on the date of the event, in the query for the asset_id on tbl_history... This then becomes the record source for the combo-box; thus, effectivly removing illogical choices from the combo-box dropdown.

The combo-box is bound to [Status_ID] value, however, it only displays the [Status_Name] to the user (i.e. if the last event from the tbl_history was 3 (“Broken”) then the user will only see “Repaired, SentOutForRepai r, ReturnedFromRep air” in the dropdown list and if the user selected “SentOutForRepa ir” the combobox.value = 5).

The combobox.value is entered into the [Status_ID] in tbl_history, along with the information from the other controls on the popup form, once the user clicks on the (Save) command button:

IF there is a more elegant way to do this, then please, give me heads-up!!!
X_____X

**Rabbit** · Apr 2 '12, 06:36 PM

What it sounds like is that you've created, in essence, a cascaded combo box. That seems a suitably elegant solution to me. Can you explain why you believe it is inelegant?

**zmbd** · Apr 4 '12, 04:20 AM

@Rabbit:

Whereas the main form has a nice, imho, feel to it with the cascading cboxs, the pop-up for a new record really didn't have the same feel when I was bulding the cbox for the next status.

No better explanation in that it just didn't feel, well, simple.

One of the most valuable lessons I learned from my Chemistry professors, "If you're banging your head against the wall over a problem, more than likely, you've missed something simple." Thus, I'm constantly looking for the simplest solution to any task, not always easy, just... elegant. In this case, elegant happens to be, normalizing the table. :)

**Mihail** · Apr 4 '12, 11:02 AM

@zmbd
Either NeoPa, either Rabbit are a lot more skilled than me in databases concept.
So you should follow their advices about normalization.

Below is a solution for your question but I have serious doubts that is a good approach for your database.

In order to develop the code first I (re)create your table (see attachment: ZBD_Table.jpg )
Then I design this code in a PUBLIC module (In VBA editor create a new module then paste this code in).

Code:

Option Compare Database
Option Explicit

Public Function IsValueInNextAllowedStatus(NextAllowedStatus) As Boolean
    IsValueInNextAllowedStatus = True
    If IsNull(Form_frmTest.txtValueToCheck) Then 'Show all records
Exit Function
    End If
    If IsNull(NextAllowedStatus) Then
        IsValueInNextAllowedStatus = False
Exit Function
    End If
On Error GoTo Ex

Dim ValueToCheck
    ValueToCheck = Form_frmTest.txtValueToCheck
Dim NextStatus
    NextStatus = Split(NextAllowedStatus, ",")
Dim i As Long, j As Long
    For i = 0 To UBound(NextStatus)
        If NextStatus(i) = ValueToCheck Then
Exit Function
        End If
    Next i
    'Not found
    IsValueInNextAllowedStatus = False
Ex:
End Function

The second step is to create the query (the SQL is:)

Code:

SELECT tblTest.ID, tblTest.Status, tblTest.NextAllowedStatus, IsValueInNextAllowedStatus([NextAllowedStatus]) AS IsValueInStatus
FROM tblTest
WHERE (((IsValueInNextAllowedStatus([NextAllowedStatus]))=True));

The last step is to create a form bound to this query.
To this form I have added an unbound text box named txtValueToCheck and a button named cmdRefresh.
The code for this button is:

Code:

Private Sub cmdRefresh_Click()
    Me.Requery
End Sub

That's all.

Attached Files

yet another partial match question...

yet another partial match question...

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment