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

Topic

New Reply

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!!

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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…

Reply | Quote

I must not be getting this.
When you say
<>
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

Reply | Quote

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!

Reply | Quote

You wrote:
<>

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

Reply | Quote

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.

SELECT tblContacts.ID, tblContacts.Name, tblContacts.Category, tblCounties.County
FROM tblContacts, tblCounties
WHERE tblContacts.Counties Like "*" & [County] & "*"

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.

SELECT tblContacts.ID As ContactID, tblCounties.ID AS CountyID
FROM tblContacts, tblCounties
WHERE tblContacts.Counties Like "*" & [County] & "*"

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.

Reply | Quote

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!

Reply | Quote