Merging with Personalised ID numbers?

[Ray Murphy]

Hi All,

I'm a rank amateur with Access 2003, but need a simple Access data base
which can be given to a few hundred people for their own data entry.

The idea is for each person to start with an identical empty data base and
then key in data and then merge their whole database with any of the other
users of the program, but it seems the automatically generated ID numbers
create problems.

Basically we're just looking at:
(a) A main page with hundreds of subjects names and their birth dates.
(b) A sub form (or "file card") for each person where we enter (say) those
subject's home movie titles.

Currently I've got a "non-mergable" project working, but I'd like to be able
to make an extra column for personalised ID numbers for the main page and
also for the sub form entries so that there can be no clashes when merging.

For example if a user of the program was named William John Smith, he could
use a prefix of WJS for all ID numbers, and those ID's would replace the
Primary key.
This "WJS" could be entered manually when the program was installed, or
perhaps it could be built into his program before delivery.

It seems that if these personalised ID numbers were used, they would need to
be generated in the normal way and then (in the separate column) have the
prefix added in front of it.ie: WJS 1222 -- which would then become text
rather than a number. Presumably this would slow the program down a lot, but
it probably wouldn't matter for this purpose - as long as we can have
seamless merging.

I haven't got a clue at this stage if this is going to be hard or easy, but
any suggestions are welcome - including email addresses of anyone who can
knock up something like this for payment (I don't know anyone).

Direct contact is fine, although I may be away from the PC for a few days
soon.

Ray

 

[Joseph Meehan]

Hi All,

I'm a rank amateur with Access 2003, but need a simple Access data
base which can be given to a few hundred people for their own data
entry.
The idea is for each person to start with an identical empty data
base and then key in data and then merge their whole database with
any of the other users of the program, but it seems the automatically
generated ID numbers create problems.

Basically we're just looking at:
(a) A main page with hundreds of subjects names and their birth dates.
(b) A sub form (or "file card") for each person where we enter (say)
those subject's home movie titles.

Currently I've got a "non-mergable" project working, but I'd like to
be able to make an extra column for personalised ID numbers for the
main page and also for the sub form entries so that there can be no
clashes when merging.
For example if a user of the program was named William John Smith, he
could use a prefix of WJS for all ID numbers, and those ID's would
replace the Primary key.
This "WJS" could be entered manually when the program was installed,
or perhaps it could be built into his program before delivery.

It seems that if these personalised ID numbers were used, they would
need to be generated in the normal way and then (in the separate
column) have the prefix added in front of it.ie: WJS 1222 -- which
would then become text rather than a number. Presumably this would
slow the program down a lot, but it probably wouldn't matter for this
purpose - as long as we can have seamless merging.

I haven't got a clue at this stage if this is going to be hard or
easy, but any suggestions are welcome - including email addresses of
anyone who can knock up something like this for payment (I don't know
anyone).
Direct contact is fine, although I may be away from the PC for a few
days soon.

Ray

I am not certain of exactly what the environment is. It appears you
have more than one person entering data. Are they all entering data on the
same machine, or their own machines or are they all accessing the same
program via a LAN?

Assuming they are all working on their own machines using their own copy
of a database, are you using replication?

 

[Ray Murphy]

I am not certain of exactly what the environment is. It appears you
have more than one person entering data. Are they all entering data on
the same machine, or their own machines or are they all accessing the same
program via a LAN?

Many people in different countries will get the program on CD by snail mail
and presumably they will be able to operate it with the free version of
Access if they don't have the full version.

Assuming they are all working on their own machines using their own
copy of a database, are you using replication?

I don't know what replication is unfortunately.

Ray

 

[Joseph Meehan]

Many people in different countries will get the program on CD by
snail mail and presumably they will be able to operate it with the
free version of Access if they don't have the full version.

There is NO free version of Access.

There is a runtime version that can be used with programs developed
under the Developers Toolkit (I can't remember exactly what it is called
now). You will need to own that kit and it is not cheap.

I don't know what replication is unfortunately.

It is the tool used in Access to keep the problems that normally arise
when doing this kind of thing as straight as possible. You can look it up
under replication in the Access help file

I suggest you review replication and how you are going to approach the
question of those without the Access program available on their machines
before you get too far into development of an Access database.

There are other options to sharing data you may wish to consider. It is
possible to set up Access on the internet. That might be a good solution
for you. I have never worked on it, so I can't offer much advice, but I
would look into it. I wish I had known about it before I retired. I had
several projects that could have greatly benefited. You also may want to
consider using Excel spreadsheets as your intermediate. Far more people
have access to Excel than to Access. While I don't think Excel is a great
tool for what you appear to be doing it may prove to be the tool of chose if
you have a problem with users not having Access on their machines and the
Developer's kit is not a option for you.

 

[Ray Murphy]

[BIG SNIP]

There is NO free version of Access.

There is a runtime version that can be used with programs developed
under the Developers Toolkit (I can't remember exactly what it is called
now). You will need to own that kit and it is not cheap.

Ah the runtime version of Access; yes that's what I used to run a commercial
data base that I bought a few years ago.
I have since acquired Access 2003 which was included with the MS Office
package for personal use, and until your reply arrived had assumed that the
full version of Access (about $700 AU) could be used to make and distribute
the type of program I mentioned in this thread, but it sounds like I would
need something bigger than that.

It is the tool used in Access to keep the problems that normally arise
when doing this kind of thing as straight as possible. You can look it up
under replication in the Access help file

I suggest you review replication and how you are going to approach the
question of those without the Access program available on their machines
before you get too far into development of an Access database.

Thanks, I've just now had a look at the Help file, and can see that
replication (identical data bases) wouldn't be needed in this case. Ninety
per cent of the users of this simple program would be uploading data to the
other 10% on a random basis - according to who they wished to share data
with.
The idea is for most of the users to contribute data to those doing the
research with it.

There are other options to sharing data you may wish to consider. It
is possible to set up Access on the internet. That might be a good
solution for you. I have never worked on it, so I can't offer much
advice, but I would look into it.

Yes, that was my original focus, but everyone I spoke to who knew anything
about them kept giving me reasons why it was all too hard. I quickly
realised that I was going nowhere, so decided to set up exactly the same
data entry system - but on each participant's desktop instead of on an
internet site.

I wish I had known about it before I
retired. I had several projects that could have greatly benefited.

I suppose there's immense potential if data is being collated 24 hrs a day
into one database on the net.

You
also may want to consider using Excel spreadsheets as your intermediate.

Another good point, although I've never used Excel myself.

Far more people have access to Excel than to Access. While I don't think
Excel is a great tool for what you appear to be doing it may prove to be
the tool of chose if you have a problem with users not having Access on
their machines and the Developer's kit is not a option for you.

Once again newsgroups have saved a lot of time and money - with good advice
like this. Thanks.
I suppose if it all gets a bit complicated, a VB stand-alone program would
solve all the problems for everyone involved, even if it is a lot slower
than Access to do queries.

Ray

 

[Joseph Meehan]

Ray Murphy wrote:
....

Once again newsgroups have saved a lot of time and money - with good
advice like this. Thanks.
I suppose if it all gets a bit complicated, a VB stand-alone program
would solve all the problems for everyone involved, even if it is a
lot slower than Access to do queries.


Ray

Excel may be a good choice as many people have it and are at least
reasonably comfortable with it. You can set up a data entry system on it
and then easily import the data into Access.

 

[Ray Murphy]

Ray Murphy wrote:
...

Excel may be a good choice as many people have it and are at least
reasonably comfortable with it. You can set up a data entry system on it
and then easily import the data into Access.

In this case there's a big problem with using anything but an easy to use
stand-alone program. Most of the potential users may have Excel on their
PC's, but most wouldn't know how to use it, nor would they try.

The project could only attract the hundreds of participants required if they
were able to simply double-click on a program icon to open it, and they
were then presented with a bunch List boxes for immediate data entry via
clickable selection (600 categories in sub divisions). I doubt if Excel can
be made to work like that from the little I've seen of it, but it works ok
in VB6 and Access - although much better in Access.

The whole thing has just 2 pages for the user.
(1) Master list of subjects (Datasheet view).
(2) File card for each subject (Form with mostly with Listbox selections).

The original idea was for users to simply upload or snail mail those two
tables to another user for merging (with unique ID's on every record that
were relevant to each user).

It sounds like it would work easily if the final Access program was
produced by a licensed Developer.

Does anyone here have any idea what the minimum Developer's Package would be
called for such a straightforward Access project WHERE the user can be given
the free Runtime version of Access that would enable the entry of data?
It seems there are different Developers' kits for different standards of
work.

Ray

 

[Joseph Meehan]

In this case there's a big problem with using anything but an easy to
use stand-alone program. Most of the potential users may have Excel
on their PC's, but most wouldn't know how to use it, nor would they
try.
The project could only attract the hundreds of participants required
if they were able to simply double-click on a program icon to open
it, and they were then presented with a bunch List boxes for
immediate data entry via clickable selection (600 categories in sub
divisions). I doubt if Excel can be made to work like that from the
little I've seen of it, but it works ok in VB6 and Access - although
much better in Access.

While I know Access can do the job, I believe that Excel also has a data
entry capability that would work. Excel has a lot of features that even few
Excel people know about. I am not an Excel expert. Again I am not saying
it will or that it is the best choice. You did indicate that they all might
not have Excel so I would think that would rule it out.

The whole thing has just 2 pages for the user.
(1) Master list of subjects (Datasheet view).
(2) File card for each subject (Form with mostly with Listbox
selections).
The original idea was for users to simply upload or snail mail those
two tables to another user for merging (with unique ID's on every
record that were relevant to each user).

It sounds like it would work easily if the final Access program was
produced by a licensed Developer.

Does anyone here have any idea what the minimum Developer's Package
would be called for such a straightforward Access project WHERE the
user can be given the free Runtime version of Access that would
enable the entry of data? It seems there are different Developers' kits
for different standards
of work.

If you have not already checked these out, here is a reference

http://msdn.microsoft.com/office/understanding/access/default.aspx


As I understand it there is only one Developers kit or edition, but the
name has changes and there may be old and current versions. I could be
wrong. There are also add on's that include tools for development and
distribution. Each user will need to have the runtime installed on their
machine. This could be a problem for some users who may be using a company
machine and they may not have the authority to install programs.

You may want to review prior threads about developer or start a new one
for this question.