Track Changes Query

[hobbit2612 via AccessMonster.com]

Hi I wonder if someone could help me please with a problem that's been
baffling for a few weeks now.

I am trying to create a query, which in turn feeds a report, that shows
tracked changes. I have previously posted on this forum and got some great
advice that's really helped me along but I'm really struggling with the query
to extract the data. I am relatively new to Access in particular VBA but I am
willing to learn.

What I would like to do is to use a date parameter to extract those fields
with an updated time stamp within a given time period, which in turn I can
show the related updated information with. I've managed to be able to do this
bit of it, but the problem I'm having is with the date pararmeter. If say for
example on a given record I have one field with a timestamp of 02/0/210,
another with the 03/02/10, if I use the 02/02/10-02/02/10 as my date
parameter it will still pull all of the fields that were updated for that
record rather than actually lifting the one field I want from the record.

Can anyone help me please?

Many thanks

Chris

 

[John W. Vinson]

What I would like to do is to use a date parameter to extract those fields
with an updated time stamp within a given time period, which in turn I can
show the related updated information with. I've managed to be able to do this
bit of it, but the problem I'm having is with the date pararmeter. If say for
example on a given record I have one field with a timestamp of 02/0/210,
another with the 03/02/10, if I use the 02/02/10-02/02/10 as my date
parameter it will still pull all of the fields that were updated for that
record rather than actually lifting the one field I want from the record.

Can anyone help me please?

With a bit more information perhaps. Could you post the fieldnames and
datatypes of the date field, and the SQL view of the query you're trying to
run?

 

[hobbit2612 via AccessMonster.com]

Hi John, many thanks for your reply.

What I've done in my table and also included in my query, is to create
another field to contain the date stamp for the changes to my fields, so the
query not only includes the date stamp fields but also the associated fields
that contain the actual data.

All the 'Date...Changed' fields are obviously Date/Time datatypes, the rest
of the fields in the query are as follows:

CRVersion - Text
AdditionalRefNo - Text
CRTitle - Text
Status - Text
LID - Date/Time
DateImplemented - Date/Time

The SQL for my query is as below:

SELECT tblCR.DateVersionChanged, tblCR.DateAdditionalRefNoChanged, tblCR.
DateTitleChanged, tblCR.DateStatusChanged, tblCR.DateLIDChanged, tblCR.
DateImplementedChanged, tblCR.CRVersion, tblCR.AdditionalRefNo, tblCR.CRTitle,
tblCR.Status, tblCR.LID, tblCR.DateImplemented
FROM tblCR;

To allow the user to input the data parameter I have created a Input form and
added this line to the criteria line in the query as an OR statement:

=[Forms]![frmDateInput]![StartDate] And <=[Forms]![frmDateInput]![EndDate]

However I'm not sure whether this is the problem so I left it out of the
query.

I hope this helps, and once again many thanks for your help.

Regards

Chris

What I would like to do is to use a date parameter to extract those fields
with an updated time stamp within a given time period, which in turn I can

[quoted text clipped - 6 lines]

Can anyone help me please?

With a bit more information perhaps. Could you post the fieldnames and
datatypes of the date field, and the SQL view of the query you're trying to
run?

 

[John W. Vinson]

What I would like to do is to use a date parameter to extract those fields
with an updated time stamp within a given time period, which in turn I can
show the related updated information with. I've managed to be able to do this
bit of it, but the problem I'm having is with the date pararmeter. If say for
example on a given record I have one field with a timestamp of 02/0/210,
another with the 03/02/10, if I use the 02/02/10-02/02/10 as my date
parameter it will still pull all of the fields that were updated for that
record rather than actually lifting the one field I want from the record.

waitaminit....

A Query either pulls the entire record, or none of it.

If you want a query to selectively pull some fields and not other fields of a
record, dynamically, you've got a very different problem.

You may need a UNION query to turn each little non-normalized block of data in
your record into a freestanding record.

 

[John W. Vinson]

