Query to extract the latest version of a record

G

Guest

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?
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
'I removed a number of fields like LO1 above to simplify
FROM tbl_Details_EXA_Transport
WHERE (((tbl_Details_EXA_Transport.AudType)="Insert" Or
(tbl_Details_EXA_Transport.AudType)="EditTo"));

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.
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"));

I'd sure appreciate some pointers to get the results I'm aiming for.
 
C

Chris2

Susan L said:
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?

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.

I'd sure appreciate some pointers to get the results I'm aiming for.


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.
 
G

Guest

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 said:
Susan L said:
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?

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.

I'd sure appreciate some pointers to get the results I'm aiming for.


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.
 
C

Chris2

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 L.,

<crossing my fingers />


Sincerely,

Chris O.
 
G

Guest

I don't think crossing your fingers was necessary. It worked perfectly first
time out! Now, though, I need to add my other data to the query (the "meat"
of the query) and am having some trouble.

Here's what the rest of my data is supposed to look like (using your sample
data -- AudID is your "SurrogateID"): the LO1, CSI, MSC are logic fields
(there are 7 in all). I must translate the -1,0 to Y, N, or blank, depending
on the action selected. Notes is a field that does not need manipulation. I
have a query that works successfully doing the translation; now I need to
incorporate into what you created.

AudID AudType AudDATE EXA_ID Notes Action LO1 CSI
MSC
1, Insert, 12/08/2005 11:46:53 AM, 34 note Enable Y
Y blank
2, EditFrom, 12/08/2005 12:00:53 AM, 34 Enable Y
Y blank
3, EditTo, 12/08/2005 12:00:53 AM, 34 a note 1 Enable Y Y
Y
4, Insert, 12/08/2005 00:00:01 AM, 35 Disable blank
blank N
5, EditFrom,12/09/2005 00:00:02 AM, 35 Disable blank
blank N
6, EditTo, 12/09/2005 00:00:02 AM, 35 Disable N
N N
7, Insert, 12/09/2005 00:00:02 AM, 36 Enable Y
Y Y
8, Insert, 12/09/2005 00:00:01 AM, 37 a note 2 Disable N N
blank
9, Delete 12/10/2005 00:00:01 AM, 35 Enable Y
Y Y

Question 1: Where do I add my other fields and Iif statements to
pull/translate the other data?

Using your SQL, I added these additional fields to your select statements,
adding T2, T02, etc. to preceed the field name. Must be doing something
wrong, as I get an error message: "Select statement includes a reserved word
or an argument name that is mispelled or missing, or the punctuation is
incorrect." I also tried it with thte field LO1 explictly stated after the
Notes field, with same result. (The "translation" query works with just the
calculation aliases.)

Here's the SQL: (Only included one of the logic fields for brevity.)
SELECT T2.AudID
,T2.AudType
,T2.AudDate
,T2.EXA_ID
,T2.Action,
,T2.Notes
,IIf([T2.LO1]=True And [Action]="Enable","Y",IIf([T2.LO1]=True And
[Action]="Disable","N","")) AS Calclo1
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.AudID
,T02.AudType
,T02.AudDate
,T02.EXA_ID
,T02.Action,
,T02.Notes
,IIf([T02.LO1]=True And [Action]="Enable","Y",IIf([T02.LO1]=True And
[Action]="Disable","N","")) AS Calclo1
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.AudID
,T3.AudType
,T3.AudDate
,T3.EXA_ID
,T3.Action,
,T3.Notes
,IIf([T3.LO1]=True And [Action]="Enable","Y",IIf([T3.LO1]=True And
[Action]="Disable","N","")) AS Calclo1
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")));

Question 2: I have another audit table for a form in which users will have
Delete privileges. (No delete privileges on the table above.) Your SQL will
work beautifully for that table as well, except I need to be able to also
pull out Deletes. Does this mean creating a 4th alias? I don't have a clue as
to where to add it to the SQL you wrote. Can you assist me there as well?
 
C

Chris2

Susan L.,

