Can MS Access do this?

[leftpeg]

Hi there,

I am new to MS Access and want to know is it the right software to do what i want it to do (see below).

We have five engineers who are constantly on the road and visiting properties for routine safety checks. We want to be able to database these checks and be able to have a databse that:

has all the properties that need to be checked
which then has the following sections for each address/property

appointment date arranaged (yes/no)
appointment date (date)
checked (yes/no)
date checked (date)
checked by who (engineer name)

each engineer has to have his own section that calculates the number of properties they have completed per week, which needs to auto completed by the software when each property status (above) has been completed. we will then use the engineer number of properties visited to calculate their wages etc.

also if possible, could the system allow engineers to use palm pc/pilots that they can use to enter property details 9i.e. date visited/customer signature etc.) that then auotmatically completes the database in the main office. alos it would be benficial if the engineers could also view properties that still require work on the pocket pc's.

is this possible in Access or are we looking at some sort of third party software.

any help will be greatly appreciated.

reagrds,

Mark

 

[The_Doc_Man]

Here is the problem...

Access normally will not do this from a palm pilot because of a little "gotcha" in the license and design of the Access interface.

Access is a LOCAL database or at most a LOCAL interface to a shared database. The LOCAL part is that whatever you are using to talk to the database file must be able to run Access. A palm pilot or really small palm-pc is a questionable possibility at best. I don't know that I've seen a hand-held that could run Access effectively.

You might search this forum for articles where someone tried to make a centralized server support multiple users via MS Terminal Services. I.e. CITRIX or some other type of remote-connect service. However, I believe that doing so is a technical violation of the "off the shelf" end-user license agreement. You would have to go to MS Sales or to an authorized reseller to get a license that allows multiple users on a single copy of Access. Not only that, I recall seeing a lot of articles about how MESSY it was to try to do that.

OK, now that I've rained on your parade...

If you were willing to consider a true laptop sized system with a wireless network plug-in card, THAT might work. The issue is the ability to run Access on the local system. A laptop can do so. A palm-sized machine is questionable.

As to the idea of having a database in a central location doing the things you want to do with field updates via wireless connections, that part most certainly IS within the range of Access abilities. My quibble was and is with the size of the "field unit" doing the updates and queries.

 

[leftpeg]

is there any other software that may have this capability?

are we looking at thir party software?

i see gas companies etc when checking meters using palm pc's, so this must be possible. perhaps this is software that has been written for them specifically?

 

[Banana]

One possible solution to implement is to write a custom application for palm os that can import/export to Access using CVS or something like that.

 

[leftpeg]

banana,

thats what i was thinking. for a complete database novice, but with some time on my hands before we have to get this thing live.....is it achievable to set up the access database and program the pilots?

i am able to learn software pretty quick......but is this just asking too much?

thanks,

mark

 

[Banana]

Not sure if I understood you.

If you asked if you can easily set up an Access quickly, the answer is a resounding yes.

Programming the pilots, I'm afraid I don't know. This will depend on how good their SDK and what IDEs supports programming for Pilot, and whether they're free (most likely not.).

Putting Access on pilots? Quite doubtful. At least a quick solution would be to just use Excel spreadsheet, which I'm pretty sure pilot can support (out of box, perhaps?) with some formatting and protection (if supported in pilot) so it behaves like a form but has data in fairly readable format and can be readily import/exported into/from Access.

 

[NigelShaw]

Hi,

