Self Join 3 Extra Results

[bahalzamon]

I have a table being pulled from SharePoint into Access. I have the same table brought in 3x naming them Acquisition, Disposition, & Rehab.
Essentially it houses info for things happening to certain addresses, so for example there is a property that has an Acquisition (bought), then Rehab (work on property) tasks are done, then a Disposition (sell property).



So I modeled the query off of an old SQL query and its not functioning the same way, i'm sure its just me not understanding the differences of SQL Server vs Access SQL. =P
Using SQL Server I get one record with all the info i need, but in Access i get 4 records. It looks like its doing some sort of FULL JOIN.

SELECT Disposition.ID, Acquisition.[Content Type], Disposition.[Property Address], Acquisition.[Acquisition Price], Acquisition.[Begin/Contract Date], Acquisition.[End/Close Date], Disposition.[End/Close Date], Disposition.[Sales Price], Disposition.[Appraised Value], Disposition.[Home Buyer/Renter AMI], Disposition.[Annual Household Income], Disposition.[Members in Household], Acquisition.[Begin/Contract Date], Acquisition.[Obligated Amount], Rehab.[Expended Amount], Disposition.[Content Type]FROM ([XXX-Activity] AS Disposition LEFT JOIN [XXX-Activity] AS Acquisition ON Disposition.[Property Address] = Acquisition.[Property Address]) LEFT JOIN [XXX-Activity] AS Rehab ON Disposition.[Property Address] = Rehab.[Property Address]
WHERE Disposition.[Content Type] Like "*Disposition*";

If I am missing anything please let me know I will attempt to add and any Suggestions are welcome. =)

[June7]

Each dataset has only 1 record for each property?

I had no problem getting 1 record for each property. Assumes every property has an Acquisition record.

SELECT ...
FROM Rehab RIGHT JOIN (Disposition RIGHT JOIN Acquisition
ON Disposition.[Property Address] = Acquisition.[Property Address])
ON Rehab.[Property Address] = Acquisition.[Property Address];

[bahalzamon]

There is 1 acquisition and 1 disposition with no rehab. I did find a way around but i am unsure if it would cause any performance issues as the data-set gets larger.
But I ended up creating 3 queries, one with a filter for dispositions, one for acquisitions, etc... then joined them in the same manner in a fourth query but this time it works using the previously created queries. =/

I would definitely still love to figure it out though. =P

Here is all the other info for this query in case it helps point me in the correct direction.
Results with some info hidden, i accidentally clipped off the id, but they all say 120, same Disposition.ID.


Design Query

[June7]

That is exactly what I did. Not sure there would be any other way to accomplish.

So my question - What exactly is coming from SharePoint when you try to pull in table 3 times with each limited to specific ContentType? Because if the datasets are not truly filtered then the result would be the unexpected output you originally described.

[bahalzamon]

Well how I had it was the table brought in 3x and named the tables Disposition, Acquisition and Rehab.
I think the issue I was having is that running a query like this within SQL would have returned the 1 record as I was expecting, but Access seems to run a bit different. =P
So moving forward I will attempt to keep that in mind. =)

I appreciate the help @June7

[June7]

So apparently your original datasets were not filtered.

What do you mean by 'within SQL' - Access is using SQL.

[bahalzamon]

@June7 you are correct, I should have had the Where clause updated to something like the below. =P

WHERE Disposition.[Content Type] Like "*Disposition*" AND Acquisition.[Content Type] Like "*Acquisition*" AND Rehab.[Content Type] Like "*Rehab*";

[June7]

I don't think the AND operator would have given what you want. I doubt any record could meet that criteria. And if you used OR operator then the dataset would have all 3 record types and you would get the same result as originally when self-joining.