Recordset to update queries - help!

Hi

First up, I am using MS Access 2002.

I have a database which records analyst rating changes for a list of
companies on a daily basis. Unfortunately, the database has been set
up (by my predecessor, I hasten to add) so that each day it creates a
copy of the record for each company, changes the date to today's date,
and prompts the user for any changes of ratings on that day. The
resulting data table grows by approx 600 records per day.

If starting from scratch, the obvious way to do this would be to
record only the changes each day, thus creating a much smaller data
table. with an eye to doing this in te future, i would like to go
through every date in the data table, comparing the rating for each
company with that of the day before, and appending a new record to a
table "tblChanges" each time a change is detected.

I have created 2 queries to extract data for the two days for
comparison, and a third query which links the 2 original queries by
Company_ID, and appends any records where the rating has changed to
the table "tblChanges". This works fine, but I do not fancy running
the query x amount of times, manually entering the new dates for each
comparison.

It seems that using recordsets in VBA is the way to automate this
process. I imagine creating 2 recordsets which can each have the date
variable passed to them from the VBA code - one with data on one day,
and one with data on the next. I then need to run the append query on
these recordsets, to append any changed records to tblChanges.

I have very little experience of using recordsets, and have pieced
together the following code from newsgroups:

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

Sub FindChanges()

Dim db As DAO.Database
Dim rstOld As DAO.Recordset
Dim rstNew As DAO.Recordset
Dim qdfOld As DAO.QueryDef
Dim qdfNew As DAO.QueryDef
Dim DateOld As Date
Dim DateNew As Date

Set db = CurrentDb()

DateOld = "01/07/03"
DateNew = "01/08/03"

Do
' create the querydef object
Set qdfOld = db.QueryDefs("qryCompareOld")
With qdfOld
'resolve the parameter
.Parameters("Date") = DateOld
'open the recordset based on the Old data querydef
Set rstOld = .OpenRecordset
End With

Set qdfNew = db.QueryDefs("qryCompareNew")
With qdfNew
'resolve the parameter
.Parameters("Date") = DateNew
'open the recordset based on the new data querydef
Set rstNew = .OpenRecordset
End With

' run the append query "qryFindChanges", which compares the
results of CompareOld and CompareNew,
'and appends differences to table "tblChanges"
DoCmd.OpenQuery "qryFindChanges"

With rstOld
.Close
End With

With rstNew
.Close
End With

'Change date variables to compare next day's data for changes
DateOld = DateNew
if DateNew+ 1 > Now() Then Exit Sub
If WorkDay(DateNew + 1) = True Then
DateNew = DateNew + 1
Else
If WorkDay(DateNew + 2) = True Then
DateNew = DateNew + 2
Else: DateNew = DateNew + 3
End If
End If
End If

Loop

' Destroy the object vars
Set qdfOld = Nothing
Set qdfNew = Nothing
Set rstOld = Nothing
Set rstNew = Nothing
Set db = Nothing

End Sub

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

The code currently halts at
..Parameters("Date") = DateOld
with the error "Item not found in this collection", although I thought
Parameters was part of QueryDefs.

I also imagine that an error will occur when running the
"qryFindChanges" query, or at least the recordset data will not be
used in that query. Does the coding for this query have to be
included within the VBA code?

If so, how would this be achieved, and how can I make the results
append to the "tblChanges" table?

Sorry for the essay - I think I can see how this should be done, but
am too new to recordsets to get the coding spot on. Any help at all
would be much appreciated.

Dom

How about this:
Create a query with two copies of your table.
Link them on CompanyID and on MyTable.RecordDate=MyTable_1.RecordDate+1
(you'll have to edit the SQL to get the latter join - it can't be built in
the grid directly.)
Use the criteria line to pick out only items where the rating has changed.
Add whatever fields you need for your new table.
Turn the query into a make-table query.

Run it once, and you should be done.

HTH
- Turtle

"Dom Boyce" <domboyce@totalise.co.uk> wrote in message
news:7c016cf5.0311210356.279c425@posting.google.co m...[color=blue]
> Hi
>
> First up, I am using MS Access 2002.
>
> I have a database which records analyst rating changes for a list of
> companies on a daily basis. Unfortunately, the database has been set
> up (by my predecessor, I hasten to add) so that each day it creates a
> copy of the record for each company, changes the date to today's date,
> and prompts the user for any changes of ratings on that day. The
> resulting data table grows by approx 600 records per day.
>
> If starting from scratch, the obvious way to do this would be to
> record only the changes each day, thus creating a much smaller data
> table. with an eye to doing this in te future, i would like to go
> through every date in the data table, comparing the rating for each
> company with that of the day before, and appending a new record to a
> table "tblChanges" each time a change is detected.
>
> I have created 2 queries to extract data for the two days for
> comparison, and a third query which links the 2 original queries by
> Company_ID, and appends any records where the rating has changed to
> the table "tblChanges". This works fine, but I do not fancy running
> the query x amount of times, manually entering the new dates for each
> comparison.
>
> It seems that using recordsets in VBA is the way to automate this
> process. I imagine creating 2 recordsets which can each have the date
> variable passed to them from the VBA code - one with data on one day,
> and one with data on the next. I then need to run the append query on
> these recordsets, to append any changed records to tblChanges.
>
> I have very little experience of using recordsets, and have pieced
> together the following code from newsgroups:
>
> ------------------------------
>
> Sub FindChanges()
>
> Dim db As DAO.Database
> Dim rstOld As DAO.Recordset
> Dim rstNew As DAO.Recordset
> Dim qdfOld As DAO.QueryDef
> Dim qdfNew As DAO.QueryDef
> Dim DateOld As Date
> Dim DateNew As Date
>
> Set db = CurrentDb()
>
> DateOld = "01/07/03"
> DateNew = "01/08/03"
>
> Do
> ' create the querydef object
> Set qdfOld = db.QueryDefs("qryCompareOld")
> With qdfOld
> 'resolve the parameter
> .Parameters("Date") = DateOld
> 'open the recordset based on the Old data querydef
> Set rstOld = .OpenRecordset
> End With
>
> Set qdfNew = db.QueryDefs("qryCompareNew")
> With qdfNew
> 'resolve the parameter
> .Parameters("Date") = DateNew
> 'open the recordset based on the new data querydef
> Set rstNew = .OpenRecordset
> End With
>
> ' run the append query "qryFindChanges", which compares the
> results of CompareOld and CompareNew,
> 'and appends differences to table "tblChanges"
> DoCmd.OpenQuery "qryFindChanges"
>
>
> With rstOld
> .Close
> End With
>
> With rstNew
> .Close
> End With
>
> 'Change date variables to compare next day's data for changes
> DateOld = DateNew
> if DateNew+ 1 > Now() Then Exit Sub
> If WorkDay(DateNew + 1) = True Then
> DateNew = DateNew + 1
> Else
> If WorkDay(DateNew + 2) = True Then
> DateNew = DateNew + 2
> Else: DateNew = DateNew + 3
> End If
> End If
> End If
>
> Loop
>
> ' Destroy the object vars
> Set qdfOld = Nothing
> Set qdfNew = Nothing
> Set rstOld = Nothing
> Set rstNew = Nothing
> Set db = Nothing
>
> End Sub
>
> -------------------------------------
>
> The code currently halts at
> .Parameters("Date") = DateOld
> with the error "Item not found in this collection", although I thought
> Parameters was part of QueryDefs.
>
> I also imagine that an error will occur when running the
> "qryFindChanges" query, or at least the recordset data will not be
> used in that query. Does the coding for this query have to be
> included within the VBA code?
>
> If so, how would this be achieved, and how can I make the results
> append to the "tblChanges" table?
>
> Sorry for the essay - I think I can see how this should be done, but
> am too new to recordsets to get the coding spot on. Any help at all
> would be much appreciated.
>
> Dom[/color]

Turtle,

thanks a lot for this - it worked perfectly and saved me a huge amount of time.

Dom

"MacDermott" <macdermott@nospam.com> wrote in message news:<8MSvb.15629$Rk5.12635@newsread1.news.atl.ear thlink.net>...[color=blue]
> How about this:
> Create a query with two copies of your table.
> Link them on CompanyID and on MyTable.RecordDate=MyTable_1.RecordDate+1
> (you'll have to edit the SQL to get the latter join - it can't be built in
> the grid directly.)
> Use the criteria line to pick out only items where the rating has changed.
> Add whatever fields you need for your new table.
> Turn the query into a make-table query.
>
> Run it once, and you should be done.
>
> HTH
> - Turtle
>
> "Dom Boyce" <domboyce@totalise.co.uk> wrote in message
> news:7c016cf5.0311210356.279c425@posting.google.co m...[color=green]
> > Hi
> >
> > First up, I am using MS Access 2002.
> >
> > I have a database which records analyst rating changes for a list of
> > companies on a daily basis. Unfortunately, the database has been set
> > up (by my predecessor, I hasten to add) so that each day it creates a
> > copy of the record for each company, changes the date to today's date,
> > and prompts the user for any changes of ratings on that day. The
> > resulting data table grows by approx 600 records per day.
> >
> > If starting from scratch, the obvious way to do this would be to
> > record only the changes each day, thus creating a much smaller data
> > table. with an eye to doing this in te future, i would like to go
> > through every date in the data table, comparing the rating for each
> > company with that of the day before, and appending a new record to a
> > table "tblChanges" each time a change is detected.
> >
> > I have created 2 queries to extract data for the two days for
> > comparison, and a third query which links the 2 original queries by
> > Company_ID, and appends any records where the rating has changed to
> > the table "tblChanges". This works fine, but I do not fancy running
> > the query x amount of times, manually entering the new dates for each
> > comparison.
> >
> > It seems that using recordsets in VBA is the way to automate this
> > process. I imagine creating 2 recordsets which can each have the date
> > variable passed to them from the VBA code - one with data on one day,
> > and one with data on the next. I then need to run the append query on
> > these recordsets, to append any changed records to tblChanges.
> >
> > I have very little experience of using recordsets, and have pieced
> > together the following code from newsgroups:
> >
> > ------------------------------
> >
> > Sub FindChanges()
> >
> > Dim db As DAO.Database
> > Dim rstOld As DAO.Recordset
> > Dim rstNew As DAO.Recordset
> > Dim qdfOld As DAO.QueryDef
> > Dim qdfNew As DAO.QueryDef
> > Dim DateOld As Date
> > Dim DateNew As Date
> >
> > Set db = CurrentDb()
> >
> > DateOld = "01/07/03"
> > DateNew = "01/08/03"
> >
> > Do
> > ' create the querydef object
> > Set qdfOld = db.QueryDefs("qryCompareOld")
> > With qdfOld
> > 'resolve the parameter
> > .Parameters("Date") = DateOld
> > 'open the recordset based on the Old data querydef
> > Set rstOld = .OpenRecordset
> > End With
> >
> > Set qdfNew = db.QueryDefs("qryCompareNew")
> > With qdfNew
> > 'resolve the parameter
> > .Parameters("Date") = DateNew
> > 'open the recordset based on the new data querydef
> > Set rstNew = .OpenRecordset
> > End With
> >
> > ' run the append query "qryFindChanges", which compares the
> > results of CompareOld and CompareNew,
> > 'and appends differences to table "tblChanges"
> > DoCmd.OpenQuery "qryFindChanges"
> >
> >
> > With rstOld
> > .Close
> > End With
> >
> > With rstNew
> > .Close
> > End With
> >
> > 'Change date variables to compare next day's data for changes
> > DateOld = DateNew
> > if DateNew+ 1 > Now() Then Exit Sub
> > If WorkDay(DateNew + 1) = True Then
> > DateNew = DateNew + 1
> > Else
> > If WorkDay(DateNew + 2) = True Then
> > DateNew = DateNew + 2
> > Else: DateNew = DateNew + 3
> > End If
> > End If
> > End If
> >
> > Loop
> >
> > ' Destroy the object vars
> > Set qdfOld = Nothing
> > Set qdfNew = Nothing
> > Set rstOld = Nothing
> > Set rstNew = Nothing
> > Set db = Nothing
> >
> > End Sub
> >
> > -------------------------------------
> >
> > The code currently halts at
> > .Parameters("Date") = DateOld
> > with the error "Item not found in this collection", although I thought
> > Parameters was part of QueryDefs.
> >
> > I also imagine that an error will occur when running the
> > "qryFindChanges" query, or at least the recordset data will not be
> > used in that query. Does the coding for this query have to be
> > included within the VBA code?
> >
> > If so, how would this be achieved, and how can I make the results
> > append to the "tblChanges" table?
> >
> > Sorry for the essay - I think I can see how this should be done, but
> > am too new to recordsets to get the coding spot on. Any help at all
> > would be much appreciated.
> >
> > Dom[/color][/color]

Upon returning to this problem, which I thought I had fixed using the
solution below, I have encountered another problem.

The database records data items for each weekday, but not on Saturday
or Sunday. This means that if a rating change takes place on a
Monday, then it will not show up in the query outlined below.
The SQL statement (included below) compares ratings from Day 0 with
those from Day 0+1.

SELECT Data.Date, Data.Rating, Data.Type, Data.[Target price],
Data.Analyst_ID, Data.Company_ID
FROM Data INNER JOIN Data AS Data_1 ON (Data.Company_ID =
Data_1.Company_ID) AND (Data.Date = Data_1.Date+1)
WHERE Data.Rating <> Data_1.Rating;

If a rating change took place on 5 Dec (Friday), then this would be
picked up when the query compared 20031204 data with data on 20031205
(YYYMMDD dates). However, data is not stored for Sat or Sun, so if
the change took place on 8 Dec (Monday), the query will not compare
Friday with Monday, but will make Monday - Tuesday the next
comparison, hence not picking up the rating change.

I have tried extending the SQL as follows to compare each day with
day+1 and day+3, so that rating chages on mondays will be picked up
(Friday+3=Monday).

AND (Data.Date = Data_1.Date+1) OR (Data.Date = Data_1.Date+3)

I realise that this would then need filtering, as each rating change
could potentially appear a few times. However, there seems to be a
huge amount of data returned (1000s of records, as opposed to the 180
returned before amending the query), which suggests that there is
something wrong with the SQL. Furthermore, this seems a very clunky
way of doing it.

Is there any way of inserting an "If" statement into the SQL, to tell
the query the if Day+1 does not exist, then (as Day must therefore be
a Friday) compare with Day+3?

Again, thanks in advance for any help anybody can provide.

Dom Boyce

