I would suggest posting a database with sample data (nothing private) that can duplicate the problem for people to look at. As Micron and orange have posted, the SQL means nothing without the underlying data/tables.
I would suggest posting a database with sample data (nothing private) that can duplicate the problem for people to look at. As Micron and orange have posted, the SQL means nothing without the underlying data/tables.
Well, after going back to a good backup and totally reconstructing, I was able to get my database working again.
HOWEVER, I still have a problem. It involves fields that have been added to a database after years of information has already been entered. My problem is that if we add a new crop, I can easily add the field, and my append query will add the harvest amounts for that crop to the database.
BUT, if I try to retrieve that information for the new fields into some of the forms I created. The harvest totals show blank, because the field didn't exist when the table was originally created and several rows are blank. I can get the field to show up with the correct numbers IF I manually put a 0 (zero) into all the previous records, however with 29,000 records that is extremely tedious and time consuming. I've tried exporting the table to Excel, filling in the ZEROs and then importing back to Access, but that's a real iffy procedure, since, even though I am careful to watch my formatting, at least half the time, it makes the new database unusable with the queries and reports already set up. Any other way to get the reports to read a blank as a zero? That would fix most of the problems I've encountered trying to update this database over the years.
Problem is, when this database was constructed, we grew three types of tomatoes. That was it. Now we grow 5 types of tomatoes, two types of squash, and bell peppers. They are also harvested in differing amounts (Lbs, RPCs, Tubs, and Bins). The reports have to take that information and convert to total pounds. That's probably not the last of it either. The system I set up 8 years ago has lasted for 8 years, and except for having to add a new crop, has worked well. Just wondering how that blank vs. zero thing could be done differently.
No, your problem is that if you must add a field that is just another item that belongs to a collection of items then the problem is poor design. There are several reasons why this should never happen, and you've hit on one of them - blanks. However, that is the least important reason.My problem is that if we add a new crop, I can easily add the field
A database is a relational model, not a spreadsheet- -i.e. data is row based, not column based as are sheets. You can patch your tables with updates, so 20,000 rows might only take a couple of seconds, but that's all it would be - a band aid. Take a look at normalization (entity/relationships) for more info.
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/
Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Ironically, this database was set up almost exclusively using templates originally provided with the Access 2000 program many years ago. I understand now that it was a mistake, but then hindsight has always been 20-20. I will pursue the normalization option to see if we can remove the redundant information, yet save the actual data we’ve collected over the last 8 years. Then i’ll decide whether there’s any way to actually keep the Access database at all, given there will be no one to maintain it after I leave.
If this database is important to the viability of the company, then management has some decisions to make
-identify (or hire) someone to take on a "dba" type role
-get that someone to work with you and get "up to speed" before you leave (could involve some contract after you retire?)
-modify the current database design to reflect current requirements
-redesign the database using appropriate database concepts/principles
If the database is not critical and management declares it so from a position of knowledge, then focus on your retirement.
In any event, enjoy your upcoming retirement.
As mentioned there is a sort term fix - query to update a number field. 29,000 records - about 3 seconds.I can get the field to show up with the correct numbers IF I manually put a 0 (zero) into all the previous records
This would make maintenance easier for someone who no doubt will be the newly appointed db admin IF the db has any importance at all. IF not, time will tell.I will pursue the normalization option
Your comment re template - it would surprise me to learn that even M$ would post an incorrectly normalized db. Maybe yours was incorrectly adapted from one that had a slightly different purpose? Anyway, I suggest you perform any required updates and go from there unless each added field introduces a lot of query/form/report/macro/code fixes. Then rebuilding might be a more sensible operation as those fixes would not carry over to a db that's properly normalized.
Just a word on running an update query on your data, make a copy of your database FIRST. Do not run a query for the first time on historical data unless you have a way to verify the query is doing what you want, in this case it's a fairly simple one but I repeat, DO NOT run an update query on your live data.
The query you want is something like
UPDATE <TableName> SET <ValueField> = 0 WHERE <ValueField) is null
In other words update your field to a value of 0 where it's not currently populated.
If you want other advice on the construction of your database I would post a sample for people to review. It's almost impossible to advise you without seeing what you have set up.