Re: Query to extract the latest version of a record

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Chris: Wow! No wonder I couldn't figure this out on my own... All of your
assumptions were correct. Sorry I wasn't more clear. Thanks for the time and
care your took. I will try it out and post back.
--
susan


"Chris2" wrote:

>
> "Susan L" <SusanL@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:A10F5D2B-51D0-4376-95A0-6C10299E4B18@xxxxxxxxxxxxxxxx
> > I have an audit table set up from which I want to extract the
> latest version
> > of a record. Records are marked as Insert, Delete, EditFrom and
> EditTo. The
> > setup is a master form with a subform on which these records are
> entered.
> > Each record on the subform has a unique ID, but in the audit
> table, all
> > "operations" related to that record (Insert,Delete, etc) are
> recorded as
> > separate records containing that record's ID (e.g., 34).
> Therefore, if there
> > are multiple operations, there are multiple records for the
> original record
> > 34. Here's an example:
> > AudType LastRev ID More
> logic fields
> > Insert 12/8/2005 11:46:53 AM 34 -1
> > EditFrom 12/8/2005 12:00:53 AM 34 -1
> > EditTo 12/8/2005 12:00:53 AM 34 -1
> >
> > What I want to do is extract the latest "EditTo"s for all records
> that have
> > been edited and also all "Inserts" that have no EditFrom/EditTo
> records (in
> > other words there is only one instance of its ID number) and .
> There is a
> > date/time stamp as shown above.
> >
> > The SQL below is as far as I have gotten. It has calculated fields
> (of which
> > I only included one) but it doesn't pull the latest version of the
> record?
>
> <snip>
>
> > Using a solution from MSDN, I also tried this WHERE clause, which
> pulled
> > only one EditTo (Max probably is maximum for the column) and now
> doesn't seem
> > to work at all.
>
> <snip>
>
> >
> > I'd sure appreciate some pointers to get the results I'm aiming
> for.
> >
> > --
> > susan
>
>
> Susan L.,
>
>
> The origianl SQL, realigned for readability.
>
> SELECT tbl_Details_EXA_Transport.AudType
> ,tbl_Details_EXA_Transport.EXA_ID
> ,IIf([LO1]=True And [Action]="Enable"
> ,"Y"
> ,IIf([LO1] = True And [Action] = "Disable","N",""))
> AS Calclo1
> FROM tbl_Details_EXA_Transport
> WHERE (tbl_Details_EXA_Transport.AudType = "Insert"
> OR tbl_Details_EXA_Transport.AudType = "EditTo");
>
>
> FROM tbl_Details_EXA_Transport
> WHERE (tbl_Details_EXA_Transport.AudDate =
> (SELECT MAX(tbl_Details_EXA_Transport.AudDate)
> FROM tbl_Details_EXA_Transport
> WHERE (tbl_Details_EXA_Transport.EXA_ID =
> tbl_Details_EXA_Transport.EXA_ID)
> AND (tbl_Details_EXA_Transport.AudType = "EditTo" );
>
> This is somewhat odd, there is a subquery, but no table aliases and
> no correlation. This is definitely a problem.
>
>
>
>
> Without your table structures and desired results, this is only an
> guess:
>
> Tables:
>
> Note: I could not succesfully determine the primary key, so I tossed
> in an AUTONUMBER column to serve for testing purposes. (There is an
> "ID" column in the sample data, but an "EXA_ID" column in the SQL, I
> was thinking these might be the same, except the data under "ID" is
> definitely not unique.) Also, I used "EXA_ID" because it was in the
> SQL, and "ID" was not.
>
> Note: There was a "LastRev" column in the sample data, but an
> "AudDate" column in the SQL. I opted for AudDate as the actual name
> of the column.
>
> Note: I have omitted the "Action" and "L01" columns found in the SQL
> above because those columns were not included in the sample data.
>
>
> CREATE TABLE tbl_Details_EXA_Transport
> (SurrogateID AUTOINCREMENT
> ,AudType TEXT(12)
> ,AudDate DATETIME
> ,EXA_ID INTEGER
> ,CONSTRAINT pk_tbl_Details_EXA_Transport
> PRIMARY KEY (SurrogateID)
> )
>
> Sample Data:
>
> Note: The sample data was insufficient to test. I invented some
> additional rows.
>
> AudType AudDATE EXA_ID
> 1, Insert, 12/08/2005 11:46:53 AM, 34
> 2, EditFrom, 12/08/2005 12:00:53 AM, 34
> 3, EditTo, 12/08/2005 12:00:53 AM, 34
> 4, EditTo, 12/08/2005 00:00:01 AM, 35
> 5, EditTo, 12/09/2005 00:00:02 AM, 35
> 6, Insert, 12/09/2005 00:00:02 AM, 36
> 7, Insert, 12/09/2005 00:00:01 AM, 37
> 8, EditFrom, 12/09/2005 00:00:02 AM, 37
>
>
> Now, let's look at what we want:
>
> > What I want to do is extract the latest "EditTo"s for all records
> that have
> > been edited
>
> This is tough. Doesn't the existence of an "EditTo" audit record
> automatically imply that the associated record "has been edited"?
> In any event, this was what I assumed.
>
>
> > and also all "Inserts" that have no EditFrom/EditTo records (in
> > other words there is only one instance of its ID number) and .
>
> This is also tough. What is an "Insert" that has no
> "EditFrom/EditTo" rows? Does this mean: For each EXA_ID value, find
> only those that have "Insert" AudTypes and no "EditFrom/EditTo"
> AudTypes? This is what I assumed.
>
> Also, from the phrasing, it appears you want both groups of records.
>
> Given the sample date, we should get back rows 3, 5, and 6.
>
> Query:
>
> The table name is lengthy, so I used table aliases. The correlation
> required by the subqueries also makes table aliases mandatory
>
> SELECT T2.SurrogateID
> ,T2.AudType
> ,T2.AudDate
> ,T2.EXA_ID
> FROM (SELECT T01.EXA_ID
> ,MAX(T01.AudDate) AS AudDate
> FROM tbl_Details_EXA_Transport AS T01
> WHERE T01.AudType = "EditTo"
> GROUP BY T01.EXA_ID) AS T1
> INNER JOIN
> (SELECT T02.SurrogateID
> ,T02.AudType
> ,T02.AudDate
> ,T02.EXA_ID
> FROM tbl_Details_EXA_Transport AS T02
> WHERE T02.AudType = "EditTo") AS T2
> ON T1.EXA_ID = T2.EXA_ID
> AND T1.AudDate = T2.AudDate
> UNION
> SELECT T3.SurrogateID
> ,T3.AudType
> ,T3.AudDate
> ,T3.EXA_ID
> FROM tbl_Details_EXA_Transport AS T3
> WHERE (T3.AudType = "Insert"
> AND
> NOT EXISTS
> (SELECT *
> FROM tbl_Details_EXA_Transport AS T03
> WHERE T03.EXA_ID = T3.EXA_ID
> AND T03.AudType
> IN ("EditTo", "EditFrom")))
>
> Results
> 3, EditTo, 12/08/2005 12:00:53 AM, 34
> 5, EditTo, 12/09/2005 00:00:02 AM, 35
> 6, Insert, 12/09/2005 00:00:02 AM, 36
>
>
> This received only minimal testing on minimal data at my end, I
> recommend more testing on your end. :)
>
>
> Sincerely,
>
> Chris O.
>
>
>
.

Relevant Pages

  • Re: Max of Group
    ... my SQL is essentially the same with Chris if you look at the 2 SQL ... The only differences are that I used Aliases and adding ... Mr. Dinh seems to have problems. ...
    (microsoft.public.access.queries)
  • Re: Linking records together
    ... the sample data is too easy. ... We need to track work items across several existing applications. ... SQL> create table event_track( ... SQL> column start_item format a10 ...
    (comp.databases.oracle.misc)
  • Re: Query to extract the latest version of a record
    ... > I have an audit table set up from which I want to extract the ... > What I want to do is extract the latest "EditTo"s for all records ... The origianl SQL, realigned for readability. ... above because those columns were not included in the sample data. ...
    (microsoft.public.access.queries)
  • Re: Help! Grouping in Pivot Table
    ... It might help if you post the SQL of your actual Queries (maybe along with sample data from your Table). ... I have a count of all the records and a field showing the age of the file. ...
    (microsoft.public.access.queries)
  • Re: Selecting rows from a distinct selection of one field
    ... Please post DDL + sample data + desired result. ... > I'm not that new to SQL, but still I can't figure this out. ... > I'm writing some routines to check through IIS logs. ...
    (microsoft.public.sqlserver.programming)