could this be done with MsAccess

[squarebanana]

I could do with some help in implementing this

I just wondered whether this could be done.

At the moment in my place of business we have 3 job boards on the wall to control workflow process, I want to try and simplify it and get it onto pc’s.

When jobs come in they are initially assessed and inputted manually onto a jobsheet. it is at this time that we detrermine which jobs need artwork doing or are ready to print, and they are placed on a board on the wall on a succession of hooks in different coloured panels, these being AN,AD,AA,PP and FF, these being:
Artwork Needed, Artwork Done, Artwork Approved, Print Production and Final Finishing.

this system has been in since last June when I thought of it and implemented it, and we have seen a big increase in our productivity and not letting customers down through lost jobs etc.

Now what I want to know could it be done either by database, speadsheet, sharepoint etc I don’t know as I ain’t that clued up on this sort of thing. I can learn and do learn quickly but sometimes you cant see the wood for the trees so to speak so I would like a few pointers as to setting this up.

the hardest part I can see is the reporting. we would need it to be so that anyone on our network can
see where any job is up to at any one time, and also that jobs will need to be “signed on” and “signed off” when each stage has been completed.

Reporting screen,

this I am at a loss with we allocate 5 to 7 working days for a product to be ready for the customer, so I said to the boss last night wouldn’t it be good if we had a reporting screen where every job appeared on a screen, slowly proceeding to the bottom by date, and changing colour dependant on where it was up to in the system so by due dates all jobs would be green for example and ones that werent ready could be flagged up as needing to be completed.

any help would be appreciated on this. there is a pdf file on our site at expressbanners.co.uk/images/jobs_db.pdf if you want to have a further look if you can offer help with it
Thanks

Alan

 

[pbaldy]

Offhand I don't see anything that couldn't be done in Access. The different boards can be done with a status field. Forms can be restricted to only display a chosen status. Sorting a form or report on date fields is simple, and there are multiple methods to change the format of a record based on date, status or whatever.

 

[NigelShaw]

Hi

There is an application built for print production that works just as you describe called Clarity. www.touchsystems.co.uk

Cheers

Nidge

 

[squarebanana]

Hi Nigel, yes i have seen clarity, and tried their trial, i am after something similar, but i would like to try and build it in access, i have sorted out a basic design using a data schema from database answers.com so have got the tables and fields in with the basic relationships, its just the form building and reporting i think i am going to have problems with.

but thanks again for taking the time to reply.

Alan

 

[sparks80]

Hi Alan,

Firstly I think the URL in your post is incorrect - I think you mean expressbanners.ltd.uk not expressbanners.co.uk!

Having found it, the pdf explains very well what you are trying to achieve.

I have an additional question - on your form or report is it important to show all jobs at once? Or would it be OK to select a particular status, say "Artwork needed" using a drop down box, and display the records matching that status. The latter approach is probably much easier to accomplish.

Mark

 

[NigelShaw]

Hi Alan

Would you post the link of the schema or a sample db to date? I'd be interested in co development. I too have started to make something similar to Clarity and want to fill the gaps that it lacks. I have already deloped a grid view of job lists and production scheduling which I have now recently completed a method of printing the visual grid ( grids do no not print )

You can pm me if you wish

Our Companies look like they manage similar things. I work for a large number 2 in uk design agency with a large print dept. I personally work on bespoke production of joinery/shopfitting but an app can be modified or generically similar.

Cheers

Nidge

 

[squarebanana]

Hi Mark,
yes as regards the reporting the way we do it now on paper, allows us to have a small clipboard with upto date lists of jobs going in, and date they were taken in, and a date for despatch. Each morning we have a group meeting and go through the job list in order of date & preference as what can be done easily and what could take longer in the day, the different products that we do from pvc banners (easy job) to signs (harder job as may include fitting) so its not cut and dried so to speak, but what the initial aim is to let all the departments know where a particular job is at anyone time so what was suggested in this meeting was a sort of grid system (like Nigel said below) where as the different departments signed on and off each task it would change colour, so if for example i came in on monday morning and someone took 15 jobs on the Saturday for artwork needed and this was say red, i would see the job blocks as red which means without looking at anything else i've got a busy morning sorting out and vectoring stuff like mspaint files so that the rip software can use them. same as one of the lads on final finishing as the print production dept finished the print and it was ready for laminating it would turn green meaning that he would know at a glance what he had on, and he would also be able to see what was in print production by its colour, and if it was going to be mad busy book the following day off LOL only joking!

Alan

And Nigel i'll email you from your site, as i cant find PM facility so i mustn't have enough posts yet.

 

[squarebanana]

oh by the way Mark i cant believe i put.co.uk, ... must have been the holsten effect LOL

for anyone else its expressbanners.ltd.uk//images/jobs_db.pdf

Alan

 

[squarebanana]

Hi Nigel try alan at squarebanana dot com

i just got my mail returned from your site email address

Alan

 

