Append Records problem

[reglarh]

I have designed a database (ACCESS 2010) running on a standalone computer. It is not hosted on a network and is not written for multiple users. However, for a short period each year two users need to update the same table. I hoped to achieve this by duplicating the database after tagging all of the records in the master copy of the table.The second person, working on the copy database, would create new records that were not flagged. After finishing his work, I hoped to run an append query extracting all non-flagged records from the table in the copy database.

However, life is never that simple, at least for me. First problem is that there are 4 calculated fields in the records and the append failed for this reason. Trying a different approach, a second source of failure was that the system assigned primary key was duplicated in the two copies of the table.

Is there a way of achieving what I want without recourse to Visual Basic which I have never used, managing to develop my system by the use macros and the very occasional single line of VB?

Any suggestions gratefully received!

 

[jdraw]

It is not a good practice to store calculated values in a table.

Can you describe -step by step simple terms - the purpose of the copy database? What exactly is the second person doing? Perhaps some concrete examples would help.

 

[SmallTomato]

Is the primary key an auto number? Why not just append in the new information but do not append in the primary key and Access will assign auto numbers to those records. Can you give more detail about the system assigned Primary key?

Many things you have said are red flags, storing calculated fields, using two copies and recombining but Jdraw will be better help with that.

 

[reglarh]

The storing of calculated fields is to ease the repetition of calculating these very time they are needed. It's mainly string manipulation to extract certain data. These reason for copy databases is that two members of the organisation need to add new members for a limited period of time. No other tables are being updated by the second user. The records added to the second can be extracted easily. I have tried eliminating the system assigned primary key from the records to be appended but Access doesn't like that. I have also tried exporting the new records to Excel, identifying the highest primary key in the master copy and re numbering the excel records sequentially from that number. It's a bit of messing around i really don't want inexperienced uses to do, but even that fails with messages about not being able to parse the calculated fields. I then deleted the data in those fields and it still fails!

 

[jdraw]

If you are having to repeatedly parse and manipulate strings, you might consider the field definition/design.
Perhaps you could provide a few examples from your data showing the original and the final strings.

 

[reglarh]

I think my last reply got lost in the ether since it hasn't come up on this thread.

An example of the calculation I do is for alpha grouping:

(IIf(Left([SurName],1)<"E","A - D",IIf(Left([SurName],1)<"L","E - K",IIf(Left([SurName],1)<"S","L - R","S - Z"))))

I am convinced it is nothing to do with the code which has been working for weeks.

The table to which data being appended is an identical copy to the table from which the data is created. It has, of course, data in the 4 fields which are defined as calculated fields. The field names are identical and I have tried deleting these 4 fields
without success.