Need Query Help! (1 Viewer)

HeatherO

Registered User.
Local time
Today, 07:51
Joined
Apr 21, 2017
Messages
45
I have myself extremely confused :banghead: My company use to do paper move-out inspections and we are now going to be doing them electronically. The tables are set up as follows...

KitchenFloor (Value list options are Okay, Needs Maintenance, Needs Cleaned)
KitchenFloor_TCOC (Value list options are Tenant Charge, Owner Charge)
KitchenFloor_NM (Short text field where user can enter description)
Microwave
Microwave_TCOC
Microwave_NM
Stove
Stove_TCOC
Stove_NM

.... and so on and so forth..... for every item in every room.

We will inspect 1000+ units over a weeks time. I want to be able to take these findings and query, per unit, just a summary of those items that say "Needs Maintenance", and then further break them down into those that are "Tenant Charge" and those that are "Owner Charge".... any idea how to accomplish this?
 

June7

AWF VIP
Local time
Today, 03:51
Joined
Mar 9, 2014
Messages
5,423
You say 'tables' - plural. Do you really have a table for kitchen floor and a separate table for microwave, etc? If so, have a single table with another field for itemID.
 
Last edited:

HeatherO

Registered User.
Local time
Today, 07:51
Joined
Apr 21, 2017
Messages
45
No, i dont have one table per item. I have multiple tables because I can only have so many fields in a table and our inspections literally cover every item in the apartment, right down to electrical outlets and baseboards. Therefore, I did one table per room. Each room has multiple categories.... floor, microwace, stove was just an example.
 

isladogs

MVP / VIP
Local time
Today, 11:51
Joined
Jan 14, 2017
Messages
18,186
Your table(s) design appears to be more suited to a spreadsheet than a database. Please can you post a screenshot of a table in design view

You also shouldn't have separate tables for each room if each table has the same or similar structure. Doing so will create a huge amount of unnecessary work and will not make effective use of database features.
 

HeatherO

Registered User.
Local time
Today, 07:51
Joined
Apr 21, 2017
Messages
45
Absolutely. I appreciate any feedback/advice!!
 

Attachments

  • Capture.JPG
    Capture.JPG
    73.2 KB · Views: 94

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:51
Joined
Jul 9, 2003
Messages
16,244
I have multiple tables because I can only have so many fields in a table.

This is a common problem for programmers moving from Excel to MS Access.

The solution requires you to think differently about your structure.

I discuss it here on my blog "Excel in Access":-

http://www.niftyaccess.com/excel-in-access/

Once you've got the Table structure right, the next problem is how do you use that new data structure in your forms and reports?

One way is to display Checklists.

I have blogged about Check Lists here:-

http://www.niftyaccess.com/add-a-check-list-to-your-ms-access-database/

I demonstrate checklists with checkboxes, checklist with text boxes to record written answers. I also show a demo video showing how to have a pop-up form provide multi choice answers.

Sent from my SM-G925F using Tapatalk
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:51
Joined
Feb 19, 2002
Messages
42,970
Once you get your head around this new way of thinking, you'll end up with a tall thin table to hold the inspection items and results and several lookup tables.

The item definition table will look something like"

ItemID (autonumber PK)
LocationID (fk to tblLocation - kitchen, bath, patio, etc)
ItemDesc

The inspection report table would look something like
ItemReportID (autonumber PK)
ReportID (fk to tblReports - summary data for the report)
ItemID (fk to tblItemDefinition)
RoomNum (default to 1 but can be 2, 3, etc to accommodate extra bathrooms and bedrooms)
Condition (OK, needs maint, needs cleaning, etc)
ChargeTo ( owner, tenant)
InspDesc

Once the data is properly normalized, calculating stats will be simple. We'll help with that later.
 

HeatherO

Registered User.
Local time
Today, 07:51
Joined
Apr 21, 2017
Messages
45
I'm trying to wrap my head around this now but unfortunately, I only have a few days to either 1)make what I built already work or 2)redesign everything (on top of my other duties). I sure with I had researched this better.

Thanks for the guidance everyone.
 

Mark_

Longboard on the internet
Local time
Today, 04:51
Joined
Sep 12, 2017
Messages
2,111
My suggestion would be as follows'
1) Make a table that holds "Inspection Items". You will have ONE record for each item that gets inspected.
2) Make a table for Tenants. This holds who is moving out and when.
3) Make one table that links the two together, "Inspection Results". This is the one you need to wrap your head around to understand.

If you were doing this in Excel, you'd have a bunch of entries in column "A", one for each inspection item. You'd then use column "B" to hold the date of the inspection, Column "C" for who did the inspection, column "D" for who is charged, ect..

In a database, you'll be storing the record ID from you "Inspection Items" table in your linking table, the record ID for your tenant in your linking item table, and the date, results, who did the inspection, who is charged, ect...

I think what is confusing you is that you need a "List" of items to check, so each item goes into its own record in a table instead of being a field in a table. This is also very true one you realize "What happens if we add a new item to check?"
 

HeatherO

Registered User.
Local time
Today, 07:51
Joined
Apr 21, 2017
Messages
45
I think I am understanding what you're telling me and honestly, I can't believe I didn't put this together before. Grrrr...

Can you look at this for me and see if I'm on the right track?

Where do you think would be the appropriate place for the inspector to list a flooring type (carpeted vs not carpeted), counts for things such as windows, light bulbs that need to be changed, etc?

