Help on Append Query 2

[zinja]

I am attempting to write a query that will append new records to an Activity table. The situation is this:
I have a table which holds all account activity for customers. The records are identified by the customerid. I have written a query to select customers which have a past due balance of greater than $20.00 and show no credits (payments) for the last 45 days.
I want to build a query that will append new records for those customers with the late charge amount in the debit field and the text "Automatic Late Charge" in the description field. The activity table does have an autonumber field. I have tried several things, but I am still a little weak on complex SQL. Can someone please point me in the right direction?

Thanks in advance!

LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.

 

[mgolla]

Here is what I would do:

1) Create an append query using the query you already built to select customers which have a past due balance of greater than $20.00 and show no credits (payments) for the last 45 days.

2) Add A couple of calculated fields to this append query like so:

DebitField: 15.75
DebitReason: 'Automatic Late Charge'

3) Make sure those fields are appending to the Debit Field and Debit Reason field in the Activity table.


This would then automatically put a 15.75 debit on every record in this query and 'Automatic Late Charge' as reason.



HTH
Mike

Dooobie...Doobie......Dooo

 

[zinja]

I now get the warning about adding records to the table (I will turn those off later), but then I also get an error due to key violations (the tblAccountActivity table has an autonumber field in it). Do I need to have that field in my query as well? Here is the SQL I am using right now for testing.

INSERT INTO tblAccountActivity ( UserID, ComponentID, Description, Credit, Debit, [Date], Source, CheckNo, Taxed, Tax, TaxExempt )
SELECT DISTINCTROW tblTempAccountActivity.UserID, tblTempAccountActivity.ComponentID, tblTempAccountActivity.Description, tblTempAccountActivity.Credit, tblTempAccountActivity.Debit, tblTempAccountActivity.Date, tblTempAccountActivity.Source, tblTempAccountActivity.CheckNo, tblTempAccountActivity.Taxed, tblTempAccountActivity.Tax, tblTempAccountActivity.TaxExempt
FROM tblTempAccountActivity;

LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.

 

[scriverb]

Mike: I hope you don't mind me jumping in her on the weekend to help out.

zinja: From the above SQL it looks like you have changed your original Select Query into a Make table and created a temporary table called, tblTempAccountActivity. That is okay but not necessary. As Mike mentioned make sure that you included the two new fields in the new temp table.

To answer your question, 'No, you do not need add the autonumber field to the Append query. ACCESS will take care of incrementing that for you.' The error you are experiencing is because one of the new fields being updated has an Index associated with it that is listed as UNIQUE and you are trying to add a record with the same value. Check the indexes of the table, tblAccountActivity and see if there is a field as I described.

Post back with your findings and we can help sort out the problem.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[zinja]

Thanks guys. You were right on the money Bob. I really appreciate it. I have also taken your advice and streamlined the query alot. It works great. Both of you get a star from me. Thanks again.

LJ Wilson

My personal saying - Just remember, it can always get worse, and usually will.