Further Adventures in SQL Append query Syntax

[LanaR]

@arnelgp base on the code you produced for me earlier I am now trying to expand that concept.

I have a de-normalised table containing grape synonyms eg. Pinot Grigio / Pinot Gris / Grauburg

What I'm trying to do is split them into 2 new records (in this case, in others it could be one or more) based on the slash and along with the ID from the Original record. So the new record will look like;

GrapeNID (PK-Autonumber), Grape (name of the grape), GrpSynID (The ID of the original grape)

My SQL now looks like;

    Const APND_QUERY = _
         "INSERT INTO TBL_GrapeNorm ( Grape,GrpSynID ) " & _
                            "SELECT p1, SELECT p2;"

And the working part of the code;

'Count number of records and save as GrapeCnt
        If Not (.BOF And .EOF) Then
            .MoveLast                   'move to last record
            GrapeCnt = .RecordCount     'assing record count to GrapeCnt variable
            .MoveFirst                  'move to first record
        End If
       
'This is were the real work happens
        Do Until .EOF                                           'Start cycleing through record set until the end is reached (Do Loop)
            GrapeOrgStr = !Grape & ""                           'Assign next record in Field[Grape] to variable GrapeOrgStr
            varValue1 = Split(GrapeOrgStr, "/")                 'Assign split string (split on commas) to variable VarVal1
            GrapeID = !GrapeNID
           
           
            For i = 0 To UBound(varValue1)                      '(loop i) - will loop the number detected by UBound()

                    With db.CreateQueryDef("", APND_QUERY)      'Create append query based on SQL previous defined (at the star)
                        .Parameters(0) = Trim$(varValue1(i))    'Insert string to be append in Append Query
                        .Parameters(1) = GrapeID
                        .Execute                                'Run append query
                    End With                                    '

            Next i                                              'End of loop i, return to start
       
            .MoveNext                                           'Move to next record
            GrapeCnt = GrapeCnt - 1                             'Incriment GrapeCnt down by one -Not sure why? as GrapeCnt dosen't seem to be in use??
        Loop                                                    'Return start of loop "Do" Loop

I'm currently getting a 3075 error Syntax error in query expression 'SELECT p2' I'm guessing I've missed a trick in assigning GrapNID to GrapeID and into p2. I'd be very grateful for a nudge in the right direction

 

[ByteMyzer]

Try changing:

"SELECT p1, SELECT p2;"

...to:

"SELECT p1, p2;"

 

[LanaR]

Thanks for that

Now to figure out why there is a mismatch between what I actually coded and what I thought I was coding

 

[arnelgp]

'Incriment GrapeCnt down by one -Not sure why? as GrapeCnt dosen't seem to be in use?

yes it is not being used.
i get it from your original Insert code, so you can remove it.