Thanks so much for the patience. It's a huge help.

:)
 

Attachments

  • Test Inspection Database.accdb
    640 KB · Views: 72

Mark_

Longboard on the internet
Local time
Today, 04:51
Joined
Sep 12, 2017
Messages
2,111
What you SHOULD wind up with is a bit more complex than what I've outlined.

You SHOULD wind up with one table that identifies each unit that can be leased. Each unit would have child records in a 'Unit-Inspection" table that links units to inspection items. This way you can print out the inspection sheet specific to a given unit when the tenant moves out. This table would be updated as work is done on a unit (replace carpeting in bathroom with tile, remove the "Check bathroom carpeting" record and add a "Check bathroom tile" entry)

You would then link between "Unit Inspections" and your "Tenants" when they move out.

This means 4 tables (One that holds ALL possible inspections, one for units, one for the inspections that apply to a given unit, one for what happens when a given tenant moves out) at the minimum. You may even want to add a "Tenant" table if you need to track who moves in/out when, contact information for the tenant, new address, and any other information you need for tenant tracking.

The layout of the tables is going to be pretty straight forward for each piece. You may want to look at your existing check out sheets from the perspective of "What is a field, what is data, and what would be stored where". This way you can re-create in normalized tables what is done with your existing sheets.
 

HeatherO

Registered User.
Local time
Today, 07:51
Joined
Apr 21, 2017
Messages
45
I actually have a database with all of the units and tenant information. We are very new to using Microsoft Access here. Everything has always been done on paper so we are building out our databases as we go. It's been requested that I create the inspection items in a different database because there are concerns (from some of the "old timers" who are use to paper) that 20 inspectors accessing the database at the same time could crash it. So yes, we are managing lease end dates and tenant information and all of that stuff from another database and it's going really well. Being that we were extremely dependent on paper files - it's drastically changed the way we do business.
 

HeatherO

Registered User.
Local time
Today, 07:51
Joined
Apr 21, 2017
Messages
45
I need to create a very user-friendly form that is easy for our inspectors to follow. As I've stated, this has been done 100% on paper for the past 20+ years. The way that I had this set up before, I could easily build a form that listed each category that needed to be inspected in each room. For example...

ENTRY WAY
Front Door ---------------- -Status, Charge To, Description
Number Plate --------------Status, Charge To, Description
Peephole -------------------Status, Charge To, Description
Lock ------------------------Status, Charge To, Description

Etc....Etc...Etc...

I'm having trouble creating something similar with the tables set up the way you've advised. It looks like I would have to count on each inspector to select items from a list of items to be inspected, as opposed to laying those items out on the form and having the inspector fill in the field values. How will I make sure they've inspected each necessary item?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:51
Joined
Jul 9, 2003
Messages
16,244
It looks like I would have to count on each inspector to select items from a list of items to be inspected,

No not really, I already pointed you to a way to automatically add list(s) like a checklists and/or text boxes.

Also there's the possibility of having a selection of predefined answers however I haven't got around to doing videos on how to do that yet....

See my blog here:-
Add a Check List to your MS Access Database

And to give you a quick idea look at this YouTube video in particular:-
https://youtu.be/JGLfH2on09M
 

Mark_

Longboard on the internet
Local time
Today, 04:51
Joined
Sep 12, 2017
Messages
2,111
It's been requested that I create the inspection items in a different database because there are concerns (from some of the "old timers" who are use to paper) that 20 inspectors accessing the database at the same time could crash it.

Will the inspector be using a mobile device of some kind to directly update your database WHILE doing the inspection?

Often in cases like this, you would print out the "Check sheet" for them to use then do data entry afterward.

I am hoping that you have already split your database into a front end for the users and a back end for the data. If not, do so as soon as possible.

Personally I'd have the inspectors using a paper copy followed by data input. Too often issues would pop up with mobile devices if you don't have a rock solid network solution in place.
 

HeatherO

Registered User.
Local time
Today, 07:51
Joined
Apr 21, 2017
Messages
45
Yes, the database will be split. The users will be logged into IPADS, in to a virtual desktop on our server. We get part time help this time of year because all of our apartments turnover at pretty much the same time (within days of eachother). We have 1000+ units to inspect within 3-4 days any given year. This is why it is so important for the inspectors to have a form that tells them exactly what they need to inspect. I need to come up with a form that is pretty much fool-proof and won't let them forget to inspect a specific item that could exist in the unit. We then want to take that data and extract our, per unit, anything that says "Needs Maintenance" (and create a work order for this) vs anything that says "Needs Cleaned" (and create a list per unit for our cleaners).

Does this sound at all reasonable?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:51
Joined
Feb 19, 2002
Messages
42,970
The database you posted is not correct. You still have all the replacements (?)as separate columns.
1. table to hold building information such as address.
2. The unit table is related to the building. It includes the unitNumber (4b, 18f, etc), the number of bedrooms, the number of bathrooms.
3. Lease - start date, end date, rent, other info related to "this" year's occupant. If multiple tenants are responsible, you need another table to hold tenant information.
4. inspectionItems
6. InspectionReport - includes any remediation required.

That's a start. You most likely need more.

PS - do NOT use table level lookups. They may seem helpful but they will only cause contusion and problems later because the obfuscate the actual contents of the foreign key fields. So linking InspectionItems to Units is from UnitID to UnitID. Also don't name every primary key as ID. Use a meaningful name.
 

Users who are viewing this thread

Top Bottom