I am working on it. However, it is taking some time. I'm off to
work early this morning, so I'll try to squeeze in an hour or two
after getting home.

You posted your desired results (thank you!).

However, it contains calculated columns based on sample data (for
the L01, CSI, and MSC columns).

I'm going to have to reverse engineer the original 0/-1 values
myself this afternoon in order to figure this out.

Is there any chance you might post them for me?


Sincerely,

Chris O.
 
G

Guest

Chris: My apologies for the complexity and time, but deeply appreciate your
help. Below is a list with values from the Yes/No field. Of course, the
Yes/No field shows a check for yes and a blank for no, which I can't show
here. Perhaps I complicated matters with -1 (Yes), 0 (No), which I found
somewhere in Help, I think.

Since my current "translation" query works with "True," then True/False as
values work. This works: "IIf([T02.LO1]=True And
[Action]="Enable","Y",IIf([T02.LO1]=True And [Action]="Disable","N","")) AS
Calclo1".

So here's the data with the original values expressed as T, F. As
background, this data from Access will be used to update an Oracle database,
which uses Y for "enabled", N for "not enabled," hence the need for a
"translation." We need the blanks to indicate that there is no change in that
field. Hope the data below clarifies.

AudID AudType AudDATE EXA_ID Notes Action LO1 CSI MSC
1, Insert, 12/08/2005 11:46:53 AM, 34, note, Enable, Y(T), Y(T), blank(F)
2, EditFrom, 12/08/2005 12:00:53 AM, 34, Enable, Y(T), Y(T), blank(F)
3, EditTo, 12/08/2005 12:00:53 AM, 34, note1, Enable, Y(T), Y(T), Y(T)
4, Insert, 12/08/2005 00:00:01 AM, 35, Disable, blank(F),
blank(F), N(T)
5, EditFrom,12/09/2005 00:00:02 AM, 35, Disable, blank(F),
blank(F), N(T)
6, EditTo, 12/09/2005 00:00:02 AM, 35, Disable, N(T), N(T), N(T)
7, Insert, 12/09/2005 00:00:02 AM, 36, Enable, Y(T), Y(T), Y(T)
8, Insert, 12/09/2005 00:00:01 AM, 37,note2, Disable, N(T), N(T), blank(F)
9, Delete 12/10/2005 00:00:01 AM, 35, Enable Y(T), Y(T), Y(T)
 
C

Chris2

Susan L said:
Chris2 said:
Susan L.,

I am working on it. However, it is taking some time. I'm off to
work early this morning, so I'll try to squeeze in an hour or two
after getting home.

You posted your desired results (thank you!).

However, it contains calculated columns based on sample data (for
the L01, CSI, and MSC columns).

I'm going to have to reverse engineer the original 0/-1 values
myself this afternoon in order to figure this out.

Is there any chance you might post them for me?


Sincerely,

Chris O.
appreciate your
help. Below is a list with values from the Yes/No field. Of course, the
Yes/No field shows a check for yes and a blank for no, which I can't show
here. Perhaps I complicated matters with -1 (Yes), 0 (No), which I found
somewhere in Help, I think.

Since my current "translation" query works with "True," then True/False as
values work. This works: "IIf([T02.LO1]=True And
[Action]="Enable","Y",IIf([T02.LO1]=True And [Action]="Disable","N","")) AS
Calclo1".

So here's the data with the original values expressed as T, F. As
background, this data from Access will be used to update an Oracle database,
which uses Y for "enabled", N for "not enabled," hence the need for a
"translation." We need the blanks to indicate that there is no change in that
field. Hope the data below clarifies.