as a thought ( probably not a good one though but i'll give it anyway )

if the central database was located on a website, then couldnt you write to it via .asp?

i have a database that uses this to get information from my website. i am using a local database to call the information from the web file through .asp.

i can if need be, update information to the website .mdb from my local copy in the office ( web site is on server in the office ) for retrieval externally via a web call through the .asp page.

Now, in all honesty, i never wrote this as i dont know .asp but it works by an ActiveX placed on a form on the local database. when i need the updated data, the ActiveX gets the data for me and places it on my form.

in regard to the Palm, this could then be a simple web interface to get or view the data. appending i am not sure about with a palm but you could append it through a web interface too.


Nigel

 

[leftpeg]

thanks nigel,

i presume then that this was something that was set up for you? if so who did it and the cost implication?

my only other option is that the engineers complete paper forms upon each visit and then they are inputted onto the central database. time consuming i know, especially considering we have 6,000 properties visit this year, with a further 30,000 if we complete the initial ones effectively.

perhaps this is something i should be getting an experienced database/software company to come in and setup. any thoughts?

thanks,

mark

 

[NigelShaw]

Hi Leftie!

the ActiveX i use was around 500$ ( £300-£350 ). there are various people who can do this but why not have a go yourself as you have mentioned that you can learn programming fast.

http://www.asp.net/ is a good place to start and they have examples & samples that can be manipulated. also, take a look at Visual studio as this creates .asp but learning isnt as easy as a quick book. my thought would be-
get a sample
change it to your needs
host the site
create pages to add data
give the web pages to your field engineers

My Brother is an engineer in the same industry if you are in gas & heating and i wrote him a specific database a little while ago that he updated. the db tracked

Engineers
Recalls
job numbers
Customers
Servicing
who revisited where
who never completed their jobs
trend analysis

is this what you do or need?


NS

 

[leftpeg]

thanks for that nigel.

i will take a look at it tomorrow and let you know how we go on.

to give a little more detail:

we are sub contracted by the UK owner of electrical meters cablings going into a dwelling. a previous engineer had incorrectly copnnected all the meters in a certain town (i wont name for obvious reasons) we have the task now of visiting those properties and carrying out full safety checks on the connections. a very quick and easy job might i add.

the company will send us a list, hopefully excel file, which have 6000 properties that we need to visit.

now my database needs to:

list the properties that need safety checks

be able to see ones that have been checked and have not

when an engineer visits a property i want him to be able to tick that off in the field.

that then automatically completes the database and fills in the follwing information for each dwelling....completed (yes or no) date completed, completed by which engineer, additional notes

i also then need to be able to access the number of dwellings each engineer has done in a certain time period i.e. per week and then they will be paid on the number of dwellings they have done.

is this possible? dont really want to the paper work route. also it owuld be an advantage having an online database as the engineer if they have completed all the schedule work could look at the database and find properties in the area that have not already been done, and then knock on the doors.

 

[NigelShaw]

Hi,

Access is definitely the man for the job.

you can import the excel file into a db ( unless you can be given the data in a .mdb or 2007 version if you have it )

you can easily add a field "Completed" as a yes / no option
you would query the table to show only entries that have a "False" value
you can query between dates ( set up input boxes too ).

so really, all your engineer needs to update is

Select a job ( customer ) number
add a date
tick a yes/no box unless there are unfinished items in which case, add another field and query by this this to give you the outstanding jobs

and thats it?

your db would be quite easy to set up.

Nigel

 

[NigelShaw]

is this possible? dont really want to the paper work route. also it would be an advantage having an online database as the engineer if they have completed all the schedule work could look at the database and find properties in the area that have not already been done, and then knock on the doors.

Just a thought from a Managers point of view. why would you give the engineers the choice to look up surrounding properties? wouldnt this be better practice to give them a list of surrounding areas instead?

i suppose another route could be to have the engineers keep a local copy of their areas in a database that could be imported each week? obviously, the same address couldnt be given to more than one engineer as you could have a tick field name " appointed " thus leaving it out of the available addresses.

i wouldn't give my staff the choice to dynamically select similar work as almost everytime, it wouldnt be done. IMHO but thats just me.


regs,


Nigel

 

[leftpeg]

and the engineers can do that in the field?

 

[leftpeg]

they are being paid on price, so the insentive is to do as many as they can. so i think that may resolve the issue you raised above

 

[Mike375]

Here and Australian website for Palm computers.

http://www.palm.com/au/products/handhelds/tx/officedocs.html

If you scroll down it would appear that you can do entries etc for Excel etc and put back on the computer when you get back.