In Progress

Retail Picture Framer - complete database with online access

First I will explain what we have been using so far for the past few months - then I will develop on what we need for the future. I think an Access DB is required but am open to other suggestions.

We have been using some online spreadsheets on google apps to allow a number of our employee users (at different workstations throughout 2 buildings) to input and share data in our picture framing business .Up to now , we had a small retail shop and a separate workshop at another location - but we are now uniting these elements into a single building next month.

Customers bring in some type of artwork (eg photo / canvas / object) that they need framed - here we assigned a job number, recorded their contact details, salesperson, date of receipt, due date of completion, work scope required, price, type of picture moulding and type of mount to be used, notes etc , id of physical folder in which artwork will be stored etc and assigned that job a status - 1.

* Separately a form on A4 sheet was filled with similar info and this sheet accompanied the artwork(identified by job no ).

* Separately also we used a costing spreadsheet template locally to work out the proposed sell price of each job - this allowed entry of the artwork size / desired border size/ number of mounts (as well as options such as spacers / fillets / designs etc)- price of mount & glass & backboard / msq , price of moulding/m , fittings and other processes along with multipliers on each - to calculate the material cost and sell price and % margin etc. The resulting "sell Price" is what would be agreed with the customer for the job.

Next that job (folder containing artwork and completed form) was delivered to the workshop and on arrival was assigned status - 2(by changing cell on Google spreadsheet) .At this point the A4 form was removed from the folder and put in "jobs to do" bin. Then when a workshop person took this A4 form to commence work they changed status to - 3. The A4 form (completed manually) contained the dimensions allowing the moulding / mount / glass etc to be cut , the artwork was removed from folder by another person at other workstation and the job was assembled and status then changed to - 4 (on separate PC). The completed job + form attached then moved to quality check area where a quality check was done - once passed status changed to 5. At final workstation (and separate PC), the completed frame was sealed and hanging hardware and stickers etc attached, and protective wrapping was attached - status now changed to 6. At shipping out of workshop at end of each day each job being shipped (still with completed form attached the status changed to 7. On receipt back in shop, jobs were checked in and given status 8. Customers were then contacted and status changed to 9. Jobs were picked up and status finally changed to [url removed, login to view] covers the job from receipt from customer to giving completed item back to the customer. By manipulation of the spreadsheet with pivot tabled etc, i am able (remotely) to see progress of all work and measure value of work taken in / daily workshop output / value of completed work to be picked up by customers etc.

Moving forward, we will have all operations run from the same building so have the option of running a database to cover the above along with other valuable info on a server but I will still need to be able to get reports remotely and have a solid back up system. This database must allow simultaneous access by multiple users continually entering and editing data.

We have 6-8 suppliers who supply us with moulding (long lengths of wood material from which the picture frame is made) We stock about 200 mouldings at present but expect this to increase towards 400/[url removed, login to view] is a sample of each moulding ( identified by code) in the shop for the salesperson to work with customers We can get some identical mouldings from more than one supplier but each supplier has their own coding. We also get some very similar mouldings from a number of suppliers allowing us to substitute depending on availability / price etc Currently we keep a spreadsheet record of all mouldings received from each supplier so that we can see what quantity / price of each moulding we purchased each month. Typically we place orders from each twice each month.

Separately we try to keep spreadsheets of the actual quantity (meters) of each moulding we have on hand This is difficult to maintain live so is mostly updated prior to placing orders.

We have a similar system for mountboard sheets. We carry about 60 color types - each of which is identified by its code.

Ordering in supplies is laborious and sometimes hit and miss. Firstly we have to physically check the completed A4 job forms to see what mouldings / mountboard we need for jobs in - then we cross check this with the quantities we have in stock to decide what quantity we have to order. We must also check what other moulding we normally buy from this supplier (what quantity we have bought per month recently and what is on hand) to add it to the order also. We then send a PO email to the supplier. Most often they will be unable to fulfill the complete order and some items will go on back order - this is important if goods have still not been received when placing the next order with the same supplier.

Moving forward, we need to incorporate all of the above into one integrated database where we make best use of our data. When we are costing a job , the dimensions and material types are entered - If the customer is proceeding with the work - a job sheet complete with Job ID is saved as file and both a detailed job sheet for workshop and a simplified copy for customer are printed out. These dimensions can determine automatically what total quantity of each material is required for jobs of status 1 & 2.(Workshop personnel will only commence a job knowing that materials are available to them immediately) Once a stock take of material on hand is updated – we could have available a report for each moulding / mountboard type the quantity on hand and the quantity needed to fulfil customer orders and the quantity of each bought in for each of past months – so we can generate better material orders.

To facilitate efficient workflow and ensure due dates are met, we need to be able to see / print out reports or priority of jobs by due date

When each person needs to update the status of a job they need to be able to do a quick look up by job number and update status ( we may need to consider that this could be done by barcode - so that as a job (and accompanying worksheet with barcode) pass from a workstation, the barcode is simply swiped – each installed barcode reader would only change job status to a pre assigned status level for the particular barcode / job Id being scanned.

When jobs move to status 8 – they need also to be assigned a storage location of 1-100 which represents the physical storage shelf on which the completed work is placed until the customer will collect.

When a customer brings in more than 1 job at the same time, these jobs need to be additionally identified as 1/x, 2/x …..x/x/ etc so that they are not notified until all of their jobs reach status 8.

When a customer brings in many identical jobs they may be considered as 1 job (but multiple quantities of materials will of course apply)

To facilitate production, we need to see work lists for the moulding cutting machine – identifying job no’s of identical mouldings – showing all the lengths that need to be cut of each moulding type. Similarly for the cutting of identical mountboard.

To facilitate identification of each moulding, a photo of each moulding can be taken and incorporated, so that it can be seen on screen wherever it is referenced. – including 4 vital dimensions A,B,C,D.

When new mouldings are introduced, they need to be easily added – supplier/ code / description/ photo / 4 dimensions (as well as what other supplier this identical moulding is available with their code)

I hope this is of adequate detail to describe what we require.

Skills: Microsoft Access, Sharepoint

See more: work retail, workflow one, apps cost develop, well referenced, value options, using google spreadsheet, using google apps, types online jobs, types online business, type online jobs, type jobs online, try code online, took jobs, suppliers online, stock take jobs, stock building supply, spreadsheets jobs, small online jobs, small business server online, sheets jobs, sheets add, sharepoint online jobs, sharepoint online cost, sharepoint jobs online

About the Employer:
( 1 review ) Carlow, Ireland

Project ID: #579867

10 freelancers are bidding on average $524 for this job

DBguy

Good Morning. Please check your PMB. Thanks for the consideration. Best Regards.

$300 USD in 7 days
(14 Reviews)
4.2
ivancp

Hello, Please read the PM

$700 USD in 30 days
(6 Reviews)
3.7
BSoftTech

I can provide the solution with online access of data and reports. Thnaks, John

$700 USD in 20 days
(1 Review)
2.7
Varun1981

Hi Sir, I went thorugh the requirement you have and I am going to give you the best solution to you, Please check PM for details. Varun

$500 USD in 10 days
(4 Reviews)
2.4
BrindabanB

Hi, Pls refer PM. Thanks & regards, Brindaban

$500 USD in 14 days
(1 Review)
0.0
diptitilani

I can provide the needed application in sharepoint in 15 days with all the functionality stated. If need can provide you POC or Sample application in 2 days.

$700 USD in 15 days
(0 Reviews)
0.0
mohabassam

Dear Sir, Using MS Access I can provide a network application as needed and provide a sample of the work within few days. Please send samples of sheets you use

$490 USD in 45 days
(0 Reviews)
0.0
DmitryYA

Hello. Is it possible to offer you a Sharepoint solution to resolve your task? I am the lead of sharepoint team in our company and we would be glad to provide you with a solution that will fullfill your needs

$500 USD in 15 days
(0 Reviews)
0.0
RandyKrupa

fineframer: Greetings from Ohio in the US. I don't beleive that I have ever seen such a well-detailed project explanation. Please see my listing on the Project Message Board. Thanks, Randy

$600 USD in 15 days
(0 Reviews)
0.0
narendrakush18

YES I CAN MAKE IT , WE HAVE TO ONE NICE RELATIONAL DATABASE FOR YOUR PROJECT

$250 USD in 15 days
(0 Reviews)
0.0