AudID AudType AudDATE EXA_ID Notes Action LO1 CSI MSC
1, Insert, 12/08/2005 11:46:53 AM, 34, note, Enable, Y(T), Y(T), blank(F)
2, EditFrom, 12/08/2005 12:00:53 AM, 34, Enable, Y(T), Y(T), blank(F)
3, EditTo, 12/08/2005 12:00:53 AM, 34, note1, Enable, Y(T), Y(T), Y(T)
4, Insert, 12/08/2005 00:00:01 AM, 35, Disable, blank(F),
blank(F), N(T)
5, EditFrom,12/09/2005 00:00:02 AM, 35, Disable, blank(F),
blank(F), N(T)
6, EditTo, 12/09/2005 00:00:02 AM, 35, Disable, N(T), N(T), N(T)
7, Insert, 12/09/2005 00:00:02 AM, 36, Enable, Y(T), Y(T), Y(T)
8, Insert, 12/09/2005 00:00:01 AM, 37,note2, Disable, N(T), N(T), blank(F)
9, Delete 12/10/2005 00:00:01 AM, 35, Enable Y(T), Y(T), Y(T)

Susan L.,

Tables:

ALTER TABLE tbl_Details_EXA_Transport
ADD COLUMN Notes TEXT(255)

ALTER TABLE tbl_Details_EXA_Transport
ADD COLUMN Action TEXT(7)

ALTER TABLE tbl_Details_EXA_Transport
ADD COLUMN L01 BIT

ALTER TABLE tbl_Details_EXA_Transport
ADD COLUMN CSI BIT

ALTER TABLE tbl_Details_EXA_Transport
ADD COLUMN MSC BIT

I manually altered SurrogateID to AudID in the GUI (because MS
Access does not support ALTER TABLE <table-name> RENAME COLUMN
<old-col-name> TO <new-col-name>).



Sample Data:

AudID, AudType, AudDATE, EXA_ID, Notes, Action, LO1, CSI, MSC
1, Insert, 12/08/2005 11:46:53 AM, 34, note, Enable, -1, -1, 0
2, EditFrom, 12/08/2005 12:00:53 AM, 34, , Enable, -1, -1, 0
3, EditTo, 12/08/2005 12:00:53 AM, 34, note1, Enable, -1, -1, -1
4, Insert, 12/08/2005 00:00:01 AM, 35, , Disable, 0, 0, -1
5, EditFrom, 12/09/2005 00:00:02 AM, 35, , Disable, 0, 0, -1
6, EditTo, 12/09/2005 00:00:02 AM, 35, , Disable, -1, -1, -1
7, Insert, 12/09/2005 00:00:02 AM, 36, , Enable, -1, -1, -1
8, Insert, 12/09/2005 00:00:01 AM, 37, note2, Disable, -1, -1, 0
9, Delete, 12/10/2005 00:00:01 AM, 35, , Enable, -1, -1, -1


Desired Results:

AudID, AudType, AudDATE, EXA_ID, Notes, Action, LO1, CSI, MSC
3, EditTo, 12/08/2005 12:00:53 AM, 34, note1, Enable, Y, Y, Y
6, EditTo, 12/09/2005 00:00:02 AM, 35, , Disable, N, N, N
7, Insert, 12/09/2005 00:00:02 AM, 36, , Enable, Y, Y, Y
8, Insert, 12/09/2005 00:00:01 AM, 37, note2, Disable, N, N, blank


Query:

In the query you produced, there are three extra commas after the
three appearances of Action. I fixed this below.

Also, there is invalid bracketing in the IIF statments for L01. []
are placed around the column name only, not the table alias name. I
fixed this below.

Also, in the L01 column, L zero 1 was typed as L oh 1. There is a
difference between "zero" and "oh". I fixed this below.

Also, L01 was not carried to the outer table references. The inner
IIF statements in the top "T2" table were creating an artificial
column name using column aliases of "CalcL01", except that the outer
table references were referring to a column named L01 (that didn't
exist because the inner table was creating CalcL01, instead). I
canged the column aliases on the IIF functions to L01.

I also duplicated the IIF functions for CSI and MSC.

I am also fairly sure that the outer IIF in T2 is unnecessary (the
inner IIF already did that).

I also removed the [], which were unnecessary.


