MGFoster, I can not thank you enough for taking the time. I hope that you
had a wonderful Easter Weekend. I apologize for not getting back sooner, but
I was out of town celebrating with family.
First, I want to answer the questions that you had from your previous post.
The data itself comes from the servers of a different database. I have no
control over the data entry or structure, I am simply an end user. The
MLSListNumber is the primary key for my table. The MLS (Multiple Listing
Service) keeps and tracks records from thousands of users, for hundreds of
thousands of properties listed each year. One thing to keep in mind about
the MLS is that a user has the ability to enter whatever information they
want, whenever they want, and however they want. In fact, they can enter the
same information twice and sometimes they do that on the same day. There are
rules and regulations in place and that is how we end up catching most of the
problems, but once a record is entered and assigned an MLS Listing Number, it
is seldom deleted. That is how we end up with properties that are Cancelled
and Active, but both listed on the same day. Properties can be listed and
bought, cancelled, expired and sold multiple times all within the same year.
We are tracking more the transaction rather than just the one property.
There are many significant date columns, in total I think we track 5-6. I
have simply chosen to use the listing date, it seems to be the one unified
date that shows up in all records. All properties are obviously listed,
however, an active property does not have a sold date, off market date, etc.
There is a date that I might contemplate using that (status date) refers to
the date (and time, something most other dates don't have attached) the
property changed status.
I believe that I may have come up with a more complicated work around. I
have used three seperate queries to filter the data that I want.
1. The first query finds all of the property records were the PID# is the
same (a duplicate record query).
2. The second uses the total column in the query design wizard to select
the Maximum list date, and since MLSListNumber is sequential (ever increasing
in order) it uses the total column to select Maximum.
3. The third query is a union query where my second query is outer joined
back to the main table by the MLSListNumber (my primary key). The first part
of the query selects only those records that are "Expired or Cancelled." The
second part of the query a having count(*)=1 statement and then selects only
those records that are of the correct status.
Some in depth testing this weekend has shown to select all and only those
records that I want, but I would like to get your opinion on the setup and if
there isn't a more effecient way to accomplish the same thing. I would also
like to post a new question (slightly related, but no time now). Would you
like to see it in a new thread or should I tack it onto my next post.
Thanks again for the help, and I would welcome the comments of anyone that
may have something constructive to point out.
:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Well, it looks like I don't understand your specifications. Just
relying on your posted query I'd say I need to know what the Primary Key
(PK) is. It also seems wierd to me that a property would be listed,
MLSListDate, more than once and, especially, with the same date. I.e.,
How can a status be both Expired and Cancelled on the same date?! Is
there another significant Date column? What is the MLSListNumber?
Here's another stab at it. It will probably return multiple records
when the dates are the same. That's why I need to know the PK - please,
let it NOT be an AutoNumber column.
SELECT MLSListNumber,
MLSListDate,
MLSPropertyIDNumber,
MLSStatus,
[MLSHouseNumber] & " " & ([MLSStreetDirectionPrefix]+" ") &
[MLSStreetName] & (" Unit "+[MLSUnitNumber]) & (", "+ [MLSCity])
& (", "+[MLSState]) & " " & [MLSZipCode] AS Address
FROM tblMLS As M
WHERE MLSListDate =
(SELECT Max(MLSListDate)
From tblMLS
WHERE MLSPropertyIDNumber = M.MLSPropertyIDNumber
AND MLSStatus In ('Expired','Cancelled'))
Order by MLSHouseNumber;
BTW, you don't have to prefix the column names w/ the table's name, it
just makes them harder to read.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQkR1F4echKqOuFEgEQJ09QCgwPd3x61OflgHXH9IXtYoc/gUBrIAn0ij
tuG2wiNKXYSlGCNEivVmKUtZ
=vEKT
-----END PGP SIGNATURE-----
MGFoster, thank you once again for taking the time to help me on this matter,
I truely do appreciate it. I did take your recommendation and used the union
query instead of union all. However, I am still coming up with duplicate
records. It might be important for me to mention that PID is not my Primary
Key in this table. There are and will continue to be multiple records with
the same PID.
To restate my dilema (hopefully in a clearer manner):
I have a table that contains records of property. It may contain multiple
records of the same property. I would like to sort out and find all the
records that are either (X,Y) status. The database is constantly being
updated, so I need to sort out only those records that currently are in that
status.
Two possible scenarios to address would be those properties that have only
one entry, and currently meet the appropriate status criteria. Second, those
properties that have more than one entry. There could be 4-5 entries for the
same Property Identifacation Number (PID). For those properties that have
more than one entry, I need to find only those records where the most recent
(based on a date field) is of the appropriate status, and I need it to return
only one record per PID.
I have gotten the query (see my last post in this thread) to work for the
most part. However, I am finding that if the property is of the appropriate
status but the date field is the same as another record of the appropriate
status, it will show both records in my query. I am also finding that if the
property isn't of the appropriate status, but the date field is the same as
one or two other records that are of the appropriate status, all records will
show up in the query. Even one's that aren't the correct status.
Anyone's thoughts are greatly appreciated.
