Table update suggestions

[mtobey]

Hello -
I used to keep my list of company and contacts in an excel spreadsheet, which are now in Access.
Each month I have to send the company/contact list to the contacts with the current month's changes/updates highlighted.
In excel I could color-code the row (required to bring attention to the change/update) as I made the change/update for that month.
Any suggestions for denoting monthly updates in Access tables so I don't have to manually keep record of what is a change/update?

thanks,
Melinda

 

[plog]

I'm not entirely certain what your structure is, but you need a date field somewhere. Then you can use that date field to identify the updates.

Post your tables and their fields and I can be more specific.

 

[mtobey]

Duh...of course...hmmm, now which table or maybe both need a date field?
Not really sure how to post my tables but here are the fields for each:

Company table fields:
CompanyID / Company / Parent Group

Contact table fields:
ContactCompanyID / Company Name / first name /last name / address / email / Parent Group

The tables are related by Parent Group.
Hope this is useful.

thanks!
Melinda

 

[plog]

First, I am honorbound to provide constructive criticism about your table structure: It seems that the Company table is completely pointless--all its data is contained within the records of the Contact table (i.e. ContactCompanyID, Company Name, Parent Group). Why is that? When linking tables you generally only link them using one field that is unique--most likely the CompanyID field.

Onto this issue--I would create a field called 'EditDate' in the Contact table to hold the date the last edit was made to that record. If you would like to note what was edited you could add a field called 'EditNotes' which would hold notes about the last edit you made. I would set the default to 'EditDate' to today's date and the default for the 'EditNotes' field to 'Record Creation'. From there, every time you edit a record you update the the Edit fields so you can now what occured.

At the end of the month you can run a report for all those records with an edit date that occured in that month and have your report.

 

[mtobey]

Plog - I'm open to and appreciate your constructive criticism!

The Contacts table has over 2000 contacts for 132 companies
The company table has those cool little plus boxes next to each company name so I can quickly look up or edit the contacts from just that company without having to scroll through the whole contacts table or create 132 queries. I may have done it backwards but it's working for me...at least for now. Probably bite me in the butt later.

Very novice at using Access (which I'm sure is apparent from my Access terminology) and although I love it, it boggles my mind!

thanks again for the suggestions and constructive criticism...keep it coming!

 

[mtobey]

Access help seems to be saying that the default for EditDate =Date() would be the creation date of the record and a macro is required for ModifyDate...or am I missing something?

 

[plog]

Access is correct on the EditDate default value. Now I never said anything about a 'ModifyDate' field. The EditDate field would contain the date the record was last edited--so by default its creation date would be populated.

To change the EditDate when you edit the rest of the record you would manually type in that day's date. A macro is overdoing things.

 

[mtobey]

Drats...I wanted one less thing to remember to enter

 

[mtobey]

Unable to find 'Record Creation'...is it a built-in expression?

 

[plog]

I have no idea what you are talking about, nor the context. And no it is not.

 

[mtobey]

I'm sorry - I was referring to your suggestion from your previous post...

"Onto this issue--I would create a field called 'EditDate' in the Contact table to hold the date the last edit was made to that record. If you would like to note what was edited you could add a field called 'EditNotes' which would hold notes about the last edit you made. I would set the default to 'EditDate' to today's date and the default for the 'EditNotes' field to 'Record Creation'. From there, every time you edit a record you update the the Edit fields so you can now what occured."

 

[plog]

My point is that the EditNotes field would, by default, have the value of today's date--by using =Date() in the default property of the field EditNotes. The other new field, EditNotes would, by default, have the value "Record Creation"--by using ="Record Creation" in the default property of EditNotes.

 

[mtobey]

Ok - thanks I think I've got it now

Also reading up on how to better ask questions