domboyce@totalise.co.uk (Dom Boyce) wrote in message news:<7c016cf5.0311240942.be6b58f@posting.google.c om>...[color=blue]
> Turtle,
>
> thanks a lot for this - it worked perfectly and saved me a huge amount of time.
>
> Dom
>
>
> "MacDermott" <macdermott@nospam.com> wrote in message news:<8MSvb.15629$Rk5.12635@newsread1.news.atl.ear thlink.net>...[color=green]
> > How about this:
> > Create a query with two copies of your table.
> > Link them on CompanyID and on MyTable.RecordDate=MyTable_1.RecordDate+1
> > (you'll have to edit the SQL to get the latter join - it can't be built in
> > the grid directly.)
> > Use the criteria line to pick out only items where the rating has changed.
> > Add whatever fields you need for your new table.
> > Turn the query into a make-table query.
> >
> > Run it once, and you should be done.
> >
> > HTH
> > - Turtle
> >
> > "Dom Boyce" <domboyce@totalise.co.uk> wrote in message
> > news:7c016cf5.0311210356.279c425@posting.google.co m...[color=darkred]
> > > Hi
> > >
> > > First up, I am using MS Access 2002.
> > >
> > > I have a database which records analyst rating changes for a list of
> > > companies on a daily basis. Unfortunately, the database has been set
> > > up (by my predecessor, I hasten to add) so that each day it creates a
> > > copy of the record for each company, changes the date to today's date,
> > > and prompts the user for any changes of ratings on that day. The
> > > resulting data table grows by approx 600 records per day.
> > >
> > > If starting from scratch, the obvious way to do this would be to
> > > record only the changes each day, thus creating a much smaller data
> > > table. with an eye to doing this in te future, i would like to go
> > > through every date in the data table, comparing the rating for each
> > > company with that of the day before, and appending a new record to a
> > > table "tblChanges" each time a change is detected.
> > >
> > > I have created 2 queries to extract data for the two days for
> > > comparison, and a third query which links the 2 original queries by
> > > Company_ID, and appends any records where the rating has changed to
> > > the table "tblChanges". This works fine, but I do not fancy running
> > > the query x amount of times, manually entering the new dates for each
> > > comparison.
> > >
> > > It seems that using recordsets in VBA is the way to automate this
> > > process. I imagine creating 2 recordsets which can each have the date
> > > variable passed to them from the VBA code - one with data on one day,
> > > and one with data on the next. I then need to run the append query on
> > > these recordsets, to append any changed records to tblChanges.
> > >
> > > I have very little experience of using recordsets, and have pieced
> > > together the following code from newsgroups:
> > >
> > > ------------------------------
> > >
> > > Sub FindChanges()
> > >
> > > Dim db As DAO.Database
> > > Dim rstOld As DAO.Recordset
> > > Dim rstNew As DAO.Recordset
> > > Dim qdfOld As DAO.QueryDef
> > > Dim qdfNew As DAO.QueryDef
> > > Dim DateOld As Date
> > > Dim DateNew As Date
> > >
> > > Set db = CurrentDb()
> > >
> > > DateOld = "01/07/03"
> > > DateNew = "01/08/03"
> > >
> > > Do
> > > ' create the querydef object
> > > Set qdfOld = db.QueryDefs("qryCompareOld")
> > > With qdfOld
> > > 'resolve the parameter
> > > .Parameters("Date") = DateOld
> > > 'open the recordset based on the Old data querydef
> > > Set rstOld = .OpenRecordset
> > > End With
> > >
> > > Set qdfNew = db.QueryDefs("qryCompareNew")
> > > With qdfNew
> > > 'resolve the parameter
> > > .Parameters("Date") = DateNew
> > > 'open the recordset based on the new data querydef
> > > Set rstNew = .OpenRecordset
> > > End With
> > >
> > > ' run the append query "qryFindChanges", which compares the
> > > results of CompareOld and CompareNew,
> > > 'and appends differences to table "tblChanges"
> > > DoCmd.OpenQuery "qryFindChanges"
> > >
> > >
> > > With rstOld
> > > .Close
> > > End With
> > >
> > > With rstNew
> > > .Close
> > > End With
> > >
> > > 'Change date variables to compare next day's data for changes
> > > DateOld = DateNew
> > > if DateNew+ 1 > Now() Then Exit Sub
> > > If WorkDay(DateNew + 1) = True Then
> > > DateNew = DateNew + 1
> > > Else
> > > If WorkDay(DateNew + 2) = True Then
> > > DateNew = DateNew + 2
> > > Else: DateNew = DateNew + 3
> > > End If
> > > End If
> > > End If
> > >
> > > Loop
> > >
> > > ' Destroy the object vars
> > > Set qdfOld = Nothing
> > > Set qdfNew = Nothing
> > > Set rstOld = Nothing
> > > Set rstNew = Nothing
> > > Set db = Nothing
> > >
> > > End Sub
> > >
> > > -------------------------------------
> > >
> > > The code currently halts at
> > > .Parameters("Date") = DateOld
> > > with the error "Item not found in this collection", although I thought
> > > Parameters was part of QueryDefs.
> > >
> > > I also imagine that an error will occur when running the
> > > "qryFindChanges" query, or at least the recordset data will not be
> > > used in that query. Does the coding for this query have to be
> > > included within the VBA code?
> > >
> > > If so, how would this be achieved, and how can I make the results
> > > append to the "tblChanges" table?
> > >
> > > Sorry for the essay - I think I can see how this should be done, but
> > > am too new to recordsets to get the coding spot on. Any help at all
> > > would be much appreciated.
> > >
> > > Dom[/color][/color][/color]

WARNING: AIR CODE

Perhaps you could use something like this:

