INSERT SQL with Access

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

I am trying to insert records into an Access table (already existing). There
are three fields in the table, one I want to update with "01", the next two
I want to populate from another table. The code is as follows. I have
carefully checked column names, types and lengths and all match. I am
getting a message saying that the "Number of query values and destination
fields are not the same".

Is this something I am doing wrong or a limitation in Access?

INSERT INTO tblBookReference
(BookNumber, BookReference, DesignNumber)
VALUES ('01',
(SELECT [Beaker Master].DougNumber, [Beaker Number].DesignNumber
FROM [Beaker Master]
WHERE (([Beaker Master].DougNumber Is Not Null) Or ([Beaker
Master].DougNumber >""))
));

Help appreciated, thanks, Michael.
 
I believe you'll need to use two subqueries to get the two fields' values
from the other tables:

INSERT INTO tblBookReference
(BookNumber, BookReference, DesignNumber)
VALUES ('01',
(SELECT [Beaker Master].DougNumber
FROM [Beaker Master]
WHERE (([Beaker Master].DougNumber Is Not Null) Or ([Beaker
Master].DougNumber >""))),
(SELECT [Beaker Number].DesignNumber
FROM [Beaker Master]
WHERE (([Beaker Master].DougNumber Is Not Null) Or ([Beaker
Master].DougNumber >""))));
 
Michael,

The syntax of the SQL is not correct. The way it stands, you are
inserting 2 Values, being '01' and then your SELECT statement. I would
do it like this...

INSERT INTO tblBookReference (BookNumber, BookReference, DesignNumber)
SELECT '01', [Beaker Master].DougNumber, [Beaker Number].DesignNumber
FROM [Beaker Master]
WHERE (Nz([Beaker Master].DougNumber,"") > "")

.... although I think (untested!) that this would also do it...

INSERT INTO tblBookReference (BookNumber, BookReference, DesignNumber)
VALUES ('01'), (SELECT [Beaker Master].DougNumber, [Beaker
Number].DesignNumber
FROM [Beaker Master]
WHERE (Nz([Beaker Master].DougNumber,"") > ""))
 
See Steve's answer....I missed the mark!

--

Ken Snell
<MS ACCESS MVP>

Ken Snell said:
I believe you'll need to use two subqueries to get the two fields' values
from the other tables:

INSERT INTO tblBookReference
(BookNumber, BookReference, DesignNumber)
VALUES ('01',
(SELECT [Beaker Master].DougNumber
FROM [Beaker Master]
WHERE (([Beaker Master].DougNumber Is Not Null) Or ([Beaker
Master].DougNumber >""))),
(SELECT [Beaker Number].DesignNumber
FROM [Beaker Master]
WHERE (([Beaker Master].DougNumber Is Not Null) Or ([Beaker
Master].DougNumber >""))));

--

Ken Snell
<MS ACCESS MVP>

Michael said:
I am trying to insert records into an Access table (already existing). There
are three fields in the table, one I want to update with "01", the next two
I want to populate from another table. The code is as follows. I have
carefully checked column names, types and lengths and all match. I am
getting a message saying that the "Number of query values and destination
fields are not the same".

Is this something I am doing wrong or a limitation in Access?

INSERT INTO tblBookReference
(BookNumber, BookReference, DesignNumber)
VALUES ('01',
(SELECT [Beaker Master].DougNumber, [Beaker Number].DesignNumber
FROM [Beaker Master]
WHERE (([Beaker Master].DougNumber Is Not Null) Or ([Beaker
Master].DougNumber >""))
));

Help appreciated, thanks, Michael.
Click to expand...
Click to expand...
 
Thank you both very much!

The first one worked perfectly Steve.

Strange though, I copied the original syntax exactly from a book on SQL !

All the best,

Michael.

Steve Schapel said:
Michael,

The syntax of the SQL is not correct. The way it stands, you are
inserting 2 Values, being '01' and then your SELECT statement. I would
do it like this...

INSERT INTO tblBookReference (BookNumber, BookReference, DesignNumber)
SELECT '01', [Beaker Master].DougNumber, [Beaker Number].DesignNumber
FROM [Beaker Master]
WHERE (Nz([Beaker Master].DougNumber,"") > "")

... although I think (untested!) that this would also do it...

INSERT INTO tblBookReference (BookNumber, BookReference, DesignNumber)
VALUES ('01'), (SELECT [Beaker Master].DougNumber, [Beaker
Number].DesignNumber
FROM [Beaker Master]
WHERE (Nz([Beaker Master].DougNumber,"") > ""))

--
Steve Schapel, Microsoft Access MVP

I am trying to insert records into an Access table (already existing). There
are three fields in the table, one I want to update with "01", the next two
I want to populate from another table. The code is as follows. I have
carefully checked column names, types and lengths and all match. I am
getting a message saying that the "Number of query values and destination
fields are not the same".

Is this something I am doing wrong or a limitation in Access?

INSERT INTO tblBookReference
(BookNumber, BookReference, DesignNumber)
VALUES ('01',
(SELECT [Beaker Master].DougNumber, [Beaker Number].DesignNumber
FROM [Beaker Master]
WHERE (([Beaker Master].DougNumber Is Not Null) Or ([Beaker
Master].DougNumber >""))
));

Help appreciated, thanks, Michael.
Click to expand...
Click to expand...
 
Back
Top