Query?

[JSB]

Hi,
I am working on a database with 2 tables.

First table has 3 fields: BACODE, CODE1 and CODE2.
Second table has 3 fields: DESCRIPTION, CODE and PRICE.
Now I need to do a query where if I give the 'BACode' it
should add 'code1 + code2' and give the description and
the total 'price' for that code.

Example:

Table1:
BACode Code1 Code2
BA01 0852 0882
BA02 0852 0900
BA03 0852

Table2:
Desc Code Price
Local 0852 $0.12
LocalExt 0882 $0.23

If I give 'BA01'in the query,
I should get
Description :'Local+LocalExt'
and PRICE :'$0.35'.

Thanks.

 

[Guest]

After playing around for a while, finally got it to work. Using the table and
field names in your example, I made created a query that gave the results you
were looking for (I think). This is the SQL for the query I came up with:

SELECT Table1.BACode, Table1.Code1, Table1.Code2, Table2.Desc AS code1desc,
Table2.Price AS code1price, Table2_1.Desc AS code2desc, Table2_1.Price AS
code2price, [code1desc] & "+" & [code2desc] AS Service,
[code1price]+[code2price] AS Cost
FROM Table2 AS Table2_1 RIGHT JOIN (Table2 RIGHT JOIN Table1 ON Table2.Code
= Table1.Code1) ON Table2_1.Code = Table1.Code2
WHERE (((Table1.BACode)=[enter a code]));


Does it *have* to be a query or could you use VBA and put the results into
unbound controls? It would be a lot easier to create a function or two to get
the results.

HTH
Steve