Page 2 of 2 FirstFirst 12
Results 16 to 22 of 22
  1. #16
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442

    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.

  2. #17
    jparker1954 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    85
    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.

  3. #18
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My problem is that if we add a new crop, I can easily add the field
    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.

    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.

  4. #19
    jparker1954 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    85
    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.

  5. #20
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.

  6. #21
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I can get the field to show up with the correct numbers IF I manually put a 0 (zero) into all the previous records
    As mentioned there is a sort term fix - query to update a number field. 29,000 records - about 3 seconds.
    I will pursue the normalization option
    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.

    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.

  7. #22
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    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.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-26-2016, 06:34 AM
  2. Replies: 7
    Last Post: 03-17-2016, 05:53 PM
  3. Replies: 5
    Last Post: 05-15-2015, 10:49 AM
  4. Replies: 15
    Last Post: 11-01-2013, 03:24 PM
  5. sql error when day part of date starts with a zero
    By OceanaPolynom in forum Programming
    Replies: 4
    Last Post: 04-01-2013, 02:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums