Append Query

[benu302000]

I have an append query that is meant to copy a certain record from one table, and put it on the end of another table, but for some reason it refuses to copy from table to table unless the destination table already has a record with a related field in it. And then if I get it to run, and run the query several times in a row, it appears that instead of merely copying the record just once to the destination table it is instead copying it into the destination table times the number of records already in the destination table. heres the SQL.

INSERT INTO [Billet History Table] ( [Billet Number], [Position Sequence], Incumbent, [PMB Date], [PMB Number], [PMB Action], [Org Code], [Org Title], [Funding Source], Title, Series, Grade, Step, FPL, [Reference Number], [Reference Document] )
SELECT [Billet Table].[Billet Number], [Billet Table].[Position Sequence], [Billet Table].Incumbent, [Billet Table].[PMB Date], [Billet Table].[PMB Number], [Billet Table].[PMB Action], [Billet Table].[Org Code], [Billet Table].[Org Title], [Billet Table].[Funding Source], [Billet Table].Title, [Billet Table].Series, [Billet Table].Grade, [Billet Table].Step, [Billet Table].FPL, [Billet Table].[Reference Number], [Billet Table].[Reference Document]
FROM [Billet Table] INNER JOIN [Billet History Table] ON [Billet Table].[Billet Number] = [Billet History Table].[Billet Number]
WHERE ((([Billet Table].[Billet Number])=[Forms]![Record Selection Form]![combo8]));


I know its ugly, any ideas? Thanks

 

[PHV]

unless the destination table already has a record
Expected behaviour because you coded this:
FROM [Billet Table] INNER JOIN [Billet History Table] ON [Billet Table].[Billet Number] = [Billet History Table].[Billet Number]


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[benu302000]

Thanks, I suspected it was the from statement