how can I build 2 queries in 1 query(VB SQL query)
SELECT query1.name, table2.add1, table2.add2
FROM table2
INNER JOIN (select table1.name, table1.id1*100 as id100 from table1) as
query1 ON table2.id2 = query1.id100;
Or you can just use a WHERE condition, not a join:
SELECT table1.name, table2.add1, table2.add2
FROM table1, table2
WHERE (table2.id2)=[table1].[id1]*100"
I can't think of a good reason to have the id in table2 100 times a counter
value in the first place, though....
HTH,
Max
Quote:
> I am thinking to build a query in Ms VB to sort the database
> I have 2 tables in Ms access database,
> table1
> id1 name
> 1 abcd
> 2 defg
> 3 ghij
> 4 xhxk
> 5 fgdj
> table2
> id2 add1 add2
> 100 fsjs jfkh
> 200 dfgg ghfg
> 300 dfgh jyuj
> 400 yuuy jghj
> 500 jlas adad
> can I use VB SQL query to link this 2 table, set table1.id1*100=table2.id2
> I tried below query, but doesn't work.
> query1= select table1.id1,table1.name, [id1]*100 as id100 from table1
> Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=test.mdb;Persist Security Info=False"
Quote:
> Adodc1.RecordSource = "select (query1).name, table2.add1, table2.add2
> from (query1) inner join table2 on (query1).idd100=table2.id2"
> Set obj1 = Adodc1.Recordset
> anybody can help to build this query in VB. please also forward a message
