If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Track Changes Query
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 -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
Track Changes Query
On Sat, 06 Feb 2010 15:04:05 GMT, "hobbit2612 via AccessMonster.com"
u27332@uwe wrote: 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? -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Track Changes Query
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 John W. Vinson wrote: 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? -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
Track Changes Query
On Sat, 06 Feb 2010 15:04:05 GMT, "hobbit2612 via AccessMonster.com"
u27332@uwe wrote: 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 [MVP] |
#5
|
|||
|
|||
Track Changes Query
On Sat, 06 Feb 2010 18:15:48 GMT, "hobbit2612 via AccessMonster.com"
u27332@uwe wrote: 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. -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Track Changes Query
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 John W. Vinson wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201002/1 |
#7
|
|||
|
|||
Track Changes Query
On Sat, 06 Feb 2010 21:52:41 GMT, "hobbit2612 via AccessMonster.com"
u27332@uwe wrote: 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 -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Track Changes Query
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 John W. Vinson wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201002/1 |
#9
|
|||
|
|||
Track Changes Query
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 hobbit2612 wrote: 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 - 35 lines] etc etc through all the fields -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/201002/1 |
#10
|
|||
|
|||
Track Changes Query
On Wed, 10 Feb 2010 20:12:56 GMT, "hobbit2612 via AccessMonster.com"
u27332@uwe wrote: 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! -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|