[NigelShaw]

Hi Alan

I'll send you an email tonight. Not sure why you had a return do I'll check that out.

Thanks

Nidge

 

[squarebanana]

Ok Nigel no problem i'll look forward to receiving your email ,i still cant work out why i cant find the PM button.

Ive just been talking to the former owner of another sign company tonight about this, and we came to the same conclusion with Clarity.

How easy is it to use?

well with my limited experience of it.... it isn't.

what does everyone think of this scenario below.

In the database, i only want 1 form for user interaction with the database, the initial "order form" nothing else (link in previous posts).

Then after that there will be just signing on and signing off of 5 processes by various employees, other than that the order should proceed straight through to invoicing.

This then, dependant on the initial order form can be made as complex or as simple as it needs to be, and also making sure that all data needed is included on the form and not left out, should make this easy for all users of the database after all this is why we implement them to make things easier isn't it.

ignore my ramblings just thinking out loud here... but what do you think

Alan

 

[NigelShaw]

Hi Alan

I used clarity for a while. My wife has a vinyl logo business and still uses it. It's quite eay to use and has good modules ie production, reports, invoicing, analysis, job cards etc and some good tools ie gerber edge, vinyl cad cut cost creation tools. It's downsides are cost and speed and limited ability to customise. It can be quite easy to Raise a cost to yourself!

I started making my own as it does lack many things.

You could have more functionality on one form. I quite like 1 form with Changable subforms.

Cheers

Nidge

 

[squarebanana]

I am still emailing you Nidge but getting email return errors
Alan

 

[squarebanana]

Thanks Nidge, don't get me wrong here i am speaking of my own experience with Clarity, the program is probably very good.

Its just that there are so many facets to the print industry that i think one solution wont fit all, same as the solution I am after making wont suit someone else, but i would like to think it would.

Alan

 

[squarebanana]

anyway here is a link to our standard order form, i've been thinking about how to split it up into relevant sections, customers, jobs, accounts, and time period, and as everyone is used to using theses in our business they wont be frightened off when it comes to going on the database.
Alan

 

[squarebanana]

well im up to 12 posts, and i still cant find the pm button
Alan

 

[Lightwave]

Square regards the PM

Have you tried clicking once with the left mouse button on his name and then on the drop down selecting

Send a private message to [name]

 

[squarebanana]

thanks for the reply Lightwave its working now

Alan

 

[sparks80]

Hi Alan,

As it sounds like you are quite new to database development I thought I could give you a few pointers in the right direction - although you should know that I am at the level of enthusiastic amateur! Sorry if any of this is obvious.

As you are going to be using the database on a network drive with multiple users I suggest that you split the database into front and back ends. The "back end" is an Access database that only contains the tables with the data. The "front end" has a link to the tables, and all the queries, forms and reports - what the user will see and interact with. You can find lots of information about this on Google.

When you create the tables make sure to include a unique ID. For example you will need a separate table for the customer details, so include a field called "CustomerID" or something like that and set the type to "Autonumber".
I presume you will have a separate table for order details, and you would include the customerID in this table as well, and then use a query to link the tables together. I would be inclined to keep this ID even if you store a separate reference number for each job. The reason being when the database has lots of records it is much faster to link the table together with a numerical index than a text one.

If I'm right you will probably have multiple orders per customer, and multiple jobs per order.

So for example

Table name: tblCustomers
Field, Type, Description (if needed)
CustomerID, Autonumber, Unique ID for each customer
CustomerName, Text
Address1, Text
Address2, Text
Town, Text
TelephoneNumber, Text
EmailAddress, Text

Table: tblOrders
Field, Type, Description
OrderID, Autonumber, Unique ID for each order
Customer ID, Number (long integer), ID corresponding to CustomerID in tblCustomers
ReceivedFrom, Text
ReceivedDate, Date/Time
PaymentType, Text, "Acc" or "On Del"
AccountNo, Number (long integer), may include this in Customer table?
Carrier, Text

Table Name: tblJobs
Field, Type, Description
JobID, Autonumber, unique ID for each job
OrderID, Number (long integer), ID corresponding to relevant OrderID in order table
JobType: Text
...
Comments, Memo, comments may be longer than 255 characters, which is the limit of the Text data type
StatusID, Number (long integer)

Table Name: tblStatus
Field, Type, Description
StatusID, Autonumber, uniqueID for each job status
StatusDescription, Text, AN, AD etc

 

[squarebanana]

Many thanks for your input on this Sparks' i have had a look at the customers and jobs data schema on database answers, and a lot of what you wrote is referenced a bit like that.
At work part of my job which started as a hobby is looking after the IT side of things we have 3 or 4 windows 7 pc's and 3 or 4 xp machines and a fileserver based on linux, i have at home a second hand dell pc i bought with windows server on and a couple of other windows 7 pcs, i could use them as a test set up to give it a try.
So basically what is the hardest thing to set up the queries and reporting side of things?

Alan