Help with Append Query

[Guest]

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!

 

[Chris2]

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.

winsa,

JET SQL (and MS SQL Server, IIRC) uses the + operator for two
purposes. Addition on numeric data types, and concatenation on string
data types.

What is going on in the Query above that makes you think JET SQL (or
MS SQL Server) isn't recognizing the + operator?

I add in mention of MS SQL Server because both of the tables named
above have "dbo." prefixing them, an MS SQL Server features (or,
vaguely, there is a chance it might be Sybase).

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.

Another way to INSERT into an existing Table from an existing Table?
No.

Secondly, is it possible to concatenate two columns of different datatypes
into one column?

In JET SQL, paste the following into the SQL View of a new Query:

SELECT "abc" + "def", "123" + "123", 123 + 123, 123 & "456", 123 +
"456";

Results:

"abcdef", "123123", 246, "123456", 579


In MS SQL Server:

SELECT 'abc' + 'def', '123' + '123', 123 + 123, 123 & '456', 123 +
'456';

------ ------ ----------- ----------- -----------
abcdef 123123 246 72 579

(1 row(s) affected)


Note that column 4 didn't quite work out the same way (& isn't the
concat operator in MS SQL Server, it's a bitwise AND operator).

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.

If the above SQL were being executed in MS Access, it would work. &
is a concat operator. In MS SQL Server, it's a bitwise AND operator.
See the example above for what happens when it's used for unintended
purposes.

Maybe someone can suggest a better way of doing this?

Change the & to + in the SQL.

Thanks heaps!

Just remember, the main place to ask MS SQL Server questions on SQL
would be microsoft.public.sqlserver.programming;
microsoft.public.access.queries is for questions about MS Access
Queries.


Sincerely,

Chris O.

 

[Guest]

Hi Chris

Thanks for replying to my post.

I'm still not sure of the differences between JetSQL and MS SQL. Since I'm
trying to programme an Access Project, I figure I'm using MS SQL?? I didn't
really think that I was having a problem with SQL as I was trying to create
an Append Query in the QBE pane, but figured I needed to tweak something in
the SQL.

Anyway, I changed the & to + and the query worked. Simple!

Thanks heaps!

Winsa

 

[Chris2]

Hi Chris

Thanks for replying to my post.

I'm still not sure of the differences between JetSQL and MS SQL. Since I'm
trying to programme an Access Project, I figure I'm using MS SQL?? I didn't
really think that I was having a problem with SQL as I was trying to create
an Append Query in the QBE pane, but figured I needed to tweak something in
the SQL.

Anyway, I changed the & to + and the query worked. Simple!

Thanks heaps!

Winsa

"Chris2" wrote:

Winsa,

There are *huge* differences in *certain* areas, between JET SQL and
Transact-SQL.


Sincerely,

Chris O.