Douglas - I think I have come up with an adequate description of the problem
and strong solution. Tell me what you think:
At the moment your query does track changes but it always uses the BASE
value in the primary table [tblListings] namely:
[tblListings.Status_ID]
Thus, even though my shadow table trackes the changes - [tblAudListings] -
of the above and your query records the
difference between .status_id in the PRIMARY and the SHADOW it ALWAYS
records the LATEST .status_id value from [tblListings] and actually
overwrites each of the new values in your query.
Thus, instead of this:
1 to 2
2 to 3
3 to 4
it becomes
4 to 2
4 to 3
4 to 4
Thus, not being accurate.
So this is what I am thinking and I'm not sure if it is what you mean:
1. I create a new table called audMktStatus
ListingsID
Old_Status_ID
New_Status_ID
ChangeDate
Every time the user edits the listing I drop in a new record with the old
value and the new value and the change date thus assuming the status id
changes from 1 (Availalable) to 2 (Under Offer) fior listing 101 (Ocean
Speed):
ListingsID Old_Status_ID New_Status_ID ChangeDate
101 1 2
09/11/03
If it changes to (3) (Under Contract) then a new record is recorded:
ListingsID Old_Status_ID New_Status_ID ChangeDate
101 1 2
09/11/03
101 2 3
09/13/03
Thus, now I can pull out every change that has been made and even do it by
day. Thus If I wanted to see the changes for today
I would just pull it out by date in a new query based on this table. It
would hunt out any changes for eg today or just pull them
all out for an historical period.....is this valid.\
Thinking ahead I can see small irritations like the fact that each time a
user edits a boat listing she could dump duplicates into
the audMarketStatus table. But, then I thought i could create TRIPLE
PRIMARY KEY on ListingsiD, OldStatus_id, New_Status_ID,
to prevent a duplicate being entered thus keeping the records unique and
clean for only alterations.
I have thought an re-thought this trhough and it seems to work. What do you
think - have I missed anything?
I really appreciate you helping me - this is a big jump for me.
- Jason
Douglas J. Steele said:
In other words, you have multiple changes for a particular Status_ID?
It's a little problematic to have to determine the latest record in your
audListings table: you essentially have to have a subquery that returns the
maximum date for each Status ID, and then join that table to your
audListings table to only return the latest entry.
Have you consider using effective/expiry dates, and keeping everything in
the same table?
Your current record would be the one with an expiry date of Null. Your
previous record would be the one with an expiry date of whatever the
effective date is for the current record.
Contract
Contract
2
Under Offer Venezia 42 1993 $189,000.00
Hi Douglas - for some reason the query is taking the AudDate prior to the
change and not the date on which the record changed. In other words, it
seems to be
to be returning the necessary records (I say seems as it may be
missing
the
very FIRST INSTANCE of changes made to Status_ID) but is not recording the
correct date. I honestly don't know how to fix this:
SELECT tblListings.ListingsID, "Market Status has changed" AS Flag,
tblListings.Name, tblListings.Status_ID, audListings.AudDate,
tblMarketStatus.Market_Status AS MS1, audListings.Status_ID,
tblMarketStatus_1.Market_Status, tblListings.Model_ID, tblListings.Size_ID,
tblListings.Year, tblListings.Original_Price
FROM tblMarketStatus AS tblMarketStatus_1 INNER JOIN ((tblMarketStatus INNER
JOIN tblListings ON tblMarketStatus.MarketStatusID = tblListings.Status_ID)
INNER JOIN audListings ON tblListings.ListingsID = audListings.ListingsID)
ON tblMarketStatus_1.MarketStatusID = audListings.Status_ID
WHERE (((tblListings.Status_ID)<>[audListings].[Status_ID]));
Look at just one part of the query:
SELECT Real.ID, "Field1 Changed", Real.Field1, Shadow.Field1
FROM Real INNER JOIN Shadow ON Real.ID = Shadow.ID
WHERE Real.Field1 <> Shadow.Field1
As you say, that's joining the 2 tables on ID, and it's only showing those
rows where the value for Field1 in the Real table is not equal to the
value
for Field1 in the Shadow table. It returns rows with 4 columns: the
ID
of
the row, a text message saying the Field 1 was changed, and the "after"
and
"before" values for Field 1 (Aside: I really should have structured the
SELECT differently so that the description was more logical! <g>)
We then look for all rows where the value for Field2 in the Real
table
is
not equal to the value for Field2 in the Shadow table, and add it to the
rows we already retrieved.
What you'll get if more than one field changed in a given row is multiple
rows back:
123 Field1 Changed ABC DEF
123 Field2 Changed XYZ STU
shows you that for record 123, 2 fields changed: Field1 changed from DEF
to
ABC, and Field2 changed from STU to XYZ.
My comment about different data types was for the situation where Field1
is
text, but Field2 is numeric. When you UNION queries together, all the
subselects must have the same number of fields, and, for a given position
in
the subselect, each field must be of the same type.
--
Doug Steele, Microsoft Access MVP
Wow, Douglas - this could be the answer I am searching for but
your
sql
is
way advanced than mine and I am not famailiar with the UNION clause.
Ok - fine - the join query connects the fields of choice. But, I still
don't
understand what delivers/triggers the changed field for display.
You seem to be joining the fields and saying that if my eg: Real Field
is
anyway different from the shadow field then do what? Show the contents
of
the real field or the contents of the shadow field or show
both...this
I
find confusing...
Further, would my shadow data and real data not always been the same
data
type - why would they differ?
Thanks
Jason
One way is to write a number of UNION queries that each join the
"real"
table and the "shadow" table on their common key, and compare one
field.
SELECT Real.ID, "Field1 Changed", Real.Field1, Shadow.Field1
FROM Real INNER JOIN Shadow ON Real.ID = Shadow.ID
WHERE Real.Field1 <> Shadow.Field1
UNION
SELECT Real.ID, "Field2 Changed", Real.Field2, Shadow.Field2
FROM Real INNER JOIN Shadow ON Real.ID = Shadow.ID
WHERE Real.Field2 <> Shadow.Field2
UNION
SELECT Real.ID, "Field3 Changed", Real.Field3, Shadow.Field3
FROM Real INNER JOIN Shadow ON Real.ID = Shadow.ID
WHERE Real.Field3 <> Shadow.Field3
etc.
Of course, this'll only work when Field1, Field2 and Field3 are all
the
same
type, so you could use Format to convert numeric or date fields to
strings.
--
Doug Steele, Microsoft Access MVP
Hi Douglas,
Actually, if I understand you correctly, my AUDIT table is in
fact
a
'SHADOW' table as it trackers the user, ip and date of the insert,
edit
or
deletion to a record but also inserts the entire range of
fields
for
any
user edit, deletion or insert thus I can compare the original table
row
to
the edited, deleted or added row in my audit table.
BUT
I am still not sure how to generate a report which compares
the
two
records
and only pulls out the data that has changed eg: Price Change....
Am i missing something here?
Thanks
Jason
If you need the details of exactly what was changed, one approach
is
to
add
a "shadow table" that's identical to the table being
changed,
