The infamous Append Query with a multi-value field

[gaker10]

I've seen posts about this in several places. Most of them don't have solutions. The ones that do, I have tried and still cannot get it to work.

I had the same problem as described in this post, and I tried the proposed solution: https://www.accessforums.net/queries...lds-43565.html

And now I get this error: "An INSERT INTO query that contains a multivalued field cannot contain another field."

My SQL code:

INSERT INTO DRAWINGS ( Drawing, [Primary], AlternateA, AlternateB, CageCodeA.[Value], CageCodeB.[Value], CageCodeC.[Value], Revision )
SELECT [Drawings Not in DRAWINGS].[Drawing#], [Drawings Not in DRAWINGS].[PrimaryP/N], [Drawings Not in DRAWINGS].[AlternateAP/N], [Drawings Not in DRAWINGS].[AlternateBP/N], [Drawings Not in DRAWINGS].CageCodeA, [Drawings Not in DRAWINGS].CageCodeB, [Drawings Not in DRAWINGS].CageCodeC, [Drawings Not in DRAWINGS].Revision
FROM [Drawings Not in DRAWINGS];

The multi value field is the three CageCodes (A, B, and C). This append is running based off a linked Excel that is set up identically to my table.

How do I get this to work?

[June7]

I don't think you can add data to the non-MV and MV fields in the same INSERT action. As the article states: "... you cannot use an append query that references a table that contains a multivalued field. For example, the following query is not valid:..."

This will probably require two INSERT actions.

Why are those 3 field MV types if you are only entering one value into each?

[gaker10]

Each MV field can have any number of values, including none.

[June7]

Okay - is the issue resolved by the suggestion?

[gaker10]

Would the SQL code look similar to my usual INSERT INTO? With the .Value operator on my MV fields?

[gaker10]

Also, would I have to do this as a separate query?

[June7]

As I said, probably two queries. Review https://support.office.com/en-us/art...n-US&ad=US#bm7

[gaker10]

I get the same error as in post #1. My code:

INSERT INTO DRAWINGS ( CageCodeA.Value, CageCodeB.Value, CageCodeC.Value )
SELECT [DrawingsImport].[CageCodeA], [DrawingsImport].[CageCodeB], [DrawingsImport].[CageCodeC]
FROM DrawingsImport
WHERE [DrawingsImport].[Drawing#] = [DRAWINGS].[Drawing];

DrawingsImport is the linked Excel.

[gaker10]

Maybe it should be an update query?

[June7]

Did you review the link? The last 3 items are relevant to your situation.

You are trying to add MV data to multiple parent records. I don't know if this is possible.

I don't use MV fields.

[gaker10]

Yes. The examples in the link are too specific. I can't name a specific value or ID (of which does not exist). It's however many records are in my linked Excel.

[gaker10]

Not to mention I need to update three MV fields, whereas the examples specify one, unless I need three queries for each MV field.

[June7]

Isn't Drawing the ID field?

I don't know if more than MV field can be in the same action, you will have to experiment, which is exactly what I would have to do.

[gaker10]

Technically, yes. But when I treat it as one in my code, it doesn't work. The following yields no results. The drawing number was entered just fine. DrawingImport Query is just a query version of my linked Excel.

So I tried a query with just CageCodeA:

UPDATE [DrawingsImport Query] INNER JOIN DRAWINGS ON [DrawingsImport Query].[Drawing#] = DRAWINGS.Drawing SET DRAWINGS.CageCodeA.[Value] = [DrawingsImport Query].[CageCodeA]
WHERE (((DRAWINGS.Drawing)=[DrawingsImport Query].[Drawing#]));

Design:

[June7]

Unless there is some compelling reason to use MV field (I think they are needed for web database through SharePoint), I recommend you abandon them in favor of a normal related table. I know the MV field uses a hidden related table but much easier to deal with a native table.

Also, multiple similar name fields with same type data is not a truly normalized structure.