excel experts

[Big Jim]

Currently our shop tracking system is one that I created using excel/vba.

I am not brilliant with vba, however what I am good at is pinching bits of code off the net and also using the record macro feature to get the system to do what I want.
It is far from perfect but it is fully customisable and I know it's limitations so I work around them.

One limitation currently is that it isn't *really* storing customer information.
I do have one part of the booking in macro that records the customers details and adds a new line to another spreadsheet.

It will record a new line regardless of whether or not the customer has been in before, which leads neatly into the next problem, I don't know how I can get the booking in sheet to call up the customer details from the database but I am pretty sure it can be done. So at the minute even if the customer has been in before we are having to retype and go over the same details that they have already given us.
I don't think this looks very good from a customer perspective, I expect people to remember me when I go somewhere so I am sure my customers expect the same.

I would also like to be able to (once the customer has left) click the new job sheet and open up a "customer file" that would have information on previous jobs and their hardware. (I could get excel to create a new spreadsheet for that customer and then update that file rather than create a new one if they come in again.
and also be able to add new hardware to their file if possible
Are these ideas within the realms of excels capability ?


currently the system works as such
it uses 6 seperate spread sheets.
1 - booking in sheet: details are input into this a work order for the customer to sign is printed off and the details transferred to 2 places (job tracking sheet & job tracker)
it saves the job tracking sheet as whatever the booking in number is and creates a link to it in the job tracker
2 - job tracking sheet: all infor from sheet 1 copied here with spaces to record parts information tech notes and other bits and pieces, the job is invoiced and closed off from this sheet
3 - job tracker: an overview of all jobs, also links to all job tracking sheets at the click of a mouse
4 - invoice tracker - just a list of all invoices made out cost of parts and total value of job
5 - invoice sheet, click a button on the job tracking sheet and it produces an electronic as well as paper copy of the invoice/receipt
6 - database - all customer details transferred to this during the booking in process as a line on the spreadsheet

 

[Boston Pro Comp Serv LLC]

It's all doable thru vba, but it looks like the solution has grown beyond excel. You'd benefit from moving the solution into access, esp if you're decent with vba. We use a small access program for customer info, drop off sheets etc.

 

[Big Jim]

I am aware that access handles databases better.
However I am almost completely useless with access, until a year ago I had never even used it. And even now all I do is run a query that someone else setup.

I am handy with excel, I can usually work my way around the VBA (even with pretty much zero programming knowledge) would trying to learn access be very time consuming ?
I am not very time rich at present,
Could I combine what I am using to be able to integrate access somehow ?
Could I get access just to handle the customer database ?

would it be possible to just "move" the spreadsheets over to access.

I areally am an access noob

 

[732914TECH]

I understand you would like to use your own abilities, but wouldnt ShopManager or something similiar be easier for you?

 

[strollin]

The reason you are struggling with this in Excel is because Excel is essentially a flat-file DB while what you need is a relational DB. That's why Access would be worth your time to learn.

Relational DBs give you the ability to "relate" a record in your "customer" table, with the data in your "job tracking" table so that your customer info is only listed once, in the "customer" table.

You can export your data from Excel to be imported to Access but it will need massaging before being imported.

A packaged solution such as ShopManager suggested by 732914tech would also work for you and I'm willing to bet you could export/import your existing Excel data even though it will require a little massaging as well.

 

[Big Jim]

hmmm,

I would be willing to try and learn access if I can manipulate it to do what I want.
As I said though I am a complete access noob, I have been using excel now for about 12 years but access only for the last year and really only using it to click a button to run a query on someone elses database so I don't actually understand what is happening in the background.

Is there a good resource I can use to start to get an understanding of it ?

I really wish I had sunk the time I have spent creating this "booking in system" into learning linux instead so that I could have used PCRT.

Oh well I am where I am.

 

[Kitten Kong]

Is there any reason as to why you cannot use PCRT Jim?

 

[Big Jim]

I needed a tracking system when I first setup and it was recomended to not run it on a windows box.
So I started to look at Linux and got very scared very quickly lol.

I don't know anything about linux and when I installed it and just got a flashing white cursor on a black screen I thought I would use the power of google and try and learn.
I very quickly got in way over what I had time to do (or at least what I thought I had time to do) So I started to make my own repair tracker using excel, which I have and it works. But just like the guys who made PCRT are constantly improving PCRT, I am constantly improving my tracker also.

So I was just trying to expand it a bit more and get it to database customers properly.

 

[Kitten Kong]

Cheers buddy. When I first started, I had a spread sheet, nowhere near as complicated as yours, but very quickly outgrew it, and looked into trackers.

I started off with TSMan, which although was good, did not give me the chance to use it onsite. I transferred over to PCRT, and have not looked back. Does everything and more I want it too.

I'm scared of linux too. I once wanted to create a file server, got in way over my head, and dropped the project like a bomb, very quickly. Would like to get the file server back up and running one day. I still have all the paperwork to hand, to build it up, just finding time inbetween the other projects i'm juggling at the moment.

 

[Big Jim]

Are you running PCRT on windows then ?
What kind of setup does it require ? (as detailed as you can be)

 

[Kitten Kong]

PCRT is web based (as im sure you know). I just ensured that my hosts had a linux platform, and that was it.

It was Martyn who set it up for me. I would have a word with him, and see if he can offer any suggestions for you. Or even Luke the author on pcrt. Check on the pcrt forum.

 

[Big Jim]

Is it ?

I was under the impression that you needed to run it on your own server ?

My only issue then (as I am sure my webhost runs on linux) is not having a good understanding of how the whole thing works, so if it went wrong (ie file corruption etc) I wouldn't know where to start to recover it.

I did contact Luke when I was considering it and his reply was a little short for me.
I needed a more in depth response which I got the feeling he didn't want to spend time giving.

 

[Kitten Kong]

Hmm that's unusual for Luke. He normally responds fully.

Re file corruption etc, there is a option to back all your data up, which I do each night.

Best thing, have a word with Martyn. I'm sure he can help point you in the right direction.

 

[Martyn]

Yes you just need a hosting site for it and you're away. Test out the demo and go buy it. Luke adds new features periodically and it has a forum where you can place requests and get support. I love it and use it out on the road with my Ipad.