By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 2,256 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

yet another partial match question...

zmbd
Expert Mod 5K+
P: 5,285
Table:
[ID] ; [str_status] ; [str_nextallowedstatus]
1 ; Received ; 3,4,11
2 ; Returned ; 3,4,5,21
3 ; Storage ; 4,5,11,23
4 ; Shipped ; 2
5 ; OutOfService ; 12,13,21
...
10 ; moredatat ; 3,5,6
11 ; boohoo ; 33,93,53
... etc...

Ok, here's the question:
I need to match exactly the partial string = "3" in the [str_nextallowedsatus] field; thus, returning only the records with "3"
i.e: [ID]=1, [ID]=2 and [ID]=10 but NOT [ID]=3, [ID]=5, nor [ID]=11 etc...
from what I've found so far with google and the search are criteria using LIKE and "*" which endup returning all the records with a "3" in them no matter if it is "3", "33", or "13" ; HOWEVER, I may need to search for a value like "13" or "33" at some point.

I am using this to filter a combo-box and really don't want to build yet another table where the allowed next status is setup.... one record for each allowed sub-string... imagine, for [ID]=1, there would be three records, [ID]=2 would have four records, etc...

I am using VBA to set the record source for the combo-box based upon the currently selected record.

Mar 31 '12 #1

✓ answered 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 characterization 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.

Share this Question
Share on Google+
15 Replies


Rabbit
Expert Mod 10K+
P: 12,315
You should normalize you data instead of storing it this way. It will make querying much easier.
Mar 31 '12 #2

zmbd
Expert Mod 5K+
P: 5,285
@Rabbit
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 "OutOfService" 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 "OutOfService." 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_nextallowedstatus] 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
Mar 31 '12 #3

Rabbit
Expert Mod 10K+
P: 12,315
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 characterization 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.
Apr 1 '12 #4

zmbd
Expert Mod 5K+
P: 5,285
@Rabbit

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_nextallowedstatus" 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_nextallowedstatus] 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.
Apr 1 '12 #5

Rabbit
Expert Mod 10K+
P: 12,315
Well, if you want to keep it the way it is, you just need to jury rig the SQL a bit.
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM sometable
  3. WHERE ',' + nextallowed + ',' LIKE '%,3,%'
Apr 1 '12 #6

NeoPa
Expert Mod 15k+
P: 31,186
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.
Apr 1 '12 #7

zmbd
Expert Mod 5K+
P: 5,285
@ 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 "unintelligent" by someone that can't even take the time to read the entire thread.

How Rude!
Apr 2 '12 #8

NeoPa
Expert Mod 15k+
P: 31,186
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.
Apr 2 '12 #9

zmbd
Expert Mod 5K+
P: 5,285
@ 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_history" (basically "tbl_history" 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.
Apr 2 '12 #10

NeoPa
Expert Mod 15k+
P: 31,186
I appreciate your response and I've replied more fully in a PM.
Apr 2 '12 #11

zmbd
Expert Mod 5K+
P: 5,285
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_StatusNextAllowed :
[StatusNextAllowed_ID](pk-autonumber),[Status_ID](fk(tbl_status)-long),[AllowedStatus](long)

(Wherein: tbl_StatusNextAllowed 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), SentOutForRepair(5)} ; {PK3, Broken(3), ReturnedFromRepair(6)}… etc.)

tbl_history:
[History_ID](pk-autonumber), [history_datetime](#date#), [Asset_ID](fk(tbl_asset)-long), [Location_ID](fk(tbl_locations)-long), [Instrument_ID](fk(tbl_instruments)-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_frmhistory_allowedstatus :
strSQL = _
“SELECT Status_ID, Status_name
FROM tbl_status RIGHT JOIN tbl_StatusNextAllowed ON tbl_status.Status_ID = tbl_StatusNextAllowed.AllowedStatus_ID
WHERE (((tbl_StatusNextAllowed.Status_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, SentOutForRepair, ReturnedFromRepair” in the dropdown list and if the user selected “SentOutForRepair” 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
Apr 2 '12 #12

Rabbit
Expert Mod 10K+
P: 12,315
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?
Apr 2 '12 #13

zmbd
Expert Mod 5K+
P: 5,285
@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. :)
Apr 4 '12 #14

100+
P: 759
@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).

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function IsValueInNextAllowedStatus(NextAllowedStatus) As Boolean
  5.     IsValueInNextAllowedStatus = True
  6.     If IsNull(Form_frmTest.txtValueToCheck) Then 'Show all records
  7. Exit Function
  8.     End If
  9.     If IsNull(NextAllowedStatus) Then
  10.         IsValueInNextAllowedStatus = False
  11. Exit Function
  12.     End If
  13. On Error GoTo Ex
  14.  
  15. Dim ValueToCheck
  16.     ValueToCheck = Form_frmTest.txtValueToCheck
  17. Dim NextStatus
  18.     NextStatus = Split(NextAllowedStatus, ",")
  19. Dim i As Long, j As Long
  20.     For i = 0 To UBound(NextStatus)
  21.         If NextStatus(i) = ValueToCheck Then
  22. Exit Function
  23.         End If
  24.     Next i
  25.     'Not found
  26.     IsValueInNextAllowedStatus = False
  27. Ex:
  28. End Function

The second step is to create the query (the SQL is:)
Expand|Select|Wrap|Line Numbers
  1. SELECT tblTest.ID, tblTest.Status, tblTest.NextAllowedStatus, IsValueInNextAllowedStatus([NextAllowedStatus]) AS IsValueInStatus
  2. FROM tblTest
  3. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdRefresh_Click()
  2.     Me.Requery
  3. End Sub
That's all.
Attached Images
File Type: jpg ZBD_Table.jpg (15.9 KB, 100 views)
Attached Files
File Type: zip ZMBD_database.zip (156.5 KB, 39 views)
Apr 4 '12 #15

zmbd
Expert Mod 5K+
P: 5,285
As I've said in another post, "...Good Chemistry is Lazy Chemistry: If you're beating your head against the wall over a problem, you've missed something easy - step away from the problem..." - The Late Dr. Palma (Chemisty Prof)

The elegant solution:
When the user clicks on the command button to launch the new history event form for the asset.
-The vba code looks at the newest record by date in the
recod set to fetch the value of the last status.
Expand|Select|Wrap|Line Numbers
  1. zstrcurrentstat = newestrecord![status_id]

- now a simple lookup on the status table:
Expand|Select|Wrap|Line Numbers
  1. zstrnextallowed = DLOOKUP("NextAllowedStatus","tbl_status","[status_id]=" & zstrcurrentstat)
-now we have the list for the next allowed states so we can build the query:

Expand|Select|Wrap|Line Numbers
  1. zSQL =
  2. "SELECT tbl_status.status_ID, tbl_status.status_name " & _
  3. "FROM tbl_status " & _
  4. "WHERE tbl_status.status_id IN (" & zstrnextallowed & ");"
This is then set as the record source for the combo-box

Amazing what getting away from the issue will do for the mind.

That wonderfull "IN" of the "WHERE" clause.

Happy Easter

-z
Attached Images
File Type: jpg postreplybutton.JPG (5.6 KB, 111 views)
Attached Files
File Type: zip poc.zip (150.4 KB, 20 views)
Apr 9 '12 #16

Post your reply

Sign in to post your reply or Sign up for a free account.