Update form/query (1 Viewer)

[ericryd]

Hi,

I need some assistance on a form.

I have a query that runs against 4 linked tables and pulls the data to a local table.

I would like to create a form that shows the last time the table was updated and then a button to update the local table. It should go run the query from the date it was last used until now and append it to the table ensuring no duplicates have been entered.

Any thoughts?

 

[HiTechCoach]

Off the top of my head:

1) store the last data used in a local table to look up last data imported so you know where to start.

2) query the data to get the last date so you know where to start.

3) based on the data, create a unique index that does not allow dups. It may need to be a compound index (using multiple fields) to make it unique. This will allow you to just append all data every time and any dups will be discarded automatically by Access. This way you will always get new records. **This woudl be my choice is possible.

 

[ericryd]

Off the top of my head:

1) store the last data used in a local table to look up last data imported so you know where to start.

2) query the data to get the last date so you know where to start.

3) based on the data, create a unique index that does not allow dups. It may need to be a compound index (using multiple fields) to make it unique. This will allow you to just append all data every time and any dups will be discarded automatically by Access. This way you will always get new records. **This woudl be my choice is possible.

I got step 1 and step 2. Step 3 is where I really get lost...I don't know how to do the unique index?

 

[HiTechCoach]

I got step 1 and step 2. Step 3 is where I really get lost...I don't know how to do the unique index?

I was not trying to give you steps, but three possible solutions.

You can use only one of them.

About the index, without seeing your data and understanding more about it, I can not really give you any suggestions on how you would do this for your database.

 

[ericryd]

I was not trying to give you steps, but three possible solutions.

You can use only one of them.

About the index, without seeing your data and understanding more about it, I can not really give you any suggestions on how you would do this for your database.

ooh...uh....yea

I'm trying some things right now with max date and today-1 to create a query range. My only problem is, duplicates can still be ran.

 

[ericryd]

I've made some progress, so i'm going to end this thread and start a more specific one inside of the queries forum.