Query Duplicating Results (1 Viewer)

I have a query that uses 3 tables - Units, Applicants, and Guarantors. Each Unit is unique but could be assigned to any number of applicants. The guarantors are then tied directly to the applicant. When I run the query, if there are multiple applicants for one unit, the guarantors are appearing multiple times in the query. Any advise would be greatly appreciated. :banghead: Below is the SQL behind the query.

SELECT Units.ID, Units.Property, Applicants.Property, Applicants.Tenant_Code, "" AS Ext_Ref_Tenant_Id, Guarantors.Roommate_PhoneNumber1, "" AS Roommate_PhoneNumber2, "" AS Roommate_PhoneNumber3, "" AS Roommate_PhoneNumber4, Guarantors.Roommate_UserDefined0, Guarantors.Roommate_UserDefined1, "" AS Roommate_UserDefined2, Guarantors.Roommate_UserDefined3, Guarantors.Roommate_UserDefined4, Guarantors.Roommate_UserDefined5, Guarantors.Roommate_UserDefined6, "" AS Roommate_UserDefined7, "" AS Roommate_UserDefined8, "" AS Roommate_UserDefined9, "" AS Roommate_SSN, Units.Property_Code, "" AS Ref_Property_ID, Units.Unit_Code, "" AS Ref_Unit_Id, "" AS Ext_Ref_Roommate_Id, "" AS Roommate_Salutation, Guarantors.[First Name], Guarantors.[Last Name], Guarantors.Roommate_Email, "" AS Roommate_ALTEmail, "" AS Roommate_MoveIn, "" AS Roommate_MoveOut, "" AS Occupant_Type, "-1" AS Roommate_Occupant, "" AS Roommate_ACHOptOut, "Guarantor" AS Roommate_Relationship, "" AS Roommate_Notes, "" AS Roommate_Code, Guarantors.[Uploaded to Yardi], Units.[Full Renewal], Applicants.[Lease Signed]
FROM (Units INNER JOIN Guarantors ON Units.ID = Guarantors.Property) INNER JOIN Applicants ON Units.ID = Applicants.Property
WHERE (((Guarantors.[Uploaded to Yardi])=No) AND ((Units.[Full Renewal])=No) AND ((Applicants.[Lease Signed])=Yes));

Use SELECT DISTINCT

The guarantors are then tied directly to the applicant

Click to expand...

That's not what your code says:


Your code says a guarantor is tied directly to the Unit, not the applicant. I really think your structure is improper.

I tried DISTINCT and wasn't successful.

Please bare with me - I was tasked with creating a simple database for work, and it has taken on a life of it's own. I'm learning as I go.

Plog - I have no doubt that you're correct... I am just not experienced enough to know what works best. I welcome any and all advice!!

How would I go about copying the database without the data so you can get a good look at what I'm trying to do?

I think there is info beyond what you stated. I see things like RoomMate. And numbered fields like
PhoneNumber1, PhoneNumber2...; things like MoveIn and MoveOut. These send up a red flag that you have hidden entities.
I suggest you start with a clear description of what you are trying to do. Clear simple English --just as you would tell a 10 yr old- no jargon, no quasi-database terms, just simple, plain English.

Good luck.

Building on jdraws request, I suggest you do 3 things for us:

1. Give us 1 paragraph of what your organization does. Pretend its career day at an elementary school and tell us what you do.

2. Give us 1 paragraph in plain-English (no database jargon) of what you need your database to do and the real-world system/process it models.

3. Set up the relationships tool in Access and upload a screenshot. No need for a full copy yet, just a screenshot of that.

I work for a property management company. We manage around 1,900 properties - some student rentals, some professional, and some commercial. We are responsible for everything from the showing, lease signings, maintenance items, rent collection, paying the owner, etc.

I need the database to be able to do the following:

1)Store the unit information - building name, unit number, address, how many bedrooms, is it pet friendly, is it carpeted, is it furnished, student rental vs professional rental, rented vs available, etc. We generate our availability list based on this information.

2)Store the lease information - Tenant names, contact information, lease dates, who rented the unit, security deposit required, rent amount, length of the lease. We generate our actual lease agreement, tenant move-in letter, master paper list of properties and tenants, and eventually our turnover list based on this information.

