Appending A Specific Record to A Table

[eriel3]

I am trying to append a just one record to a new table. But everytime I append it appends the whole table. How to I tell it to append the current record. Actually, I would like for it to append from a form when a person hits save.

As you can tell, i am not a programmer, but I do understand a little code.

Help!

 

[BNPMike]

If you append one record to a new table, then you have indeed appended the whole table. If you are trying to move a record from one table into another then always first write the ordinary select to check you are selecting the records you intend. When you've got that query down to one record you can change it to an append query and you know the right data is going across.

Perhaps you could post the SQL here for us to see.

As for forms, if you have created a new record on the form then it is automatically appended as you move off that record. You don't need to program anything.

 

[eriel3]

Here is a copy of the script. it is just a basic append query. Now when I put the append query on my form as a command button, it does not append any records.

INSERT INTO tb_MethPlan2
SELECT tb_MethPlan.*
FROM tb_MethPlan INNER JOIN tb_MethPlan2 ON tb_MethPlan.DailyID = tb_MethPlan2.DailyID;

 

[BNPMike]

Your SQL seems to be selecting all the records that are already in MethPlan2. So firstly you are not adding one record but any number. Secondly whatever you select will be rejected because it is already present.

Can you say in English how you want to identify the record you want to add?

 

[eriel3]

okay, i am trying to add the record by the id number.
I created an autonumber ID field.

 

[BNPMike]

I suggest if you do LEFT JOIN and add WHERE tbl_MethPlan2.DailyID IS NULL you will pick up any records not already in MethPlan2 and insert them.

Just in case - you can't have autonumbers in both tables or at least if you do you'll have to be careful.

 

[eriel3]

Okay, this worked. Now for the next question, if you don't mind.

I have a table to make it simple for my own understanding this is just a sample table

Fields are:
RecordID
ACCOUNT1
ACCOUNT2
CREDIT
AVAILABLE CASH

What I am trying to do is (account1 + account2 - credit)=Total Cash

I am trying to do a running total for Total Cash.

I put this is a update query, but when it updates is only updates the current record and it also deletes the Total Cash for the previous record.

I actually want it to update all records and when information is changed in one record it will automatically update all of the records.

 

[eriel3]

Oh yeh, and how can I add two commands into one. If i were to run to queries is it possible to run the queries on one command button, and how?

 

[BNPMike]

You can't do running totals in relational tables as relations are not supposed to have order. Generate your record-by-record Available Cash by creating a select query (ie you're not updating anything). To get a running total, base a report on that query. Running totals are easy in reports. Don't store calculated data in base tables.