SELECT T2.AudID
,T2.AudType
,T2.AudDate
,T2.EXA_ID
,T2.Action
,T2.Notes
,T2.L01
,T2.CSI
,T2.MSC
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.AudID
,T02.AudType
,T02.AudDate
,T02.EXA_ID
,T02.Action
,T02.Notes
,IIf(T02.L01=True And Action="Enable"
,"Y"
,IIf(T02.L01=True And Action="Disable"
,"N"
,"")) AS L01
,IIf(T02.CSI=True And Action="Enable"
,"Y"
,IIf(T02.CSI=True And Action="Disable"
,"N"
,"")) AS CSI
,IIf(T02.MSC=True And Action="Enable"
,"Y"
,IIf(T02.MSC=True And Action="Disable"
,"N"
,"")) AS MSC
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.AudID
,T3.AudType
,T3.AudDate
,T3.EXA_ID
,T3.Action
,T3.Notes
,IIf(T3.L01=True And Action="Enable"
,"Y"
,IIf(T3.L01=True And Action="Disable"
,"N"
,"")) AS L01
,IIf(T3.CSI=True And Action="Enable"
,"Y"
,IIf(T3.CSI=True And Action="Disable"
,"N"
,"")) AS CSI
,IIf(T3.MSC=True And Action="Enable"
,"Y"
,IIf(T3.MSC=True And Action="Disable"
,"N"
,"")) AS MSC
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:

AudID, AudType, AudDATE, EXA_ID, Action, Notes, LO1, CSI, MSC
3, EditTo, 12/08/2005 12:00:53 AM, 34, Enable, note1, Y, Y, Y
6, EditTo, 12/09/2005 00:00:02 AM, 35, Disable, , N, N, N
7, Insert, 12/09/2005 00:00:02 AM, 36, Enable, , Y, Y, Y
8, Insert, 12/09/2005 00:00:01 AM, 37, Disable, note2, N, N, blank


Well, that appears to be working.

Let me know how it goes. :)


Sincerely,

Chris O.
 
G

Guest

Chris: I hope you did not have to spend hours on this. But indeed, it does
work beautifully, after a couple of glitches adding my other fields. It's
just wonderful. Monday am I am going to try writing the SQL to pull Deletes
from one of my other tables. If I'm not presuming too much (and I feel as if
I already have...), could I post to this thread to ask you to review the code
if I can't get it to work?

If not, I'll understand. In the meantime, what you've done to assist me has
really been invaluable. I know I couldn't have learned how to structure this
SQL in the timeframe I have. Thanks for lending your expertise.
--
susan


Chris2 said:
Susan L said:
Chris2 said:
Susan L.,

I am working on it. However, it is taking some time. I'm off to
work early this morning, so I'll try to squeeze in an hour or two
after getting home.

You posted your desired results (thank you!).

However, it contains calculated columns based on sample data (for
the L01, CSI, and MSC columns).

I'm going to have to reverse engineer the original 0/-1 values
myself this afternoon in order to figure this out.

Is there any chance you might post them for me?


Sincerely,

Chris O.


Chris: My apologies for the complexity and time, but deeply
appreciate your
help. Below is a list with values from the Yes/No field. Of course, the
Yes/No field shows a check for yes and a blank for no, which I can't show
here. Perhaps I complicated matters with -1 (Yes), 0 (No), which I found
somewhere in Help, I think.

Since my current "translation" query works with "True," then True/False as
values work. This works: "IIf([T02.LO1]=True And
[Action]="Enable","Y",IIf([T02.LO1]=True And [Action]="Disable","N","")) AS
Calclo1".

So here's the data with the original values expressed as T, F. As
background, this data from Access will be used to update an Oracle database,
which uses Y for "enabled", N for "not enabled," hence the need for a
"translation." We need the blanks to indicate that there is no change in that
field. Hope the data below clarifies.

