• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

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
0
pablito70
Asked:
pablito70
  • 2
1 Solution
 
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
 
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
 
pablito70Author Commented:
It works. Thank you.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now