Newbie, designing an idea. Can records be sent from one location to another vie email?

[Mike Barnard]

Hi.

I am looking to see if access will help with a companies data
collection problem. I will be teaching myself how to use it, but I'm
not totally computer illiterate and I have books and the web. The data
is simple. I have just started working for the company and have no
history with them.

Site A is vehicle(s) that do offsite work for the company. They send
worksheets to site B, a local office. Currently site B enters them
into excel worksheets which are then emailed to site C, the main
office where I now work. These then have to be manually copied from
the previous one to the latest one to tally up the figures and are in
a dreadful state to understand.

The data collected is of the format name, date, type, location and
other stuff that sounds far more like database data than excel data to
me. The only real calculating is working out how many days it took to
do the jobs.

My idea would be for site B to use access[1] instead of excel, but the
question is that of transferring data from B to C. I don't think I'll
get permission to have the sites connected by the net. They have no
other possible connectivity.

Can a series of access records be, ideally at the press of an
operators button, copied from the database at site B, zipped and
emailed to site C? What needs to be done at site C to include them in
the database there?

Any websites or insight on this aspect of the potential job would be
apprecieated. Thanks.

Mike.




[1]2003. It's already on the systems so is convenient to use. Would
something else be better?

 

[bhicks11 via AccessMonster.com]

Hi Mike,

You can easily import/export tables to and from spreadsheets in Access. You
can also LINK the spreadsheet. So, from the FILE menu, select IMPORT, LINK
and go select the spreadsheet. Then if they send a file daily of the same
name, save it over yesterdays file and it will now be the data you see in
Access. I would run a little batch file to backup yesterdays file to a
dataed file name in case you need it for referral.

You can then run a query to append or whatever the data as you need. I do
this kind of thing all the time. By the way - I use ftp and have even
included a button in my Access form that sends or gets the file via ftp.

Bonnie

http://www.dataplus-svc.com

Hi.

I am looking to see if access will help with a companies data
collection problem. I will be teaching myself how to use it, but I'm
not totally computer illiterate and I have books and the web. The data
is simple. I have just started working for the company and have no
history with them.

Site A is vehicle(s) that do offsite work for the company. They send
worksheets to site B, a local office. Currently site B enters them
into excel worksheets which are then emailed to site C, the main
office where I now work. These then have to be manually copied from
the previous one to the latest one to tally up the figures and are in
a dreadful state to understand.

The data collected is of the format name, date, type, location and
other stuff that sounds far more like database data than excel data to
me. The only real calculating is working out how many days it took to
do the jobs.

My idea would be for site B to use access[1] instead of excel, but the
question is that of transferring data from B to C. I don't think I'll
get permission to have the sites connected by the net. They have no
other possible connectivity.

Can a series of access records be, ideally at the press of an
operators button, copied from the database at site B, zipped and
emailed to site C? What needs to be done at site C to include them in
the database there?

Any websites or insight on this aspect of the potential job would be
apprecieated. Thanks.

Mike.

[1]2003. It's already on the systems so is convenient to use. Would
something else be better?

 

[Mike Barnard]

Hi Mike,

Hi, thanks for the heads up. It's good to know that it will work.

You can easily import/export tables to and from spreadsheets in Access.

OK; over time the db / tables get bigger. If site B sends the entire
db every time it will get into huge filesizes. I assume. So can the
database only send records that have not previously been sent? I'e.
the records for a weeks work not the entire year for the umpteenth
time then at site C have them added to the existing db?

Again, hints on commands so I can look em up or sites?

You
can also LINK the spreadsheet. So, from the FILE menu, select IMPORT, LINK
and go select the spreadsheet. Then if they send a file daily of the same
name, save it over yesterdays file and it will now be the data you see in
Access.

Again, parts or the whole?

I would run a little batch file to backup yesterdays file to a
dataed file name in case you need it for referral.

Backup or die!

You can then run a query to append or whatever the data as you need. I do
this kind of thing all the time. By the way - I use ftp and have even
included a button in my Access form that sends or gets the file via ftp.

As I said, it's new to me! FTP sounds OK, but email is simpler for the
less techy at site B.

Thanks very much.

Mike.

Bonnie

http://www.dataplus-svc.com

Hi.

I am looking to see if access will help with a companies data
collection problem. I will be teaching myself how to use it, but I'm
not totally computer illiterate and I have books and the web. The data
is simple. I have just started working for the company and have no
history with them.