SELECT tblCR.DateVersionChanged, tblCR.DateAdditionalRefNoChanged, tblCR.
DateTitleChanged, tblCR.DateStatusChanged, tblCR.DateLIDChanged, tblCR.
DateImplementedChanged, tblCR.CRVersion, tblCR.AdditionalRefNo, tblCR.CRTitle,
tblCR.Status, tblCR.LID, tblCR.DateImplemented
FROM tblCR;

To allow the user to input the data parameter I have created a Input form and
added this line to the criteria line in the query as an OR statement:

=[Forms]![frmDateInput]![StartDate] And <=[Forms]![frmDateInput]![EndDate]

However I'm not sure whether this is the problem so I left it out of the
query.

See my reply elsewhere in the thread. If you're expecting the query to pull
only certain fields, you're misunderstanding how queries work! They either
pull the whole record, or nothing at all.

Do post the full SQL with the WHERE clause though, it's certainly part of the
problem. You say "as an OR statement" but I have no idea what you're "or'ing"
it with.

 

[hobbit2612 via AccessMonster.com]

Hi,

John,

Please find the whole SQL including the WHERE statement.

SELECT tblCR.DateVersionChanged, tblCR.DateAdditionalRefNoChanged, tblCR.
DateTitleChanged, tblCR.DateStatusChanged, tblCR.DateLIDChanged, tblCR.
DateImplementedChanged, tblCR.CRVersion, tblCR.AdditionalRefNo, tblCR.CRTitle,
tblCR.Status, tblCR.LID, tblCR.DateImplemented
FROM tblCR
WHERE (((tblCR.DateVersionChanged)>=[Forms]![frmDateInput]![StartDate] And
(tblCR.DateVersionChanged)<=[Forms]![frmDateInput]![EndDate])) OR (((tblCR.
DateAdditionalRefNoChanged)>=[Forms]![frmDateInput]![StartDate] And (tblCR.
DateAdditionalRefNoChanged)<=[Forms]![frmDateInput]![EndDate])) OR (((tblCR.
DateTitleChanged)>=[Forms]![frmDateInput]![StartDate] And (tblCR.
DateTitleChanged)<=[Forms]![frmDateInput]![EndDate])) OR (((tblCR.
DateStatusChanged)>=[Forms]![frmDateInput]![StartDate] And (tblCR.
DateStatusChanged)<=[Forms]![frmDateInput]![EndDate])) OR (((tblCR.
DateLIDChanged)>=[Forms]![frmDateInput]![StartDate] And (tblCR.DateLIDChanged)
<=[Forms]![frmDateInput]![EndDate])) OR (((tblCR.DateImplementedChanged)>=
[Forms]![frmDateInput]![StartDate] And (tblCR.DateImplementedChanged)<=[Forms]
![frmDateInput]![EndDate]));

It sounds as if a Union query is what I will need, could you tell me please,
is this is easy to pull together?


Many thanks

Chris

What I would like to do is to use a date parameter to extract those fields
with an updated time stamp within a given time period, which in turn I can

[quoted text clipped - 4 lines]

parameter it will still pull all of the fields that were updated for that
record rather than actually lifting the one field I want from the record.

waitaminit....

A Query either pulls the entire record, or none of it.

If you want a query to selectively pull some fields and not other fields of a
record, dynamically, you've got a very different problem.

You may need a UNION query to turn each little non-normalized block of data in
your record into a freestanding record.

 

[John W. Vinson]

Hi,

John,

Please find the whole SQL including the WHERE statement.

