Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011
    Posts
    1

    Thumbs up Designing a database to log equipment rentals and rental revenue.

    Hi everyone, I work for a company that deals with the hire of medical equipment to NHS and private hospitals (hospital beds, electric chairs, commodes etc.)

    We currently use an Excel spreadsheet to log all of our rentals (where the equipment is, how long for and how much money it is bringing in).

    The format of the spreadsheet is a sort of calendar lay out, with each row relating to a piece of equipment and each column relating to a day of the month (we start a new sheet for each calendar month).

    If an item is out on rental on a particular day, we put the daily rental charge in that cell (eg. 50.00) and keep doing this for every day untill the equipment is returned. This gives us a daily total at the bottom of the sheet, then a monthly total once all the days have been filled in. It does mean the sheet needs updating every single morning even if no equipment has gone out/come in.

    The list of equipment is always growing and we now have 150+ items available. As you can imagine, that's a lot of cells on a very large spreadsheet and mistakes have been made which have had an effect on invoicing and other areas.

    My idea is to use MS Access (2010) to create a database that stores each piece of equipment we have, information about that piece of equipment, its daily rental price and whether we have that equipment available. The database would be accessed through easy to use forms which even the less computer literate colleagues can easily use to let the database know if a piece of equipment has either gone out on rental or been collected.

    The database must be able to give us a current daily rental rate (preferably constantly displayed somewhere) and monthly/annual reports. It must have a search function whereby someone can instantly search for a particular type of equipment e.g. Hospital Bed, and the database will show all beds of that type which are available.

    It must also store past rentals so that when an item is collected, a new record is created showing that this equipment was at this hospital from this date to that date.



    Am I asking too much or is this a realistic concept?

    Obviously there are many finer points of this database I would need to discuss with someone who is more comfortable with Access than me. If anyone thinks I could create this database myself (I have very limited experience with Access but pick things up quickly) and can offer some guidance for a beginner I would greatly appreciate it. Alternatively if anyone thinks they can make this database themselves, please contact me.

    I can send a copy of the spreadsheet currently in use if needed.

  2. #2
    Join Date
    Nov 2011
    Posts
    30
    Quote Originally Posted by Benmor View Post
    Hi everyone, I work for a company that deals with the hire of medical equipment to NHS and private hospitals (hospital beds, electric chairs, commodes etc.)

    We currently use an Excel spreadsheet to log all of our rentals (where the equipment is, how long for and how much money it is bringing in).

    The format of the spreadsheet is a sort of calendar lay out, with each row relating to a piece of equipment and each column relating to a day of the month (we start a new sheet for each calendar month).

    If an item is out on rental on a particular day, we put the daily rental charge in that cell (eg. 50.00) and keep doing this for every day untill the equipment is returned. This gives us a daily total at the bottom of the sheet, then a monthly total once all the days have been filled in. It does mean the sheet needs updating every single morning even if no equipment has gone out/come in.

    The list of equipment is always growing and we now have 150+ items available. As you can imagine, that's a lot of cells on a very large spreadsheet and mistakes have been made which have had an effect on invoicing and other areas.

    My idea is to use MS Access (2010) to create a database that stores each piece of equipment we have, information about that piece of equipment, its daily rental price and whether we have that equipment available. The database would be accessed through easy to use forms which even the less computer literate colleagues can easily use to let the database know if a piece of equipment has either gone out on rental or been collected.

    The database must be able to give us a current daily rental rate (preferably constantly displayed somewhere) and monthly/annual reports. It must have a search function whereby someone can instantly search for a particular type of equipment e.g. Hospital Bed, and the database will show all beds of that type which are available.

    It must also store past rentals so that when an item is collected, a new record is created showing that this equipment was at this hospital from this date to that date.



    Am I asking too much or is this a realistic concept?

    Obviously there are many finer points of this database I would need to discuss with someone who is more comfortable with Access than me. If anyone thinks I could create this database myself (I have very limited experience with Access but pick things up quickly) and can offer some guidance for a beginner I would greatly appreciate it. Alternatively if anyone thinks they can make this database themselves, please contact me.

    I can send a copy of the spreadsheet currently in use if needed.
    Databases are tricky to create primarily because they need to be created correctly in order to be in working order. I've seen a lot of databases that professionals are designed incorrectly and it makes the hairs on my neck stand up. Moreover, is any data going to be imported into the newly-created database?

    So, let me give you a brief overview of what I do when first starting a database. The first thing you want to define is your tables. If you are going to have two or more of anything (for example, customers, equipment, rentals, etc. you need to have tables for them.

    Once you have all the tables defined, then you want to start adding attributes (fields) to the tables. Fields are important because this is the data that is going to be stored within the table. Each table can have one, and only one primary key. So, for the customer table, the primary key for it could be CustomerID. A primary key uniquely defines a record, like a social security number, or a driver's license number.

    Once you have both the tables and the attributes created, the next part is a little tricky: defining the relationships between the tables. There are three types of relationships: one-to-one, one-to-many, and many-to-many. You will probably be using all of these, so I will explain each one to better help you understand.

    A one-to-one relationship can be something like this:
    One and one person can have one and only one social security number.

    I know that isn't the best example, but it's all I could think of at the time of writing this (lol ). It is useful because it helps to keep sensitive information in one table (for example, you wouldn't use a person's social security number as a primary key because it would be linked to many tables, and appear places. For security purposes, you could use their CustomerID because that is only relevant to your company; outside of the company, it means nothing.

    A one-to-many relationship is much easier to explain:
    One customer can rent many machines.

    Lastly, many-to-many is can be explained like this:
    One customer can rent many machines. One machine can be rented by many customers.

    The tricky thing about many-to-many relationships is that they don't exist in real life, but only on paper when explaining relationships. A third table (called the associative entity) is created to store both primary keys of the tables (in the case, the MachineID from the Machine table, and the CustomerID from the Customer table.) The combination of the two fields creates the unique record. See below for an example.

    Joe -- Lawn Mower
    Joe -- Rake
    Joe -- Snow Blower
    Ryan -- Rake
    Margret -- Lawn Mower

    Notice how Joe can be more than one, and Lawn Mower can be used for than once. It is the combination of both the CustomerID and the Machine ID (the above example uses names, and names should not be used as primary keys, but it's only an exampe) that uniquely defined the record.

    I hope this helps you to get started on your database!!

    Good luck, and post back if you have any more questions!

    Martin

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •