Thoughts on design for a Repair Shop (1 Viewer)

[maworkman763]

Hello all,
I’m somewhat familiar with access, but not a programmer by any means. I’m looking for tips and advice on a data base I’m creating for an electronics repair shop.
I am using Access 2010.
I have created my tables with basic entry forms.
My tables with fields with brief descriptions of each are as follows:

CustomerTable; CustomerID(Key), CompanyName, StoreNum, FirstName, LastName, CompanyName, Customer, Address, City, State, PhoneNum, NumAssets, CustSince.
I have a Query set to take first & last name and move it to Customer is Company Field is null. Then I have Company field to concatenate the store number. I’ve added the NumAssets field so that I might be able to add the “Number of Assets” each customer has that I have worked on. Then of course the Customer Since Field is a standard Date Field.

ModelTable; ID, Model(Key), Description, Manufacture, PurchasePrice, Attachment
This table is basic information about models available, without serial numbers. My thoughts were to use this as a table to fill information on a per cust., per repair basis.

ReturnAuthorazationTable; ID(Key), RA, ArrivalCond, ReceivedDate, TrackingNum,RepairDate,Customer,OEMPackage,Model,EmpID
RA field is to enter a number that main office generates specially to that Customer and return.

RepairTable; ID(Key), RA, SerialNum,Model, Repair, HoursToRepair, OutputVoltage, Load, Hz, Condition, RepairDate, Tech
This table I envisioned tracking the repair of the unit and its final outcome. (More to come on the flow of the assets handling and the db)

Assetstable; ID, SerialNum(Key), Description, Category, Condition, AquiredDate, Model, Repairs, Attacments, RetiredDate, CustID.

I also have two tables for City, State and Zipcode to use as auto fill for Customer information that seems to work well.

WheW!!! Now for the ones still with me.

I have an Electronics repair facility; I receive packages that customers have sent back with an RA generated by main office. I would like to be able to log these in as they come in on a daily basis with Customer Information, tracking number, RA and Basic Model.
Then I would like to be able to recall that information or link it to a Repair, with more information that I have gleamed from the repair. For example the Serial Number, the point of failure, the actual repair, the time it took to repair, if it was repaired or scraped.
My thoughts then, I could build reports or more queries’s to gain knowledge of Customer to Assets to Repair.

Now to my questions:
I realize there are some redundancies in the fields and that takes up room. Should I thin these down to the bare bones?
How do I link the tables to get the information I need?
Should I just have one form to enter all the information and do a look-up each time I need to add information? For instance, have one for that has every field on it, enter the first part of the data I have (RA, Customer, Tracking etc,) close and save. A few day later, when the repair is being made, do a look up and enter the rest of the data on the same form?

Thanks in Advance to any suggestions you may have.

 

[jdraw]

My advice is to review this tutorial, then work with the existing description of your requirements and apply the same steps.
Create some test data and scenarios and see if your model will support the test data.
Relationships are not something you "apply" to your database after you have forms. Relationships represent how the "things" in your business inter-relate based on your understanding of the business.

 

[maworkman763]

Thanx...

I'm really pleased with the way it works so far pulling information from the previous entry via a lookup from a table. Just worried that I might get a lot of work done then hit a stone wall on the home stretch.

I just hit the high points in my initial post.

It looks like a good read. Once I get home and have the comforts of a couch and a cold beer I'll be able to digest it better!

I'll probably have more questions tomorrow.

Thanx again.