Advice needed on how to upgrade my database

I have built a Database to take care of everything a rental building requires: work orders, purchase orders, task scheduling and so on… it work great but wanted to make it more efficient. Here are some of the things I need to change and wanted to know if it is possible to do it in a way that would be bullet proof.

I currently use the “Work Orders” portion of the database to create a record when a building resident requires work performed at their suite. Once the request has been entered I print out a “Service Request Receipt” with action to contact me to make an appointment in order to go see the problem.

Once I received a call and enter it in my appointment portion of my database I then print out a “Work ticket.” I bring this work ticket with me on my call and then write information what I need to do and purchase for the job.

Once I have this information on paper I enter it in the work order detail section where I have 4 dynamic dependent combo boxes where each take its list of value from query.

Example:

Building = current building

Unit = list result of possible suite # and or common area that are possible for “current building”

Quarter= list of results (kitchen, bathroom bedroom…) that are possible given the [type] of [unit]

Element = List of results (light switch, tap, floor, wall…) that are possible for the [type] of quarter

On another form I have:

WorkOrderID and WorkOrderDetailID and ElementID

Which gives me a possible list of product to order on my purchase order:

Product = list of results( doorknob, light switch, gasket…) that is possible for the [type] of Element
Supplier = a list of potential suppliers who carry the part or parts that I need.

This all work fine and I like the simplicity as everything chains up together beautifully.

The upgrade that I want to do is as follow:

My lists. They are created using a table with two fields: example [unit] and [quarter]

Unit can be suite# 101 , 102 103.… and each suite have Kitchen, bathroom, bedroom if the suite is a one bedroom suite and may have a patio if it is on the corner of the building. All filters work great and I have no problem with that. The problem that I face is when I add an item to my tables. I have to add this item by creating a record for everything starting from the top with [buildingName] [Unit] [quarter] [element] [prduct] [supplier] which make on the fly adding of a record time consuming.

I was wondering if there is a way to just have one table of each but alone by themselves?

Instead of having to type the name of the building and a unit and a quarter each time when I could just simply add an [Element] record to the element table?

Right now I have no choice to double entry everything and it goes against good database design and need a way to write this in codes to refer to each table as a single entity and with the result of one combo box the second one would take its value from a table instead of a query that takes its value from current form control preceding what I now want to lookup for the next step.

Reason why I designed the database as it is was because I needed to keep track of this aging building and accurately demonstrate aging components. If I get 220 calls to fix plumbing at the end it will make sense to put the overall plumbing of the building under review for a total overhaul instead of paying for repair calls which adds up fast.

Any idea on how I can make this process more efficient?

re: Advice needed on how to upgrade my database

Sounds like you have a proper one to many relationship in a 3rd normal form database and what you are wanting to do is to break that relationship and thus denormalize the database. While in some situations it may be required to do such a thing but my thought here is have you thought about speeding this process up just like you do for the service call by adding a form specifically for this purpose...




Good Luck

re: Advice needed on how to upgrade my database

Yes, I think you understand what it is I want to try to do. I am not sure for which purpose you refer to when you say "specifically for this purpose" though?

If you mean a pop up form to hold my controls of [Buildings],[units],[quarters] and [elements] so I can update them on the fly, yes I do have one of those.

I think where I went wrong was to create 4 different tables and then reunite the controls in a lookup query. I have 4 combo boxes, each one of them taking its value from 4 different queries. Each query only has two control in it excepted the first one [buildings] who takes its from the building table.

Then the value in my combo box [building] returns me a one to many [units] that I can choose from, then the value of [unit] gives me a reduced list of values for the [element] value. the problem with this is the [quarter] control which sits in two different queries. if I want to update [element] with a "Bathroom exhaust fan" record, I also need to create an entry in the [quarter] otherwise my lookup query will not pick it up. the thing is I also have to go in my other query prior to this update and update the new string for all the [units] and each [building] each building has an average of 45 suite in them so every time I need to add a new item I also need to add it for every suite of the building... I know I am not doing this right because I am doubling upi on a lot of record and it is extremely complicated and will lead to problem but the thing is that my database is new and still very small and the design works wonderfully when all the lookup fields that I need to create a record have been entered already. Even the lamest people in the organization won't be able to mess it up lol.

I just want to try to find a way that is simpler to enter a new item into those tables without having me to type it all up, just one entry, is it possible in some way to accomplish this?

re: Advice needed on how to upgrade my database

Without seeing your code and your database it is a little hard to visualize but we can theorize...

tblBuilding
iBuildingID
'address of building and so on

tblUnit
IUnitID 'auto number
iBuildingID 'foreign key relationship to tblBuilding.iBuildingID
'description of unit, location within building, etc.

Okay, so far so good as this relationship should not be broken but since every apartment/condo is different, especially between buildings in different complexes you might want to break the direct one to many relationship between tblUnit and tblRoom (quarter) but still keep the relationship via a mid table...

tblUnitMidRoom
iMidID 'autonumber
iUnitID
iRoomID

tblRoom
iRoomID
vRoom 'description of room (kitchen, bathroom, master bedroom, master bath, etc.)

This way you can add directly to the room table without needing tblBuilding and tblUnit

Another way to accomplish this is to just break the relationship altogether and use tblRoom as a lookup table for the work order table and the same can be said for the work done table (Element table).

Hope this helps.



Good Luck

re: Advice needed on how to upgrade my database

Funny that you mentioned it. I decided to proceed by eliminating all relationship and simply gathering everything that I have under one table which I now use as a lookup table. We think alike. I am a real newbie but I think I am starting to see how this program works. its hard and slow and often try to imagine how mnay hours you pros out there have spent to learn this system and especially the VBA aspect of it which is still jebruish to me. I can't even start to understand how you guys have the ability to even choose the write function and how to write it. For me whenever I try its almost like my computer is giving me the finger especially if it is on Tuesdays and if it rains outside, seems that it is connected in a weird way with Access lol

re: Advice needed on how to upgrade my database

Practice, practice, practice, and before you know it, you will know it.




Good Luck