Can I create a query (or any nifty easy way) to do this?

[jj]

I have three tables: Furniture, Titles, Descriptions. There's no
relationships (joins, etc)
between any of them.

Here's the design & some sample data:

Table: Furniture
--------------------------------------------------------------------------
Field: Title Field: Description
---------------------- -------------------------------------------
-
Black Satin Sheet Bought at JC Penney's for $20
Old English Brass Lamp Lightbulb broken, needs replacing
Edwardian Chair White with one broken leg
Georgian Dresser Needs polishing with $20 polish from
Safeway



Table: Titles
----------------------
Field: Titles
----------------------
Lamp
Satin Sheet
Edwardian Chair




Table: Descriptions
----------------------
Field: Descriptions
----------------------
$20
broken


Basically the Furniture Table is a list of all possible Titles and
Descriptions, and the other two tables include stuff that I want to delete
from all entries in the Furniture Table. I've written a procedure that loops
through all of the records in the Titles Table looking for entries, using
LIKE in a SELECT statement, and then goes & does the same for the
Descriptions table -- which works, but it's very slow.

I can't seem to figure out how to make a query, because the records in both
the Titles and the Descriptions tables may or may not describe the entire
record in the Furniture table, and if it's not the whole record, it doesn't
even start in the same place. So, I need something that's dynamic for each
record.

Does anyone else have any bright ideas?

Thanks in advance,

JJ

 

[K Dales]

If I understand what you are trying to do:

Create an update query containing the Furniture Table and
the Titles table. DON'T join them the way you normally
would in Design view; instead, either use your LIKE
expression as the criteria for the Title field or make a
calculated column:
FoundTitle:InStr([Furniture].[Title],[Titles].[Title])
And set the criteria to be >0

This should take care of finding the records you want to
correct. Do another similar query for your Descriptions.

 

[jj]

Hi

Thanks for that - except I must have misunderstood, cuz all
I get is a syntax error for your example.

I did manage to change [Furniture].[Title] to
[Furniture]![Title] (think that's an Access 2000 thing - probably
should have said which version - sorry) and can therefore get
a select query to work... it gives a list of numbers. That's a bit
weird too, cuz if I tell it to show Field: Title, Table: Titles, I notice
that the numbers refer to several iterations of the same Title (as in
my example below - there are several listings - with different numbers
for the word Lamp) - and that's just weird.

The update query though still doesn't work at all.

I get an error message which says

Syntax error in query expression
'(((Furniture.[InStr([Furniture]![Title],[Titles]![[Title])])>0))'

Tried changing the Table: entry to Titles - that gives

Syntax error in query expression
'(((Titles.[InStr([Furniture]![Title],[Titles]![[Title])])>0))'

and nothing in the Table: box gives

Query must have at least one destination field.

Any suggestions K?

-JJ

If I understand what you are trying to do:

Create an update query containing the Furniture Table and
the Titles table. DON'T join them the way you normally
would in Design view; instead, either use your LIKE
expression as the criteria for the Title field or make a
calculated column:
FoundTitle:InStr([Furniture].[Title],[Titles].[Title])
And set the criteria to be >0

This should take care of finding the records you want to
correct. Do another similar query for your Descriptions.

-----Original Message-----
I have three tables: Furniture, Titles, Descriptions. There's no
relationships (joins, etc)
between any of them.

Here's the design & some sample data:

Table: Furniture
---------------------------------------------------------- ----------------
Field: Title Field: Description
---------------------- ------------------------- ------------------
-
Black Satin Sheet Bought at JC Penney's for $20
Old English Brass Lamp Lightbulb broken, needs replacing
Edwardian Chair White with one broken leg
Georgian Dresser Needs polishing with $20 polish from
Safeway



Table: Titles
----------------------
Field: Titles
----------------------
Lamp
Satin Sheet
Edwardian Chair




Table: Descriptions
----------------------
Field: Descriptions
----------------------
$20
broken


Basically the Furniture Table is a list of all possible Titles and
Descriptions, and the other two tables include stuff that I want to delete
from all entries in the Furniture Table. I've written a procedure that loops
through all of the records in the Titles Table looking for entries, using
LIKE in a SELECT statement, and then goes & does the same for the
Descriptions table -- which works, but it's very slow.

I can't seem to figure out how to make a query, because the records in both
the Titles and the Descriptions tables may or may not describe the entire
record in the Furniture table, and if it's not the whole record, it doesn't
even start in the same place. So, I need something that's dynamic for each
record.

Does anyone else have any bright ideas?

Thanks in advance,

JJ


.