Small Inventory

[ResTech]

I am creating a database for our small service department, I have about 25 loaner devices that will go out while a device is getting repaired, is there an easy way to track these? The loaner will be tied to a repair number while in use, then it will come back in to be available for use again. I'm hoping to use very little or no code with this project.

Thanks in advance for any ideas.

 

[ResTech]

AccessBlaster,
These are the two tables I have so far, I am not sure how to go about tracking the Loaner devices so I know when they are in or out. Initially I had the loaner table separate. Thanks

Patient Info -
PatientId
Search
Date of Call
PatientLastName
PatientFirstName
Address
City
State
Zip
Phone
Email
Complaint

Repair -
RepairId
SerialNum
DateOfService
RepairTech
ProblemFound
PartsUsed
PatientId
LoanerSer
LoanerHMR
LoanerSW
PartNum

 

[ResTech]

So make another table with dates and serial numbers that links to the other two tables and then I query that third table to see what serial number is in or out? Or do I make a form for that table also?

 

[way2bord]

For proper normalization you may want to consider the following logic and create tables to accommodate:


1 patient can have many complaints
Complaint

1 Complaint can have many Calls about it
DateOfCall

1 repair can have many parts
PartsUsed
PartNum

and potentially:

1 Repair can have many problems found
ProblemFound

can 1 repair have more than 1 tech working the case?
can 1 repair ever take more than 1 day? (if so, your DateOfService field should be split into a "date start" "date end")

 

[ResTech]

One patient could have many complaints but there would be different dates to separate or sort them. I was going to have a drop down combo box with part numbers and descriptions, but if multiple parts are used I should probably keep that in a separate table then? There will only be 1 tech (for now)...:banghead:

Also...the patient form/table will be used by one person and the repair used by another.

Thanks to both of you!

 

[ResTech]

Maybe I should join them by RepairId, the person taking the call will fill out the customer complaint form and the RepairId will be an auto Number...then when the repair tech works on it they search by the RepairId? Does that make more sense? I guess the patientId number was my way of the repair tech verifying patients name.

 

[ResTech]

I also have another dilemma, this is supposed to launch on 7/8/13 with a minimum of the repair tech entering his info into the repair section of the database and the patient info form being used later if it isn't ready. I initially was going to link them by RepairId and have the person taking the call generate the auto repair number, but if repair starts first I'd want them to generate that number?? My two current tables are listed. Then I need another table to track the loaners that are in or out. :banghead:

Patient-
PatientId
DateOfCall
PatientsLast
PatientsFirst
Address
City
State
Zip
Phone
Email
Case#
Complaint
RepairId

Repair-
RepairId
SerNumb
DateOfService
RepairTech
ProblemFound
RepairsMade
PartsUsed
RepairNotes
PatientId
LoanerSer
LoanerHMR
LoanerSWV