Page 1 of 3 123 LastLast
Results 1 to 15 of 45
  1. #1
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtering Mechansim (Access 2000)

    Hi Guys,
    I'm wondering if someone can help me on this. I was helped previously on the importation process, thanks kindly to Pat, in which he did a terrific job.
    But something else has sprung up.
    I have an access database that has a text field named "Description"
    I want to do a vb module in access (or anything else) that would act like a filtering mechanism. Ok here is the problem..
    A description field would have something like this

    PIPE, 6", Sch. XS, BE, SEAMLESS, ASME B36.10, Charpy per ASME B31.3, Section 323.3 at -45

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Filtering Mechansim (Access 2000)

    Hi Mark,

    I think you may be able to do what you want with a simple query that lets you find various things based on the first few characters of your record. But rather than creating separate tables, I would simply put a new field in your existing table that indicates what kind of category it is - pipe, consumale, valve, etc. The trick for doing that is to create want is called an update query, and use LIKE "PIPE*" OR LIKE "ELBOW*" OR ... SQL statements in the WHERE clause of the query. I haven't looked at your database so I can't construct the actual SQL statement, but if you use the query designer, I don't think you'll have much trouble. But let me suggest you make a copy of your table (or the whole database) and practice on it so you can make sure you've got it right. Post back if you need further assistance.
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Filtering Mechansim (Access 2000)

    Off hand, I'd say that with the existing data structure, what you want to do is largely impossible. Such is the penalty for a non-nomalized database.

    The Description field should have become a Description table (a child table to your other table), in which there were separate records for each category currently listed in Description.

    To try to get what you want now, you'd pretty much have to create a temp table to do what you should have done in the first place; that is, extract pieces of the Description into separate records.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Mechansim (Access 2000)

    Hi Mark,
    I do understand what you mean. But the issue comes about that these are the result of imported data from excel.
    Also Wendell, you make a good point, I'll try and do the sql statement now.
    Thanks

  5. #5
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Mechansim (Access 2000)

    Brilliant Wendell, i dont know how i even overlooked the notion of queries in the first place. thanks heaps.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Filtering Mechansim (Access 2000)

    Hi Mark
    I didn't get a chance to have a go at this yesterday, kids and "The Matrix Reloaded" and all.

    Obviously Wendell has set you on the straight and narrow, he's good, isn't he !

  7. #7
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Mechansim (Access 2000)

    Hi Pat,
    Oh boy, it seems everyone i know has seen that movie except me. I think I'll wait till the cinema ques have cooled down a bit [img]/forums/images/smilies/smile.gif[/img]
    Wendels suggestion was good, although I wanted to ask you, do you think the solution should be implemented during the importation process?.
    As in, it might faciliate the normalisation process better. Just like the vb module you helped me with, would it be possible to extract the part names
    and place them in a seperate column?. Or is there a way to automate the queries to do that. Thanks once again.
    Just to clarify, my objective is, to do an asp script (which i've been learning) that will calculate each part, the sum of all the weight for all parts, and grouped by
    material grade. eg the sum of all weight for pipes, grouped by material grade.
    I just have to rush to the doctors, will be back soon. thanks Pat.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Filtering Mechansim (Access 2000)

    >>Wendels suggestion was good, although I wanted to ask you, do you think the solution should be implemented during the importation process?. <<
    Yes it certainly can, you should know better, you can do anything in Access (well almost) <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    >>would it be possible to extract the part names and place them in a seperate column?<<
    What are the path names, give an example ot two.

    I am about to rush to the doctor in about 20 minutes myself .

  9. #9
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Mechansim (Access 2000)

    Lol, you can learn alot in a forum. The column i'm refering to is to create one called
    "parts", which would be extracted from "Description"
    PIPE, 2", Sch.XXS, BE, SEAMLESS, ASME B36.10, Charpy per ASME B31.3, Section 323.3 at -45

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Filtering Mechansim (Access 2000)

    What you are going to have to do is to define the list of "parts" and their aliases.
    eg. PIPE, 2"... would result in the word "Pipes" in the column Parts. ELBOW, 2", 90... would result in the word "Pipes" in the column Parts.
    I can change the import procedure to convert a given prefix into a word for the column Parts. As Wendell stated all you need are a series of UPDATE queries to generate the column Parts.

  11. #11
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Mechansim (Access 2000)

    ok, thanks Pat, here it is below,

    Pipes
    ----------
    Pipe
    elbow
    flange
    bend
    Conc. Reducer
    Ecc. Reducer
    TEE
    UNION Connector


    CONSUMABLES
    ----------
    Gaskets

    INSTRUMENTS
    -----------
    (none)

    VALVES
    -----------
    Instrument Valves (not to be confused with the instrument part above)


    Incidently, there are different parts for valves, but I didnt include them above because the word valve is actually mentioned in the description for each of them, eg
    Relief valve, valve ball etc etc.

    Am I going about this the right way?.

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Filtering Mechansim (Access 2000)

    Yes, you are, but you say that the letters can appear anywhere in the description. eg. valve.
    From what you have supplied there will be NO Instruments, only Pipes, Consumables and Valves.

    Is this correct?

  13. #13
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Mechansim (Access 2000)

    Yep, thats correct. I say this because the word instrument appears in every field of the excel/description field. So i guess its made easier in that sense.
    But there still has to be four categories, would it be wise to put an extra one for unsuccessful ones?. The latter could be due to a spelling mistake, which i fear might happen.

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Filtering Mechansim (Access 2000)

    What do the following come under?
    HUB
    INSTRUMENT
    SPECTACLE BLIND
    STUD BOLT
    WELDOFLANGE
    WELDOLET

  15. #15
    2 Star Lounger
    Join Date
    May 2003
    Location
    Perth, Western Australia, Australia
    Posts
    150
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Mechansim (Access 2000)

    oh, they all come under PIPES.
    All instruments would come under instruments.
    But if there is an instrument valve, then that would come under VALVES.

Page 1 of 3 123 LastLast

Posting Permissions

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