3)Store roommate information - Tenant name, contact information. Need these names to show on the lease along with the lead tenant. Also need them to receive communications we may send throughout the year, along with the lead tenant.

4)Track security deposit payments - We may receive the entire security deposit in one payment, or we could receive 5 separate checks from 5 different roommates at 5 different times. We need to be able to store the payment information and query to see who owes a balance. (Note, we begin showing and signing leases for our student rentals in October for apartments they will rent beginning in August - this is way, way in advance!)

5)Track communications with our tenants - If we notify them of something important, or they notify us of something important, we no longer keep a paper file so we need to have a place to note it and be able to reference it later.

6)Store pet information - Does the tenant have any pets? What kind? What size? What color? Is this pet an emotional support animal? Etc.

7)Store sublet information - Have any of the original tenants sublet their lease to someone else? Who? How long? Etc.

8)Create Uploads to our Master System - We have software that was purchased from a vendor (this was before my time here) and it doesn't really work for the way we do business. This is why the database was created. Once the tenant information is entered into the database, along with security deposits received, etc, certain pieces have to be uploaded to the vendor software so that the accounting functions can be done through the vendor software. Coincidentally, right after I started building the database, our vendor software underwent an update that actually took away our ability to add tenants to their system manually. They now MUST be done through an upload. We have to upload the main tenant record on one upload, followed by the roommates on another upload, and then the lease charges on another upload.

The database is operational and I've been able to accomplish most of what I need it to do. However, I am not certain that I have it set up in a way that makes the most sense from a programming perspective (again, I am learning as I go - it's been months of reading books and my trusty friend google!, along with trying to do my normal job on top of this). So when I say that I appreciate the help, please know that I truly do mean that!! This has been stressful :banghead::banghead::banghead:

Here is a link to a generic data model that may be useful. It shows many of the "things"/entities involved typically and how they often relate to each other.

Note this is generic and deals with the more common entities/attributes and relationshships. Your requirements may or may not be depicted in the model. You can alter, remove, add, adjust the model as necessary to suit your needs. It is meant as a starting point to give a general view to assist with organizing your data/database.

There are other free generic models at Barry Williams' site(http://www.databaseanswers.org/data_models/index.htm)

http://www.databaseanswers.org/data_models/apartment_rentals/index.htm
http://www.databaseanswers.org/data_models/real_estate_rentals/index.htm
Good luck.

Here's the issues I see:

1. Where's Guarantors? You have it in your SQL that you initially posted, but its not in your relationships.

2A. Circular path. There should only be 1 way to trace a path between tables, you have set up Units, Applicants and Roomates in a circuit. They all 3 should not be directly related to each other.


2B. Numerated Field names. All those [Tenant X] fields need to come out of Applicants. When you start adding numbers to the end of field names, its time for a new table. The correct way to sort out a many-many relationship is via a junction table.

3. Spaces in names. Table and field names should only have alphanumeric and underscore characters in them--it makes coding and querying easier later on. So remove all the spaces from your names ([Assigned By]->[AssignedBy], [Type of Pet]->[PetType], etc.)

4. Reserved word names. You should not use any words on this list (https://support.office.com/en-us/ar...-symbols-E33EB3A9-8BAA-4335-9F57-DA237C63EABE) as names. That means you need to change the fields [Name] & [Property] to better ones. Usually you prefix what they actually are to them (e.g. [DepositName]).

5. Values as field names. [Cash], [Check] & [Money Order] are all types of payments. So instead of a field for each, you remove them and replace them with 1 field that holds each of those as values (e.g. [PaymentType]).

You really need to address those before you move on to whatevever issue you posted about initially. The biggest thing you need to figure out is how Roomates and Tenants fit into this. What's the difference between a Roomate and a Tenant?

Thank you so much for the feedback!! I will take a look at those example databases.

Plog- To answer your questions...

1)For whatever reason, that table wasn't showing... I have updated it and reattached.

2A)I see what you're saying here - I initially thought it would be as simple as just linking the roommate to the property. But then when you search the roommates for that property, you get them all, regardless if they're living there at different times. I think I need to remove the relationship between Roommates and Units, and the relationship should be between Applicants and Roommates?