SELECT Data.Date, Data.Rating, Data.Type, Data.[Target price],
Data.Analyst_ID, Data.Company_ID
FROM Data , Data AS Data_1 WHERE (Data.Company_ID =
Data_1.Company_ID) AND (Data.Date =
IIF(WeekDay(Data.Date)=vbFriday,Data_1.Date+3,Data _1.Date+1) AND
Data.Rating <> Data_1.Rating;

Changing from an inner join to a cross-product with WHERE conditions is not
something I have a good reason for - it just felt better to me here. You
could probably do it with an inner join, too.

HTH
- Turtle
"Dom Boyce" <domboyce@totalise.co.uk> wrote in message
news:7c016cf5.0312150338.435624eb@posting.google.c om...[color=blue]
> Upon returning to this problem, which I thought I had fixed using the
> solution below, I have encountered another problem.
>
> The database records data items for each weekday, but not on Saturday
> or Sunday. This means that if a rating change takes place on a
> Monday, then it will not show up in the query outlined below.
> The SQL statement (included below) compares ratings from Day 0 with
> those from Day 0+1.
>
> SELECT Data.Date, Data.Rating, Data.Type, Data.[Target price],
> Data.Analyst_ID, Data.Company_ID
> FROM Data INNER JOIN Data AS Data_1 ON (Data.Company_ID =
> Data_1.Company_ID) AND (Data.Date = Data_1.Date+1)
> WHERE Data.Rating <> Data_1.Rating;
>
>
>
> If a rating change took place on 5 Dec (Friday), then this would be
> picked up when the query compared 20031204 data with data on 20031205
> (YYYMMDD dates). However, data is not stored for Sat or Sun, so if
> the change took place on 8 Dec (Monday), the query will not compare
> Friday with Monday, but will make Monday - Tuesday the next
> comparison, hence not picking up the rating change.
>
> I have tried extending the SQL as follows to compare each day with
> day+1 and day+3, so that rating chages on mondays will be picked up
> (Friday+3=Monday).
>
> AND (Data.Date = Data_1.Date+1) OR (Data.Date = Data_1.Date+3)
>
>
> I realise that this would then need filtering, as each rating change
> could potentially appear a few times. However, there seems to be a
> huge amount of data returned (1000s of records, as opposed to the 180
> returned before amending the query), which suggests that there is
> something wrong with the SQL. Furthermore, this seems a very clunky
> way of doing it.
>
> Is there any way of inserting an "If" statement into the SQL, to tell
> the query the if Day+1 does not exist, then (as Day must therefore be
> a Friday) compare with Day+3?
>
> Again, thanks in advance for any help anybody can provide.
>
> Dom Boyce
>
>
> domboyce@totalise.co.uk (Dom Boyce) wrote in message[/color]
news:<7c016cf5.0311240942.be6b58f@posting.google.c om>...[color=blue][color=green]
> > Turtle,
> >
> > thanks a lot for this - it worked perfectly and saved me a huge amount[/color][/color]
of time.[color=blue][color=green]
> >
> > Dom
> >
> >
> > "MacDermott" <macdermott@nospam.com> wrote in message[/color][/color]
news:<8MSvb.15629$Rk5.12635@newsread1.news.atl.ear thlink.net>...[color=blue][color=green][color=darkred]
> > > How about this:
> > > Create a query with two copies of your table.
> > > Link them on CompanyID and on[/color][/color][/color]
MyTable.RecordDate=MyTable_1.RecordDate+1[color=blue][color=green][color=darkred]
> > > (you'll have to edit the SQL to get the latter join - it can't be[/color][/color][/color]
built in[color=blue][color=green][color=darkred]
> > > the grid directly.)
> > > Use the criteria line to pick out only items where the rating has[/color][/color][/color]
changed.[color=blue][color=green][color=darkred]
> > > Add whatever fields you need for your new table.
> > > Turn the query into a make-table query.
> > >
> > > Run it once, and you should be done.
> > >
> > > HTH
> > > - Turtle
> > >
> > > "Dom Boyce" <domboyce@totalise.co.uk> wrote in message
> > > news:7c016cf5.0311210356.279c425@posting.google.co m...
> > > > Hi
> > > >
> > > > First up, I am using MS Access 2002.
> > > >
> > > > I have a database which records analyst rating changes for a list of
> > > > companies on a daily basis. Unfortunately, the database has been set
> > > > up (by my predecessor, I hasten to add) so that each day it creates[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> > > > copy of the record for each company, changes the date to today's[/color][/color][/color]
date,[color=blue][color=green][color=darkred]
> > > > and prompts the user for any changes of ratings on that day. The
> > > > resulting data table grows by approx 600 records per day.
> > > >
> > > > If starting from scratch, the obvious way to do this would be to
> > > > record only the changes each day, thus creating a much smaller data
> > > > table. with an eye to doing this in te future, i would like to go
> > > > through every date in the data table, comparing the rating for each
> > > > company with that of the day before, and appending a new record to a
> > > > table "tblChanges" each time a change is detected.
> > > >
> > > > I have created 2 queries to extract data for the two days for
> > > > comparison, and a third query which links the 2 original queries by
> > > > Company_ID, and appends any records where the rating has changed to
> > > > the table "tblChanges". This works fine, but I do not fancy running
> > > > the query x amount of times, manually entering the new dates for[/color][/color][/color]
each[color=blue][color=green][color=darkred]
> > > > comparison.
> > > >
> > > > It seems that using recordsets in VBA is the way to automate this
> > > > process. I imagine creating 2 recordsets which can each have the[/color][/color][/color]
date[color=blue][color=green][color=darkred]
> > > > variable passed to them from the VBA code - one with data on one[/color][/color][/color]
day,[color=blue][color=green][color=darkred]
> > > > and one with data on the next. I then need to run the append query[/color][/color][/color]
on[color=blue][color=green][color=darkred]
> > > > these recordsets, to append any changed records to tblChanges.
> > > >
> > > > I have very little experience of using recordsets, and have pieced
> > > > together the following code from newsgroups:
> > > >
> > > > ------------------------------
> > > >
> > > > Sub FindChanges()
> > > >
> > > > Dim db As DAO.Database
> > > > Dim rstOld As DAO.Recordset
> > > > Dim rstNew As DAO.Recordset
> > > > Dim qdfOld As DAO.QueryDef
> > > > Dim qdfNew As DAO.QueryDef
> > > > Dim DateOld As Date
> > > > Dim DateNew As Date
> > > >
> > > > Set db = CurrentDb()
> > > >
> > > > DateOld = "01/07/03"
> > > > DateNew = "01/08/03"
> > > >
> > > > Do
> > > > ' create the querydef object
> > > > Set qdfOld = db.QueryDefs("qryCompareOld")
> > > > With qdfOld
> > > > 'resolve the parameter
> > > > .Parameters("Date") = DateOld
> > > > 'open the recordset based on the Old data querydef
> > > > Set rstOld = .OpenRecordset
> > > > End With
> > > >
> > > > Set qdfNew = db.QueryDefs("qryCompareNew")
> > > > With qdfNew
> > > > 'resolve the parameter
> > > > .Parameters("Date") = DateNew
> > > > 'open the recordset based on the new data querydef
> > > > Set rstNew = .OpenRecordset
> > > > End With
> > > >
> > > > ' run the append query "qryFindChanges", which compares the
> > > > results of CompareOld and CompareNew,
> > > > 'and appends differences to table "tblChanges"
> > > > DoCmd.OpenQuery "qryFindChanges"
> > > >
> > > >
> > > > With rstOld
> > > > .Close
> > > > End With
> > > >
> > > > With rstNew
> > > > .Close
> > > > End With
> > > >
> > > > 'Change date variables to compare next day's data for changes
> > > > DateOld = DateNew
> > > > if DateNew+ 1 > Now() Then Exit Sub
> > > > If WorkDay(DateNew + 1) = True Then
> > > > DateNew = DateNew + 1
> > > > Else
> > > > If WorkDay(DateNew + 2) = True Then
> > > > DateNew = DateNew + 2
> > > > Else: DateNew = DateNew + 3
> > > > End If
> > > > End If
> > > > End If
> > > >
> > > > Loop
> > > >
> > > > ' Destroy the object vars
> > > > Set qdfOld = Nothing
> > > > Set qdfNew = Nothing
> > > > Set rstOld = Nothing
> > > > Set rstNew = Nothing
> > > > Set db = Nothing
> > > >
> > > > End Sub
> > > >
> > > > -------------------------------------
> > > >
> > > > The code currently halts at
> > > > .Parameters("Date") = DateOld
> > > > with the error "Item not found in this collection", although I[/color][/color][/color]
thought[color=blue][color=green][color=darkred]
> > > > Parameters was part of QueryDefs.
> > > >
> > > > I also imagine that an error will occur when running the
> > > > "qryFindChanges" query, or at least the recordset data will not be
> > > > used in that query. Does the coding for this query have to be
> > > > included within the VBA code?
> > > >
> > > > If so, how would this be achieved, and how can I make the results
> > > > append to the "tblChanges" table?
> > > >
> > > > Sorry for the essay - I think I can see how this should be done, but
> > > > am too new to recordsets to get the coding spot on. Any help at all
> > > > would be much appreciated.
> > > >
> > > > Dom[/color][/color][/color]

Thanks again for your help. I had to modify the suggested solution,
as "vbFriday" was not recognised in SQL - replacing it with "6", the
day number of Friday, at least allowed the query to run.

however, the returned results were not as desired - they contained a
lot of duplicate records and, een after hours of fiddling, i could not
return the correct data.

By creating two queries, one for Fridays and one for other weekdays, I
can pull the required data out. The SQL for this follows. NB this has
been extended from the last SQL posted, in order to view changes not
only in Rating, but also in Type and Analyst, but the FROM and WHERE
sections illustrate how a full set of data is obtained:

qryFriday
SELECT Data_1.Date, Data_1.Rating AS [New Rating], Data_1.Type AS [New
Type], Analysts_1.LastName AS [New Analyst], Data.Rating AS [Old
Rating], Data.Type AS [Old Type], Analysts.LastName AS [Old Analyst],
Data.[Target price], Companies.Name,
iif([Data].[Analyst_ID]<>[Data_1].[Analyst_ID],IIf(nz([New
Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
Type],"RTA","RA"),IIf([New Type]<>[Old Type],"TA","A")), IIf(nz([New
Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
Type],"RT","R"),IIf([New Type]<>[Old Type],"T",""))) AS [Change Type]
FROM (Companies INNER JOIN (Analysts INNER JOIN (Data INNER JOIN Data
AS Data_1 ON (Data.Company_ID = Data_1.Company_ID) AND
(Data_1.Date=Data.Date+3)) ON Analysts.Analyst_ID = Data.Analyst_ID)
ON Companies.Company_ID = Data.Company_ID) INNER JOIN Analysts AS
Analysts_1 ON Data_1.Analyst_ID = Analysts_1.Analyst_ID
WHERE (((nz([Data].[Rating]))<>nz([Data_1].[Rating])) AND
((Weekday([Data].[Date]))=6)) OR (((Data.Type)<>[Data_1].[Type]) AND
((Weekday([Data].[Date]))=6)) OR
(((Data.Analyst_ID)<>[Data_1].[Analyst_ID]) AND
((Weekday([Data].[Date]))=6))
ORDER BY Data_1.Date, Companies.Name;

qryOtherDays
SELECT Data_1.Date, Data_1.Rating AS [New Rating], Data_1.Type AS [New
Type], Analysts_1.LastName AS [New Analyst], Data.Rating AS [Old
Rating], Data.Type AS [Old Type], Analysts.LastName AS [Old Analyst],
Data.[Target price], Companies.Name,
iif([Data].[Analyst_ID]<>[Data_1].[Analyst_ID],IIf(nz([New
Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
Type],"RTA","RA"),IIf([New Type]<>[Old Type],"TA","A")), IIf(nz([New
Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
Type],"RT","R"),IIf([New Type]<>[Old Type],"T",""))) AS [Change Type]
FROM (Companies INNER JOIN (Analysts INNER JOIN (Data INNER JOIN Data
AS Data_1 ON (Data.Company_ID = Data_1.Company_ID) AND
(Data_1.Date=Data.Date+1)) ON Analysts.Analyst_ID = Data.Analyst_ID)
ON Companies.Company_ID = Data.Company_ID) INNER JOIN Analysts AS
Analysts_1 ON Data_1.Analyst_ID = Analysts_1.Analyst_ID
WHERE (((nz([Data].[Rating]))<>nz([Data_1].[Rating])) AND
((Weekday([Data].[Date]))<>6)) OR (((Data.Type)<>[Data_1].[Type]) AND
((Weekday([Data].[Date]))<>6)) OR
(((Data.Analyst_ID)<>[Data_1].[Analyst_ID]) AND
((Weekday([Data].[Date]))<>6))
ORDER BY Data_1.Date, Companies.Name;

However, I now need to combine these two queries so I can view the
results in a form. is there a way of doing this, other than making
both queries Append queries, basing a form on the resulting table and
coding a FormLoad event to clear the table then run both append
queries?

Again, any help would be much appreciated. Dom

"MacDermott" <macdermott@nospam.com> wrote in message news:<XvCDb.997$Ts5.747@newsread2.news.atl.earthli nk.net>...[color=blue]
> WARNING: AIR CODE
>
> Perhaps you could use something like this:
>
> SELECT Data.Date, Data.Rating, Data.Type, Data.[Target price],
> Data.Analyst_ID, Data.Company_ID
> FROM Data , Data AS Data_1 WHERE (Data.Company_ID =
> Data_1.Company_ID) AND (Data.Date =
> IIF(WeekDay(Data.Date)=vbFriday,Data_1.Date+3,Data _1.Date+1) AND
> Data.Rating <> Data_1.Rating;
>
> Changing from an inner join to a cross-product with WHERE conditions is not
> something I have a good reason for - it just felt better to me here. You
> could probably do it with an inner join, too.
>
> HTH
> - Turtle
> "Dom Boyce" <domboyce@totalise.co.uk> wrote in message
> news:7c016cf5.0312150338.435624eb@posting.google.c om...[color=green]
> > Upon returning to this problem, which I thought I had fixed using the
> > solution below, I have encountered another problem.
> >
> > The database records data items for each weekday, but not on Saturday
> > or Sunday. This means that if a rating change takes place on a
> > Monday, then it will not show up in the query outlined below.
> > The SQL statement (included below) compares ratings from Day 0 with
> > those from Day 0+1.
> >
> > SELECT Data.Date, Data.Rating, Data.Type, Data.[Target price],
> > Data.Analyst_ID, Data.Company_ID
> > FROM Data INNER JOIN Data AS Data_1 ON (Data.Company_ID =
> > Data_1.Company_ID) AND (Data.Date = Data_1.Date+1)
> > WHERE Data.Rating <> Data_1.Rating;
> >
> >
> >
> > If a rating change took place on 5 Dec (Friday), then this would be
> > picked up when the query compared 20031204 data with data on 20031205
> > (YYYMMDD dates). However, data is not stored for Sat or Sun, so if
> > the change took place on 8 Dec (Monday), the query will not compare
> > Friday with Monday, but will make Monday - Tuesday the next
> > comparison, hence not picking up the rating change.
> >
> > I have tried extending the SQL as follows to compare each day with
> > day+1 and day+3, so that rating chages on mondays will be picked up
> > (Friday+3=Monday).
> >
> > AND (Data.Date = Data_1.Date+1) OR (Data.Date = Data_1.Date+3)
> >
> >
> > I realise that this would then need filtering, as each rating change
> > could potentially appear a few times. However, there seems to be a
> > huge amount of data returned (1000s of records, as opposed to the 180
> > returned before amending the query), which suggests that there is
> > something wrong with the SQL. Furthermore, this seems a very clunky
> > way of doing it.
> >
> > Is there any way of inserting an "If" statement into the SQL, to tell
> > the query the if Day+1 does not exist, then (as Day must therefore be
> > a Friday) compare with Day+3?
> >
> > Again, thanks in advance for any help anybody can provide.
> >
> > Dom Boyce
> >
> >
> > domboyce@totalise.co.uk (Dom Boyce) wrote in message[/color]
> news:<7c016cf5.0311240942.be6b58f@posting.google.c om>...[color=green][color=darkred]
> > > Turtle,
> > >
> > > thanks a lot for this - it worked perfectly and saved me a huge amount[/color][/color]
> of time.[color=green][color=darkred]
> > >
> > > Dom
> > >
> > >
> > > "MacDermott" <macdermott@nospam.com> wrote in message[/color][/color]
> news:<8MSvb.15629$Rk5.12635@newsread1.news.atl.ear thlink.net>...[color=green][color=darkred]
> > > > How about this:
> > > > Create a query with two copies of your table.
> > > > Link them on CompanyID and on[/color][/color]
> MyTable.RecordDate=MyTable_1.RecordDate+1[color=green][color=darkred]
> > > > (you'll have to edit the SQL to get the latter join - it can't be[/color][/color]
> built in[color=green][color=darkred]
> > > > the grid directly.)
> > > > Use the criteria line to pick out only items where the rating has[/color][/color]
> changed.[color=green][color=darkred]
> > > > Add whatever fields you need for your new table.
> > > > Turn the query into a make-table query.
> > > >
> > > > Run it once, and you should be done.
> > > >
> > > > HTH
> > > > - Turtle
> > > >
> > > > "Dom Boyce" <domboyce@totalise.co.uk> wrote in message
> > > > news:7c016cf5.0311210356.279c425@posting.google.co m...
> > > > > Hi
> > > > >
> > > > > First up, I am using MS Access 2002.
> > > > >
> > > > > I have a database which records analyst rating changes for a list of
> > > > > companies on a daily basis. Unfortunately, the database has been set
> > > > > up (by my predecessor, I hasten to add) so that each day it creates[/color][/color]
> a[color=green][color=darkred]
> > > > > copy of the record for each company, changes the date to today's[/color][/color]
> date,[color=green][color=darkred]
> > > > > and prompts the user for any changes of ratings on that day. The
> > > > > resulting data table grows by approx 600 records per day.
> > > > >
> > > > > If starting from scratch, the obvious way to do this would be to
> > > > > record only the changes each day, thus creating a much smaller data
> > > > > table. with an eye to doing this in te future, i would like to go
> > > > > through every date in the data table, comparing the rating for each
> > > > > company with that of the day before, and appending a new record to a
> > > > > table "tblChanges" each time a change is detected.
> > > > >
> > > > > I have created 2 queries to extract data for the two days for
> > > > > comparison, and a third query which links the 2 original queries by
> > > > > Company_ID, and appends any records where the rating has changed to
> > > > > the table "tblChanges". This works fine, but I do not fancy running
> > > > > the query x amount of times, manually entering the new dates for[/color][/color]
> each[color=green][color=darkred]
> > > > > comparison.
> > > > >
> > > > > It seems that using recordsets in VBA is the way to automate this
> > > > > process. I imagine creating 2 recordsets which can each have the[/color][/color]
> date[color=green][color=darkred]
> > > > > variable passed to them from the VBA code - one with data on one[/color][/color]
> day,[color=green][color=darkred]
> > > > > and one with data on the next. I then need to run the append query[/color][/color]
> on[color=green][color=darkred]
> > > > > these recordsets, to append any changed records to tblChanges.
> > > > >
> > > > > I have very little experience of using recordsets, and have pieced
> > > > > together the following code from newsgroups:
> > > > >
> > > > > ------------------------------
> > > > >
> > > > > Sub FindChanges()
> > > > >
> > > > > Dim db As DAO.Database
> > > > > Dim rstOld As DAO.Recordset
> > > > > Dim rstNew As DAO.Recordset
> > > > > Dim qdfOld As DAO.QueryDef
> > > > > Dim qdfNew As DAO.QueryDef
> > > > > Dim DateOld As Date
> > > > > Dim DateNew As Date
> > > > >
> > > > > Set db = CurrentDb()
> > > > >
> > > > > DateOld = "01/07/03"
> > > > > DateNew = "01/08/03"
> > > > >
> > > > > Do
> > > > > ' create the querydef object
> > > > > Set qdfOld = db.QueryDefs("qryCompareOld")
> > > > > With qdfOld
> > > > > 'resolve the parameter
> > > > > .Parameters("Date") = DateOld
> > > > > 'open the recordset based on the Old data querydef
> > > > > Set rstOld = .OpenRecordset
> > > > > End With
> > > > >
> > > > > Set qdfNew = db.QueryDefs("qryCompareNew")
> > > > > With qdfNew
> > > > > 'resolve the parameter
> > > > > .Parameters("Date") = DateNew
> > > > > 'open the recordset based on the new data querydef
> > > > > Set rstNew = .OpenRecordset
> > > > > End With
> > > > >
> > > > > ' run the append query "qryFindChanges", which compares the
> > > > > results of CompareOld and CompareNew,
> > > > > 'and appends differences to table "tblChanges"
> > > > > DoCmd.OpenQuery "qryFindChanges"
> > > > >
> > > > >
> > > > > With rstOld
> > > > > .Close
> > > > > End With
> > > > >
> > > > > With rstNew
> > > > > .Close
> > > > > End With
> > > > >
> > > > > 'Change date variables to compare next day's data for changes
> > > > > DateOld = DateNew
> > > > > if DateNew+ 1 > Now() Then Exit Sub
> > > > > If WorkDay(DateNew + 1) = True Then
> > > > > DateNew = DateNew + 1
> > > > > Else
> > > > > If WorkDay(DateNew + 2) = True Then
> > > > > DateNew = DateNew + 2
> > > > > Else: DateNew = DateNew + 3
> > > > > End If
> > > > > End If
> > > > > End If
> > > > >
> > > > > Loop
> > > > >
> > > > > ' Destroy the object vars
> > > > > Set qdfOld = Nothing
> > > > > Set qdfNew = Nothing
> > > > > Set rstOld = Nothing
> > > > > Set rstNew = Nothing
> > > > > Set db = Nothing
> > > > >
> > > > > End Sub
> > > > >
> > > > > -------------------------------------
> > > > >
> > > > > The code currently halts at
> > > > > .Parameters("Date") = DateOld
> > > > > with the error "Item not found in this collection", although I[/color][/color]
> thought[color=green][color=darkred]
> > > > > Parameters was part of QueryDefs.
> > > > >
> > > > > I also imagine that an error will occur when running the
> > > > > "qryFindChanges" query, or at least the recordset data will not be
> > > > > used in that query. Does the coding for this query have to be
> > > > > included within the VBA code?
> > > > >
> > > > > If so, how would this be achieved, and how can I make the results
> > > > > append to the "tblChanges" table?
> > > > >
> > > > > Sorry for the essay - I think I can see how this should be done, but
> > > > > am too new to recordsets to get the coding spot on. Any help at all
> > > > > would be much appreciated.
> > > > >
> > > > > Dom[/color][/color][/color]

If you don't need to update the data on the form, you could use a union
query.

HTH
- Turtle

"Dom Boyce" <domboyce@totalise.co.uk> wrote in message
news:7c016cf5.0312240541.52e28c36@posting.google.c om...[color=blue]
> Thanks again for your help. I had to modify the suggested solution,
> as "vbFriday" was not recognised in SQL - replacing it with "6", the
> day number of Friday, at least allowed the query to run.
>
> however, the returned results were not as desired - they contained a
> lot of duplicate records and, een after hours of fiddling, i could not
> return the correct data.
>
> By creating two queries, one for Fridays and one for other weekdays, I
> can pull the required data out. The SQL for this follows. NB this has
> been extended from the last SQL posted, in order to view changes not
> only in Rating, but also in Type and Analyst, but the FROM and WHERE
> sections illustrate how a full set of data is obtained:
>
> qryFriday
> SELECT Data_1.Date, Data_1.Rating AS [New Rating], Data_1.Type AS [New
> Type], Analysts_1.LastName AS [New Analyst], Data.Rating AS [Old
> Rating], Data.Type AS [Old Type], Analysts.LastName AS [Old Analyst],
> Data.[Target price], Companies.Name,
> iif([Data].[Analyst_ID]<>[Data_1].[Analyst_ID],IIf(nz([New
> Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
> Type],"RTA","RA"),IIf([New Type]<>[Old Type],"TA","A")), IIf(nz([New
> Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
> Type],"RT","R"),IIf([New Type]<>[Old Type],"T",""))) AS [Change Type]
> FROM (Companies INNER JOIN (Analysts INNER JOIN (Data INNER JOIN Data
> AS Data_1 ON (Data.Company_ID = Data_1.Company_ID) AND
> (Data_1.Date=Data.Date+3)) ON Analysts.Analyst_ID = Data.Analyst_ID)
> ON Companies.Company_ID = Data.Company_ID) INNER JOIN Analysts AS
> Analysts_1 ON Data_1.Analyst_ID = Analysts_1.Analyst_ID
> WHERE (((nz([Data].[Rating]))<>nz([Data_1].[Rating])) AND
> ((Weekday([Data].[Date]))=6)) OR (((Data.Type)<>[Data_1].[Type]) AND
> ((Weekday([Data].[Date]))=6)) OR
> (((Data.Analyst_ID)<>[Data_1].[Analyst_ID]) AND
> ((Weekday([Data].[Date]))=6))
> ORDER BY Data_1.Date, Companies.Name;
>
>
> qryOtherDays
> SELECT Data_1.Date, Data_1.Rating AS [New Rating], Data_1.Type AS [New
> Type], Analysts_1.LastName AS [New Analyst], Data.Rating AS [Old
> Rating], Data.Type AS [Old Type], Analysts.LastName AS [Old Analyst],
> Data.[Target price], Companies.Name,
> iif([Data].[Analyst_ID]<>[Data_1].[Analyst_ID],IIf(nz([New
> Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
> Type],"RTA","RA"),IIf([New Type]<>[Old Type],"TA","A")), IIf(nz([New
> Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
> Type],"RT","R"),IIf([New Type]<>[Old Type],"T",""))) AS [Change Type]
> FROM (Companies INNER JOIN (Analysts INNER JOIN (Data INNER JOIN Data
> AS Data_1 ON (Data.Company_ID = Data_1.Company_ID) AND
> (Data_1.Date=Data.Date+1)) ON Analysts.Analyst_ID = Data.Analyst_ID)
> ON Companies.Company_ID = Data.Company_ID) INNER JOIN Analysts AS
> Analysts_1 ON Data_1.Analyst_ID = Analysts_1.Analyst_ID
> WHERE (((nz([Data].[Rating]))<>nz([Data_1].[Rating])) AND
> ((Weekday([Data].[Date]))<>6)) OR (((Data.Type)<>[Data_1].[Type]) AND
> ((Weekday([Data].[Date]))<>6)) OR
> (((Data.Analyst_ID)<>[Data_1].[Analyst_ID]) AND
> ((Weekday([Data].[Date]))<>6))
> ORDER BY Data_1.Date, Companies.Name;
>
> However, I now need to combine these two queries so I can view the
> results in a form. is there a way of doing this, other than making
> both queries Append queries, basing a form on the resulting table and
> coding a FormLoad event to clear the table then run both append
> queries?
>
> Again, any help would be much appreciated. Dom
>
>
>
> "MacDermott" <macdermott@nospam.com> wrote in message[/color]
news:<XvCDb.997$Ts5.747@newsread2.news.atl.earthli nk.net>...[color=blue][color=green]
> > WARNING: AIR CODE
> >
> > Perhaps you could use something like this:
> >
> > SELECT Data.Date, Data.Rating, Data.Type, Data.[Target price],
> > Data.Analyst_ID, Data.Company_ID
> > FROM Data , Data AS Data_1 WHERE (Data.Company_ID =
> > Data_1.Company_ID) AND (Data.Date =
> > IIF(WeekDay(Data.Date)=vbFriday,Data_1.Date+3,Data _1.Date+1) AND
> > Data.Rating <> Data_1.Rating;
> >
> > Changing from an inner join to a cross-product with WHERE conditions is[/color][/color]
not[color=blue][color=green]
> > something I have a good reason for - it just felt better to me here.[/color][/color]
You[color=blue][color=green]
> > could probably do it with an inner join, too.
> >
> > HTH
> > - Turtle
> > "Dom Boyce" <domboyce@totalise.co.uk> wrote in message
> > news:7c016cf5.0312150338.435624eb@posting.google.c om...[color=darkred]
> > > Upon returning to this problem, which I thought I had fixed using the
> > > solution below, I have encountered another problem.
> > >
> > > The database records data items for each weekday, but not on Saturday
> > > or Sunday. This means that if a rating change takes place on a
> > > Monday, then it will not show up in the query outlined below.
> > > The SQL statement (included below) compares ratings from Day 0 with
> > > those from Day 0+1.
> > >
> > > SELECT Data.Date, Data.Rating, Data.Type, Data.[Target price],
> > > Data.Analyst_ID, Data.Company_ID
> > > FROM Data INNER JOIN Data AS Data_1 ON (Data.Company_ID =
> > > Data_1.Company_ID) AND (Data.Date = Data_1.Date+1)
> > > WHERE Data.Rating <> Data_1.Rating;
> > >
> > >
> > >
> > > If a rating change took place on 5 Dec (Friday), then this would be
> > > picked up when the query compared 20031204 data with data on 20031205
> > > (YYYMMDD dates). However, data is not stored for Sat or Sun, so if
> > > the change took place on 8 Dec (Monday), the query will not compare
> > > Friday with Monday, but will make Monday - Tuesday the next
> > > comparison, hence not picking up the rating change.
> > >
> > > I have tried extending the SQL as follows to compare each day with
> > > day+1 and day+3, so that rating chages on mondays will be picked up
> > > (Friday+3=Monday).
> > >
> > > AND (Data.Date = Data_1.Date+1) OR (Data.Date = Data_1.Date+3)
> > >
> > >
> > > I realise that this would then need filtering, as each rating change
> > > could potentially appear a few times. However, there seems to be a
> > > huge amount of data returned (1000s of records, as opposed to the 180
> > > returned before amending the query), which suggests that there is
> > > something wrong with the SQL. Furthermore, this seems a very clunky
> > > way of doing it.
> > >
> > > Is there any way of inserting an "If" statement into the SQL, to tell
> > > the query the if Day+1 does not exist, then (as Day must therefore be
> > > a Friday) compare with Day+3?
> > >
> > > Again, thanks in advance for any help anybody can provide.
> > >
> > > Dom Boyce
> > >
> > >
> > > domboyce@totalise.co.uk (Dom Boyce) wrote in message[/color]
> > news:<7c016cf5.0311240942.be6b58f@posting.google.c om>...[color=darkred]
> > > > Turtle,
> > > >
> > > > thanks a lot for this - it worked perfectly and saved me a huge[/color][/color][/color]
amount[color=blue][color=green]
> > of time.[color=darkred]
> > > >
> > > > Dom
> > > >
> > > >
> > > > "MacDermott" <macdermott@nospam.com> wrote in message[/color]
> > news:<8MSvb.15629$Rk5.12635@newsread1.news.atl.ear thlink.net>...[color=darkred]
> > > > > How about this:
> > > > > Create a query with two copies of your table.
> > > > > Link them on CompanyID and on[/color]
> > MyTable.RecordDate=MyTable_1.RecordDate+1[color=darkred]
> > > > > (you'll have to edit the SQL to get the latter join - it can't[/color][/color][/color]
be[color=blue][color=green]
> > built in[color=darkred]
> > > > > the grid directly.)
> > > > > Use the criteria line to pick out only items where the rating has[/color]
> > changed.[color=darkred]
> > > > > Add whatever fields you need for your new table.
> > > > > Turn the query into a make-table query.
> > > > >
> > > > > Run it once, and you should be done.
> > > > >
> > > > > HTH
> > > > > - Turtle
> > > > >
> > > > > "Dom Boyce" <domboyce@totalise.co.uk> wrote in message
> > > > > news:7c016cf5.0311210356.279c425@posting.google.co m...
> > > > > > Hi
> > > > > >
> > > > > > First up, I am using MS Access 2002.
> > > > > >
> > > > > > I have a database which records analyst rating changes for a[/color][/color][/color]
list of[color=blue][color=green][color=darkred]
> > > > > > companies on a daily basis. Unfortunately, the database has been[/color][/color][/color]
set[color=blue][color=green][color=darkred]
> > > > > > up (by my predecessor, I hasten to add) so that each day it[/color][/color][/color]
creates[color=blue][color=green]
> > a[color=darkred]
> > > > > > copy of the record for each company, changes the date to today's[/color]
> > date,[color=darkred]
> > > > > > and prompts the user for any changes of ratings on that day.[/color][/color][/color]
The[color=blue][color=green][color=darkred]
> > > > > > resulting data table grows by approx 600 records per day.
> > > > > >
> > > > > > If starting from scratch, the obvious way to do this would be to
> > > > > > record only the changes each day, thus creating a much smaller[/color][/color][/color]
data[color=blue][color=green][color=darkred]
> > > > > > table. with an eye to doing this in te future, i would like to[/color][/color][/color]
go[color=blue][color=green][color=darkred]
> > > > > > through every date in the data table, comparing the rating for[/color][/color][/color]
each[color=blue][color=green][color=darkred]
> > > > > > company with that of the day before, and appending a new record[/color][/color][/color]
to a[color=blue][color=green][color=darkred]
> > > > > > table "tblChanges" each time a change is detected.
> > > > > >
> > > > > > I have created 2 queries to extract data for the two days for
> > > > > > comparison, and a third query which links the 2 original queries[/color][/color][/color]
by[color=blue][color=green][color=darkred]
> > > > > > Company_ID, and appends any records where the rating has changed[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > > > > the table "tblChanges". This works fine, but I do not fancy[/color][/color][/color]
running[color=blue][color=green][color=darkred]
> > > > > > the query x amount of times, manually entering the new dates for[/color]
> > each[color=darkred]
> > > > > > comparison.
> > > > > >
> > > > > > It seems that using recordsets in VBA is the way to automate[/color][/color][/color]
this[color=blue][color=green][color=darkred]
> > > > > > process. I imagine creating 2 recordsets which can each have[/color][/color][/color]
the[color=blue][color=green]
> > date[color=darkred]
> > > > > > variable passed to them from the VBA code - one with data on one[/color]
> > day,[color=darkred]
> > > > > > and one with data on the next. I then need to run the append[/color][/color][/color]
query[color=blue][color=green]
> > on[color=darkred]
> > > > > > these recordsets, to append any changed records to tblChanges.
> > > > > >
> > > > > > I have very little experience of using recordsets, and have[/color][/color][/color]
pieced[color=blue][color=green][color=darkred]
> > > > > > together the following code from newsgroups:
> > > > > >
> > > > > > ------------------------------
> > > > > >
> > > > > > Sub FindChanges()
> > > > > >
> > > > > > Dim db As DAO.Database
> > > > > > Dim rstOld As DAO.Recordset
> > > > > > Dim rstNew As DAO.Recordset
> > > > > > Dim qdfOld As DAO.QueryDef
> > > > > > Dim qdfNew As DAO.QueryDef
> > > > > > Dim DateOld As Date
> > > > > > Dim DateNew As Date
> > > > > >
> > > > > > Set db = CurrentDb()
> > > > > >
> > > > > > DateOld = "01/07/03"
> > > > > > DateNew = "01/08/03"
> > > > > >
> > > > > > Do
> > > > > > ' create the querydef object
> > > > > > Set qdfOld = db.QueryDefs("qryCompareOld")
> > > > > > With qdfOld
> > > > > > 'resolve the parameter
> > > > > > .Parameters("Date") = DateOld
> > > > > > 'open the recordset based on the Old data querydef
> > > > > > Set rstOld = .OpenRecordset
> > > > > > End With
> > > > > >
> > > > > > Set qdfNew = db.QueryDefs("qryCompareNew")
> > > > > > With qdfNew
> > > > > > 'resolve the parameter
> > > > > > .Parameters("Date") = DateNew
> > > > > > 'open the recordset based on the new data querydef
> > > > > > Set rstNew = .OpenRecordset
> > > > > > End With
> > > > > >
> > > > > > ' run the append query "qryFindChanges", which compares the
> > > > > > results of CompareOld and CompareNew,
> > > > > > 'and appends differences to table "tblChanges"
> > > > > > DoCmd.OpenQuery "qryFindChanges"
> > > > > >
> > > > > >
> > > > > > With rstOld
> > > > > > .Close
> > > > > > End With
> > > > > >
> > > > > > With rstNew
> > > > > > .Close
> > > > > > End With
> > > > > >
> > > > > > 'Change date variables to compare next day's data for changes
> > > > > > DateOld = DateNew
> > > > > > if DateNew+ 1 > Now() Then Exit Sub
> > > > > > If WorkDay(DateNew + 1) = True Then
> > > > > > DateNew = DateNew + 1
> > > > > > Else
> > > > > > If WorkDay(DateNew + 2) = True Then
> > > > > > DateNew = DateNew + 2
> > > > > > Else: DateNew = DateNew + 3
> > > > > > End If
> > > > > > End If
> > > > > > End If
> > > > > >
> > > > > > Loop
> > > > > >
> > > > > > ' Destroy the object vars
> > > > > > Set qdfOld = Nothing
> > > > > > Set qdfNew = Nothing
> > > > > > Set rstOld = Nothing
> > > > > > Set rstNew = Nothing
> > > > > > Set db = Nothing
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > -------------------------------------
> > > > > >
> > > > > > The code currently halts at
> > > > > > .Parameters("Date") = DateOld
> > > > > > with the error "Item not found in this collection", although I[/color]
> > thought[color=darkred]
> > > > > > Parameters was part of QueryDefs.
> > > > > >
> > > > > > I also imagine that an error will occur when running the
> > > > > > "qryFindChanges" query, or at least the recordset data will not[/color][/color][/color]
be[color=blue][color=green][color=darkred]
> > > > > > used in that query. Does the coding for this query have to be
> > > > > > included within the VBA code?
> > > > > >
> > > > > > If so, how would this be achieved, and how can I make the[/color][/color][/color]
results[color=blue][color=green][color=darkred]
> > > > > > append to the "tblChanges" table?
> > > > > >
> > > > > > Sorry for the essay - I think I can see how this should be done,[/color][/color][/color]
but[color=blue][color=green][color=darkred]
> > > > > > am too new to recordsets to get the coding spot on. Any help at[/color][/color][/color]
all[color=blue][color=green][color=darkred]
> > > > > > would be much appreciated.
> > > > > >
> > > > > > Dom[/color][/color][/color]

This is exactly what I wanted Turtle, thanks again for all your help.

I say exactly what i wanted - it is exactly what I asked for but, as
usual, another issue has cropped up. I now use the union query to
effectively display records from two queries, which detect changes in
one or all of three variables from one day to another for each
Company.

However, i need to include in this query's results:
1. A record each time a completely new copmany is added.
2. Company information on the FIRST day the data begins

Currently, the queries are linked on Company_ID so that each day, each
company is compared with its status the day before. However, when a
new company is added on a particular day, and for companies on the
first day that data was recorded, there is no Company_ID for that
company on the previous day, hence no comparison takes place, and no
record is shown in the query.

Is it possible to add another UNION to the query, which would
calculate something like:

SELECT Data_1.Company_ID, Data_1.Date etc
FROM (not sure what would go here)
WHERE Data.CompanyID Does not exist for any given Data_1.Company_ID

Sorry for being a pain about this - I am fairly new to SQL, but am
being forced to pick it up very quickly, and appreciate all the help I
can get.

Happy New Year,

Dom

"MacDermott" <macdermott@nospam.com> wrote in message news:<fImGb.8037$IM3.713@newsread3.news.atl.earthl ink.net>...[color=blue]
> If you don't need to update the data on the form, you could use a union
> query.
>
> HTH
> - Turtle
>
> "Dom Boyce" <domboyce@totalise.co.uk> wrote in message
> news:7c016cf5.0312240541.52e28c36@posting.google.c om...[color=green]
> > Thanks again for your help. I had to modify the suggested solution,
> > as "vbFriday" was not recognised in SQL - replacing it with "6", the
> > day number of Friday, at least allowed the query to run.
> >
> > however, the returned results were not as desired - they contained a
> > lot of duplicate records and, een after hours of fiddling, i could not
> > return the correct data.
> >
> > By creating two queries, one for Fridays and one for other weekdays, I
> > can pull the required data out. The SQL for this follows. NB this has
> > been extended from the last SQL posted, in order to view changes not
> > only in Rating, but also in Type and Analyst, but the FROM and WHERE
> > sections illustrate how a full set of data is obtained:
> >
> > qryFriday
> > SELECT Data_1.Date, Data_1.Rating AS [New Rating], Data_1.Type AS [New
> > Type], Analysts_1.LastName AS [New Analyst], Data.Rating AS [Old
> > Rating], Data.Type AS [Old Type], Analysts.LastName AS [Old Analyst],
> > Data.[Target price], Companies.Name,
> > iif([Data].[Analyst_ID]<>[Data_1].[Analyst_ID],IIf(nz([New
> > Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
> > Type],"RTA","RA"),IIf([New Type]<>[Old Type],"TA","A")), IIf(nz([New
> > Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
> > Type],"RT","R"),IIf([New Type]<>[Old Type],"T",""))) AS [Change Type]
> > FROM (Companies INNER JOIN (Analysts INNER JOIN (Data INNER JOIN Data
> > AS Data_1 ON (Data.Company_ID = Data_1.Company_ID) AND
> > (Data_1.Date=Data.Date+3)) ON Analysts.Analyst_ID = Data.Analyst_ID)
> > ON Companies.Company_ID = Data.Company_ID) INNER JOIN Analysts AS
> > Analysts_1 ON Data_1.Analyst_ID = Analysts_1.Analyst_ID
> > WHERE (((nz([Data].[Rating]))<>nz([Data_1].[Rating])) AND
> > ((Weekday([Data].[Date]))=6)) OR (((Data.Type)<>[Data_1].[Type]) AND
> > ((Weekday([Data].[Date]))=6)) OR
> > (((Data.Analyst_ID)<>[Data_1].[Analyst_ID]) AND
> > ((Weekday([Data].[Date]))=6))
> > ORDER BY Data_1.Date, Companies.Name;
> >
> >
> > qryOtherDays
> > SELECT Data_1.Date, Data_1.Rating AS [New Rating], Data_1.Type AS [New
> > Type], Analysts_1.LastName AS [New Analyst], Data.Rating AS [Old
> > Rating], Data.Type AS [Old Type], Analysts.LastName AS [Old Analyst],
> > Data.[Target price], Companies.Name,
> > iif([Data].[Analyst_ID]<>[Data_1].[Analyst_ID],IIf(nz([New
> > Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
> > Type],"RTA","RA"),IIf([New Type]<>[Old Type],"TA","A")), IIf(nz([New
> > Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
> > Type],"RT","R"),IIf([New Type]<>[Old Type],"T",""))) AS [Change Type]
> > FROM (Companies INNER JOIN (Analysts INNER JOIN (Data INNER JOIN Data
> > AS Data_1 ON (Data.Company_ID = Data_1.Company_ID) AND
> > (Data_1.Date=Data.Date+1)) ON Analysts.Analyst_ID = Data.Analyst_ID)
> > ON Companies.Company_ID = Data.Company_ID) INNER JOIN Analysts AS
> > Analysts_1 ON Data_1.Analyst_ID = Analysts_1.Analyst_ID
> > WHERE (((nz([Data].[Rating]))<>nz([Data_1].[Rating])) AND
> > ((Weekday([Data].[Date]))<>6)) OR (((Data.Type)<>[Data_1].[Type]) AND
> > ((Weekday([Data].[Date]))<>6)) OR
> > (((Data.Analyst_ID)<>[Data_1].[Analyst_ID]) AND
> > ((Weekday([Data].[Date]))<>6))
> > ORDER BY Data_1.Date, Companies.Name;
> >
> > However, I now need to combine these two queries so I can view the
> > results in a form. is there a way of doing this, other than making
> > both queries Append queries, basing a form on the resulting table and
> > coding a FormLoad event to clear the table then run both append
> > queries?
> >
> > Again, any help would be much appreciated. Dom
> >
> >
> >
> > "MacDermott" <macdermott@nospam.com> wrote in message[/color]
> news:<XvCDb.997$Ts5.747@newsread2.news.atl.earthli nk.net>...[color=green][color=darkred]
> > > WARNING: AIR CODE
> > >
> > > Perhaps you could use something like this:
> > >
> > > SELECT Data.Date, Data.Rating, Data.Type, Data.[Target price],
> > > Data.Analyst_ID, Data.Company_ID
> > > FROM Data , Data AS Data_1 WHERE (Data.Company_ID =
> > > Data_1.Company_ID) AND (Data.Date =
> > > IIF(WeekDay(Data.Date)=vbFriday,Data_1.Date+3,Data _1.Date+1) AND
> > > Data.Rating <> Data_1.Rating;
> > >
> > > Changing from an inner join to a cross-product with WHERE conditions is[/color][/color]
> not[color=green][color=darkred]
> > > something I have a good reason for - it just felt better to me here.[/color][/color]
> You[color=green][color=darkred]
> > > could probably do it with an inner join, too.
> > >
> > > HTH
> > > - Turtle
> > > "Dom Boyce" <domboyce@totalise.co.uk> wrote in message
> > > news:7c016cf5.0312150338.435624eb@posting.google.c om...
> > > > Upon returning to this problem, which I thought I had fixed using the
> > > > solution below, I have encountered another problem.
> > > >
> > > > The database records data items for each weekday, but not on Saturday
> > > > or Sunday. This means that if a rating change takes place on a
> > > > Monday, then it will not show up in the query outlined below.
> > > > The SQL statement (included below) compares ratings from Day 0 with
> > > > those from Day 0+1.
> > > >
> > > > SELECT Data.Date, Data.Rating, Data.Type, Data.[Target price],
> > > > Data.Analyst_ID, Data.Company_ID
> > > > FROM Data INNER JOIN Data AS Data_1 ON (Data.Company_ID =
> > > > Data_1.Company_ID) AND (Data.Date = Data_1.Date+1)
> > > > WHERE Data.Rating <> Data_1.Rating;
> > > >
> > > >
> > > >
> > > > If a rating change took place on 5 Dec (Friday), then this would be
> > > > picked up when the query compared 20031204 data with data on 20031205
> > > > (YYYMMDD dates). However, data is not stored for Sat or Sun, so if
> > > > the change took place on 8 Dec (Monday), the query will not compare
> > > > Friday with Monday, but will make Monday - Tuesday the next
> > > > comparison, hence not picking up the rating change.
> > > >
> > > > I have tried extending the SQL as follows to compare each day with
> > > > day+1 and day+3, so that rating chages on mondays will be picked up
> > > > (Friday+3=Monday).
> > > >
> > > > AND (Data.Date = Data_1.Date+1) OR (Data.Date = Data_1.Date+3)
> > > >
> > > >
> > > > I realise that this would then need filtering, as each rating change
> > > > could potentially appear a few times. However, there seems to be a
> > > > huge amount of data returned (1000s of records, as opposed to the 180
> > > > returned before amending the query), which suggests that there is
> > > > something wrong with the SQL. Furthermore, this seems a very clunky
> > > > way of doing it.
> > > >
> > > > Is there any way of inserting an "If" statement into the SQL, to tell
> > > > the query the if Day+1 does not exist, then (as Day must therefore be
> > > > a Friday) compare with Day+3?
> > > >
> > > > Again, thanks in advance for any help anybody can provide.
> > > >
> > > > Dom Boyce
> > > >
> > > >
> > > > domboyce@totalise.co.uk (Dom Boyce) wrote in message[/color][/color]
> news:<7c016cf5.0311240942.be6b58f@posting.google.c om>...[color=green][color=darkred]
> > > > > Turtle,
> > > > >
> > > > > thanks a lot for this - it worked perfectly and saved me a huge[/color][/color]
> amount
> of time.[color=green][color=darkred]
> > > > >
> > > > > Dom
> > > > >
> > > > >
> > > > > "MacDermott" <macdermott@nospam.com> wrote in message[/color][/color]
> news:<8MSvb.15629$Rk5.12635@newsread1.news.atl.ear thlink.net>...[color=green][color=darkred]
> > > > > > How about this:
> > > > > > Create a query with two copies of your table.
> > > > > > Link them on CompanyID and on[/color][/color]
> MyTable.RecordDate=MyTable_1.RecordDate+1[color=green][color=darkred]
> > > > > > (you'll have to edit the SQL to get the latter join - it can't[/color][/color]
> be
> built in[color=green][color=darkred]
> > > > > > the grid directly.)
> > > > > > Use the criteria line to pick out only items where the rating has[/color][/color]
> changed.[color=green][color=darkred]
> > > > > > Add whatever fields you need for your new table.
> > > > > > Turn the query into a make-table query.
> > > > > >
> > > > > > Run it once, and you should be done.
> > > > > >
> > > > > > HTH
> > > > > > - Turtle
> > > > > >
> > > > > > "Dom Boyce" <domboyce@totalise.co.uk> wrote in message
> > > > > > news:7c016cf5.0311210356.279c425@posting.google.co m...
> > > > > > > Hi
> > > > > > >
> > > > > > > First up, I am using MS Access 2002.
> > > > > > >
> > > > > > > I have a database which records analyst rating changes for a[/color][/color]
> list of[color=green][color=darkred]
> > > > > > > companies on a daily basis. Unfortunately, the database has been[/color][/color]
> set[color=green][color=darkred]
> > > > > > > up (by my predecessor, I hasten to add) so that each day it[/color][/color]
> creates
> a[color=green][color=darkred]
> > > > > > > copy of the record for each company, changes the date to today's[/color][/color]
> date,[color=green][color=darkred]
> > > > > > > and prompts the user for any changes of ratings on that day.[/color][/color]
> The[color=green][color=darkred]
> > > > > > > resulting data table grows by approx 600 records per day.
> > > > > > >
> > > > > > > If starting from scratch, the obvious way to do this would be to
> > > > > > > record only the changes each day, thus creating a much smaller[/color][/color]
> data[color=green][color=darkred]
> > > > > > > table. with an eye to doing this in te future, i would like to[/color][/color]
> go[color=green][color=darkred]
> > > > > > > through every date in the data table, comparing the rating for[/color][/color]
> each[color=green][color=darkred]
> > > > > > > company with that of the day before, and appending a new record[/color][/color]
> to a[color=green][color=darkred]
> > > > > > > table "tblChanges" each time a change is detected.
> > > > > > >
> > > > > > > I have created 2 queries to extract data for the two days for
> > > > > > > comparison, and a third query which links the 2 original queries[/color][/color]
> by[color=green][color=darkred]
> > > > > > > Company_ID, and appends any records where the rating has changed[/color][/color]
> to[color=green][color=darkred]
> > > > > > > the table "tblChanges". This works fine, but I do not fancy[/color][/color]
> running[color=green][color=darkred]
> > > > > > > the query x amount of times, manually entering the new dates for[/color][/color]
> each[color=green][color=darkred]
> > > > > > > comparison.
> > > > > > >
> > > > > > > It seems that using recordsets in VBA is the way to automate[/color][/color]
> this[color=green][color=darkred]
> > > > > > > process. I imagine creating 2 recordsets which can each have[/color][/color]
> the
> date[color=green][color=darkred]
> > > > > > > variable passed to them from the VBA code - one with data on one[/color][/color]
> day,[color=green][color=darkred]
> > > > > > > and one with data on the next. I then need to run the append[/color][/color]
> query
> on[color=green][color=darkred]
> > > > > > > these recordsets, to append any changed records to tblChanges.
> > > > > > >
> > > > > > > I have very little experience of using recordsets, and have[/color][/color]
> pieced[color=green][color=darkred]
> > > > > > > together the following code from newsgroups:
> > > > > > >
> > > > > > > ------------------------------
> > > > > > >
> > > > > > > Sub FindChanges()
> > > > > > >
> > > > > > > Dim db As DAO.Database
> > > > > > > Dim rstOld As DAO.Recordset
> > > > > > > Dim rstNew As DAO.Recordset
> > > > > > > Dim qdfOld As DAO.QueryDef
> > > > > > > Dim qdfNew As DAO.QueryDef
> > > > > > > Dim DateOld As Date
> > > > > > > Dim DateNew As Date
> > > > > > >
> > > > > > > Set db = CurrentDb()
> > > > > > >
> > > > > > > DateOld = "01/07/03"
> > > > > > > DateNew = "01/08/03"
> > > > > > >
> > > > > > > Do
> > > > > > > ' create the querydef object
> > > > > > > Set qdfOld = db.QueryDefs("qryCompareOld")
> > > > > > > With qdfOld
> > > > > > > 'resolve the parameter
> > > > > > > .Parameters("Date") = DateOld
> > > > > > > 'open the recordset based on the Old data querydef
> > > > > > > Set rstOld = .OpenRecordset
> > > > > > > End With
> > > > > > >
> > > > > > > Set qdfNew = db.QueryDefs("qryCompareNew")
> > > > > > > With qdfNew
> > > > > > > 'resolve the parameter
> > > > > > > .Parameters("Date") = DateNew
> > > > > > > 'open the recordset based on the new data querydef
> > > > > > > Set rstNew = .OpenRecordset
> > > > > > > End With
> > > > > > >
> > > > > > > ' run the append query "qryFindChanges", which compares the
> > > > > > > results of CompareOld and CompareNew,
> > > > > > > 'and appends differences to table "tblChanges"
> > > > > > > DoCmd.OpenQuery "qryFindChanges"
> > > > > > >
> > > > > > >
> > > > > > > With rstOld
> > > > > > > .Close
> > > > > > > End With
> > > > > > >
> > > > > > > With rstNew
> > > > > > > .Close
> > > > > > > End With
> > > > > > >
> > > > > > > 'Change date variables to compare next day's data for changes
> > > > > > > DateOld = DateNew
> > > > > > > if DateNew+ 1 > Now() Then Exit Sub
> > > > > > > If WorkDay(DateNew + 1) = True Then
> > > > > > > DateNew = DateNew + 1
> > > > > > > Else
> > > > > > > If WorkDay(DateNew + 2) = True Then
> > > > > > > DateNew = DateNew + 2
> > > > > > > Else: DateNew = DateNew + 3
> > > > > > > End If
> > > > > > > End If
> > > > > > > End If
> > > > > > >
> > > > > > > Loop
> > > > > > >
> > > > > > > ' Destroy the object vars
> > > > > > > Set qdfOld = Nothing
> > > > > > > Set qdfNew = Nothing
> > > > > > > Set rstOld = Nothing
> > > > > > > Set rstNew = Nothing
> > > > > > > Set db = Nothing
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > > -------------------------------------
> > > > > > >
> > > > > > > The code currently halts at
> > > > > > > .Parameters("Date") = DateOld
> > > > > > > with the error "Item not found in this collection", although I[/color][/color]
> thought[color=green][color=darkred]
> > > > > > > Parameters was part of QueryDefs.
> > > > > > >
> > > > > > > I also imagine that an error will occur when running the
> > > > > > > "qryFindChanges" query, or at least the recordset data will not[/color][/color]
> be[color=green][color=darkred]
> > > > > > > used in that query. Does the coding for this query have to be
> > > > > > > included within the VBA code?
> > > > > > >
> > > > > > > If so, how would this be achieved, and how can I make the[/color][/color]
> results[color=green][color=darkred]
> > > > > > > append to the "tblChanges" table?
> > > > > > >
> > > > > > > Sorry for the essay - I think I can see how this should be done,[/color][/color]
> but[color=green][color=darkred]
> > > > > > > am too new to recordsets to get the coding spot on. Any help at[/color][/color]
> all[color=green][color=darkred]
> > > > > > > would be much appreciated.
> > > > > > >
> > > > > > > Dom[/color][/color][/color]

You can use an aggregate (or totals) query to get the first (Min) date for
each company in your DATA table.
Then join this back to the DATA table to get the other information you want
for that record.
You can then UNION this query with the ones you already have.

I think it could also be done with a subquery, but it escapes me at the
moment just how that would work.

HTH
- Turtle

"Dom Boyce" <domboyce@totalise.co.uk> wrote in message
news:7c016cf5.0401070824.3c2129d2@posting.google.c om...[color=blue]
> This is exactly what I wanted Turtle, thanks again for all your help.
>
> I say exactly what i wanted - it is exactly what I asked for but, as
> usual, another issue has cropped up. I now use the union query to
> effectively display records from two queries, which detect changes in
> one or all of three variables from one day to another for each
> Company.
>
> However, i need to include in this query's results:
> 1. A record each time a completely new copmany is added.
> 2. Company information on the FIRST day the data begins
>
>
> Currently, the queries are linked on Company_ID so that each day, each
> company is compared with its status the day before. However, when a
> new company is added on a particular day, and for companies on the
> first day that data was recorded, there is no Company_ID for that
> company on the previous day, hence no comparison takes place, and no
> record is shown in the query.
>
> Is it possible to add another UNION to the query, which would
> calculate something like:
>
> SELECT Data_1.Company_ID, Data_1.Date etc
> FROM (not sure what would go here)
> WHERE Data.CompanyID Does not exist for any given Data_1.Company_ID
>
> Sorry for being a pain about this - I am fairly new to SQL, but am
> being forced to pick it up very quickly, and appreciate all the help I
> can get.
>
> Happy New Year,
>
> Dom
>
>
>
> "MacDermott" <macdermott@nospam.com> wrote in message[/color]
news:<fImGb.8037$IM3.713@newsread3.news.atl.earthl ink.net>...[color=blue][color=green]
> > If you don't need to update the data on the form, you could use a union
> > query.
> >
> > HTH
> > - Turtle
> >
> > "Dom Boyce" <domboyce@totalise.co.uk> wrote in message
> > news:7c016cf5.0312240541.52e28c36@posting.google.c om...[color=darkred]
> > > Thanks again for your help. I had to modify the suggested solution,
> > > as "vbFriday" was not recognised in SQL - replacing it with "6", the
> > > day number of Friday, at least allowed the query to run.
> > >
> > > however, the returned results were not as desired - they contained a
> > > lot of duplicate records and, een after hours of fiddling, i could not
> > > return the correct data.
> > >
> > > By creating two queries, one for Fridays and one for other weekdays, I
> > > can pull the required data out. The SQL for this follows. NB this has
> > > been extended from the last SQL posted, in order to view changes not
> > > only in Rating, but also in Type and Analyst, but the FROM and WHERE
> > > sections illustrate how a full set of data is obtained:
> > >
> > > qryFriday
> > > SELECT Data_1.Date, Data_1.Rating AS [New Rating], Data_1.Type AS [New
> > > Type], Analysts_1.LastName AS [New Analyst], Data.Rating AS [Old
> > > Rating], Data.Type AS [Old Type], Analysts.LastName AS [Old Analyst],
> > > Data.[Target price], Companies.Name,
> > > iif([Data].[Analyst_ID]<>[Data_1].[Analyst_ID],IIf(nz([New
> > > Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
> > > Type],"RTA","RA"),IIf([New Type]<>[Old Type],"TA","A")), IIf(nz([New
> > > Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
> > > Type],"RT","R"),IIf([New Type]<>[Old Type],"T",""))) AS [Change Type]
> > > FROM (Companies INNER JOIN (Analysts INNER JOIN (Data INNER JOIN Data
> > > AS Data_1 ON (Data.Company_ID = Data_1.Company_ID) AND
> > > (Data_1.Date=Data.Date+3)) ON Analysts.Analyst_ID = Data.Analyst_ID)
> > > ON Companies.Company_ID = Data.Company_ID) INNER JOIN Analysts AS
> > > Analysts_1 ON Data_1.Analyst_ID = Analysts_1.Analyst_ID
> > > WHERE (((nz([Data].[Rating]))<>nz([Data_1].[Rating])) AND
> > > ((Weekday([Data].[Date]))=6)) OR (((Data.Type)<>[Data_1].[Type]) AND
> > > ((Weekday([Data].[Date]))=6)) OR
> > > (((Data.Analyst_ID)<>[Data_1].[Analyst_ID]) AND
> > > ((Weekday([Data].[Date]))=6))
> > > ORDER BY Data_1.Date, Companies.Name;
> > >
> > >
> > > qryOtherDays
> > > SELECT Data_1.Date, Data_1.Rating AS [New Rating], Data_1.Type AS [New
> > > Type], Analysts_1.LastName AS [New Analyst], Data.Rating AS [Old
> > > Rating], Data.Type AS [Old Type], Analysts.LastName AS [Old Analyst],
> > > Data.[Target price], Companies.Name,
> > > iif([Data].[Analyst_ID]<>[Data_1].[Analyst_ID],IIf(nz([New
> > > Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
> > > Type],"RTA","RA"),IIf([New Type]<>[Old Type],"TA","A")), IIf(nz([New
> > > Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
> > > Type],"RT","R"),IIf([New Type]<>[Old Type],"T",""))) AS [Change Type]
> > > FROM (Companies INNER JOIN (Analysts INNER JOIN (Data INNER JOIN Data
> > > AS Data_1 ON (Data.Company_ID = Data_1.Company_ID) AND
> > > (Data_1.Date=Data.Date+1)) ON Analysts.Analyst_ID = Data.Analyst_ID)
> > > ON Companies.Company_ID = Data.Company_ID) INNER JOIN Analysts AS
> > > Analysts_1 ON Data_1.Analyst_ID = Analysts_1.Analyst_ID
> > > WHERE (((nz([Data].[Rating]))<>nz([Data_1].[Rating])) AND
> > > ((Weekday([Data].[Date]))<>6)) OR (((Data.Type)<>[Data_1].[Type]) AND
> > > ((Weekday([Data].[Date]))<>6)) OR
> > > (((Data.Analyst_ID)<>[Data_1].[Analyst_ID]) AND
> > > ((Weekday([Data].[Date]))<>6))
> > > ORDER BY Data_1.Date, Companies.Name;
> > >
> > > However, I now need to combine these two queries so I can view the
> > > results in a form. is there a way of doing this, other than making
> > > both queries Append queries, basing a form on the resulting table and
> > > coding a FormLoad event to clear the table then run both append
> > > queries?
> > >
> > > Again, any help would be much appreciated. Dom
> > >
> > >
> > >
> > > "MacDermott" <macdermott@nospam.com> wrote in message[/color]
> > news:<XvCDb.997$Ts5.747@newsread2.news.atl.earthli nk.net>...[color=darkred]
> > > > WARNING: AIR CODE
> > > >
> > > > Perhaps you could use something like this:
> > > >
> > > > SELECT Data.Date, Data.Rating, Data.Type, Data.[Target price],
> > > > Data.Analyst_ID, Data.Company_ID
> > > > FROM Data , Data AS Data_1 WHERE (Data.Company_ID =
> > > > Data_1.Company_ID) AND (Data.Date =
> > > > IIF(WeekDay(Data.Date)=vbFriday,Data_1.Date+3,Data _1.Date+1) AND
> > > > Data.Rating <> Data_1.Rating;
> > > >
> > > > Changing from an inner join to a cross-product with WHERE conditions[/color][/color][/color]
is[color=blue][color=green]
> > not[color=darkred]
> > > > something I have a good reason for - it just felt better to me here.[/color]
> > You[color=darkred]
> > > > could probably do it with an inner join, too.
> > > >
> > > > HTH
> > > > - Turtle
> > > > "Dom Boyce" <domboyce@totalise.co.uk> wrote in message
> > > > news:7c016cf5.0312150338.435624eb@posting.google.c om...
> > > > > Upon returning to this problem, which I thought I had fixed using[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > > solution below, I have encountered another problem.
> > > > >
> > > > > The database records data items for each weekday, but not on[/color][/color][/color]
Saturday[color=blue][color=green][color=darkred]
> > > > > or Sunday. This means that if a rating change takes place on a
> > > > > Monday, then it will not show up in the query outlined below.
> > > > > The SQL statement (included below) compares ratings from Day 0[/color][/color][/color]
with[color=blue][color=green][color=darkred]
> > > > > those from Day 0+1.
> > > > >
> > > > > SELECT Data.Date, Data.Rating, Data.Type, Data.[Target price],
> > > > > Data.Analyst_ID, Data.Company_ID
> > > > > FROM Data INNER JOIN Data AS Data_1 ON (Data.Company_ID =
> > > > > Data_1.Company_ID) AND (Data.Date = Data_1.Date+1)
> > > > > WHERE Data.Rating <> Data_1.Rating;
> > > > >
> > > > >
> > > > >
> > > > > If a rating change took place on 5 Dec (Friday), then this would[/color][/color][/color]
be[color=blue][color=green][color=darkred]
> > > > > picked up when the query compared 20031204 data with data on[/color][/color][/color]
20031205[color=blue][color=green][color=darkred]
> > > > > (YYYMMDD dates). However, data is not stored for Sat or Sun, so[/color][/color][/color]
if[color=blue][color=green][color=darkred]
> > > > > the change took place on 8 Dec (Monday), the query will not[/color][/color][/color]
compare[color=blue][color=green][color=darkred]
> > > > > Friday with Monday, but will make Monday - Tuesday the next
> > > > > comparison, hence not picking up the rating change.
> > > > >
> > > > > I have tried extending the SQL as follows to compare each day with
> > > > > day+1 and day+3, so tha rating chages on mondays will be picked[/color][/color][/color]
up[color=blue][color=green][color=darkred]
> > > > > (Friday+3=Monday).
> > > > >
> > > > > AND (Data.Date = Data_1.Date+1) OR (Data.Date = Data_1.Date+3)
> > > > >
> > > > >
> > > > > I realise that this would then need filtering, as each rating[/color][/color][/color]
change[color=blue][color=green][color=darkred]
> > > > > could potentially appear a few times. However, there seems to be[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> > > > > huge amount of data returned (1000s of records, as opposed to the[/color][/color][/color]
180[color=blue][color=green][color=darkred]
> > > > > returned before amending the query), which suggests that there is
> > > > > something wrong with the SQL. Furthermore, this seems a very[/color][/color][/color]
clunky[color=blue][color=green][color=darkred]
> > > > > way of doing it.
> > > > >
> > > > > Is there any way of inserting an "If" statement into the SQL, to[/color][/color][/color]
tell[color=blue][color=green][color=darkred]
> > > > > the query the if Day+1 does not exist, then (as Day must therefore[/color][/color][/color]
be[color=blue][color=green][color=darkred]
> > > > > a Friday) compare with Day+3?
> > > > >
> > > > > Again, thanks in advance for any help anybody can provide.
> > > > >
> > > > > Dom Boyce
> > > > >
> > > > >
> > > > > domboyce@totalise.co.uk (Dom Boyce) wrote in message[/color]
> > news:<7c016cf5.0311240942.be6b58f@posting.google.c om>...[color=darkred]
> > > > > > Turtle,
> > > > > >
> > > > > > thanks a lot for this - it worked perfectly and saved me a huge[/color]
> > amount
> > of time.[color=darkred]
> > > > > >
> > > > > > Dom
> > > > > >
> > > > > >
> > > > > > "MacDermott" <macdermott@nospam.com> wrote in message[/color]
> > news:<8MSvb.15629$Rk5.12635@newsread1.news.atl.ear thlink.net>...[color=darkred]
> > > > > > > How about this:
> > > > > > > Create a query with two copies of your table.
> > > > > > > Link them on CompanyID and on[/color]
> > MyTable.RecordDate=MyTable_1.RecordDate+1[color=darkred]
> > > > > > > (you'll have to edit the SQL to get the latter join - it[/color][/color][/color]
can't[color=blue][color=green]
> > be
> > built in[color=darkred]
> > > > > > > the grid directly.)
> > > > > > > Use the criteria line to pick out only items where the rating[/color][/color][/color]
has[color=blue][color=green]
> > changed.[color=darkred]
> > > > > > > Add whatever fields you need for your new table.
> > > > > > > Turn the query into a make-table query.
> > > > > > >
> > > > > > > Run it once, and you should be done.
> > > > > > >
> > > > > > > HTH
> > > > > > > - Turtle
> > > > > > >
> > > > > > > "Dom Boyce" <domboyce@totalise.co.uk> wrote in message
> > > > > > > news:7c016cf5.0311210356.279c425@posting.google.co m...
> > > > > > > > Hi
> > > > > > > >
> > > > > > > > First up, I am using MS Access 2002.
> > > > > > > >
> > > > > > > > I have a database which records analyst rating changes for a[/color]
> > list of[color=darkred]
> > > > > > > > companies on a daily basis. Unfortunately, the database has[/color][/color][/color]
been[color=blue][color=green]
> > set[color=darkred]
> > > > > > > > up (by my predecessor, I hasten to add) so that each day it[/color]
> > creates
> > a[color=darkred]
> > > > > > > > copy of the record for each company, changes the date to[/color][/color][/color]
today's[color=blue][color=green]
> > date,[color=darkred]
> > > > > > > > and prompts the user for any changes of ratings on that day.[/color]
> > The[color=darkred]
> > > > > > > > resulting data table grows by approx 600 records per day.
> > > > > > > >
> > > > > > > > If starting from scratch, the obvious way to do this would[/color][/color][/color]
be to[color=blue][color=green][color=darkred]
> > > > > > > > record only the changes each day, thus creating a much[/color][/color][/color]
smaller[color=blue][color=green]
> > data[color=darkred]
> > > > > > > > table. with an eye to doing this in te future, i would like[/color][/color][/color]
to[color=blue][color=green]
> > go[color=darkred]
> > > > > > > > through every date in the data table, comparing the rating[/color][/color][/color]
for[color=blue][color=green]
> > each[color=darkred]
> > > > > > > > company with that of the day before, and appending a new[/color][/color][/color]
record[color=blue][color=green]
> > to a[color=darkred]
> > > > > > > > table "tblChanges" each time a change is detected.
> > > > > > > >
> > > > > > > > I have created 2 queries to extract data for the two days[/color][/color][/color]
for[color=blue][color=green][color=darkred]
> > > > > > > > comparison, and a third query which links the 2 original[/color][/color][/color]
queries[color=blue][color=green]
> > by[color=darkred]
> > > > > > > > Company_ID, and appends any records where the rating has[/color][/color][/color]
changed[color=blue][color=green]
> > to[color=darkred]
> > > > > > > > the table "tblChanges". This works fine, but I do not fancy[/color]
> > running[color=darkred]
> > > > > > > > the query x amount of times, manually entering the new dates[/color][/color][/color]
for[color=blue][color=green]
> > each[color=darkred]
> > > > > > > > comparison.
> > > > > > > >
> > > > > > > > It seems that using recordsets in VBA is the way to automate[/color]
> > this[color=darkred]
> > > > > > > > process. I imagine creating 2 recordsets which can each[/color][/color][/color]
have[color=blue][color=green]
> > the
> > date[color=darkred]
> > > > > > > > variable passed to them from the VBA code - one with data on[/color][/color][/color]
one[color=blue][color=green]
> > day,[color=darkred]
> > > > > > > > and one with data on the next. I then need to run the[/color][/color][/color]
append[color=blue][color=green]
> > query
> > on[color=darkred]
> > > > > > > > these recordsets, to append any changed records to[/color][/color][/color]
tblChanges.[color=blue][color=green][color=darkred]
> > > > > > > >
> > > > > > > > I have very little experience of using recordsets, and have[/color]
> > pieced[color=darkred]
> > > > > > > > together the following code from newsgroups:
> > > > > > > >
> > > > > > > > ------------------------------
> > > > > > > >
> > > > > > > > Sub FindChanges()
> > > > > > > >
> > > > > > > > Dim db As DAO.Database
> > > > > > > > Dim rstOld As DAO.Recordset
> > > > > > > > Dim rstNew As DAO.Recordset
> > > > > > > > Dim qdfOld As DAO.QueryDef
> > > > > > > > Dim qdfNew As DAO.QueryDef
> > > > > > > > Dim DateOld As Date
> > > > > > > > Dim DateNew As Date
> > > > > > > >
> > > > > > > > Set db = CurrentDb()
> > > > > > > >
> > > > > > > > DateOld = "01/07/03"
> > > > > > > > DateNew = "01/08/03"
> > > > > > > >
> > > > > > > > Do
> > > > > > > > ' create the querydef object
> > > > > > > > Set qdfOld = db.QueryDefs("qryCompareOld")
> > > > > > > > With qdfOld
> > > > > > > > 'resolve the parameter
> > > > > > > > .Parameters("Date") = DateOld
> > > > > > > > 'open the recordset based on the Old data querydef
> > > > > > > > Set rstOld = .OpenRecordset
> > > > > > > > End With
> > > > > > > >
> > > > > > > > Set qdfNew = db.QueryDefs("qryCompareNew")
> > > > > > > > With qdfNew
> > > > > > > > 'resolve the parameter
> > > > > > > > .Parameters("Date") = DateNew
> > > > > > > > 'open the recordset based on the new data querydef
> > > > > > > > Set rstNew = .OpenRecordset
> > > > > > > > End With
> > > > > > > >
> > > > > > > > ' run the append query "qryFindChanges", which compares[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > > > > > results of CompareOld and CompareNew,
> > > > > > > > 'and appends differences to table "tblChanges"
> > > > > > > > DoCmd.OpenQuery "qryFindChanges"
> > > > > > > >
> > > > > > > >
> > > > > > > > With rstOld
> > > > > > > > .Close
> > > > > > > > End With
> > > > > > > >
> > > > > > > > With rstNew
> > > > > > > > .Close
> > > > > > > > End With
> > > > > > > >
> > > > > > > > 'Change date variables to compare next day's data for[/color][/color][/color]
changes[color=blue][color=green][color=darkred]
> > > > > > > > DateOld = DateNew
> > > > > > > > if DateNew+ 1 > Now() Then Exit Sub
> > > > > > > > If WorkDay(DateNew + 1) = True Then
> > > > > > > > DateNew = DateNew + 1
> > > > > > > > Else
> > > > > > > > If WorkDay(DateNew + 2) = True Then
> > > > > > > > DateNew = DateNew + 2
> > > > > > > > Else: DateNew = DateNew + 3
> > > > > > > > End If
> > > > > > > > End If
> > > > > > > > End If
> > > > > > > >
> > > > > > > > Loop
> > > > > > > >
> > > > > > > > ' Destroy the object vars
> > > > > > > > Set qdfOld = Nothing
> > > > > > > > Set qdfNew = Nothing
> > > > > > > > Set rstOld = Nothing
> > > > > > > > Set rstNew = Nothing
> > > > > > > > Set db = Nothing
> > > > > > > >
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > > -------------------------------------
> > > > > > > >
> > > > > > > > The code currently halts at
> > > > > > > > .Parameters("Date") = DateOld
> > > > > > > > with the error "Item not found in this collection", although[/color][/color][/color]
I[color=blue][color=green]
> > thought[color=darkred]
> > > > > > > > Parameters was part of QueryDefs.
> > > > > > > >
> > > > > > > > I also imagine that an error will occur when running the
> > > > > > > > "qryFindChanges" query, or at least the recordset data will[/color][/color][/color]
not[color=blue][color=green]
> > be[color=darkred]
> > > > > > > > used in that query. Does the coding for this query have to[/color][/color][/color]
be[color=blue][color=green][color=darkred]
> > > > > > > > included within the VBA code?
> > > > > > > >
> > > > > > > > If so, how would this be achieved, and how can I make the[/color]
> > results[color=darkred]
> > > > > > > > append to the "tblChanges" table?
> > > > > > > >
> > > > > > > > Sorry for the essay - I think I can see how this should be[/color][/color][/color]
done,[color=blue][color=green]
> > but[color=darkred]
> > > > > > > > am too new to recordsets to get the coding spot on. Any help[/color][/color][/color]
at[color=blue][color=green]
> > all[color=darkred]
> > > > > > > > would be much appreciated.
> > > > > > > >
> > > > > > > > Dom[/color][/color][/color]

Thanks again for your help, Turtle. Used the Min function well then
linked back to original data. quite a messy solution with a lot of
queries based on queries, which will probably need cleaning up, but it
works for now so thanks again.

Dom

"MacDermott" <macdermott@nospam.com> wrote in message news:<QW_Lb.1787$q4.582@newsread3.news.atl.earthli nk.net>...[color=blue]
> You can use an aggregate (or totals) query to get the first (Min) date for
> each company in your DATA table.
> Then join this back to the DATA table to get the other information you want
> for that record.
> You can then UNION this query with the ones you already have.
>
> I think it could also be done with a subquery, but it escapes me at the
> moment just how that would work.
>
> HTH
> - Turtle
>
>
> "Dom Boyce" <domboyce@totalise.co.uk> wrote in message
> news:7c016cf5.0401070824.3c2129d2@posting.google.c om...[color=green]
> > This is exactly what I wanted Turtle, thanks again for all your help.
> >
> > I say exactly what i wanted - it is exactly what I asked for but, as
> > usual, another issue has cropped up. I now use the union query to
> > effectively display records from two queries, which detect changes in
> > one or all of three variables from one day to another for each
> > Company.
> >
> > However, i need to include in this query's results:
> > 1. A record each time a completely new copmany is added.
> > 2. Company information on the FIRST day the data begins
> >
> >
> > Currently, the queries are linked on Company_ID so that each day, each
> > company is compared with its status the day before. However, when a
> > new company is added on a particular day, and for companies on the
> > first day that data was recorded, there is no Company_ID for that
> > company on the previous day, hence no comparison takes place, and no
> > record is shown in the query.
> >
> > Is it possible to add another UNION to the query, which would
> > calculate something like:
> >
> > SELECT Data_1.Company_ID, Data_1.Date etc
> > FROM (not sure what would go here)
> > WHERE Data.CompanyID Does not exist for any given Data_1.Company_ID
> >
> > Sorry for being a pain about this - I am fairly new to SQL, but am
> > being forced to pick it up very quickly, and appreciate all the help I
> > can get.
> >
> > Happy New Year,
> >
> > Dom
> >
> >
> >
> > "MacDermott" <macdermott@nospam.com> wrote in message[/color]
> news:<fImGb.8037$IM3.713@newsread3.news.atl.earthl ink.net>...[color=green][color=darkred]
> > > If you don't need to update the data on the form, you could use a union
> > > query.
> > >
> > > HTH
> > > - Turtle
> > >
> > > "Dom Boyce" <domboyce@totalise.co.uk> wrote in message
> > > news:7c016cf5.0312240541.52e28c36@posting.google.c om...
> > > > Thanks again for your help. I had to modify the suggested solution,
> > > > as "vbFriday" was not recognised in SQL - replacing it with "6", the
> > > > day number of Friday, at least allowed the query to run.
> > > >
> > > > however, the returned results were not as desired - they contained a
> > > > lot of duplicate records and, een after hours of fiddling, i could not
> > > > return the correct data.
> > > >
> > > > By creating two queries, one for Fridays and one for other weekdays, I
> > > > can pull the required data out. The SQL for this follows. NB this has
> > > > been extended from the last SQL posted, in order to view changes not
> > > > only in Rating, but also in Type and Analyst, but the FROM and WHERE
> > > > sections illustrate how a full set of data is obtained:
> > > >
> > > > qryFriday
> > > > SELECT Data_1.Date, Data_1.Rating AS [New Rating], Data_1.Type AS [New
> > > > Type], Analysts_1.LastName AS [New Analyst], Data.Rating AS [Old
> > > > Rating], Data.Type AS [Old Type], Analysts.LastName AS [Old Analyst],
> > > > Data.[Target price], Companies.Name,
> > > > iif([Data].[Analyst_ID]<>[Data_1].[Analyst_ID],IIf(nz([New
> > > > Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
> > > > Type],"RTA","RA"),IIf([New Type]<>[Old Type],"TA","A")), IIf(nz([New
> > > > Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
> > > > Type],"RT","R"),IIf([New Type]<>[Old Type],"T",""))) AS [Change Type]
> > > > FROM (Companies INNER JOIN (Analysts INNER JOIN (Data INNER JOIN Data
> > > > AS Data_1 ON (Data.Company_ID = Data_1.Company_ID) AND
> > > > (Data_1.Date=Data.Date+3)) ON Analysts.Analyst_ID = Data.Analyst_ID)
> > > > ON Companies.Company_ID = Data.Company_ID) INNER JOIN Analysts AS
> > > > Analysts_1 ON Data_1.Analyst_ID = Analysts_1.Analyst_ID
> > > > WHERE (((nz([Data].[Rating]))<>nz([Data_1].[Rating])) AND
> > > > ((Weekday([Data].[Date]))=6)) OR (((Data.Type)<>[Data_1].[Type]) AND
> > > > ((Weekday([Data].[Date]))=6)) OR
> > > > (((Data.Analyst_ID)<>[Data_1].[Analyst_ID]) AND
> > > > ((Weekday([Data].[Date]))=6))
> > > > ORDER BY Data_1.Date, Companies.Name;
> > > >
> > > >
> > > > qryOtherDays
> > > > SELECT Data_1.Date, Data_1.Rating AS [New Rating], Data_1.Type AS [New
> > > > Type], Analysts_1.LastName AS [New Analyst], Data.Rating AS [Old
> > > > Rating], Data.Type AS [Old Type], Analysts.LastName AS [Old Analyst],
> > > > Data.[Target price], Companies.Name,
> > > > iif([Data].[Analyst_ID]<>[Data_1].[Analyst_ID],IIf(nz([New
> > > > Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
> > > > Type],"RTA","RA"),IIf([New Type]<>[Old Type],"TA","A")), IIf(nz([New
> > > > Rating])<>nz([Old Rating]),IIf([New Type]<>[Old
> > > > Type],"RT","R"),IIf([New Type]<>[Old Type],"T",""))) AS [Change Type]
> > > > FROM (Companies INNER JOIN (Analysts INNER JOIN (Data INNER JOIN Data
> > > > AS Data_1 ON (Data.Company_ID = Data_1.Company_ID) AND
> > > > (Data_1.Date=Data.Date+1)) ON Analysts.Analyst_ID = Data.Analyst_ID)
> > > > ON Companies.Company_ID = Data.Company_ID) INNER JOIN Analysts AS
> > > > Analysts_1 ON Data_1.Analyst_ID = Analysts_1.Analyst_ID
> > > > WHERE (((nz([Data].[Rating]))<>nz([Data_1].[Rating])) AND
> > > > ((Weekday([Data].[Date]))<>6)) OR (((Data.Type)<>[Data_1].[Type]) AND
> > > > ((Weekday([Data].[Date]))<>6)) OR
> > > > (((Data.Analyst_ID)<>[Data_1].[Analyst_ID]) AND
> > > > ((Weekday([Data].[Date]))<>6))
> > > > ORDER BY Data_1.Date, Companies.Name;
> > > >
> > > > However, I now need to combine these two queries so I can view the
> > > > results in a form. is there a way of doing this, other than making
> > > > both queries Append queries, basing a form on the resulting table and
> > > > coding a FormLoad event to clear the table then run both append
> > > > queries?
> > > >
> > > > Again, any help would be much appreciated. Dom
> > > >
> > > >
> > > >
> > > > "MacDermott" <macdermott@nospam.com> wrote in message[/color][/color]
> news:<XvCDb.997$Ts5.747@newsread2.news.atl.earthli nk.net>...[color=green][color=darkred]
> > > > > WARNING: AIR CODE
> > > > >
> > > > > Perhaps you could use something like this:
> > > > >
> > > > > SELECT Data.Date, Data.Rating, Data.Type, Data.[Target price],
> > > > > Data.Analyst_ID, Data.Company_ID
> > > > > FROM Data , Data AS Data_1 WHERE (Data.Company_ID =
> > > > > Data_1.Company_ID) AND (Data.Date =
> > > > > IIF(WeekDay(Data.Date)=vbFriday,Data_1.Date+3,Data _1.Date+1) AND
> > > > > Data.Rating <> Data_1.Rating;
> > > > >
> > > > > Changing from an inner join to a cross-product with WHERE conditions[/color][/color]
> is
> not[color=green][color=darkred]
> > > > > something I have a good reason for - it just felt better to me here.[/color][/color]
> You[color=green][color=darkred]
> > > > > could probably do it with an inner join, too.
> > > > >
> > > > > HTH
> > > > > - Turtle
> > > > > "Dom Boyce" <domboyce@totalise.co.uk> wrote in message
> > > > > news:7c016cf5.0312150338.435624eb@posting.google.c om...
> > > > > > Upon returning to this problem, which I thought I had fixed using[/color][/color]
> the[color=green][color=darkred]
> > > > > > solution below, I have encountered another problem.
> > > > > >
> > > > > > The database records data items for each weekday, but not on[/color][/color]
> Saturday[color=green][color=darkred]
> > > > > > or Sunday. This means that if a rating change takes place on a
> > > > > > Monday, then it will not show up in the query outlined below.
> > > > > > The SQL statement (included below) compares ratings from Day 0[/color][/color]
> with[color=green][color=darkred]
> > > > > > those from Day 0+1.
> > > > > >
> > > > > > SELECT Data.Date, Data.Rating, Data.Type, Data.[Target price],
> > > > > > Data.Analyst_ID, Data.Company_ID
> > > > > > FROM Data INNER JOIN Data AS Data_1 ON (Data.Company_ID =
> > > > > > Data_1.Company_ID) AND (Data.Date = Data_1.Date+1)
> > > > > > WHERE Data.Rating <> Data_1.Rating;
> > > > > >
> > > > > >
> > > > > >
> > > > > > If a rating change took place on 5 Dec (Friday), then this would[/color][/color]
> be[color=green][color=darkred]
> > > > > > picked up when the query compared 20031204 data with data on[/color][/color]
> 20031205[color=green][color=darkred]
> > > > > > (YYYMMDD dates). However, data is not stored for Sat or Sun, so[/color][/color]
> if[color=green][color=darkred]
> > > > > > the change took place on 8 Dec (Monday), the query will not[/color][/color]
> compare[color=green][color=darkred]
> > > > > > Friday with Monday, but will make Monday - Tuesday the next
> > > > > > comparison, hence not picking up the rating change.
> > > > > >
> > > > > > I have tried extending the SQL as follows to compare each day with
> > > > > > day+1 and day+3, so that rating chages on mondays will be picked[/color][/color]
> up[color=green][color=darkred]
> > > > > > (Friday+3=Monday).
> > > > > >
> > > > > > AND (Data.Date = Data_1.Date+1) OR (Data.Date = Data_1.Date+3)
> > > > > >
> > > > > >
> > > > > > I realise that this would then need filtering, as each rating[/color][/color]
> change[color=green][color=darkred]
> > > > > > could potentially appear a few times. However, there seems to be[/color][/color]
> a[color=green][color=darkred]
> > > > > > huge amount of data returned (1000s of records, as opposed to the[/color][/color]
> 180[color=green][color=darkred]
> > > > > > returned before amending the query), which suggests that there is
> > > > > > something wrong with the SQL. Furthermore, this seems a very[/color][/color]
> clunky[color=green][color=darkred]
> > > > > > way of doing it.
> > > > > >
> > > > > > Is there any way of inserting an "If" statement into the SQL, to[/color][/color]
> tell[color=green][color=darkred]
> > > > > > the query the if Day+1 does not exist, then (as Day must therefore[/color][/color]
> be[color=green][color=darkred]
> > > > > > a Friday) compare with Day+3?
> > > > > >
> > > > > > Again, thanks in advance for any help anybody can provide.
> > > > > >
> > > > > > Dom Boyce
> > > > > >
> > > > > >
> > > > > > domboyce@totalise.co.uk (Dom Boyce) wrote in message[/color][/color]
> news:<7c016cf5.0311240942.be6b58f@posting.google.c om>...[color=green][color=darkred]
> > > > > > > Turtle,
> > > > > > >
> > > > > > > thanks a lot for this - it worked perfectly and saved me a huge
> > > amount
> > > of time.
> > > > > > >
> > > > > > > Dom
> > > > > > >
> > > > > > >
> > > > > > > "MacDermott" <macdermott@nospam.com> wrote in message[/color][/color]
> news:<8MSvb.15629$Rk5.12635@newsread1.news.atl.ear thlink.net>...[color=green][color=darkred]
> > > > > > > > How about this:
> > > > > > > > Create a query with two copies of your table.
> > > > > > > > Link them on CompanyID and on[/color][/color]
> MyTable.RecordDate=MyTable_1.RecordDate+1[color=green][color=darkred]
> > > > > > > > (you'll have to edit the SQL to get the latter join - it[/color][/color]
> can't[color=green][color=darkred]
> > > be
> > > built in
> > > > > > > > the grid directly.)
> > > > > > > > Use the criteria line to pick out only items where the rating[/color][/color]
> has
> changed.[color=green][color=darkred]
> > > > > > > > Add whatever fields you need for your new table.
> > > > > > > > Turn the query into a make-table query.
> > > > > > > >
> > > > > > > > Run it once, and you should be done.
> > > > > > > >
> > > > > > > > HTH
> > > > > > > > - Turtle
> > > > > > > >
> > > > > > > > "Dom Boyce" <domboyce@totalise.co.uk> wrote in message
> > > > > > > > news:7c016cf5.0311210356.279c425@posting.google.co m...
> > > > > > > > > Hi
> > > > > > > > >
> > > > > > > > > First up, I am using MS Access 2002.
> > > > > > > > >
> > > > > > > > > I have a database which records analyst rating changes for a[/color][/color]
> list of[color=green][color=darkred]
> > > > > > > > > companies on a daily basis. Unfortunately, the database has[/color][/color]
> been
> set[color=green][color=darkred]
> > > > > > > > > up (by my predecessor, I hasten to add) so that each day it
> > > creates
> > > a
> > > > > > > > > copy of the record for each company, changes the date to[/color][/color]
> today's
> date,[color=green][color=darkred]
> > > > > > > > > and prompts the user for any changes of ratings on that day.[/color][/color]
> The[color=green][color=darkred]
> > > > > > > > > resulting data table grows by approx 600 records per day.
> > > > > > > > >
> > > > > > > > > If starting from scratch, the obvious way to do this would[/color][/color]
> be to[color=green][color=darkred]
> > > > > > > > > record only the changes each day, thus creating a much[/color][/color]
> smaller
> data[color=green][color=darkred]
> > > > > > > > > table. with an eye to doing this in te future, i would like[/color][/color]
> to
> go[color=green][color=darkred]
> > > > > > > > > through every date in the data table, comparing the rating[/color][/color]
> for
> each[color=green][color=darkred]
> > > > > > > > > company with that of the day before, and appending a new[/color][/color]
> record
> to a[color=green][color=darkred]
> > > > > > > > > table "tblChanges" each time a change is detected.
> > > > > > > > >
> > > > > > > > > I have created 2 queries to extract data for the two days[/color][/color]
> for[color=green][color=darkred]
> > > > > > > > > comparison, and a third query which links the 2 original[/color][/color]
> queries
> by[color=green][color=darkred]
> > > > > > > > > Company_ID, and appends any records where the rating has[/color][/color]
> changed
> to[color=green][color=darkred]
> > > > > > > > > the table "tblChanges". This works fine, but I do not fancy[/color][/color]
> running[color=green][color=darkred]
> > > > > > > > > the query x amount of times, manually entering the new dates[/color][/color]
> for
> each[color=green][color=darkred]
> > > > > > > > > comparison.
> > > > > > > > >
> > > > > > > > > It seems that using recordsets in VBA is the way to automate[/color][/color]
> this[color=green][color=darkred]
> > > > > > > > > process. I imagine creating 2 recordsets which can each[/color][/color]
> have[color=green][color=darkred]
> > > the
> > > date
> > > > > > > > > variable passed to them from the VBA code - one with data on[/color][/color]
> one
> day,[color=green][color=darkred]
> > > > > > > > > and one with data on the next. I then need to run the[/color][/color]
> append[color=green][color=darkred]
> > > query
> > > on
> > > > > > > > > these recordsets, to append any changed records to[/color][/color]
> tblChanges.[color=green][color=darkred]
> > > > > > > > >
> > > > > > > > > I have very little experience of using recordsets, and have[/color][/color]
> pieced[color=green][color=darkred]
> > > > > > > > > together the following code from newsgroups:
> > > > > > > > >
> > > > > > > > > ------------------------------
> > > > > > > > >
> > > > > > > > > Sub FindChanges()
> > > > > > > > >
> > > > > > > > > Dim db As DAO.Database
> > > > > > > > > Dim rstOld As DAO.Recordset
> > > > > > > > > Dim rstNew As DAO.Recordset
> > > > > > > > > Dim qdfOld As DAO.QueryDef
> > > > > > > > > Dim qdfNew As DAO.QueryDef
> > > > > > > > > Dim DateOld As Date
> > > > > > > > > Dim DateNew As Date
> > > > > > > > >
> > > > > > > > > Set db = CurrentDb()
> > > > > > > > >
> > > > > > > > > DateOld = "01/07/03"
> > > > > > > > > DateNew = "01/08/03"
> > > > > > > > >
> > > > > > > > > Do
> > > > > > > > > ' create the querydef object
> > > > > > > > > Set qdfOld = db.QueryDefs("qryCompareOld")
> > > > > > > > > With qdfOld
> > > > > > > > > 'resolve the parameter
> > > > > > > > > .Parameters("Date") = DateOld
> > > > > > > > > 'open the recordset based on the Old data querydef
> > > > > > > > > Set rstOld = .OpenRecordset
> > > > > > > > > End With
> > > > > > > > >
> > > > > > > > > Set qdfNew = db.QueryDefs("qryCompareNew")
> > > > > > > > > With qdfNew
> > > > > > > > > 'resolve the parameter
> > > > > > > > > .Parameters("Date") = DateNew
> > > > > > > > > 'open the recordset based on the new data querydef
> > > > > > > > > Set rstNew = .OpenRecordset
> > > > > > > > > End With
> > > > > > > > >
> > > > > > > > > ' run the append query "qryFindChanges", which compares[/color][/color]
> the[color=green][color=darkred]
> > > > > > > > > results of CompareOld and CompareNew,
> > > > > > > > > 'and appends differences to table "tblChanges"
> > > > > > > > > DoCmd.OpenQuery "qryFindChanges"
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > With rstOld
> > > > > > > > > .Close
> > > > > > > > > End With
> > > > > > > > >
> > > > > > > > > With rstNew
> > > > > > > > > .Close
> > > > > > > > > End With
> > > > > > > > >
> > > > > > > > > 'Change date variables to compare next day's data for[/color][/color]
> changes[color=green][color=darkred]
> > > > > > > > > DateOld = DateNew
> > > > > > > > > if DateNew+ 1 > Now() Then Exit Sub
> > > > > > > > > If WorkDay(DateNew + 1) = True Then
> > > > > > > > > DateNew = DateNew + 1
> > > > > > > > > Else
> > > > > > > > > If WorkDay(DateNew + 2) = True Then
> > > > > > > > > DateNew = DateNew + 2
> > > > > > > > > Else: DateNew = DateNew + 3
> > > > > > > > > End If
> > > > > > > > > End If
> > > > > > > > > End If
> > > > > > > > >
> > > > > > > > > Loop
> > > > > > > > >
> > > > > > > > > ' Destroy the object vars
> > > > > > > > > Set qdfOld = Nothing
> > > > > > > > > Set qdfNew = Nothing
> > > > > > > > > Set rstOld = Nothing
> > > > > > > > > Set rstNew = Nothing
> > > > > > > > > Set db = Nothing
> > > > > > > > >
> > > > > > > > > End Sub
> > > > > > > > >
> > > > > > > > > -------------------------------------
> > > > > > > > >
> > > > > > > > > The code currently halts at
> > > > > > > > > .Parameters("Date") = DateOld
> > > > > > > > > with the error "Item not found in this collection", although[/color][/color]
> I
> thought[color=green][color=darkred]
> > > > > > > > > Parameters was part of QueryDefs.
> > > > > > > > >
> > > > > > > > > I also imagine that an error will occur when running the
> > > > > > > > > "qryFindChanges" query, or at least the recordset data will[/color][/color]
> not
> be[color=green][color=darkred]
> > > > > > > > > used in that query. Does the coding for this query have to[/color][/color]
> be[color=green][color=darkred]
> > > > > > > > > included within the VBA code?
> > > > > > > > >
> > > > > > > > > If so, how would this be achieved, and how can I make the[/color][/color]
> results[color=green][color=darkred]
> > > > > > > > > append to the "tblChanges" table?
> > > > > > > > >
> > > > > > > > > Sorry for the essay - I think I can see how this should be[/color][/color]
> done,
> but[color=green][color=darkred]
> > > > > > > > > am too new to recordsets to get the coding spot on. Any help[/color][/color]
> at
> all[color=green][color=darkred]
> > > > > > > > > would be much appreciated.
> > > > > > > > >
> > > > > > > > > Dom[/color][/color][/color]

Recordset to update queries - help!

re: Recordset to update queries - help!

re: Recordset to update queries - help!

re: Recordset to update queries - help!

re: Recordset to update queries - help!

re: Recordset to update queries - help!

re: Recordset to update queries - help!

re: Recordset to update queries - help!

re: Recordset to update queries - help!

re: Recordset to update queries - help!

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about