SELECT tblCR.DateVersionChanged, tblCR.DateAdditionalRefNoChanged, tblCR.
DateTitleChanged, tblCR.DateStatusChanged, tblCR.DateLIDChanged, tblCR.
DateImplementedChanged, tblCR.CRVersion, tblCR.AdditionalRefNo, tblCR.CRTitle,
tblCR.Status, tblCR.LID, tblCR.DateImplemented
FROM tblCR
WHERE (((tblCR.DateVersionChanged)>=[Forms]![frmDateInput]![StartDate] And
(tblCR.DateVersionChanged)<=[Forms]![frmDateInput]![EndDate])) OR (((tblCR.
DateAdditionalRefNoChanged)>=[Forms]![frmDateInput]![StartDate] And (tblCR.
DateAdditionalRefNoChanged)<=[Forms]![frmDateInput]![EndDate])) OR (((tblCR.
DateTitleChanged)>=[Forms]![frmDateInput]![StartDate] And (tblCR.
DateTitleChanged)<=[Forms]![frmDateInput]![EndDate])) OR (((tblCR.
DateStatusChanged)>=[Forms]![frmDateInput]![StartDate] And (tblCR.
DateStatusChanged)<=[Forms]![frmDateInput]![EndDate])) OR (((tblCR.
DateLIDChanged)>=[Forms]![frmDateInput]![StartDate] And (tblCR.DateLIDChanged)
<=[Forms]![frmDateInput]![EndDate])) OR (((tblCR.DateImplementedChanged)>=
[Forms]![frmDateInput]![StartDate] And (tblCR.DateImplementedChanged)<=[Forms]
![frmDateInput]![EndDate]));

It sounds as if a Union query is what I will need, could you tell me please,
is this is easy to pull together?

Maybe I'm not understanding the problem.

What - specifically - results do you want to see?

As written this will find all records (and all of each record) where any one
of the date changed fields is within the date range.

You're having difficulty because (as has been said here before...!) your data
structure is WRONG. "Fields are expensive, records are cheap"; if you're
trying to keep a field-by-field audit trail you need a relationship to a
second table with fields such as FieldChanged, DateChanged, and any other
desired info (such as the ID of the person making the change).

Failing that, you can simulate it with a UNION query. You can't do this in the
query design grid, it's a SQL-only operation; you would edit code like

SELECT "Version" AS WhatChanged, tblCR.DateVersionChanged
FROM tblCR
WHERE (((tblCR.DateVersionChanged)>=[Forms]![frmDateInput]![StartDate]
And (tblCR.DateVersionChanged)<=[Forms]![frmDateInput]![EndDate]))
UNION ALL
SELECT "Additional Ref" AS WhatChanged, tblCR.DateAdditionalRefNoChanged
FROM tblCR
WHERE (((tblCR.DateAdditionalRefNoChanged)>=[Forms]![frmDateInput]![StartDate]
And (tblCR.DateAdditionalRefNoChanged)<=[Forms]![frmDateInput]![EndDate]))
UNION ALL
SELECT "Title" AS WhatChanged, tblCR.DateTitleChanged
FROM tblCR
WHERE (((tblCR.DateTitleChanged)>=[Forms]![frmDateInput]![StartDate]
And (tblCR.DateTitleChanged)<=[Forms]![frmDateInput]![EndDate]))
UNION ALL

<etc etc through all the fields>

 

[hobbit2612 via AccessMonster.com]

Hi John,

Many thanks for your continuing help.

The results I would like to see are only the fields with the date stamp that
the user has keyed in on the entry form and the associated data field. I
guess it's very similar to how you would write a copy and paste macro in
Excel. I want it to look through all the fields picking and taking the fields
that fit within the criteria and pasting them elsewhere.

I think the Union query is perhaps the way to go. I'll have a go at this and
see how I get on.

Many thanks and regards

Chris

Hi,

[quoted text clipped - 22 lines]

It sounds as if a Union query is what I will need, could you tell me please,
is this is easy to pull together?

Maybe I'm not understanding the problem.

What - specifically - results do you want to see?

As written this will find all records (and all of each record) where any one
of the date changed fields is within the date range.

You're having difficulty because (as has been said here before...!) your data
structure is WRONG. "Fields are expensive, records are cheap"; if you're
trying to keep a field-by-field audit trail you need a relationship to a
second table with fields such as FieldChanged, DateChanged, and any other
desired info (such as the ID of the person making the change).

Failing that, you can simulate it with a UNION query. You can't do this in the
query design grid, it's a SQL-only operation; you would edit code like

