Database workflow question (1 Viewer)

[gschimek]

I'm looking for some suggestions on a workflow for a database. Here are the details:

I maintain a website that is used for online registrations. Until now, when a visitor would fill out a registration form, that information would be emailed to someone, and that person would take the email and type the visitor's information into an Access database.

I'm in the process of changing the website so that instead of emailing the information, it will insert it into a SQL database on the site. Then that data could simply be imported into the Access database to avoid having to type everything again.

However, I've got a few things that I'm not sure about. First, I need a way for the person using the Access database to check and possibly modify the data that is being imported. For example, if someone reverses first and last name in the online form, I'd want that fixed before the final import into Access. I think I could handle that by just importing into a temp table and then after modifications are done, move it to the correct table. I haven't researched how to do that yet, but it seems fairly straightforward.

What I really need advice on is workflow. Let's say that 10 people register online and those 10 registrations are imported into Access, verified, and then moved to the correct table. What happens next week when 5 more have registered? If I import into Access again, those original 10 will come with it, and I need a good way to be able to either mark them as already inserted so I don't get duplicates, or to somehow only select the new registrations for moving over to the correct table. I think I'd probably empty the temp table before each import, unless someone can tell me a reason not to.

Hopefully that makes sense. Can anyone offer any suggestions on which direction to go with this?

Thanks.

 

[spikepl]

Get records saved at your SQL db with a timestamp. Keep track of what you import. Alternatively, if you use Outlook, retain the email, and let automation digest it and stuff it into Access.

 

[gschimek]

Timestamps are a good idea. But I'd still have issue with people who fill out multiple registrations. Sometimes people will fill out the form twice because they didn't fill out some of the optional fields the first time. I'd need a way to deal with those duplicates, and timestamps wouldn't be enough.

 

[spikepl]

Huh? How do you know if it is the same "filler"? Either you know or you don't. If you don't then it is a new user. If you know how to id that it is the same user, then delete the previous reg and keep the new one.

 

[gschimek]

Right, I normally do want to delete the previous registration and keep the new one. I'm mainly looking for ideas on the best way to handle that, among other issues. I could import 10 records, delete 1 duplicate, and move the remaining 9 to the correct table. I'm just trying to figure out the best way to go about that, without reinventing the wheel if I don't have to. Maybe have an extra field with a flag for moving to the correct table or not? I'm just brainstorming at this point.

Thanks!