Limit To 2 Records of Identification Code

ramsay18

New member
Local time
Today, 03:19
Joined
May 13, 2006
Messages
8
I am attempting to right program for work and here is my problem. Every Unit in the Army has a Unit Identification Code and my boss wants two records of each UIC stored. But when you add a new record for that UIC he wants the oldest one purged, so he only wants 2 records on file in order to maintain some sort of volume problems. Any ideas would be greatly appreciated

SPC Joshua Ramsay
HHC 2/104th AVN FTIG
 
Sorry, what? :confused:

Surly you mean display the "Latest" two records? Why would he want to delete a record? How bad is his storage problem? 10meg?!:eek:

It's a db thing, but you shouldn't go "deleting" records. You should "retire" them. What if you (of some really stupid user) makes a mistake? You would want to have access to your "retired" records so you fix the problem.

Check these two ideas...

Delete queries
Display Latest

In the Search area of this forum.
 
I want to retain the last two records of each UIC on file in my table. That way if they want to view the past two it is possible. I only need those two records anything else can be deleted or I can just overwrite those two records. Memory will be a problem because it is going to be operated nationwide and 10meg could be acheived in under a day.
 
Hello, Ramsay. I can't offer consulting services (contractor issue for military sites) but since I work with the U.S. Navy Reserve, I fully understand your requirement. We have something similar at our shop.

There are a few ways to achieve your goal. Some are easier than others. I will suggest one simple way to do this. NOTE: If you can have multiple records entered for the same UIC on the same day, this method could cause a problem requiring a second "adjustment" to what you delete and when.

Add a numeric field to your record, call it "Ordinal" or some other arbitrary name. INTEGER or BYTE might be adequate. LONG might be overkill, but if you have room, LONG is probably better.

Now write an update query that you would run at a time when nobody is doing any record additions. Be kind to yourself by assuring that such a time exists. Access probably isn't robust enough to do this while the DB is active to your users in the field.

You might need to look up DCount function for this. The help files will tell you what you need to know. Remember that the Criteria argument of DCount is like a WHERE clause in a SELECT query.

Update the new field to show you how many records with the same UIC have dates NEWER than the current record. (The DCount function can count them for you, and DCount can legally occur in a query.) For dates, NEWER = Greater Than. The newest date will have zero records newer than itself. The next-newest should have 1 newer record. The next-next-newest, 2 newer records, and so on. BE WARNED: This isn't necessarily very fast if you have thousands of records. On the other hand, you aren't likely to have that many UICs if your army units are like our navy units. Hundreds, yes. Thousands, unlikely.

Next, write a delete query that deletes all records for which this new field has a value greater than 1 (or 2, depending on exactly how you write the update query.) This is where having multiple updates in the same date might cause trouble.

The method I described above is an example of "divide and conquer." This style works well with Access. Make one pass to MARK records and a second pass to DELETE records.

This separation also gives you a chance to apply other queries that would filter out options such as "OLDEST record not old enough yet" - which is how you would detect multiple changes on the same day. Run the queries to "unmark" (reset to 0) the marker field for any record you wanted to keep longer because of secondary limits. Run the "unmarker" queries between the initial marker query and the final deletion query.

Hope that helps.
 
Doc_Man thankyou that is exactly what i was looking for i wanted to count records in a delete query but it wouldnt work Thanks
 
Question

ramsay18 said:
Doc_Man thankyou that is exactly what i was looking for i wanted to count records in a delete query but it wouldnt work Thanks
just a question if i should change anything on this
DCount(
![UIC],
,
![UIC])
which criteria does this go into along with what do i update?
 
I believe you need a little bit more than this. You need a date tag on each record that can be deleted so you can determine its age.

You want to find all records for a given UIC where the number of NEWER records (implying an age field exists) is greater than 1. Mark those records. Then if any unmarking is required, do the unmarking (to preserve things you need to preserve.) FINALLY, write your delete query to delete marked records.

Put some time into designing your entries to have dates. Also work on a business rule to define whether you can have more than one change per day. This is a case where a lot of thought up front will save a lot of hair (tearing) later. Or, depending on just how forceful your NCOs can be, it can prevent post-hole auger anatomical rearrangement.

If you cannot see it yet, you aren't to the stage where you can hope to properly implement it. If you don't see the value in dates on the record, then remember the old programmer's rule: Access can't tell you anything you didn't tell it first. So when you say, "tell me about old records" and it doesn't have a date field, it will (figuratively) say "Old? What's this OLD stuff?" Unless you gave it a date so it could TEST ages and determine "old."
 
DocMan would you just be able to send me an email i am kindof on time constrants and it would be easier to talk to you directly since you know exactly what i am attempting to accomplish
 
Sorry, I've been away for a while. I'm on a hot project. Can't get there from here. And our mail is monitored by some no-sense-of-humor security types. I'm sure you have a few of your own. This forum is your best chance to get help in a timely manner.

Look in the help files on Append queries, Delete queries, and the DateDiff function (the latter as a way to compare ages). DCount is your best bet for determining the number of multiple entries for any unit.
 
okay i added "ordinal" to my table and made it a long. i created the update query, the problem i am having is getting the DCOUNT() work to be honost i was following the direction provided by the Microsoft and i have no clue on how it works it would be a great help if you could just explain what should go in what for the DCOUNT(). I got assigned to this project because i new visual basic and taught myself a little bit of access so i dont know a whole lot. Thanks
 
Think of this SQL query:

SELECT something FROM someplace WHERE someotherthing=somevalue ;

A DCount has essentially three fields:

x = DCount( "something", "someplace", "someotherthing=somevalue" ) ;

So put the correct name of the thing, the name of the place where you find it, and the condition that selects it. Think of it in context of the corresponding SELECT statement. Look at the Help files again, but this time image the SELECT statement that must have been used to build the query that feeds the DCount. That might help you understand DCount better.
 

Users who are viewing this thread

Back
Top Bottom