Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2014
    Posts
    21

    Unanswered: DLookUp with a muti value field

    Hi there
    I have a multi value field into which I have placed several different values. I wish to compare a value that is entered by the user with the multi value field, to find if the value entered is there. I am assuming ( and also hoping) that I can use DLookUp to do this. At the moment I get all of the multi values returned by DlookUp and they are separated by commas and the value that I want to find is in there. However this is giving me an error because of the separating commas. How can I isolate/extract the value that I want so that I can make a comparison ? Is it something like MyFieldName.Value? Any and all help desperately and urgently appreciated.

    Piscator

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    From a relational database perspective, the correct way to handle this problem is to store the DLookUp data as one row per item. This keeps the data in at least 1NF (First Normal Form), and more importantly it allows the use of indexes which will drastically improve performance as the list grows.

    To do this, you need to split the DLookUp data into its component pieces (at the commas), and store each piece in its own row. Build an index on the DLookUp values, and probably also on whatever FK (Foreign Key) is appropriate.

    There are ways to do this using creative coding, but all of those break 1NF and require full table scans, so I avoid them like the plague.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2014
    Posts
    21

    Red face Dlookup

    Hi Pat
    Thanks for your reply. I apologise, but I didn't make myself entirely clear. I have already set up 30 records of identical type but with unique primary keys. These records form the details of separate items on a ticket. However, most of these records are mutually exclusive to specific others on the ticket. One of the fields in record is of the same type as the primary key, and it is in this field that I have designated as multi value, so that I can enter the values of the primary key of the other records that are mutually exclusive. It is, in effect, a true lookup table - nothing else. I can't imagine that MS would provide the ability to store multiple values without the means to check for specific values! when an item has been created on the ticket any further items that are mutually excusive are not allowed. So basically, my table is in 1st normalised form. Just need to know how to check for specific MV items. Piscator:

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In that case I can't help you. Good luck, and I'd love to hear how you solve this!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    I have already set up 30 records of identical type but with unique primary keys.
    Rows are not anything like records. The term field is one of the parts of a temporal data type {YEAR, MONTH, DAY, HOUR, MINUTE, SECOND}.

    These records [sic] form the details of separate items on a ticket. However, most of these records [sic] are mutually exclusive to specific others on the ticket. One of the fields [sic] in record [sic] is of the same type as the primary key, and it is in this field [sic] that I have designated as multi value, so that I can enter the values of the primary key of the other records [sic] that are mutually exclusive.
    The relational model uses only scalar values drawn from known domains. This makes no sense at all.

    when an item has been created on the ticket any further items that are mutually exclusive are not allowed.
    Such details are called a weak entity; to exist, they need a strong entity (i.e the ticket) to reference. There are tricks for allowing only certain combinations.

    So basically, my table is in 1st normalized form. Just need to know how to check for specific MV items.
    Read that slowly. The definition of 1NF requires scalar values that cannot be decomposed without loss of information. Dr. Codd's 12 rules, etc.

  6. #6
    Join Date
    Mar 2014
    Posts
    21
    I have taken on board all that has been said, and have now abandoned the idea of MVI's. Many thanks for all your comments. Instead, I have set up a many to many relationship based on my item table, with foreign keys of the junction table containing the mutually exclusive items respectively. Apologies for terminology, I was weaned in the late 60,s and early 70's on Univac 1100, and 1050 systems and IBM 360 mainframes using machine code and assembler language. In those days we had files which had records which had fields. I am familiar with relational database structure, and with Codd and also Date, but have obviously made the mistake of equating records to rows!

  7. #7
    Join Date
    Mar 2014
    Posts
    21
    In the context of a relational database, a row—also called a record —represents a single, implicitly structured data item in a table. In simple terms, a database table can be thought of as consisting of rows and columns or fields. Each row in a table represents a set of related data, and every row in the table has the same structure.

    For example, in a table that represents companies, each row would represent a single company. Columns might represent things like company name, company street address, whether the company is publicly held, its VAT number, etc.. In a table that represents the association of employees with departments, each row would associate one employee with one department.

    In a less formal usage, e.g. for a database which is not formally relational, a record is equivalent to a row as described above, but is not usually referred to as a row.

    The implicit structure of a row, and the meaning of the data values in a row, requires that the row be understood as providing a succession of data values, one in each column of the table. The row is then interpreted as a relvar composed of a set of tuples, with each tuple consisting of the two items: the name of the relevant column and the value this row provides for that column.

    Each column expects a data value of a particular type. For example, one column might require a unique identifier, another might require text representing a person's name, another might require an integer representing hourly pay in cents.


    -

    Column 1

    Column 2

    Row (Record) 1 Row 1, Column (Field)1 Row 1, Column 2
    Row 2 Row 2, Column 1 Row 2, Column 2
    Row 3 Row 3, Column 1 Row 3, Column 2

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I started with a DEC PDP-8, graduated up to an HP-3000, then stumbled into a time warp and spent years on the largest IBM 360 that I've ever heard of (and have some fantastic stories from those days).

    I'm sure that I'm missing something, but I can't find a question in your last two posts. I also don't see a logical connection between your last post and your first post. I'm probably just being "thick as a brick", so please coach me a bit.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Mar 2014
    Posts
    21

    Multi value fields

    Hi Pat P
    My last post was in reply to a post by CELKO. This to me seemed more about letting me know how brilliant he seems to think he is rather that affording any kind of assistance. I have taken on board what you have said in your earlier post, and set up a many to many relationship on my Item description table which has a primary key for the Item_code field ( or as CELKO would say a "column"), and a Item_ description column. The junction table, now, obviously, has two columns each containing the Item Code. Each of these columns uses a Lookup table (in table design) that selects the Item_ description. The left hand is then populated with the many items of the same code and the right hand column is populated with all the different codes that I wish to select. An example of why this would be needed is as follows:
    Suppose a doctor prescribes two different medicines for the same patient. It may be that the combination of the two drugs, taken at the same time, could have serious consequences for the patient. Each drug has a unique code and description. Having already prescribed the first drug, a message should be flagged up when the second drug is prescribed by the doctor. In effect, the two drugs should be mutually exclusive to each other. I was initially using the MV items, not as keys in any column, but purely as a lookup to set up code values that matched the item codes that were mutually exclusive. So for example drug code 1 could be exclusive to drug codes 7, 8, 23, 102. Now, as you can see what I have is a lookup table with multiple values. Originally, I set these up successfully, but was unable to interrogate the multi values on the lookup table to see if the second drug selected was amongst them. Hope this makes sense. IF there were a facility supplied my MS to interrogate MV's it would still serve the purpose that I require. Many thanks for your interest and assistance. Piscator

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    CELKO is an old friend of mine, and from a forum perspective he's really a mixed blessing. He's really a good-hearted soul, but he's got the soul of a college professor in an advanced topic, which is compounded by his very thick skin. I think that CELKO is brilliant but sometimes you need to be able to disregard the abrasive way that he presents himself and look at the value that he presents instead of the way that he presents it. As many of the DBForums moderators have (repeatedly) pointed out I tolerate a lot more from CELKO than I do from anyone else, but I think that he's earned that tolerance.

    Based on your description of drug interactions, I think that you're concerned with the tip of the iceberg. You are looking at simple binary interactions, where one drug interacts with another. I think that you need to look at this from a larger scope, looking at all of the drugs (including the proposed addition) and looking for all of the interactions. In many cases, there can be more than one stage in the conflict.

    As an example, you can combine Vicodin with either Fenofibrate or with Amoxicillin without serious side effects. For patients with compromised vascular response, the combination can have serious complications. If you look at any two of the criteria, there's no problem. If you look at any three criteria, you might or might not find a problem depending on what you check and how you check it. If you check all four (a condition and three drugs) at once, the problem pops out.

    This might be overkill for your needs, especially if you only used the drug interactions as an analogy. Coach me a bit, and we can find a clean and useful idea!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Mar 2014
    Posts
    21
    Hi Pat
    Firstly, apologies to Celko. The essence of my problem is that, having selected an item (or items), to mutually exclude one or more following items of further selections that could/will cause problems with those already selected. Further items are allowed, as long as there is no conflict with existing items. In effect, it is to stop users entering erroneous data. Those further selections will have no direct relationship with each other in this context. I used the drug selections as an example. I wish that I could tell you precisely what I am doing, but at the moment there are no other products on the market for the database that I am developing, and wish to market. I am currently involved with an "Angel" investor with the purpose of marketing the product. I have effectively finished the database, apart from a few input data validation macros.

    Regards Piscator

  12. #12
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    No, rows are not records

    In the context of a relational database, a row—also called a record —represents a single, implicitly structured data item in a table.
    No, a record is a physical unit of storage, made up of physically contiguous fields.

    A row is a logical unit of storage and it might be virtual, as in a VIEW, so it has no records on disk. Its records might be repeated in several physical locations in a columnar database or one based on a RAID model.

    Furthermore, RDBMS has only tables; there is no structured data types.

  13. #13
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    This sounds like a relational division or equivalence class problem. The divisor table would be a class name column, and class member column. The trick might be to have a higher level than just simple members. That is, you would have acids = {...}, bases = {..} with particular members are allowed in the same class (baking soda +lime juice) or a general rule that the more general classes do not mix.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •