W
winsa
Hi
I'm a complete newbie to SQL and Access.
I need some help with an append query. Here is my code so far:
INSERT INTO dbo.tbl_MSTRACC
(MST_TRANS_IDX, DEBTOR_IDX, TYPE, TRANS_DATE, REF, NARRATIVE,
AMOUNT, OUTSTANDING)
SELECT 100 + CR_TRANS_IDX AS Expr1, DEBTOR_IDX, 'PMT' AS Expr2, TRANS_DATE,
REF, 'Cash Receipt ' & CR_TRANS_IDX AS Expr3, 0 - AMT_PD AS Expr4, 0 AS Expr5
FROM dbo.tbl_RECEIPTS
Firstly, "Expr1", I'm trying to add 100 to the CR_TRANS_IDX which is an
"int" datatype. I think SQL doesn't recognise "+" in calculations. Is there
another way to do this? This really is just for development as when I go
live I intend to change this field to an autonumber.
Secondly, is it possible to concatenate two columns of different datatypes
into one column? For "Expr3", I'm trying to concatenate the text string
"Cash Receipt" and then the ID number of the particular cash receipt
(CR_TRANS_IDX), but as these are two different datatypes, I'm getting errors.
Maybe someone can suggest a better way of doing this?
Thanks heaps!
I'm a complete newbie to SQL and Access.
I need some help with an append query. Here is my code so far:
INSERT INTO dbo.tbl_MSTRACC
(MST_TRANS_IDX, DEBTOR_IDX, TYPE, TRANS_DATE, REF, NARRATIVE,
AMOUNT, OUTSTANDING)
SELECT 100 + CR_TRANS_IDX AS Expr1, DEBTOR_IDX, 'PMT' AS Expr2, TRANS_DATE,
REF, 'Cash Receipt ' & CR_TRANS_IDX AS Expr3, 0 - AMT_PD AS Expr4, 0 AS Expr5
FROM dbo.tbl_RECEIPTS
Firstly, "Expr1", I'm trying to add 100 to the CR_TRANS_IDX which is an
"int" datatype. I think SQL doesn't recognise "+" in calculations. Is there
another way to do this? This really is just for development as when I go
live I intend to change this field to an autonumber.
Secondly, is it possible to concatenate two columns of different datatypes
into one column? For "Expr3", I'm trying to concatenate the text string
"Cash Receipt" and then the ID number of the particular cash receipt
(CR_TRANS_IDX), but as these are two different datatypes, I'm getting errors.
Maybe someone can suggest a better way of doing this?
Thanks heaps!