Insert SQL statement with Access using fixed values and joined table

Posted on 2012-08-14
Last Modified: 2012-08-16
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]
    [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
Question by:pablito70
    LVL 29

    Expert Comment

    Do you have an asset field in your location table or should that be t.asset = 'SRV15012' instead?
    LVL 2

    Author Comment

    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.
    LVL 84

    Accepted Solution

    Try enclosing the sub-select in parentheses:

    INSERT INTO [serials] ([model], [serial], [location])
    (SELECT 'DL380', 'CZC999999', [t].[location]
        [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?
    LVL 2

    Author Closing Comment

    It works. Thank you.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Forms or tables Name 4 48
    Running Sum Issue 7 21
    IF SQL Statement Access SQL 6 38
    Access left join query 5 17
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now