collation conflicts and agrogate functions

[ghell]

im trying to select 2 things in my database, in 2 tables

table1 contains memb_id (a string) and rcreds (an int)

table2 contains custom (a string) and item_name (a string)

item name is such that you can cut off the " Credits" at the end of "200000 Credits" for example, and get the 200000 withCAST(SubString(table2.item_name, 1, 6) AS Int)where all the numbers there are 5 or 6 characters long but "40000 " is still casted to 40000 so that space doesnt matter

im trying to get table1.rcreds where table1.memb_id = 'ghell'
and SUM(CAST(SubString(table2.item_name, 1, 6) AS Int)) As intTotalCreds where table2.custom = 'ghell'

memb_id and custom are of the same datatype (enum 202) and length (10)

if i try and select both (table1.rcreds and table2.inttotalcreds) i get this error:Column 'table1.rcreds' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.if i try and select 1 of these on its own i get this error:Cannot resolve collation conflict for equal to operation.

my full sql string is"SELECT table1.rcreds, SUM( CAST(SubString(table2.item_name, 1, 6) FROM table1 INNER JOIN table2 ON table1.memb_id = table2.custom WHERE table2.custom = '" & strAccountID & "'"what sql string can i use to get what i want?

[Bullschmidt]

Seems like subquerying would be needed somehow and here is a little memo to myself which I wrote about that.

Example of one query (QueryB) based on the results of another query (QueryA):

QueryA = "SELECT CustID FROM tblCUSTOMERS WHERE CustName = 'A%'"

QueryB = "SELECT CustID, CustName FROM tblCUSTOMERS WHERE CustID IN (" & QueryA & ")"

But the following is even faster and allows for more than one field to be returned in QueryA:

QueryB = "SELECT tblCUSTOMERS.CustID, CustName FROM (" & strSQLA & ") AS tblSQLA INNER JOIN tblCUSTOMERS ON tblSQLA.CustID = tblCUSTOMERS.CustID"

So QueryA would include all the CustID's for customers starting with A.

And QueryB would include more fields in the customers table (i.e. not just the CustID field) for the records returned in QueryA (which was the customers starting with A).

I suppose it wouldn't hurt to always use LEFT JOIN's in QueryB and build from the tblSQLA on the left to other tables that have fields you want to return.