Questions before undertaking a huge project.

[pablotx]

Here is the scenario:

I have a bunch of people with laptops who go on reviews throughout the state and leave reports on site with the facilities they review. They go out in multiple person teams and work together on reviews. Currently, each person types out their own citations in MS Word on their own computer, saves them to a flash drive and then the facilitator compiles them all together and prints out a report. Then the facilitator comes back to the office, provides their support staff a copy of the report, and then the support staff finalizes it and sends out a letter.

Unfortunately, none of our laptops have Internet capability out in the field.

My idea is to have a multi-user split main database that is stored at our main office. This is for the support staff to store all of the reports. Then I propose to have a local copy of the database (much smaller) on each of the laptops.

Here are my questions:
Lets say that 3 people are out on review together. They each have their entry form on their own computer. The form will be sending information to a few different tables. How can they easily export out their results so that the facilitator could easily import them into their database on their computer.

If all of that were to work somehow, how could the facilitator easily export out the review information tables so that the support staff could import them into the main database.

One of the many stumbling blocks I see relates to Primary keys. The main database will have a main table with a PK of 2008.100 for example. While out on review the reviewers will not be able to use the naming conventions of the main database PK. They might use something like the City name and date (Pensacola02222008). Could that PK be automatically switched into the other format upon import?

Let me know ya'lls thoughts. I have been trying to get my head around this for a few days and have not been able to come up with a feasible solution.

Thanks in advance.

 

[GolferGuy]

Each individual's database on their own laptop would have to know who is using the database, and have a specific code for that person to be included in each record created while in the field. No matter what table has new records created, each record would carry the users code. I would also be sure that each record created, again, no matter which table, would have a date/time stamp in the record. Now, when the field laptops come back to the "Mother" computer in the office, with the main DB, all the records created in the field are EASILY identified by date, and by who was the user. Then, within a batch type of process, the records created in the field can be used to update the main DB.
There are a lot of details that you will need to work out, but I hope this will help you get a good start.
Please let us know how it goes.

 

[pbaldy]

Also, you might search on replication. I've never needed it, but from the sound of it you might be a candidate.

Hey Vic, I made it as far as Auburn a couple of weeks ago. Played the Ridge in a little "let's go over the hill where it's warm" tournament.

 

[pablotx]

Thanks for the input.

As I am really a novice when it comes to Access, I need all of the suggestions I can get, especially on how to do the batch process necessary to move data back and forth.

 

[GolferGuy]

Paul,
I'm really getting my game back, so I'm really looking forward to you getting all the way over the hill to my area. How was the Ridge? I love the course, but it eats me alive each time I've played it. So, why do I like it????

pablotx
A batch process would be where you write one or more queries to copy the records from the indivdual laptop DBs into the main database. It just where a lot of work is done rather than work that is done mainly by an operator keying information into the DB.

 

[pablotx]

Are ya'll talking about the Robert Trent Jones Trail? If so, I played about 8 of the courses a few years back. Loved it. The Judge and Legislature ate my lunch however.

 

[pbaldy]

pablotx; it's a Robert Trent Jones course, but in California between where Vic and I live. I've heard good things about the RTJ Trail down south there, but never played any of the courses. It's about a 2,000 mile drive from here!

Vic; I enjoyed it. I've played it a couple of times before. It was a scramble so I didn't have a score, but I played with what we call "intermittent brilliance". Some pretty good shots, and some pretty bad ones. I hadn't touched a club since November, so I guess no surprise that I was inconsistent. I like tougher courses too. While they may kick our you-know-whats, they make the round more interesting I think.

 

[GolferGuy]

Paul, OK, that's why I like the Ridge. It is beautiful, and a VERY good challenge. The last time I play there was the last day the course was open before the last time the LPGA played there. The greens were the fastest I have ever putted on, but also very true. Maybe after you get some more cobwebs out we should be at the Ridge for a round?

pablotx, Never heard of a golf hole eating your lunch, but I can undestand the feeling. Wish I couldn't, but I sure do.

 

[pbaldy]

I'd like that Vic, though with the weather we've been having up here the spiders are rapidly rebuilding the cobwebs.

My wife's been itching to play too, so we might actually try to get down there the next nice weekend. If you're interested I'll email you. We'd likely play the Ridge or Darkhorse, but open to suggestions.

 

[gemma-the-husky]

i thnik replication is needed there

i think the idea is that replication produces a unique id

perhaps its generated by time instances or something, so there is only a micro chance of a clash. it means your remote users are guaranteed to get unique record ids, so that you can merge them later

so give each user a copy of the dbs, with their own data, then merge the results later.if you have a system working at HO, it will still work on a distributed basis

i think thats the general idea.

 

[Rabbie]

Just remember with Replication that autonumbers behave differently. They wont go up in single steps in a replicated database. This isn't a problem if you only use them as Keys and don't read any other significance into them