Append records to table periodically

[ponneri]

Hi.

I have a peculiar problem with my work envi. There is a user who updates a 'bills' table every few days. And he copies that table to a shared folder location on our network.

And I have to append this table's current records into another table. (From which I generate reports) This another table that I have; has a few hundred records from the previous years. So, it's a strict case of append only !

But, how do I ensure that every time I append - I overwrite or update only the last records of the bills table data without touching the previous years data ?

One or more fields in one or more records may change in the 'bills' table each week.

Can someone guide me please ?

 

[sneuberg]

But, how do I ensure that every time I append - I overwrite or update only the last records of the bills table data without touching the previous years data ?

If it's simply a matter of not updating the previous years you can ensure that in the WHERE clause of the update statement but I'm confused as you are talking about appending and then updating; two different thing. The solution to your problem may be simply an unmatched query, but I need to have more details. Please give us examples of the tables, fields and data involved and show us what you want to happen with an example of a desired output.

 

[ponneri]

Sorry, if my words were confusing.

What I mean is : The user will update his set of data every few days either by adding completely new records to his table or by changing the values in some fields of the existing records.

And I need to append this set to my table each time, before I run the reports; to get the correct and current info in them.

I hope I'm clear now ?

Unfortunately, my work envi does not allow me send any file outside my organization.

Thanks for understanding

 

[sneuberg]

This still isn't clear. If the records you are "appending" are new records there shouldn't be any problems. If the records that you are "appending" already exist in your table then you probably don't want to append records, but rather update them. You can (or I can) create queries that will append the one that don't exist and update the ones that do, but I would need to know the structure (field names) of these tables and most importantly the field(s) that uniquely identify the records.

 

[ponneri]

As an example, consider this scenario.

Both tables have same structure of about 22 fields each. My 'old bills' table has a few hundred records of the previous years. The other user has 'new bills' table that is updated for the current year only.

When I run the reports; I need to have all the records of 'old bills' and 'new bills' in one table as the underlying table. The issue is that after some days, the user could have new records in his 'new bills' table or he might have changed some field values of existing records.

There is no way to know which records are edited in the 'new bills' or new ones added (except by count of records).

 

[ponneri]

Perfect !

" If the records that you are "appending" already exist in your table then you probably don't want to append records, but rather update them. You can (or I can) create queries that will append the one that don't exist and update the ones that do "

So, can you please show me how to write the query "that will append the one that don't exist and update the ones that do" ?

You can take any example. I'll try to understand that and use it in my application.

 

[sneuberg]

Maybe you could make a union query of the two tables and make that the record source for your report.

 

[sneuberg]

So, can you please show me how to write the query "that will append the one that don't exist and update the ones that do" ?

You can take any example. I'll try to understand that and use it in my application.

Forget the union query idea for now. I'll get back to you with an example, shortly, I hope.

 

[plog]

Both tables have same structure of about 22 fields each. My 'old bills' table has a few hundred records of the previous years. The other user has 'new bills' table that is updated for the current year only.

Why do you have 2 tables? This data should exist in only 1 table at all times. I think you need to structure your tables properly and this is a non-issue.

 

[sneuberg]

The attached database has two queries. qryAppendNewStuff is an unmatch query combined with an append query so it appends any records to the tblOldStuff from tblNewStuff if they are not already in tblNewStuff. The query qryUpdateOldStuffWithNewStuff updates any existing records in tblOldStuff with existing records in tblNewStuff. It update whether or not there have been any changes or not but so what and that's the price you pay for having two tables.

By the way? Why do you have two tables?

 

[ponneri]

Hi. Thank you very much for taking time to create an example for me. That's so nice of you.

BTW, the union query worked well too. In fact, I used it in a similar situation a few years ago. Thanks again !

I've downloaded your example and will check it soon.

And to answer your query, the reason I use two tables is : the user updates the records for the current year only and by some biz logic (may sound odd) does not need to know or have the previous years records in the same table.

 

[jdraw]

I agree with plog (post #9). There should not be confusion re records in the table. It seems to be a structure issue (tables and relationships to match the business requirements). Why not 1 shared table for this activity?

 

[sneuberg]

Following on to jdraws question is this a security issue in that you are trying to limit access to need to know. Are you the US State Department or NSA and you don't want to let this employee who is currently entering the date to be able to provide last years data to WikiLeaks?

If this is the case then you are probably stuck with two tables. I don't believe there is any way to implement record level security in Access. Is there?

 

[ponneri]

Thank you all very much for the inputs. However, the union query solved my issue and the peculiar setup I'm working in !