query question

[arvintheoc]

I have 2 tables, t1 and t2.

t1 has 2 columns, t1col1 and t1col2.

t1col1 is a list of names while t1col2 has numbers. For example:

t1col1,t1col2
john smith,22
warren hastings,33
andrew thomas starr,44
warren scott,10

Note: each entry in t1col1 has any number of words separated by spaces

t2 has only 1 col, t2col1, which is a list single word entries. For example:
t2col1
warren
jason
narita
john

I want to compare t2 with t1: Each time an entry in t2 appears in col1 of
t1, I want it to count, and sum the values in t1 col2.

For example, the result in this case would be:
Result:
t2col1, count, sum_of_t1col2
warren,2,43
jason,0,0
narita,0,0
john,1,22

AS you can see, warren appears twice in t1 and the corresponding col2
entries are added.

How do I do this?

Thanks.

 

[John W. Vinson]

I have 2 tables, t1 and t2.

t1 has 2 columns, t1col1 and t1col2.

t1col1 is a list of names while t1col2 has numbers. For example:

t1col1,t1col2
john smith,22
warren hastings,33
andrew thomas starr,44
warren scott,10

Note: each entry in t1col1 has any number of words separated by spaces

t2 has only 1 col, t2col1, which is a list single word entries. For example:
t2col1
warren
jason
narita
john

I want to compare t2 with t1: Each time an entry in t2 appears in col1 of
t1, I want it to count, and sum the values in t1 col2.

For example, the result in this case would be:
Result:
t2col1, count, sum_of_t1col2
warren,2,43
jason,0,0
narita,0,0
john,1,22

AS you can see, warren appears twice in t1 and the corresponding col2
entries are added.

How do I do this?

A "Non equi join" will do this. It's risky - do you want the record "john, 1,
22" to match up with "Margaret Johnson" or "Daniel John Richards"? Or only
with names that are at the very start of the field (presumably first names)?

Try

SELECT t2col1, Count(*), Sum([t1col2])
FROM t1 INNER JOIN t2
ON t1.t1col1 LIKE "*" & [t2].[t2col1] & "*"

or if you want to match only the start of the string, use

ON t1.t1col1 LIKE [t2].[t2col1] & "*"

 

[arvintheoc]

Thanks John. The partial match of John with Johnson case is OK with me.

However, when I try this, I get an error:

You tried to execute a query that does not include the specified expression
<name> as part of an aggregate function. (Error 3122)


What did I do wrong?
Thanks,

I have 2 tables, t1 and t2.

t1 has 2 columns, t1col1 and t1col2.

t1col1 is a list of names while t1col2 has numbers. For example:

t1col1,t1col2
john smith,22
warren hastings,33
andrew thomas starr,44
warren scott,10

Note: each entry in t1col1 has any number of words separated by spaces

t2 has only 1 col, t2col1, which is a list single word entries. For example:
t2col1
warren
jason
narita
john

I want to compare t2 with t1: Each time an entry in t2 appears in col1 of
t1, I want it to count, and sum the values in t1 col2.

For example, the result in this case would be:
Result:
t2col1, count, sum_of_t1col2
warren,2,43
jason,0,0
narita,0,0
john,1,22

AS you can see, warren appears twice in t1 and the corresponding col2
entries are added.

How do I do this?

A "Non equi join" will do this. It's risky - do you want the record "john, 1,
22" to match up with "Margaret Johnson" or "Daniel John Richards"? Or only
with names that are at the very start of the field (presumably first names)?

Try

SELECT t2col1, Count(*), Sum([t1col2])
FROM t1 INNER JOIN t2
ON t1.t1col1 LIKE "*" & [t2].[t2col1] & "*"

or if you want to match only the start of the string, use

ON t1.t1col1 LIKE [t2].[t2col1] & "*"

 

[John W. Vinson]

Thanks John. The partial match of John with Johnson case is OK with me.

However, when I try this, I get an error:

You tried to execute a query that does not include the specified expression
<name> as part of an aggregate function. (Error 3122)


What did I do wrong?

Just followed my incomplete instructions... sorry!! Should be

SELECT t2col1, Count(*), Sum([t1col2])
FROM t1 INNER JOIN t2
ON t1.t1col1 LIKE "*" & [t2].[t2col1] & "*"
GROUP BY t2col1;

 

[arvintheoc]

John -- excellent - it works. Best,
Arvind

Thanks John. The partial match of John with Johnson case is OK with me.

However, when I try this, I get an error:

You tried to execute a query that does not include the specified expression
<name> as part of an aggregate function. (Error 3122)


What did I do wrong?

Just followed my incomplete instructions... sorry!! Should be

SELECT t2col1, Count(*), Sum([t1col2])
FROM t1 INNER JOIN t2
ON t1.t1col1 LIKE "*" & [t2].[t2col1] & "*"
GROUP BY t2col1;