Site A is vehicle(s) that do offsite work for the company. They send
worksheets to site B, a local office. Currently site B enters them
into excel worksheets which are then emailed to site C, the main
office where I now work. These then have to be manually copied from
the previous one to the latest one to tally up the figures and are in
a dreadful state to understand.

The data collected is of the format name, date, type, location and
other stuff that sounds far more like database data than excel data to
me. The only real calculating is working out how many days it took to
do the jobs.

My idea would be for site B to use access[1] instead of excel, but the
question is that of transferring data from B to C. I don't think I'll
get permission to have the sites connected by the net. They have no
other possible connectivity.

Can a series of access records be, ideally at the press of an
operators button, copied from the database at site B, zipped and
emailed to site C? What needs to be done at site C to include them in
the database there?

Any websites or insight on this aspect of the potential job would be
apprecieated. Thanks.

Mike.

[1]2003. It's already on the systems so is convenient to use. Would
something else be better?

 

[bhicks11 via AccessMonster.com]

HI Mike,

1. There's a few ways you could do that. I assume you are appending every
day or weeks data to a master table at your site. From that Master table you
could make a parameter query that asks for the beginning date so that the
only data that you get back from the query and send to C is the new data.
You could save the through date to a seperate one field, one record table
each week and use it as your parameter for the query (I would do this all
programmatically but start out manually and as you progress improve your
process). Or - maybe the through date is NOW() and you don't have to
interact with it, just make the criteria for the date = NOW(). I'm not sure
of your process exactly but this is some ideas. (For either the linked or
imported data.)

You can add ftp later. It can be done with a DOS batch file that you run
from Access. The advantage is that it doesn't take as much human
intervention and will save time and be more efficient.

Bonnie

http://www.dataplus-svc.com

Hi Mike,

Hi, thanks for the heads up. It's good to know that it will work.

You can easily import/export tables to and from spreadsheets in Access.

OK; over time the db / tables get bigger. If site B sends the entire
db every time it will get into huge filesizes. I assume. So can the
database only send records that have not previously been sent? I'e.
the records for a weeks work not the entire year for the umpteenth
time then at site C have them added to the existing db?

Again, hints on commands so I can look em up or sites?

You
can also LINK the spreadsheet. So, from the FILE menu, select IMPORT, LINK
and go select the spreadsheet. Then if they send a file daily of the same
name, save it over yesterdays file and it will now be the data you see in
Access.

Again, parts or the whole?

I would run a little batch file to backup yesterdays file to a
dataed file name in case you need it for referral.

Backup or die!

You can then run a query to append or whatever the data as you need. I do
this kind of thing all the time. By the way - I use ftp and have even
included a button in my Access form that sends or gets the file via ftp.

As I said, it's new to me! FTP sounds OK, but email is simpler for the
less techy at site B.

Thanks very much.

Mike.

Bonnie

[quoted text clipped - 37 lines]

[1]2003. It's already on the systems so is convenient to use. Would
something else be better?

 

[Mike Barnard]

HI Mike,

Thanks for corresponding on this. Do you mind if I keep picking at
you on this? As I said, I'm new to databases and books are my main
source of teaching.

1. There's a few ways you could do that. I assume you are appending every
day or weeks data to a master table at your site.

Yes, that is exactly what I intend. Although no proper database exists
yet. It's an idea in progress, and this thread on here *is* the
progress! (That and a few unlinked experimental tables on my PC.)

From that Master table you
could make a parameter query that asks for the beginning date so that the
only data that you get back from the query and send to C is the new data.

I understand that, in principle. Thanks. This being done at B. I was
going to have a yes/no field in the main job table called
"SentToEmployer". To be marked yes by access when the "send latest
records to employer" button gets pressed. Good / bad idea?

The most important thing I need to know at the moment is the mechanism
for the user at B to seperate a series of records from their original
db and package it in such a way that it can be transported, outside of
a database enviroment[1], to myself at C where it has to be
re-incorporated. (Now that was a long sentence!) So I need to look up
the methods of copying off and then appending this independant packet
of records. Can you suggest some commands that I can research please?

[1]Such as email, FTP, USB stick, CD, floppy disk etc.

You could save the through date to a seperate one field, one record table
each week and use it as your parameter for the query (I would do this all
programmatically but start out manually and as you progress improve your
process). Or - maybe the through date is NOW() and you don't have to
interact with it, just make the criteria for the date = NOW(). I'm not sure
of your process exactly but this is some ideas. (For either the linked or
imported data.)

