Way in Over my head!!! HELP!

[MTMann122]

OK, I don't know what I did to get where I am, but it's apparent that I have been charged with setting up a database of over 1000 hotel rooms spread over 12 different hotels. The boss thinks we can track the condition of each item in the room (96 to be exact) based on the last inspection. The database isn't really the scarry part it's the reports he hopes to get from it. Such as;

1. When was the last time each room was inspected?
2. What are the cleanliness issues in each room?
3. Rank the condition of each item on a scale of 1-4
4. Create an exception report of items that need repair/replacement.
5. Create a list of rooms that are candidates for renovation based on overall condition.

I consider myself an intermediate level user of excel and know very little about VBA. If there is anyone interested in helping with this challenge, I would greatly appreciate it. I'm not asking for someone to write the thing, just maybe help me to approach the problem and guidance along the way.

 

[iliace]

Sounds like you need an actual database for the task, not a spreadsheet. You'll need to have a table of hotels, table of rooms (with hotel as a foreign key), and a table of items (with room as a foreign key). You can set this up as three separate worksheets in a workbook, but something like an Access is really the way to go. 1000 rooms x 96 items is already 96,000 which means if you are using Excel 2003 or earlier, normalization is not possible. You'll need to list items by hotel, in separate sheets, and that will give you reporting nightmares (and inaccuracies).

List the properties of each object (hotel, room, item).

For hotel, it would be name, address, manager, and other relevant information.

For room, it will be the hotel that it's in (some sort of unique identifier), room number, floor, etc.

For item, it will be the room that it's in, and other relevant data.

You'll possibly need a table that keeps track of each time a room is cleaned. That way you can query

SELECT tblRooms.roomNumber, MAX(tblCleanings.cleaningDate) 
FROM tblRooms INNER JOIN tblCleanings on tblRooms.roomId = tblCleanings.roomId 
GROUP BY tblRooms.roomNumber

will return the last date each room was cleaned. Very basic example, but basically what you want.