Problem with Autonumber in append query

[jlbartley]

I have two tables:
FixedFields: ID (AutoNumber), OrigYear, Mid, Specdetail
VarFields: ID (joined to FixedFields.ID), RecordType, PlanYear, 4DWFTE)

I want to append records into both tables, grouping by ID where sum(VarFields.4DWFTW)<>0 and VarFields.PlanYear = 2001. How do I do an append query and append into both tables? If I have to do seperate ones, how do I pick up FixedFields.ID and insert it into VarFields.ID? I don't know VB so am trying to do all of this through SQL. Any help would be appreciated. Thanks!

 

[smedvid]

That is why I avoid autonumbers when at all possible. But, you should be able to do the following.

INSERT FixedFields: (OrigYear, Mid, Specdetail)
DMAX() to get maximum ID
INSERT ID (joined to FixedFields.ID), RecordType, PlanYear, 4DWFTE)

'don't include the Autonumber field on 1st INSERT also.

htwh
Steve Medvid
&quot;IT Consultant & Web Master&quot;

http://users.erols.com/medvid/

 

[jlbartley]

Thank you for the post.

Could you help me out a little bit more with the sql.
Right now I have

INSERT INTO FixedFields (OrigYear, Mid, Specdetail)
SELECT FixedFields.OrigYear, FixedFields.Mid, FixedFields.Specdetail, Sum(VarFields.[4DWFTE]
FROM FixedFields INNER JOIN VarFields ON FixedFields.InitiativeNo = VarFields.InitiativeNo
GROUP BY FixedFields.OrigYear, FixedFields.Mid, FixedFields.Specdetail
HAVING (Sum(VarFields.[4DWFTE])<>0) AND ((VarFields.PlanYear) = &quot;2001&quot

I'm not sure where I would put in the DMAX, would it be right after the having and do I need to do a second join?
Thanks for more help!

 

[smedvid]

The approach I suggested was broken into 3 sections...

1. Insert SQL - Parent Table
2. lnVariable = DMax(..) A seperate line in a procedure.
3. Insert SQL - Child Table - using lnVariable() for ID field.

-> But, u could possibly avoid using the lnVariable and imbed the DMax() within the Select, it may work, never tried it. Good thought though.

My preference would be to develop a procedure to handle this as opposed to putting everything into SQL. Steve Medvid
&quot;IT Consultant & Web Master&quot;

http://users.erols.com/medvid/