SQL

  • Thread starter Thread starter d
  • Start date Start date
D

d

Could anyone help with this:-

Can this be achieved in a SQL statement other than making physical tables
(other than the FromTo table) ?

I have one table called auaudit and need one query to select records that
have a field called aufrom and a 2nd query to select all records that have a
field called auTo

A 3rd query would use the results from the above queries to test if field RD
in both are not equal. If not equal would put the HD field from query 1 and
the HD field from query2 and the data field from query2 into a new table
called FromTo

And a 4th query would do the same as the 3rd but this time would test a
field called RD this also would be placed in the table called FromTo also If
not equal.
 
Could anyone help with this:-

Can this be achieved in a SQL statement other than making physical tables
(other than the FromTo table) ?

I have one table called auaudit and need one query to select records that
have a field called aufrom and a 2nd query to select all records that have a
field called auTo
Click to expand...

Please explain what you mean here. All records in a Table have the same
fieldnames. If any record in the table has a field named auFrom then every
record in the table has a field of that name - you CAN'T have a table with
different fields in different records.

I'm clearly misunderstanding the question.

What are the fieldnames and datatypes of the fields in the table named
auAudit?

Whate are some typical values for these fields?

What are "aufrom" and "auto"?

John W. Vinson [MVP]
 
Hi, John

I am trying to use the data produced by Allen Browne's Audit program
<http://allenbrowne.com/appAudit.html>. As such the table in my case that
contains the audit data is auaudit. This table contains in addition to the
fields in my database the following fields autype, audate and auUser. Each
time a field in the main database is updated a record is placed in this
table showing the states of all fields before any changes are made and in
the autype field EditFrom is placed and the date etc.
Then all the fields are copied again this time with one of the fields
changed and the autype field shows EditTo etc the edit from / editto
time/date fields are always the same.

My interest is only in changed data between editfrom and editto records (it
also does delete inserts)


At the moment I am playing around with ideas, my first try, I made a table
that contains just
EditFrom (audHLD_f ) data and a 2nd table that contains EditTo (audHLD_t )
then the following query:-

SELECT audHLD_t.audID, " H D" AS [Changed Field], audHLD_f.[H D] AS [Changed
From], audHLD_t.[H D] AS [Changed To], audHLD_t.IDRef, audHLD_t.audDate,
audHLD_t.audUser

FROM audHLD_t INNER JOIN audHLD_f ON audHLD_t.audID = audHLD_f.audID

WHERE (((audHLD_t.[H D])<>[audHLD_f].[H D]))

UNION ALL

SELECT audHLD_t.audID," R D" AS [Changed Field] , audHLD_f.[R D] AS
[Changed From], audHLD_t.[R D] AS [Changed
To],audHLD_t.IDRef,audHLD_t.audDate,audHLD_t.auduser

FROM audHLD_t INNER JOIN audHLD_f ON audHLD_t.audID = audHLD_f.audID

WHERE (((audHLD_t.[R D])<>[audHLD_f].[R D]))

UNION ALL
SELECT audHLD_t.audID," PMC" AS [Changed Field] , audHLD_f.[ PMC l] AS
[Changed From], audHLD_t.[ PMC] AS [Changed
To],audHLD_t.IDHLD_t.audDate,audHLD_t.auduser


FROM audHLD_t INNER JOIN audHLD_f ON audHLD_t.audID = audHLD_f.audID
WHERE (((audHLD_t.[ PMC])<>[audHLD_f].[ PMC]));

Hope all this helps, and thank you for your assistance
 
Back
Top