AudID AudType AudDATE EXA_ID Notes Action LO1 CSI MSC
1, Insert, 12/08/2005 11:46:53 AM, 34, note, Enable, Y(T), Y(T), blank(F)
2, EditFrom, 12/08/2005 12:00:53 AM, 34, Enable, Y(T), Y(T), blank(F)
3, EditTo, 12/08/2005 12:00:53 AM, 34, note1, Enable, Y(T), Y(T), Y(T)
4, Insert, 12/08/2005 00:00:01 AM, 35, Disable, blank(F),
blank(F), N(T)
5, EditFrom,12/09/2005 00:00:02 AM, 35, Disable, blank(F),
blank(F), N(T)
6, EditTo, 12/09/2005 00:00:02 AM, 35, Disable, N(T), N(T), N(T)
7, Insert, 12/09/2005 00:00:02 AM, 36, Enable, Y(T), Y(T), Y(T)
8, Insert, 12/09/2005 00:00:01 AM, 37,note2, Disable, N(T), N(T), blank(F)
9, Delete 12/10/2005 00:00:01 AM, 35, Enable Y(T), Y(T), Y(T)

Susan L.,

Tables:

ALTER TABLE tbl_Details_EXA_Transport
ADD COLUMN Notes TEXT(255)

ALTER TABLE tbl_Details_EXA_Transport
ADD COLUMN Action TEXT(7)

ALTER TABLE tbl_Details_EXA_Transport
ADD COLUMN L01 BIT

ALTER TABLE tbl_Details_EXA_Transport
ADD COLUMN CSI BIT

ALTER TABLE tbl_Details_EXA_Transport
ADD COLUMN MSC BIT

I manually altered SurrogateID to AudID in the GUI (because MS
Access does not support ALTER TABLE <table-name> RENAME COLUMN
<old-col-name> TO <new-col-name>).



Sample Data:

AudID, AudType, AudDATE, EXA_ID, Notes, Action, LO1, CSI, MSC
1, Insert, 12/08/2005 11:46:53 AM, 34, note, Enable, -1, -1, 0
2, EditFrom, 12/08/2005 12:00:53 AM, 34, , Enable, -1, -1, 0
3, EditTo, 12/08/2005 12:00:53 AM, 34, note1, Enable, -1, -1, -1
4, Insert, 12/08/2005 00:00:01 AM, 35, , Disable, 0, 0, -1
5, EditFrom, 12/09/2005 00:00:02 AM, 35, , Disable, 0, 0, -1
6, EditTo, 12/09/2005 00:00:02 AM, 35, , Disable, -1, -1, -1
7, Insert, 12/09/2005 00:00:02 AM, 36, , Enable, -1, -1, -1
8, Insert, 12/09/2005 00:00:01 AM, 37, note2, Disable, -1, -1, 0
9, Delete, 12/10/2005 00:00:01 AM, 35, , Enable, -1, -1, -1


Desired Results:

AudID, AudType, AudDATE, EXA_ID, Notes, Action, LO1, CSI, MSC
3, EditTo, 12/08/2005 12:00:53 AM, 34, note1, Enable, Y, Y, Y
6, EditTo, 12/09/2005 00:00:02 AM, 35, , Disable, N, N, N
7, Insert, 12/09/2005 00:00:02 AM, 36, , Enable, Y, Y, Y
8, Insert, 12/09/2005 00:00:01 AM, 37, note2, Disable, N, N, blank


Query:

In the query you produced, there are three extra commas after the
three appearances of Action. I fixed this below.

Also, there is invalid bracketing in the IIF statments for L01. []
are placed around the column name only, not the table alias name. I
fixed this below.

Also, in the L01 column, L zero 1 was typed as L oh 1. There is a
difference between "zero" and "oh". I fixed this below.

Also, L01 was not carried to the outer table references. The inner
IIF statements in the top "T2" table were creating an artificial
column name using column aliases of "CalcL01", except that the outer
table references were referring to a column named L01 (that didn't
exist because the inner table was creating CalcL01, instead). I
canged the column aliases on the IIF functions to L01.

I also duplicated the IIF functions for CSI and MSC.

I am also fairly sure that the outer IIF in T2 is unnecessary (the
inner IIF already did that).

I also removed the [], which were unnecessary.


