# of Records does not equal the displayed #
- Thread starter ChapJohn
- Start date
What I meant was the indicated record number of the total # of records indicator at the bottom of the Table was not an accuracte number.
I have over 5300 records but the total showing was 2677. It was more than a little disconcerting to be looking at Record #4800 when the Record number at the bottom said that it was Record #2110!
This is a database for an organization that was poorly designed and even more poorly operated. The person who gave it to me said, "I don't know what I'm doing with it. It's too complicated for me!"
Apparently what she and others had been doing was physically deleting the records when members left over the past 23 years, not realizing that they were removing the numbers from the total even though the autonumber was increasing each new record number! In order to get the autonumber for the records to equal the indicated total number of records, I have to put in over 2600 blank records, replacing the lost record numbers. As the number of new members arrive, I'll just give them one of the "blank spots". To put in "blank records" I had to turn off the autonumber property of their registration ID# and set it to No Duplicates to still give it a unique value.
I have over 5300 records but the total showing was 2677. It was more than a little disconcerting to be looking at Record #4800 when the Record number at the bottom said that it was Record #2110!
This is a database for an organization that was poorly designed and even more poorly operated. The person who gave it to me said, "I don't know what I'm doing with it. It's too complicated for me!"
Apparently what she and others had been doing was physically deleting the records when members left over the past 23 years, not realizing that they were removing the numbers from the total even though the autonumber was increasing each new record number! In order to get the autonumber for the records to equal the indicated total number of records, I have to put in over 2600 blank records, replacing the lost record numbers. As the number of new members arrive, I'll just give them one of the "blank spots". To put in "blank records" I had to turn off the autonumber property of their registration ID# and set it to No Duplicates to still give it a unique value.
- Local time
- Today, 02:20
- Joined
- Jan 23, 2006
- Messages
- 15,576
You are misusing/misinterpreting autonumber.
Ms Access autonumbers are unique - not necessarily incremental, nor positive.
Please see
http://www.utteraccess.com/wiki/index.php/Autonumbers
Ms Access autonumbers are unique - not necessarily incremental, nor positive.
Please see
http://www.utteraccess.com/wiki/index.php/Autonumbers
Yeah, sounds like the blind passing the buck to the blind.
I second jdraw's link to autonumbers. They are not what you think they are and you are starting to head down a bad path with what you have done by inserting blank rows.
Maybe we can help allay your fears. What problem do you foresee if the last autonumber doesn't match the number of records in your database? Other than in your mind, what specific issues are you having where the autonumber/record count is causing a problem?
boblarson
Smeghead
- Local time
- Yesterday, 23:20
- Joined
- Jan 12, 2001
- Messages
- 32,059
If you care that there are spaces in autonumbers then you are using them incorrectly. They should have no meaning other than being something for the system to use to maintain referential integrity as a surrogate key. You should not be putting in blank records to make the number of records match with the autonumber. And, it will become an onerous situation to maintain.
Oh, it would be so nice to live in a perfect world where everyone understands not only why databases can be helpful, but also how to use them. I have been trying for decades to get them to have someone take care of the database, provide documentation for its use, and set it up so that someone who knows nothing about them can still input the data and get information from it. Instead, those who stored the data first used Paradox, then a spreadsheet, and finally Access without planning or thinking all the way through how the data could be used and made accessible to the LCD.
If access to ACCESS remained only in the hands of capable people, I wouldn't have a problem with it. When I inherited the current database, there was no documentation on what some of the fields meant, no forms for inputting data, no procedures to inputting/editting/deleting data, six autonumber ID #'s assigned to each individual and two more ID#'s that weren't useful at all! The table structure was fractured to the point that only ID #'s were used for almost all of the tables, leading to duplicates that couldn't be resolved, hidden records, and some queries that didn't work because they attempted to relate to fields from tables that no longer existed. Multiple source files were inputted without saving the source files so the data that was missing could not be reconstructed. There are no report forms, no listing of ongoing reports needed by the various departments, and multiple departments were inputting data without anyone checking to see if duplicate data already existed (in both the db as well as other input sources). Mail was getting labels printed on dead people, half of the db looked like it was hidden and no one had the skills to look to see where the error was, and no one was updating the corrected mailing addresses from each mail-out.
The last relational db I used was Alpha Software (10 years ago) and back then they were at least at the level Access is today. I am brushing up on relational db's and learning how Access does things. My goal is to streamline the system, make it more user friendly, and minimize the problems this db accumulated. Until it gets to the point where the LCD can use it without seeing the structure underneath, I will have to do things I wouldn't normally do - like blank records. Those blanks will get filled in by the new people who go through our educational/volunteering system. Thus the "blanks" will disappear and no one will see where they are located, only that their data can be readily retrieved.
If access to ACCESS remained only in the hands of capable people, I wouldn't have a problem with it. When I inherited the current database, there was no documentation on what some of the fields meant, no forms for inputting data, no procedures to inputting/editting/deleting data, six autonumber ID #'s assigned to each individual and two more ID#'s that weren't useful at all! The table structure was fractured to the point that only ID #'s were used for almost all of the tables, leading to duplicates that couldn't be resolved, hidden records, and some queries that didn't work because they attempted to relate to fields from tables that no longer existed. Multiple source files were inputted without saving the source files so the data that was missing could not be reconstructed. There are no report forms, no listing of ongoing reports needed by the various departments, and multiple departments were inputting data without anyone checking to see if duplicate data already existed (in both the db as well as other input sources). Mail was getting labels printed on dead people, half of the db looked like it was hidden and no one had the skills to look to see where the error was, and no one was updating the corrected mailing addresses from each mail-out.
The last relational db I used was Alpha Software (10 years ago) and back then they were at least at the level Access is today. I am brushing up on relational db's and learning how Access does things. My goal is to streamline the system, make it more user friendly, and minimize the problems this db accumulated. Until it gets to the point where the LCD can use it without seeing the structure underneath, I will have to do things I wouldn't normally do - like blank records. Those blanks will get filled in by the new people who go through our educational/volunteering system. Thus the "blanks" will disappear and no one will see where they are located, only that their data can be readily retrieved.
- Local time
- Today, 02:20
- Joined
- Feb 19, 2002
- Messages
- 47,685
How in the world does having blank records help you? All you are doing is causing future confusion. What if someone looks at an old paper record and sees ID 1234 which at that point was assigned to person A but is now assigned to person B. Do NOT go down this path. Leave the gaps and move on. You can easily see the record count, who cares if the highest ID number equals that. For the future, add a "deleted" flag and rather than deleting people just flag them so you can ignore them for all "current" activity.
There are no old paper records.
There is no current or old documentation.
There is no one to explain anything having to do with how the data was collected, how it was stored, or even who made the decisions to use the various software programs that have been used.
There IS confusion over how the current database is to be used. The person who has been using Access for the past couple of years did not use entry forms for entering data nor did she understand that a form could be designed to enter data into more than one form at a time. When asked about the database, she just said that it was too complex for her to understand.
There have been several who have asked why the record number does not equal the master record ID number that the autonumber field generated. No one had the answer. As for marking records that have been deleted or that are no longer active, no one consistently followed that logically. For example, some members have moved and no longer are in contact with the organization. Some of these people are labelled "lost" while some are not labelled any differently than the active members except that they don't always receive mailings. Not receiving mailings is not limited to just those who are "lost" but also others who don't have current mail addresses but who occasionally volunteer, but there is nothing to indicate a difference between the different groups. Even those who have died are still listed, so it isn't for that reason. Why then have 2700 deletions occurred in an autonumbered db size of 5300. No one knows.
My conjecture is that because multiple people have had access to the db and have uploaded multiple excel ss's to it, a rather large number of duplicates built up that were deleted.
The organization has finally decided to limit access to the db. It has centralized the handling of the data and now only two of us can directly enter the data and generate the reports. The db will be redesigned and forms will be used to standardize its use. Finally, the database will receive a final layer that will allow those who don't understand but who can enter data (the majority of those who have been interacting with the db so far) the ability to do so without changing the structure of the db.
Will the autonumber field ever be reactivated? Yes, of course it will. Once the blank records have been given new entries, autonumber will be reinstituted so that its size will continue to grow. The db doesn't care whether its records sequentially reflect when each record chronologically occurred. We have a field that gives that information that is not an autonumber generated field, nor would autonumber fit every situation. Actually, db's don't care if you have blank field information as long as its operators understand why and how to handle those blanks.
There is no current or old documentation.
There is no one to explain anything having to do with how the data was collected, how it was stored, or even who made the decisions to use the various software programs that have been used.
There IS confusion over how the current database is to be used. The person who has been using Access for the past couple of years did not use entry forms for entering data nor did she understand that a form could be designed to enter data into more than one form at a time. When asked about the database, she just said that it was too complex for her to understand.
There have been several who have asked why the record number does not equal the master record ID number that the autonumber field generated. No one had the answer. As for marking records that have been deleted or that are no longer active, no one consistently followed that logically. For example, some members have moved and no longer are in contact with the organization. Some of these people are labelled "lost" while some are not labelled any differently than the active members except that they don't always receive mailings. Not receiving mailings is not limited to just those who are "lost" but also others who don't have current mail addresses but who occasionally volunteer, but there is nothing to indicate a difference between the different groups. Even those who have died are still listed, so it isn't for that reason. Why then have 2700 deletions occurred in an autonumbered db size of 5300. No one knows.
My conjecture is that because multiple people have had access to the db and have uploaded multiple excel ss's to it, a rather large number of duplicates built up that were deleted.
The organization has finally decided to limit access to the db. It has centralized the handling of the data and now only two of us can directly enter the data and generate the reports. The db will be redesigned and forms will be used to standardize its use. Finally, the database will receive a final layer that will allow those who don't understand but who can enter data (the majority of those who have been interacting with the db so far) the ability to do so without changing the structure of the db.
Will the autonumber field ever be reactivated? Yes, of course it will. Once the blank records have been given new entries, autonumber will be reinstituted so that its size will continue to grow. The db doesn't care whether its records sequentially reflect when each record chronologically occurred. We have a field that gives that information that is not an autonumber generated field, nor would autonumber fit every situation. Actually, db's don't care if you have blank field information as long as its operators understand why and how to handle those blanks.
