Insert SQL statement with Access using fixed values and joined table

Hello,
I need to populate an Access table using default values and one joined table field.
I have two tables:

- assets
- serials

INSERT INTO [serials] ([model], [serial], [location])
SELECT 'DL380', 'CZC999999', [t].[location]
FROM
    [assets] AS t
    INNER JOIN [locations] AS p
    ON (t.location = p.location)
WHERE (p.asset = 'SRV15012');

but when I run this query, Access return the error:

Process ERROR: No value given for one or more required parameters.
Process ERROR: Invalid SQL statement; expected 'DELETE', 'INSERT'

Thanks in advance
LVL 2
pablito70Asked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try enclosing the sub-select in parentheses:

INSERT INTO [serials] ([model], [serial], [location])
(SELECT 'DL380', 'CZC999999', [t].[location]
FROM
    [assets] AS t
    INNER JOIN [locations] AS p
    ON (t.location = p.location)
WHERE (p.asset = 'SRV15012'))

This error:

No value given for one or more required parameters

generally means you've got a table or field name incorrect. Can you run JUST the sub-select correctly?
0
 
IrogSintaCommented:
Do you have an asset field in your location table or should that be t.asset = 'SRV15012' instead?
0
 
pablito70Author Commented:
Yes,
I need to populate the table serials (which are the serial no of the assets), but the relationship between tables is the field [location] (I known this is strange but I'm not the owner of database).

So I have to create a script that manually the serials for 'SRV15012' but the only one chance for joining the table is using the [location] fields.

This job I need to repeat for every asset (approx 950 pc).

Thank you.
0
 
pablito70Author Commented:
It works. Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.