SELECT T2.AudID
,T2.AudType
,T2.AudDate
,T2.EXA_ID
,T2.Action
,T2.Notes
,T2.L01
,T2.CSI
,T2.MSC
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.AudID
,T02.AudType
,T02.AudDate
,T02.EXA_ID
,T02.Action
,T02.Notes
,IIf(T02.L01=True And Action="Enable"
,"Y"
,IIf(T02.L01=True And Action="Disable"
,"N"
,"")) AS L01
,IIf(T02.CSI=True And Action="Enable"
,"Y"
,IIf(T02.CSI=True And Action="Disable"
,"N"
,"")) AS CSI
,IIf(T02.MSC=True And Action="Enable"
,"Y"
,IIf(T02.MSC=True And Action="Disable"
,"N"
,"")) AS MSC
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.AudID
,T3.AudType
,T3.AudDate
,T3.EXA_ID
,T3.Action
,T3.Notes
,IIf(T3.L01=True And Action="Enable"
,"Y"
,IIf(T3.L01=True And Action="Disable"
,"N"
,"")) AS L01
,IIf(T3.CSI=True And Action="Enable"
,"Y"
,IIf(T3.CSI=True And Action="Disable"
,"N"
,"")) AS CSI
,IIf(T3.MSC=True And Action="Enable"
,"Y"
,IIf(T3.MSC=True And Action="Disable"
,"N"
,"")) AS MSC
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:

AudID, AudType, AudDATE, EXA_ID, Action, Notes, LO1, CSI, MSC
3, EditTo, 12/08/2005 12:00:53 AM, 34, Enable, note1, Y, Y, Y
6, EditTo, 12/09/2005 00:00:02 AM, 35, Disable, , N, N, N
7, Insert, 12/09/2005 00:00:02 AM, 36, Enable, , Y, Y, Y
8, Insert, 12/09/2005 00:00:01 AM, 37, Disable, note2, N, N, blank


Well, that appears to be working.

Let me know how it goes. :)


Sincerely,

Chris O.
 
C

Chris2

Susan L said:
Chris: I hope you did not have to spend hours on this. But indeed, it does
work beautifully, after a couple of glitches adding my other fields. It's
just wonderful. Monday am I am going to try writing the SQL to pull Deletes
from one of my other tables. If I'm not presuming too much (and I feel as if
I already have...), could I post to this thread to ask you to review the code
if I can't get it to work?

If not, I'll understand. In the meantime, what you've done to assist me has
really been invaluable. I know I couldn't have learned how to structure this
SQL in the timeframe I have. Thanks for lending your expertise.

Susan L.,

I will be watching.


Time spent?

From the second set of information, let's see.

45 minutes, 10 minutes, 20 minutes.

When I wrote the short note requesting the sample data for the
second round, I hadn't yet realized that first chart presented in
the second round was part sample data, and part desired results, and
that the AudTypes had changed. Basically, I was running the
debugged query, and I wasn't getting the same rows back as I had
originally in my first answer. That threw me for a loop, and I
could not determine what was going on until I did a line by line
check of the new sample data and found that the AudTypes had
changed.



The following web page can be a great help, if you read and
understand it thoroughly.

Although meant for an SQL Server newsgroup, the
following link is still applicable for MS Access:
http://www.aspfaq.com/etiquette.asp?id=5006, and
is excellent when it comes to detailing how to
provide the information that will best enable
others to answer your questions.



Yet More Information:

-------------------------------

Formatting:

Please use a monospace font (Courier New, etc.) when writing out
your examples (all descriptions, charts, SQL, etc.).

-------------------------------

Process Description:

Please only include the shortest possible narrative of what is going
on with the query. (Include all that is necessary, and nothing
more.)

When parts of your query make calculations, show the exact code or
nearest readable plain-text math formula you can create.

When you are done with this section, re-read it several times before
posting to assure yourself that you are accurately describing the
situation in way you believe others will understand.

-------------------------------

Table Structures/Description:

Post a description of your table structures . . . (The below are
only descriptions, and are not exact copies of DDL syntax!)

Although it can be a source of information, please do not copy and
paste information directly from MS Access' Documenter. It is
virtually unreadable. Please distill down and legibly format only
the relevant table information.

