Query Creation Help

[yamanory]

Hi,

I'm trying to create a query in MS Access/SQL and can't seem to figure out how to do it exactly.

Just say I have 3 columns:
Name - City - Data

I have a list of cities, and a list of data 'keywords' (ie. a word that is found in the data).

I want a name for each unique keyword in each city, so for example, the output I want is:

namea-city1-keyword1
nameb-city1-keyword2
namec-city1-keyword6
named-city2-keyword1
namee-city3-keyword1
namef-city3-keyword2
nameg-city3-keyword5

Try to wrap your head around this one! I can write it in code, but have fairly basic SQL skills.

p.s. all I really care to extract is the list of names, but I don't want to extract more data than I need

 

[yamanory]

The general pseudocode I would use to do this would be something like:


cities=[array of cities]
keywords[array of keywords]
for each city in cities:
for each keyword in keywords:
sql_query=("SELECT TOP 1 Table.Name, Table.City, Table.Data FROM Table WHERE (Table.Data Like (keyword)) AND (Table.City) Like (city))

 

[dhookom]

It would help if we had some context for this question. Do you want the name to persist in the table? If all you want is a unique name, I would add an autonumber field.


BTW: can we assume you didn't actually name your name field name?

Duane
Hook'D on Access
MS Access MVP