SELECT "Version" AS WhatChanged, tblCR.DateVersionChanged
FROM tblCR
WHERE (((tblCR.DateVersionChanged)>=[Forms]![frmDateInput]![StartDate]
And (tblCR.DateVersionChanged)<=[Forms]![frmDateInput]![EndDate]))
UNION ALL
SELECT "Additional Ref" AS WhatChanged, tblCR.DateAdditionalRefNoChanged
FROM tblCR
WHERE (((tblCR.DateAdditionalRefNoChanged)>=[Forms]![frmDateInput]![StartDate]
And (tblCR.DateAdditionalRefNoChanged)<=[Forms]![frmDateInput]![EndDate]))
UNION ALL
SELECT "Title" AS WhatChanged, tblCR.DateTitleChanged
FROM tblCR
WHERE (((tblCR.DateTitleChanged)>=[Forms]![frmDateInput]![StartDate]
And (tblCR.DateTitleChanged)<=[Forms]![frmDateInput]![EndDate]))
UNION ALL

<etc etc through all the fields>

 

[hobbit2612 via AccessMonster.com]

Hi John,

You very kindly gave me a section of SQL code to help me creating a query
that showed tracked changes. Since I got this I've been working with it and
understanding how they work.

I'm now trying to incorporate another field into the query. i.e a record ID
already built into the table just to give the user some more information that
I just can't seem to get to work. I've added the ID field at the beginning of
the code because, well to me it seemed the logical place to put it so the
coding looks like this:

SELECT tblCR.ID, "Version" AS WhatChanged, tblCR.DateVersionChanged, tblCR.
Version
FROM tblCR
WHERE (((tblCR.DateVersionChanged)>=[Forms]![frmDateInput]![StartDate]
And (tblCR.DateVersionChanged)<=[Forms]![frmDateInput]![EndDate]))
UNION ALL
etc

The problem is, is that I get an error message that says the 'number of
columns in the two selected tables or queries of a union query to not match'.
Now I'm pretty sure that the error occurs because I know in a Union query the
tables must match in terms of the number of fields, but I'm just not sure
where I can add the ID field.

Could you perhaps point me in the right direction please?

Many thanks and regards

Chris

Hi John,

Many thanks for your continuing help.

The results I would like to see are only the fields with the date stamp that
the user has keyed in on the entry form and the associated data field. I
guess it's very similar to how you would write a copy and paste macro in
Excel. I want it to look through all the fields picking and taking the fields
that fit within the criteria and pasting them elsewhere.

I think the Union query is perhaps the way to go. I'll have a go at this and
see how I get on.

Many thanks and regards

Chris
[quoted text clipped - 35 lines]

<etc etc through all the fields>

 

[John W. Vinson]

I'm now trying to incorporate another field into the query. i.e a record ID
already built into the table just to give the user some more information that
I just can't seem to get to work. I've added the ID field at the beginning of
the code because, well to me it seemed the logical place to put it so the
coding looks like this:

SELECT tblCR.ID, "Version" AS WhatChanged, tblCR.DateVersionChanged, tblCR.
Version
FROM tblCR
WHERE (((tblCR.DateVersionChanged)>=[Forms]![frmDateInput]![StartDate]
And (tblCR.DateVersionChanged)<=[Forms]![frmDateInput]![EndDate]))
UNION ALL
etc

The problem is, is that I get an error message that says the 'number of
columns in the two selected tables or queries of a union query to not match'.
Now I'm pretty sure that the error occurs because I know in a Union query the
tables must match in terms of the number of fields, but I'm just not sure
where I can add the ID field.

Could you perhaps point me in the right direction please?

What's the point of the ID? What will you do with it when you see it?

The error suggests that you have the ID in your first SELECT statement but not
in your others. A UNION query is very specific in its requirements: it
consists of two or more SELECT statements separated by UNION (or UNION ALL)
operators. Each SELECT statement must return the same number of fields, of
matching datatypes; your SELECT above returns four fields - a (number) ID, a
literal text string "Version", a Date, and the text Version. For your UNION
to work, every single one of the SELECT statements must also return four
fields - a number, a text, a date, and another text field, in that order. My
guess is that you need to include the ID field in all of the SELECTS.


HOWEVER....

As I've said before, *your table design IS WRONG*, which is why you're having
all these problems, and why you need the UNION query in the first place!!!!
I'd really strongly suggest *fixing your table design* first, rather than
continuing to struggle with the endless problems your incorrect design will
give you!