Track changes in a field with the date (1 Viewer)

Z

Zak14

Registered User.
Local time
Today, 10:57
Joined
Jun 27, 2014
Messages
166
I want a field in my table (called 'ContactTotal') that tracks the total number of times the date was changed in the field 'LastContacted' - through either table or a form, but it should exclude any changes made on the same day.

I'd also like a second field (called 'Popularity') that does the same thing, but multiplies any changes made in the last 3 months by 3, the last 3-6 months by 2, the last 12 months by 1 and anything before that by 0.

Thanks much.
 
V

vbaInet

AWF VIP
Local time
Today, 10:57
Joined
Jan 22, 2010
Messages
26,374
As for your first question there's an Audit trail database somewhere in this forum. Try the Database Samples section. Maybe GinaWhipp might have a sample of her own somewhere (if she comes by this thread). She's quite resourceful.

For the Popularity field you can do that in a query. Just get the first one working first.
 
Z

Zak14

Registered User.
Local time
Today, 10:57
Joined
Jun 27, 2014
Messages
166
Thanks a lot guys, but I've decided to let this project go; my boss doesn't think it's necessary. Maybe some time in the future. I have looked into what you guys suggested, and I kind of got the hang of it, so I'll be able to do it in the future with less help.
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 05:57
Joined
Jun 21, 2011
Messages
5,900
I had a Client like that, put it in anyway and guess what, I used it to track down a mistake that was made while entering so we could get the *old* correct value back, since no one could remember it. He whispered a *Thank you!* to me afterwards.

So, the lesson is... you might want to put it in place anyway, just saying! :D
 
Z

Zak14

Registered User.
Local time
Today, 10:57
Joined
Jun 27, 2014
Messages
166
GinaWhipp said:
I had a Client like that, put it in anyway and guess what, I used it to track down a mistake that was made while entering so we could get the *old* correct value back, since no one could remember it. He whispered a *Thank you!* to me afterwards.

So, the lesson is... you might want to put it in place anyway, just saying! :D
Click to expand...

Ok Gina, I'll do it then lol. It's just not on my priority list atm, so once I've finished designing the database entirely, I'll reply in this thread and hopefully continue. Thank you.
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 05:57
Joined
Jun 21, 2011
Messages
5,900
No one said it had to be at the top of list... :p
 
Z

Zak14

Registered User.
Local time
Today, 10:57
Joined
Jun 27, 2014
Messages
166
Can't I just use a Data/Table Macro for this,
using an "After Update" Event on the table that is linked to the form to log the changes,
and then put that data on the form whilst linking it to the relevant primary keys.
I watched a Data Macro Lynda.com video btw.
 
Z

Zak14

Registered User.
Local time
Today, 10:57
Joined
Jun 27, 2014
Messages
166
Okay, so far I created an "after update" macro on the table (let's call it "tblMain") that tracks new changes in the 'LastContacted' field within and records every update as a new record in a new table (tblLog). It records the ID and LastContacted. So one ID will have many contact dates.

Question now is:
How do I count the total number of records there are for one ID in tblLog and record that in date groups (e.g. Last 0-3 months, 3-6 months, etc). Perhaps record it in another table (although a less messy way would be preferred), with fields: ID, DateGroup, RecordCount.

Thanks much.
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 05:57
Joined
Jun 21, 2011
Messages
5,900
You know I always forget about those things because I don't use them. :rolleyes:

As for your question, use a Groups and Totals query against the table. That should give you what you want.
 
Z

Zak14

Registered User.
Local time
Today, 10:57
Joined
Jun 27, 2014
Messages
166
And what queries would I use for that? I'm a noob
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 05:57
Joined
Jun 21, 2011
Messages
5,900
It's called a *Groups and Totals* query... while in Design View of the Query press the *Totals* buttons, then look below and you'll see different things you can do.
 
Z

Zak14

Registered User.
Local time
Today, 10:57
Joined
Jun 27, 2014
Messages
166
But, if I create a query for this, will I be able to add it to my form? Can I relate the ID to the primary ID in my form.
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 05:57
Joined
Jun 21, 2011
Messages
5,900
Add to Form? You wanted to able to view tblLog with criteria (Post 9), what Form are you trying to add it to, I thought you wanted a Report.
 
Z

Zak14

Registered User.
Local time
Today, 10:57
Joined
Jun 27, 2014
Messages
166
No, not a report. I wanted to log/calculate everything into a table with a foreign ID key, so that I'll be able to add it to the form created for tblMain (post#9). I don't think I can do that with a query though.
 
GinaWhipp

GinaWhipp

AWF VIP
Local time
Today, 05:57
Joined
Jun 21, 2011
Messages
5,900
You are correct, I misunderstood what you wanted. You Log in the table and count in the query... can't do both in the Log table.
 
You must log in or register to reply here.

Similar threads

T
how to save data from textbox to a table field
Replies
5
Views
139
Pat Hartman
P
R
Solved Importing and calculating the data
Replies
12
Views
489
Robrobby
R
B
Data display question (was Me)
Replies
18
Views
370
bob fitz
bob fitz
BlingGirl
Font Changed to "System" on Buttons
2
Replies
21
Views
1,667
isladogs
isladogs
L
Auto Populate a few date fields
Replies
7
Views
568
XPS35
XPS35

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom