query help

[buggy2]

I have a table, 3000 rows. The first column is the date, 2nd a name, 3rd and 4 are integer ratings. I want to create a query or vba module that returns the names of the people who's ratings in column 4 have changed in the previous day. The date is in format dd/mm/yy.

Any help would be great.

 

[Dugantrain]

If your change Date is captured by your first column, then in the criteria of that column, just type:

Date()-1

to return all records with a date of the current day -1.

 

[buggy2]

thank you, but that will give me all the records for the previous day, I am trying to capture the entries who's column 4 ratings have changed. I presume I need to write a module, since I have one single table I cant query it to return changing entries.

 

[Dugantrain]

Access has no built in Transaction log feature; you'd need to build it yourself. What you'll need is a field in your table which Uniquely identifies each record (Autonum, presumably) and then a child table which, whenever a change is made in your original table, populates with the Parent record's Autonum as its foreign key, the Change Date, and the Change made. You would do this with VBA; at form-level, whenever a change is made to the Column 4 control, then the Before_Update (After_Update would work too) event would fire, triggering the Insert code. However, bear in mind that with a one-to-many parent/child relationship, a 3000 record database could increase to 5 or 6 times its size (maybe more, depending on how often changes are made), so you'll need to decide whether this level of change-tracking is worth the effort.

 

[buggy2]

ok thats a no go, apologies for my typing there are 30000 rows in this table, so the log is out of the question. My vba is not good at all, but could I not write a proceedure, that compares each name from the current day with all the names from every other day and if the name is the same then check if the rating is the same and if the rating is different write the name and rating into a new table.

 

[Dugantrain]

ok, so in this table, the same name will appear more than once? Sounds to me like this table should be split in two: a parent table for the Names on the 'one' side which relates to a child table for the Ratings on the 'many' side. Either way, that's for you to decide. If you leave it as is, then you would probably do just as well to do this tracking in pre-built queries rather than code. Your first query would capture all Names, Ratings for the current day. The Where Clause for the date field would be:

Date()

Your second query would be the same as the first except the Where Clause would be:

Date()-1

You would then tie these two queries together in a third query. Inner join on the name field/fields (or hopefully you have something which uniquely identifies each name as you could easily have, for example, two people named 'Joe Smith'), pull in the Rating fields from both queries, and the Where Clause of the first Rating field would be:

<>[Your_Second_Query].[Your_Rating_Field]

This query returns all rows where a change in Ratings was made between the Current Day and the Previous Day. You can make this query an Append Query and Append the results to another table.

 

[drjekyll325]

You could always take a new snapshot of the table each day, and compare the current table to the previous day's table:

select curr.name, curr.col4, old.col4
from current_table curr
inner join old_table old on (curr.name = old.name)
where curr.col4 <> old.col4

(I'm assuming from your description of the table that you are using the name as a key field.)