Newb with a problem

[crazyrat25]

Hi, I teach IT in a secondary school and have just been given a new course to teach and was told my previous experience of database work would be sufficient. Previously, the level I worked at was 3 tables, linked with some parameter queries, forms and reports.



The new course requires a database solution for a bicycle repair shop. It must contain customer records, bicycle records (more than one per customer) and a table for stock of bicycle parts.

The system must also allow customers to book their bicycles in for repairs/servicing, generate a report for parts that are running low in stock, a report to posted to customers telling them that their bike is due a service, a 'job card' for an engineer to work on the bike and add any parts used and an invoice to be passed to the customer.

I've managed most of this but I cannot for the life of me think how to create a working option for booking a service, generate an editable job card and then have that tied to an invoice.

The setup I have is:

Table - 1 customers

Table 2 Bikes

Table 3 parts

Table 4 repairs

Please help - I am at a total loss!!!

[ItsMe]

Purchase Order Table

[crazyrat25]

Thanks - could you be a bit more specific? I'm already at the limit of my Access skill and understanding!

[ItsMe]

Yup, what you are describing is an exchange between a customer and a business. It may be service based or it may be retail. It may be both. It is pretty common to manage these with a purchase order, even before computers.

If you go to Office Depot and ask where their PO books are you will find an example of what I am referring to. The PO book will be something similar to what your report will look like. You need to reverse engineer that. Take a picture with your phone.

In the upper right there will be a PO #. There will be an area for customer name, etc. in the Header area. The header area and PO # will represent your PO table. One PO# for one Record/Row in the PO table.

Below the header is the detail section. There will be Many rows for Many services and or products. Store this info in an Events table. The PO table will have a One to many relationship with your Events table.

tblPO
tblDetail

[John_G]

Hi -

Just some thoughts on what I might do.

The main ("parent" if you like) table is Repairs (I assume that would be service as well, since the is not much difference)

When a customer comes in, a service/repair record is created. You might or might not have to create a new record in the Customers table. They will have a bike for service, and as before, you might or might not have to create a new record for the bike.

As the work is done, parts will be used - there can be zero or many parts records for one Service. The process to assign parts to the Service can also update the inventory.

The repair work can be done by one or more technicians, in one or more sessions - so there can be one or more Work records for each repair (you don't have a table for that yet)

When the work is completed, you now have all the information needed to produce the invoice -
- the customer data (name, address,....)
- the bike data
- this list of parts used (if any) with associated proces
- the record(s) of work done - labour costs

So, now we have at least these tables:

ServiceRequest
Customer
Bicycle
PartsUsed
WorkRecord
PartsInventory
Invoice

That's really only an outline of the core of your system - your description indicates there will be lots of other processes that need to be developed, but most of those can can be implemented as "upgrades" after the main part is working. If you get the core part right, the add-ons are not usually that difficult.

That is a fun project, and will keep your students on their toes.

Just some thoughts

John

[ItsMe]

I was considering Table 4 repairs as a lookup table for "Canned" services offered. Much like Table 3 parts, you will need something to keep track of "Services Offered". In order to support the business rules, these tables would ultimately need to track Inventory and create statistics for Accounting.

Like John_G mentioned. Get the core down and then worry about the bells and whistles. Nothing worse than building on top of a bad foundation, even if you have to add Foreign Keys and run UPDATE queries later.

Another point made by John_G regarding students on their toes. One might think a simple business like a bike shop is easy to create a DB for. I have yet to create a "Simple" DB for even one aspect of a business. Managing all data for all aspects from a single point is monumental. It takes experience to not be overwhelmed while trying to tie in aspects such as, Retail, Service, Accounting, Inventory, Vendors, HR, etc.

Pick an aspect and build a module for that aspect, keeping in mind a common development philosophy. Then join the modules.

[crazyrat25]

Thank you all for the help so far it has helped a lot but I'm still having trouble. I've included a link to the current setup for the database and the task from the exam board - which is a website in the link below if anybody could have a look for me?

https://www.dropbox.com/sh/5f0ivta4byx6puz/H6eLJtLxH2

Thanks!!