Tool Rental Data Base Problem

[dsthomson]

Hi,

I am building a Tool Rental Data Base and need help with tracking length of time a tool is on a job and billing for it. I have my tables built and have Forms and Queries that Transfer a tool to a job. That is as far as I have gotten. I need to build a monthly billing system etc

The database is only tracking movement of a tool to a job and not from the job. I have responded to a thread that uses DMax and DCount to find the last transfer of a tool and that may be useful if I can get it to work.

Someone may have a better solution to my project or some examples of similar data bases ?

Thanks

 

[pbaldy]

Your structure would assume that a tool is always rented out. Is it? Doesn't seem likely. I'd have an end field.

 

[dsthomson]

The tool may not always be rented out but it is always somewhere - even if at the warehouse. The tool may go straight from one jobsite to another without getting to the warehouse at all. It seems to me that an end date would require 2 transfers instead of one. There may be a work around scheme in the process but I am not sure what that would look like?
I am open to changing the scheme for functionality.
Thanks for your response

 

[dsthomson]

Attached is most of what I have so far. It works but I am not sure how to track dates that tools are Transferred from a specific job.
I will need to use that information to compute days rent for specific months. Ideas or similar database examples would be appreciated.
Thank you.

 

[Joe8915]

I would think you would have to have additional date.
You have it going to Jims Pizza first.
Are they going to let you know the date when its going to Joes Pizza? or to the next job site

 

[jdraw]

I agree with Joe8915. You have to consider what is going on --independent of whatever tables or queries you currently have.

What do you mean by tool rental? Seems it should be something like

A tool from my inventory of tools is rented to a Customer for a period of X hours/days beginning Nov 4/2013 (may include time if hourly rentals). That rental agreement should end on MM/DD/YYYY. This allows you to query what tools have not been returned according to the contract/agreement? And allows you to act accordingly.

You should work out these "facts" before setting up your database. The old story -- "if you can't define it, no one can build it."

The design of your database tables and relationships are based on your business facts/business rules. Not on Access.

 

[dsthomson]

Fixed rental periods for tools are not a norm for the business and even if they were it would be rare to that the date did not change. Most tools will be out for months at a time. At least in our situation. So each month every job needs to be billed for the tools on the job for as many days as they have been there for the month. Therefore being able to determine the end of rent is determined by when it is transferred to the warehouse or another jobsite. That said it is not practical to enter the return date on the same record as the original transfer at the same time. So I believe that I need to find a way to add the return date to the original record after it is transferred away from the job. BTW -The information in the database is fictitious.
It may be more practical to do this by linking an excel sheet?
Thank you for your replies.

 

[jdraw]

I suggest you continue with more detailed description so readers can understand your business. It will help in any discussion/advice.

You'll need some sort of transaction to track/identifu the movement of equipment.

It sounds a little like the rental of large equipment -- read that as expensive -- and you should know where your inventory is.

Once you get the facts together and clarified, I suggest you then decide whether database or spreadsheet is appropriate, and then design accordingly.
Good luck with your project.

 

[dsthomson]

I work for a commercial construction company. We have several different types of contracts with several different criteria. Some allow rental fees for any tool over 1 value level and 1 uses another etc. Billing is done on a monthly basis. We mostly supply basic power tools mostly under $2000.00. 400 plus tools. We may have 10 small jobs and 3 large one going at the same time.
I have enough objects in place to be able to transfer tools and to find the current location of a tool based on the last transfer that is associated with it.

Now I need to find methods to see how many days a given tool was on a given job in a given month. Then I need to insert the variable rent criteria into the system and bill for it.
Any help or examples would be very much appreciated!

 

[jdraw]

It's getting clearer with the added detail. I think your main entities are

Tool
RentalContract
RentalContractType
RentalFees
Job or JobSite
ToolAtJobSite

again more details may show more and clarify relationships between/among entities.

One thing you mentioned isn't clear to me ---

We may have 10 small jobs and 3 large one going at the same time.

If Tools are returned to a Main office, or if Tools move from 1 JobSite to another directly, you'll need a transaction to identify that Tool X left JobSite Y on Date Z and is now at JobSite Q....

If you work through this tutorial you'll see how to identify the entities and relationships generally http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip



I attached a small sample data base to a different forum and thread involving moving material from one location to another. It may help with an approach. It's located at
http://www.accessforums.net/forms/how-swap-records-separate-tables-using-form-24504.html#14

Good luck with your project.

 

[Joe8915]

I think jdraw is right on. I would follow his lead on this. Good luck with db

 

[dsthomson]

Thanks to those that have added to this thread. I have attached my work to this point. I think what I have is functional for transferring tools but I could still use some help getting to the monthly billing portion of the work. I will need to be able to figure days rent on each tool at each job and then apply proper rates for the contract type. Any suggestions or examples would be appreciated.

Thank you!

 

[jdraw]

I don't see in your model (relationships window) a reference to Contract/RentalAgreement.

If you list all of you business rules, the "rules" must be accounted for in the model if it is going to support your business.

I think you have a great start.

 

[dsthomson]

This problem was solved in another thread
http://www.access-programmers.co.uk/forums/showthread.php?t=258485&highlight=billing