Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL LIKE operation and MakeTable (2000 SR-1, Win 2000 Prof)

    I have some experience working with recordests via an ADOB connection in VBScript to pull data from an access table based on a criteria, but i haven't worked with doing this internally on an Access db and, unfortunately, I need to use the LIKE operator to pull the necessary records from one table and, perhaps, make a new table based on the query. The other issue is I need to use another table as a look-up for the values being matched.

    To be specific: I have a table of contact info with one column that lists a series of counties (one or more) comma-deliminated. I have another table with each county listed. Here is a working SQL string that can pull a given record from the contacts table based on a *SPECIFIC* value (passed from a drop-down box):

    SQLstring = "SELECT * FROM LocalCRCGContacts where Categories LIKE '%" & County & "%' ;"

    My problem, and it is certainly very basic (but not so simple for me at the moment!) is to apply this code in Access to page thru a look-up table of County names and copy out every record in the contacts table where the LIKE condition finds a match. This would generate a recordset and that recordset could then be written to a table (one I'd probably create previously and refer in code). I'll have several sub-queries eventually once I get the master code to function but I am not clear on how one does the following:

    1) Open existing tables -- this is what I have:

    DoCmd.SelectObject acTable, "CNTYCODE"
    DoCmd.SelectObject acTable, "LocalCRCGContacts"

    2) Set up a loop tied to a recordset (or even more directly, open a third table and write matches from the LocalCRCGContacts based on the value of CNTYCODE). The problem here is, instead of passing one value, one pages thru the lookup table for every record in the contacts table and copy out a range of data.

    3) Determine the fields written to the results table with the matched county written to the same record row as the matched record so that a multi-county record will be tagged to one county at a time.

    This should all be fairly easy but i have gone thru a lot of the previous posts and haven't found a clear enough example.

    Thanks in advance for your help!!

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

    Re: SQL LIKE operation and MakeTable (2000 SR-1, Win 2000 Prof)

    What is it exactly that you are trying to achieve?
    You say you want to copy a whole lot of records to a table based upon some like condition.
    Why do you want to do this?
    Is it ultimately for a report or something?
    Knowing what you want to achieve in broad terms can help make finding the solution a lot easier.
    Pat <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL LIKE operation and MakeTable (2000 SR-1, Win 2000 Prof)

    sorry i thought i was being specific enough not to bore with too many details. but, this is what I am working on:

    1) contact records that have a particular field, 'Categories', with a comma-deliminated list of counties (one or more) -- this is from Outlook, so it's the Categories information.
    2) a report based on a per-county list to show who covers a particular county (I can easily do a report that shows contacts with all the counties listed but i need to do a report that breaks out each contact and lists them for *each* county in the look-up table, not just in the batch as Categories has them).

    Simple enough -- I need to use the LIKE operator to match a county in a string of counties and page thru the contacts table and write the matching records to a new table (or an existing empty one) whenever there is a match. Now, in the case of a contact that has Bastrop, Fayette, Lee in the Categories field, the result in the target table should be

    Contact, Bastrop
    Contact, Fayette
    Contact, Lee

    where the Contact information is the same, just the county differs.

    Later on, once that gets done, I'll need to run reports detailing on a by-county basis contacts who are Chairs, Coordinators or CRCG, CRCGA or CRCGF types. That should be easy to do; it's just finding and listing out on a per-county basis these records that is giving me problems.

    once again, apologies for not showing the whole enchilada.

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

    Re: SQL LIKE operation and MakeTable (2000 SR-1, Win 2000 Prof)

    The like expression uses *'s and not %'s in Access.

    If you want to create a table based upon the like the you will need either an APPEND query or a MAKE table query.

    A MAKE table query based upon what you have would be:
    SELECT * FROM LocalCRCGContacts INTO NewTablename where Categories LIKE *" & County & "*" .
    You would need to run this from a DoCmd.RunSql command.

    For an APPEND query it is:
    INSERT INTO TargetTablename (field names separated by commas)
    SELECT * FROM LocalCRCGContacts where Categories LIKE *" & County & "*" .
    Again you would need to run this from a DoCmd.RunSql command.

    HTH
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL LIKE operation and MakeTable (2000 SR-1, Win 2000 Prof)

    DoCmd.RunSQL is something that can be run from a macro. interesting.

    There's a problem with the recommended SQL strings -- they are not looking up anything from the County name look-up table but seem to be expecting an argument. I need to, basically, run the SQL for every table in the Contact table based on an iteration thru the County table. I don't care if it's an Append or a Make Table at this moment; really need to figure out the programming/logic on reference a look-up table for the LIKE criteria. If I could do all this in a Macro that might be a bit easier to fool with...

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

    Re: SQL LIKE operation and MakeTable (2000 SR-1, Win 2000 Prof)

    I must not be getting this. <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    When you say
    <<run the SQL for every table in the Contact table based on an iteration thru the County table>>
    do you want all the records from the Contacts table for all the records in the County table ?
    Would you give me an example, showing what the tables are and what's in them (field wise). Also give an example of the data in these tables and what you want the SQL to select.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL LIKE operation and MakeTable (2000 SR-1, Win 2000 Prof)

    Ok here goes: I have a table like so: (these are very abbreviated examples to show the crux of the problem)

    ID --> 1
    Name --> Steve Skelton
    Category --> CRCG
    Counties --> Bastrop, Fayette, Lee

    I want to produce another table with 3 rows instead of one:

    ID --> 1
    Name --> Steve Skelton
    Category --> CRCG
    Counties --> Bastrop

    ID --> 2
    Name --> Steve Skelton
    Category --> CRCG
    Counties --> Fayette

    ID --> 3
    Name --> Steve Skelton
    Category --> CRCG
    Counties --> Lee

    I have a lookup table with each county:
    ID --> 1
    County --> Bastrop

    ID --> 2
    County --> Fayette

    ID --> 3
    County --> Lee

    The SQL statement needs to do a Select [FieldNames] from Table1 where Category LIKE '"%Table2.County%'" (I'm using the % character as a wildcard as the ADO-style SQL string uses that to pull the data from Access; also it's easier to pick out)

    Apparently I need to generate a recordset, iterate through the records and test *each* row in Table1 against all the County fields in Table2 and write out the results in a new table (from a recordset?) combining the basic data needed from Table1 with the matching County from Table2 -- also, include the county list for purposes of testing for "bad" matches (for example, Harrison county will get picked in a LIKE %HARRIS% query - there's a few that do this and I need to make exceptions for those).

    when i started working on this, naturally i went to the data grid; that doesn't allow the LIKE operation in linking fields, alas. this makes me think i'll have to code a module, which is no big deal; i just don't have a handle on the syntax for the operation and the Online help hasn't been. so, to the lounge...

    Hope this makes sense. I'm pretty sure I'm not trying to do something completely strange, just never done it before.

    Thanks!

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

    Re: SQL LIKE operation and MakeTable (2000 SR-1, Win 2000 Prof)

    You wrote:
    <<
    Apparently I need to generate a recordset, iterate through the records and test *each* row in Table1 against all the County fields in Table2 and write out the results in a new table (from a recordset?) combining the basic data needed from Table1 with the matching County from Table2 -- also, include the county list for purposes of testing for "bad" matches (for example, Harrison county will get picked in a LIKE %HARRIS% query - there's a few that do this and I need to make exceptions for those).
    >>

    This has effectively answered your question. If you need some code example please post and tell me.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL LIKE operation and MakeTable (2000 SR-1, Win 2000 Prof)

    It is possible to use queries to do this, code is not necessary. You can specify the join in the criteria.

    Here is the SQL for a select query that does what you asked; I have named the tables tblContacts and tblCounties.<pre>SELECT tblContacts.ID, tblContacts.Name, tblContacts.Category, tblCounties.County
    FROM tblContacts, tblCounties
    WHERE tblContacts.Counties Like "*" & [County] & "*"</pre>

    If you like, you can make this into a make table query.

    In fact, you have a many-to-many relationship between contacts and counties: one contact can belong to several counties, one county can have several contacts. Therefore, I think you should have 3 tables: a contacts table (without county information; just remove the Counties field from tblContacts after the conversion is finished), the existing counties table and a link table linking these two. This link table should just contain pairs of contact ID and county ID.

    Here is the SQL for a select query that returns all (contact ID, county ID) pairs; it can be made into a make table query to generate the link table.<pre>SELECT tblContacts.ID As ContactID, tblCounties.ID AS CountyID
    FROM tblContacts, tblCounties
    WHERE tblContacts.Counties Like "*" & [County] & "*"</pre>

    I have attached a zipped Access 97 database that contains sample tables and queries. You'll have to unzup and convert it. Look at the queries in design view and datasheet view.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL LIKE operation and MakeTable (2000 SR-1, Win 2000 Prof)

    that query worked perfectly. I had forgotten that you can specify two tables in the SELECT FROM section of the SQL. I KNEW there was an easier way to do this but was put off by the apparent obscurity of the LIKE operator in normal query grid operations. now i can go on... thanks for the quick and precise help, Hans, and for everyone else who was so responsive to my problem! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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