INSERT and SELECT statement problem

[Wilson Hew]

Can I insert record from the SELECT statement as below:

INSERT INTO Table1(Field1, Field2)
SELECT B.Field1, Count(B.Field1)
FROM Table2 B
WHERE (B.Field2 BETWEEN #01/01/2003# AND #25/01/2003#)
GROUP BY B.Field1;

I have tried in MS Access 2000, it gave me error. It said data type conversion error!

Below is the data structure:

Table1
=====
Field1 ---> Text - 15
Field2 ---> Number - Integer

Table2
=====
Field1 ---> Text - 15
Field2 ---> Date/Time

Can I cast the data type in the SELECT. If not, how? Please help. Thanks.

Wilson

 

[jdjewell]

Have you tried...

...running the smaller pieces of your subquery separately to see what value is being returned?

SELECT B.Field1
FROM Table2 B
WHERE (B.Field2 BETWEEN #01/01/2003# AND #25/01/2003#)
GROUP BY B.Field1;

and

SELECT Count(B.Field1)
FROM Table2 B
WHERE (B.Field2 BETWEEN #01/01/2003# AND #25/01/2003#)
GROUP BY B.Field1;

also, shouldn't your syntax look like this:

INSERT INTO Table1(Field1, Field2)
values (SELECT B.Field1, Count(B.Field1)
FROM Table2 B
WHERE (B.Field2 BETWEEN #01/01/2003# AND #25/01/2003#)
GROUP BY B.Field1);

I don't know if either suggestion will help you or make a difference. Let us know how it goes.

 

[Wilson Hew]

Thank you for your replied. I was managed to correct the statement. It was not data type conversion error but just the Access gave wrong message. I corrected and fixed it already!

Bye.

Wilson