Using Append Query To Update Tables

[mrssevans]

I am using an append query to take information from a linked table which I am not able to edit, to a table that I can edit and add one field to. Here is my problem...I would like if everytime I ran the query I would not duplicate the records, but only update the information. I am going to run this everytime I open a particular form so I will have updated data in my form. Am I using the correct query or is there a better way to do this?

 

[pono1]

M,

I'm not sure if I see the bigger picture here (almost never do), but I will try to address your specific question:

If the record you're appending will always be present in the append-to table, then instead of using an append query, use an update query. (See Access Help and search this forum for examples.)

If sometimes the record will already be in the target table and sometimes it won't, then you could first check if the rec is present and act one way if it is (Update), another if it isn't (Append). You can check if a rec is present using DAO, ADO, or even good old DCOUNT or DLOOKUP. Roughly, you're looking at something like the following:

IF DCOUNT("fieldname","tableName","fieldname = 123") > 0 Then
     Update it
Else
    Append it
End if

Regards,
Tim

 

[mrssevans]

I am not sure if we are on the same page. Let me try to explain a little further.
Everyday we enter trades into a Linux database and these trades are stored in the open trades table until they are closed months later and they are moved to a closed trades table. I am needing to reconcile these trades on a daily basis so I want to create a form that I can use to reconcile these. In order to do this I need a table with a yes/no field. Because I am using linked tables to this Linux database I cannot add this yes/no field so I need a tbl with all the same data as in a linked table but a yes/no field. The trades don't have a unique ID becuase the linus system doesn't contain one and there isn't anything I can do to change this. Am I out of luck?

 

[pono1]

M,

Sorry, but I have more questions than answers...

Without some sort of cross-reference how will you ever know if a trade in DB1's table is the same trade in DB2's table?

Are there any fields in the read-only DB that can uniquely identify a record and that you can access? A time stamp? Are you sure the DB on the Linux server doesn't use some sort a primary key in it's table?

Off the cuff: Unless this is one of those "we don't want them to know what were doing" situations, I would hunt down the Unix geek who oversees the server and explain what you need...

Regards,
Tim

 

[mrssevans]

The only uniqueness we have is to compare the date...price...# of contracts...etc. We kind of have to look at each one and reconcile it with a statement from our investor. It sucks!!!! The programmers are morons and should be fired!!!

 

[pono1]

Your best hope is to get someone with power on your side and let them swing the hammer... You're situation isn't unique, if that's any comfort. Hang in there.

Regards,
Tim

 

[mrssevans]

I still am pondering over this issue and looking for a solution. Here is my problem...I am using a linked table as stated above and I am not able to edit the properties. I am trying to make a form to reconcile some trades. I have all the trades that I need in one table, but I need to add a check box or something to determine if the trade is reconciled or not. I have made a unique ID by combining several of the fields, but not sure about the check box. Any suggestions?

 

[mrssevans]

Is it possible somehow to add a yes/no box to a linked table through VB or anything?