2B)I will look into a junction table, as I am unfamiliar. The ONLY reason those "Tenant 2", "Tenant 3", "Tenant 4", etc, fields are there is because when I generated the leases (through a mail merge document), I couldn't get the Tenant and then a list of roommates to merge. So I linked those "tenant 2", "tenant 3", etc to the roommates table and made them look up fields. Any suggestions on how to accomplish the roommate names printing out on the lease if I don't have this set up?

3)Will do.

4)Will do.

5)Will do.

There is no difference between a roommate and a tenant, execpt for the way that they are set up in our vendor's software. The main lease record lists a "Lead Tenant" and then you click on "Roommates" to get a list of roommates. I would ideally like to have everyone listed as a "Tenant", then be able to pull up the lease details on a form and have it show me a list of tenants for that particular lease. I had planned to link the sd payments, guarantors, communications, etc by the lead tenant (Assigned To in my database) so I would need to figure out how to do that if everyone was listed as a tenant instead.

Thank you so much for the fantastic feedback - I appreciate it!!

The main issue you have is not breaking down each entity into just the data it comprises. You've mingled your Property and people into 3 tables which is causing issues. You need to break each entity out into its own table. I see 4 entities; Units, Applicants, Roomates which should become 4 tables (the 4th table will tie data together):

Units
--remains the same

Residents
--structure similar to current Roomates table but with just data about the person--no lease information here (remove Property, MoveIn, etc.)

Leases
--structure of current Applicants table, but with all Tenant/Roomate data removed.

Tenants
--junction table to sort out what Residents are on what Leases.

Tenants will have these fields:
TenantID, autonumber, primary key
ResidentID, number, foreign key to Residents
LeaseID, number, foreign key to Leases
MoveInDate, date, holds Roomate.MoveInDate field data
MoveOutDate, date, holds Roomate.MoveInDate field data

That structure should sort out what person is in which property under which lease.

For your lease document, I would suggest you redo it in Access as a Report. That way you can use a sub-report to generate a list of all Tenants. To keep it as a word document will be possible, its just going to require writing a query properly.

Wow - this absolutely makes perfect sense to me!! You have given me so much to work with and for that, I am extremely thankful!! I am going to get started on this today. I will let you know how everything works out. Again, thank you so much for taking the time...

I have attached the relationship summary - does it look correct? I am not understanding what I need to do with the units table? I think it should have a one-to-many relationship with residents?

The Units table is linked to the NewLeases table. I believe Units.ID linked to Applicants.Property in your prior screenshot. So now it should be Units.UnitId linked to NewLeases.Property, of course it would be better named NewLeases.UnitsID.

Otherwise the relationships look good. Of course you need to remove the MoveIn/Out dates from NewResidents--as well as any other fields in there that belong in NewTenants.

Would you be proud to know that I actually figured that piece out before I read your response? Maybe there's a brain in this head after all??

I have this set up and it's working beautifully based on my testing thus far. One thing that I am unsure of is how to create the query that I'll need to print a lease. I can see times when we would need to do this by mail merge (when we print our renewal leases, it's nice to batch print them) but as we rent throughout the year, it would be nice to be able to print a lease individually through a report. Since I don't have "Tenant 2", "Tenant 3", etc, I am unsure of how to do the query so that it pulls the lease details AND a list of roommates. Does that make sense??

An Access report can "batch" print things as well. You set up your query correctly and your report and it can print as many leases as the query tells it to. Via that query or a filter that exact same report can also print 1 specific lease as well.

To generate a query that your current Word document can use you would employ a cross-tab query (https://support.office.com/en-us/ar...cc8-ba60-2cd8484667e8?ui=en-US&rs=en-US&ad=US). It converts values in one row to column headings. That will allow you to get all tenants into one record.

I suggest though an Access report with a sub-report. That way you can just list each tenant in a an area on your lease like so:

Tenant 1: John Smith
Tenant 2: Steve Smith
Tenant 3: Laura Smith

I am just now seeing this - was out inspecting apartments all day. Yuck!!

I am familiar with a cross-tab query. I think what I don't understand is how to to create the report. I will take a look at this at some point over the next few days (I am inspecting all weekend) and will let you know if I have any success.

Thanks again for all of your help - you have saved me hours of frustration!!!

UPDATE: I figured out how to do the report based on your suggestions!! I am currently working on building it out. Everything seems to be working great so far, based on the testing I've done. Thank you again for the time you've spent and sharing your knowledge - I appreciate all the help!!