How to retrieve the autonumber ID

[rosepetal]

I'm almost positive that MS Access assigns a hidden sequential number to each new record as it is created.

If this is true, how do I use it in a query? I need to know (relatively) when certain records were created.

Probably a simple question, but any help is appreciated.

 

[THWatson]

rosepetal
You can add an AutoNumber field to your table (call it RecordID or whatever you wish). This will number each record consecutively. You can then call that into your query.

Tom

 

[earthandfire]

Could you not just at a field with a default value of Date() or Now()?


Hope this helps.

 

[rosepetal]

Thanks, THWatson and earthandfire.

I was hoping to tap into an EXISTING field that had been in existence since the database was started (before my time). Some of the records are very old but I'm not sure how to evaluate their worth without knowing how old they are.

Both of your ideas are good for me to implement now so that I can avoid this in the future.Thanks, again.

 

[THWatson]

rosepetal
Without some sort of record identifier, or date it was created, it's impossible to determine when it was created.

You can add an AutoNumber field, and update all the records with a record identifier by using an Update query. However that in itself would not guarantee which is the oldest record, and so on.

Can you indicate the fields that are currently in the table?

Tom

 

[rosepetal]

It's basically used fur a mailing list. So there's a lot of contact information, and about a dozen checkboxes for each of the different documents we mail out, and checkboxes for identifying the type of contact.

I have suspected for a long time (ever since I started my current job) that many of the contacts are receiving inappropriate mailings and just not contacting us. So for now I can only rely on the post off (returned mail).

 

[THWatson]

rosepetal
I see. Then merely identifying the age of the record wouldn't determine whether or not that record should be removed or kept.

Here's what I might suggest...
1. Add 3 fields to your table. One would be a Date field (call it something such as CreatedDate). The second would be a Yes/No field (call it Removed). The third would be a Date field (call it RemovedDate).
2. In your query which populates the mailings, add the Removed field, and put in criteria "No." That way, any records that have Yes will not get the mailings.
3. Run an Update query to populate all records with the current date, and No in the Removed field.

This will not correct any historical stuff, but it will give you a better handle on things from here on. You can also go through and enter Yes in the Removed field for any records you already know you should not be mailing stuff to anymore.

The virtue in doing things this way is that all records are preserved in your database. So if CustomerA no longer requires a mailing you can remove CustomerA from the mailing list but keep the contact information data...so if CustomerA comes back at some point you still have everything on file.

Tom

 

[rosepetal]

Tom, thanks for the suggestions. They make good sense and I can easily implement them.

rosepetal