If reading the information in MS Access' Documenter is too
intimidating (I know what its output says, myself, and I still
dislike going over its output listings), open your table in Design
View, view the column names and data types in it, and then type out
the column names and data types *that are necessary* (do not include
columns that are not absolutely necessary for the query). Use the
Index dialog box (you can get at it by clicking on the "key" icon on
the toolbar) to locate information on primary and foreign keys and
other indexes and type out that information, as well.

Note: For table descriptions (or DDL) lining up the column names,
data type names, and key/index information in neat columns is quite
helpful.

Note: If you know how to write DDL SQL (CREATE TABLE), please post
that (including constraints) instead of text descriptions. (Please
post only the portion of the DDL that is relevant.)


Example (text description):

MyTableOne
MyTableOneID AUTOINCREMENT PK
ColTwo INTEGER NOT NULL DEFAULT 0
ColThree TEXT(10)

MyTableTwo
MyTableTwoID AUTOINCREMENT PK\
MyTableOneID INTEGER PK/-- Composite Primary Key
ColThree INTEGER FK -- MyTableOne ColOne
ColFour DATETIME
ColFive CURRENCY
ColSix BIT
ColSeven TEXT(1)
ColEight TEXT(1)

etc., etc., etc.


Example (DDL SQL/CREATE TABLE):

CREATE TABLE MyTableOne
(MyTableOneID AUTOINCREMENT
,ColTwo INTEGER
,ColThree TEXT(10)
,CONSTRAINT pk_MyTableOne
PRIMARY KEY (MyTableOneID)
)

CREATE TABLE MyTableTwo
(MyTableTwoID AUTOINCREMENT
,MyTableOneID INTEGER
,ColThree INTEGER
,ColFour DATETIME
,ColFive CURRENCY
,ColSix BIT
,ColSeven TEXT(1)
,ColEight TEXT(1)
,CONSTRAINT pk_MyTableTwo
PRIMARY KEY (MyTableTwoID)
,CONSTRAINT fk_MyTableTwo_MyTableOne_MyTableOneID
FOREIGN KEY (MyTableOneID)
REFERENCES MyTableOne (MyTableOneID)
)

The Primary Key and Foreign Key notes (or constraints in the DDL
SQL) are *critical*.

-------------------------------

Sample Data (using comma delimited lists):

Note: If your sample data is "wide" across the screen, and you can't
trim out any columns because they are needed, make *two* (or more)
charts, and then clearly note that the second chart is the
continuation of the first chart for the same table. It is far
easier to convert a comma delimited chart into a table in MS Word or
import it directly into MS Excel (where the data can be copied and
pasted into a new table in MS Access) or even MS Access than it is
to manually undo the line-break on *every* row of a line-wrapped
chart (in fact, manually undoing the line-breaks caused by newsgroup
posting is a huge pain in the neck).

Note: In a comma delimited list, it is not absolutely necessary
(although it is nice) to have the data in the columns lined straight
up and down, like I have in my examples below. When the data is
finally imported into MS Access, a quick glance at the table in
datasheet view will show things lined up straight. It is not
necessary to expend extra effort on your chart here. (The right
data does have to be in the right position of each row of the chart,
of course.)

Note: Use the real table and column names whenever possible. Use
invented table names and column names (like I use below in my
example) only when you absolutely have to.

Note: When naming the columns on this chart, use the same column
names as is the table structures above. Using shortened names may
save space and prevent line-wraps, but it can be severely confusing.
Make two (or more) charts if you have to, as noted above.

Note: Please include just enough rows of sample data so that
sufficient tests of the various possibilities ("test cases") can be
made.

Note: Please do not attempt to post endless rows of data. 3-5 rows
are probably the minimum, and 10-20 row are probably the maximum.
(Post only what is necessary, and no more.)

Note: Please try and use real data when possible. Real people's
personal information, or private information (banking, proprietary,
etc.), should never be posted. When you have information that
cannot be posted, you will have to invent test data that can produce
results similar to what the real data would produce.


MyTableOne
MyTableOneID, ColTwo, ColThree
1, 2, a
3, 4, b
5, 6, c

