DB for rental Company (1 Viewer)

speakers_86

Registered User.
Local time
Today, 07:07
Joined
May 17, 2007
Messages
1,919
My company rents equipment to our customers. I am looking for a db to track our stuff. There are three possible locations of everything. Warehouse, employee trucks, and Customers. I assume that warehouse can be added to the employee table. I can set up these 2 tables and one for our equipment list, but the hardest thing I am having with is with the table for transactions(I believe it should be a table), this needs to allow batch transations, because typically more than one piece of equipment is moved at a time.

I will need a report that will show each equipment id number in a customers home from date to date (date it was placed and date it was removed), one that will show a list of all equipment locations on a given date, and one more that will show equipment that has been in a customers house for 5 days or more so I can verify that it is still there, becuase equipment typically should never be with a customer for more than 5 days.


Right now I am using excel, but its not working well. I would greatly appreciate some help! The attachment only has the info I need for the 3 tables I mentioned above that I know how to do, and my best guess at what the transaction table should look like.



See atached.
 

Attachments

  • Equipment Tracking.zip
    372.1 KB · Views: 155

speakers_86

Registered User.
Local time
Today, 07:07
Joined
May 17, 2007
Messages
1,919
I think I attached the wrong file!
 

Attachments

  • Equipment.zip
    19.7 KB · Views: 122

speakers_86

Registered User.
Local time
Today, 07:07
Joined
May 17, 2007
Messages
1,919
Out of Frustration

This hasnt been up long, but maybe I can explain it better. I have my equipment, customer, and employee tables set up. What I dont know how to do is how to record the transfer of multiple pieces of equipment from employee to customer, or customer to employee, or even employee to employee.
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 04:07
Joined
Nov 8, 2005
Messages
3,296
Ok what you need is a continous form layout aas a sub form
and on this you will need to have a list (via a qry-combo) of the equipment

Tables
main
equipment
equipmentout


so
mainrecord
subform-(tied to a table called equipmentout or something)
on this you need the id of the main record
and then equipment id
you will end up with in your equipment out table

an ID number fromth e main record and idnumbers for the equipment

now you will also need date fields and a
transaction number field or batch number

the date fields will be I presume
booked out
Booked in

the batch invocie number you will need to review - and look at invoice numbers in the samples section

but basically it is a number that will ally to the complete list that you have selected

so you will end u with

main record in (say) 5
equipment id (say )45
date out 25/12/07
date in (blank)
and then you need this batch number option ( this will allow infinate equipmets to be tied to 1 main recrd and 1 booking)
 

CEH

Curtis
Local time
Today, 06:07
Joined
Oct 22, 2004
Messages
1,187
I took a look at this last night... no 2007 on my computer here... I think you need to look at your tables. Do not use just "ID" for primary key in every table...you'll run into total confusion! Use like "CustomerID" "EmployeeID" something that uniquely identifies the field. Further more you need to link your primary key to foreign key in a second table.(Both should have the same name) You have one transaction that could contain many items. Similar to an inventory DB, with a twist. :)
 

speakers_86

Registered User.
Local time
Today, 07:07
Joined
May 17, 2007
Messages
1,919
Thanks for helping me, but Im at work now, so ill try your suggestions later.

If you want to see what I am starting with, which isnt much, attached is 02-03.
 

Attachments

  • Equipment.zip
    12.4 KB · Views: 115

speakers_86

Registered User.
Local time
Today, 07:07
Joined
May 17, 2007
Messages
1,919
Overwhelmed

I read your post gary, and I think this may be over my head. :confused:
 

speakers_86

Registered User.
Local time
Today, 07:07
Joined
May 17, 2007
Messages
1,919
Can anybody word Gary's answer a little differently?
 

CEH

Curtis
Local time
Today, 06:07
Joined
Oct 22, 2004
Messages
1,187
If I were you... I would look at some inventory databases. Probably alot of examples around. Your basically going to have the same setup... Instead of creating an invoice for a sale your creating one for a rental. Not much difference, you have a few more variables. I'm not sure what you mean by "Batch". With a Transaction table and primary key of "TransactionID" each rental ...of one or twenty items will have a TransactionID or "InvoiceID" assigned (autonumber)
 

Users who are viewing this thread

Top Bottom