Thanks for them.

You can add ftp later.

It is unlikely that would be any more convenient than email but
thanks.

It can be done with a DOS batch file that you run
from Access.

Ah, the heady days of DOS! Thank the lord I kept my DOS 6.2 instant
reference on the shelf. (fx, blows off dust.)

The advantage is that it doesn't take as much human
intervention and will save time and be more efficient.

Anything is more efficient than the current one. Thanks.

Mike.

http://www.dataplus-svc.com

Aha, an expert. As an aside, how much (VERY approximately $1k?
$.5k? $3k?) would it cost to have something like this developed
professionally? (I'm in the UK by the way.)

 

[bhicks11 via AccessMonster.com]

Hi Mike,

Responses below:

Thanks for corresponding on this. Do you mind if I keep picking at
you on this? As I said, I'm new to databases and books are my main
source of teaching.


Yes, that is exactly what I intend. Although no proper database exists
yet. It's an idea in progress, and this thread on here *is* the
progress! (That and a few unlinked experimental tables on my PC.)


I understand that, in principle. Thanks. This being done at B. I was
going to have a yes/no field in the main job table called
"SentToEmployer". To be marked yes by access when the "send latest
records to employer" button gets pressed. Good / bad idea?

Sure, you could update that field whenever you send the data.

The most important thing I need to know at the moment is the mechanism
for the user at B to seperate a series of records from their original
db and package it in such a way that it can be transported, outside of
a database enviroment[1], to myself at C where it has to be
re-incorporated. (Now that was a long sentence!) So I need to look up
the methods of copying off and then appending this independant packet
of records. Can you suggest some commands that I can research please?

ANSWER

I think exporting to a csv or whatever and importing at the other site. I
would hit a button that runs some VBA code that:

Runs the query that filters the data you need to send
Runs TransferText to export the data filtered in the query
Runs a batch file that FTP's that data to whatever ftp site you use.
(Probably included in your ISP service but you may need to upgrade for
security)
Runs the query that tags the records sent (SentToEmployer)

And you can include whatever data validation procedures you like during this
process. So you can see, once you have the processes in place - your daily
work on this file involves hitting a button. If you need some consulting
help, go to my site and get my direct email and drop me a note:

http://www.dataplus-svc.com

Bonnie

[1]Such as email, FTP, USB stick, CD, floppy disk etc.

You could save the through date to a seperate one field, one record table
each week and use it as your parameter for the query (I would do this all

[quoted text clipped - 3 lines]

of your process exactly but this is some ideas. (For either the linked or
imported data.)

Thanks for them.

You can add ftp later.

It is unlikely that would be any more convenient than email but
thanks.

It can be done with a DOS batch file that you run
from Access.

Ah, the heady days of DOS! Thank the lord I kept my DOS 6.2 instant
reference on the shelf. (fx, blows off dust.)

The advantage is that it doesn't take as much human
intervention and will save time and be more efficient.

Anything is more efficient than the current one. Thanks.

Mike.

http://www.dataplus-svc.com

Aha, an expert. As an aside, how much (VERY approximately $1k?
$.5k? $3k?) would it cost to have something like this developed
professionally? (I'm in the UK by the way.)

 

[Mike Barnard]

Hi Mike,


work on this file involves hitting a button. If you need some consulting
help, go to my site and get my direct email and drop me a note:

http://www.dataplus-svc.com

Bonnie

Hi again.

Just a quick not to say the company are receptive, but it will take a
long time before I have enough detail to work out a complete scheme.
Months probably, I have so much more to do.

I'm going to start reading as much as possible about Access and get
the basics in solid before I go on to this.

Thanks for that offer, I am very likely to take you up on it in the
future. But don't let me become a chain around your neck!

 

[bhicks11 via AccessMonster.com]

Hope it works out well for you.

Bonnie
http://www.dataplus-svc.com

Hi Mike,

[quoted text clipped - 4 lines]

Bonnie

Hi again.

Just a quick not to say the company are receptive, but it will take a
long time before I have enough detail to work out a complete scheme.
Months probably, I have so much more to do.

I'm going to start reading as much as possible about Access and get
the basics in solid before I go on to this.

Thanks for that offer, I am very likely to take you up on it in the
future. But don't let me become a chain around your neck!