MyTableTwo (Part One)
MyTableTwoID, MyTableOneID, ColThree, ColFour, ColFive
1, 5, 1, 01/01/06, 1.01


MyTableTwo (Part Two)
ColSix, ColSeven, ColEight
-1 g, h

-------------------------------

Desired Results

.. . . <whatever it is you want your query to produce; "the right
stuff", if you will forgive the pun>

(Same chart style as for sample data.)

-------------------------------

Query:

Your SQL query code attempts to date. (If "SQL code" throws you for
a loop, open your Query in Design View, and then use the menus, View
SQL View, to switch to a window that will show the SQL code. Copy
and paste that into your new post to the newsgroup.)

Note: There is a huge temptation to merely copy and past the SQL
code. Usually, this is completely unreadable, and whoever reads it
must re-align the code in order to make heads or tails of it (yes,
there are a few out there who can read endless unbroken streams of
code packed together, but I am not one of them). If you know how,
spend some time straightening out and aligning the SQL before
posting it.

Note: In some situations, of course, you will have no query or SQL
code at all.

-------------------------------

Current Results:

.. . . <the incorrect results the current query(s) is producing>

(Same chart style as for sample data.)

-------------------------------

Lots Of Work:

Does all this sound like a lot of work?

Remember, whatever work you haven't done will have to be done by
whoever tries to answer your question.

Any information that is not included may have to be asked for,
necessitating additional posts (sometimes many) before someone can
begin answering your question.

Time spent doing these things is time spent not answering your
question.


Sincerely,

Chris O.
 
G

Guest

I owe you more thanks. I did not know how to properly post questions on
queries. Have often been perplexed about how best to express/post my
questions. I'll do my homework before posting again. Thanks!
 
G

Guest

Chris: I was able to do the SQL to extract the "Deletes" from the audit
table, using an IN clause in your last WHERE clause. WHERE (T3.AudType IN (
"Insert", "Delete").
Thanks ever(!) so much for your help and info on posting. Will do a better
job from now on...
 
C

Chris2

Susan L said:
Chris: I was able to do the SQL to extract the "Deletes" from the audit
table, using an IN clause in your last WHERE clause. WHERE (T3.AudType IN (
"Insert", "Delete").
Thanks ever(!) so much for your help and info on posting. Will do a better
job from now on...

Susan L.,

As ever, I was glad to be of assistance.

I thank you for your comments. So many of the answers given here go
without any kind of response, and it's always good to hear what
happened.


Sincerely,

Chris O.
 
G

Guest

Chris: I was not going to answer your last post for fear of "littering' your
e-mail and extending this post. Your comment was important, however. I always
research before I post a question, and I see that people don't take the time
to say thanks or to let those who spent time and personal energy/inhterest in
answering questions often enough. I hope some of the questioners will see
this post.

When I took began work with Access in Sept. 2004, I knew practially nothing
about it and absolutely nothing about SQL or Visual Basic. Thanks to the
contributions in these communities, I have learned *volumes*, and have earned
a corporate prize and the highest performance rating at our company in large
part because of what I have been given by this community of generous people.
Our client (a US federal agency) wanted "ownership" of the database under our
contract after a demonstration. Granted it was my thinking and planning that
contributed, but I could not have done anything I envisioned without learning
how execute it at MSDN.

Deeply felt thanks to the generosity of all of you and, for this post, to
you personally, ChrisO.
susan
 
C

Chris2

Susan L said:
Chris: I was not going to answer your last post for fear of "littering' your
e-mail

Susan L.,

I use a newsreader that presents a threaded/tree type view, so
posting doesn't "litter". We're still on topic, so it's not bad.

and extending this post. Your comment was important, however. I always
research before I post a question, and I see that people don't take the time
to say thanks or to let those who spent time and personal energy/inhterest in
answering questions often enough. I hope some of the questioners will see
this post.

Deeply felt thanks to the generosity of all of you and, for this post, to
you personally, ChrisO.
susan


<shuffles feet, looks embarrassed /> ;)


Sincerely,

Chris O.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top