create new field with append query

Question

I am trying to append records from one table to another existing table - the existing table has an invoice number that is the primary key and it is NOt in the table that I am appending from.  sooo the record will not append in.

I would also like to automatically creat the Invoice number in the table that I am going to append it into to include this YEAR plus some other times to Make up the Invoice Number????  Do I need to create the Invoice number 1st in the table that I am appending it from?? If the yes/no  field - creat Invoice =yes and Type= lender than the record is appended - But not sure how to handle the creation of the Invoice number???

thanks,

Barb

Answer 1

just

LeadId Autonumber

firsname text

lastname text

address etc. all text

createinvoicenumber yes/no

form now has unboundtext box with the above if then calc. but not sure what to put for the true part - as of right now only returning 2010 want what above post noted??

thanks

barb

Answer 2

Post your table and field names with data type.

---

Build a little, test a little.

Answer 3

I am creating an unbound text box in the 'from table' with a calculation in the control source = iif(createinvoice=yes,year(date()),"")  Not sure what to put if want to return 20100907 for today and add -1, -2 for each new one added

ie. 20100907-1

 20100907-2

 20100907-3

tomorrow would be

20100908-1

20100908-2

20100908-3 etc.  thanks,

barb

Answer 4

Have the 'from table' record in a form with an unbound text box to enter your invoice number. The append query can use the unbound text box with 'from table' record in the form.

---

Build a little, test a little.

Answer 5

No.  If you can make up the invoice number from fields in the a record, you can use a calculated field in the append query.

Maybe a little like:

INSERT INTO [existing table]

SELECT Year(Date()) & <some other stuff> As InvoiceNum, *

FROM [one table]

WHERE [creat Invoice] AND [type] = "lender"