Results 1 to 15 of 15
  1. #1
    sharone is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    8

    Report not working - alwasys copied from previous year - worked as of March 5


    I have an Access report that I have set up to give me contest entries totals by district. I worked as of March 5 as I am looking at the hard copy report. The database is copied each year and I update the database name and tables. I started adding more entries to the main database and now the report does not work. When I pull up the report, all I get is blanks where it would normally have numbers for District 1, District 2, etc. I am stumped. I even resaved it again, deleted the information and started over. I am stumped! The report pulls from sum of queries which are blank. The essay contest total by district queries have the totals but not the sum of the total queries so they can pull it in the report. I hope I am not confusing anyone but I am not that savvy with Access and hope someone understands. Don't know if I am able to attach a copy of the database if that would be helpful to anyone, but thought I'd give this forum a shot. I checked back to the 2014 database and it all seems the same so can't tell what is wrong with the queries or report. I have District 1 - District 11. four districts don't have any totals yet. The total all districts query that this report is attached to is blank. The numbers that should be in each district (like the one below) should be in the total all districts query for the report but all fields are blank whether they have totals in them or not. But all total districts (with the exception of 4) have numbers in them like the one below.

    SumOfSchool Total District 2
    37 x

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Hopefully when you say you 'updated the tables', that you did not rename them. That will destroy the queries.
    Also check for a time restriction. If your old one works but not the new one, it could be looking at 2014 in a date/year field.

    Click image for larger version. 

Name:	District_12.png 
Views:	11 
Size:	17.0 KB 
ID:	20130

  3. #3
    sharone is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    8
    Yes, I renamed them from EssayContestEntries2014 to to 2015. I do that every year since 2002 with no issues. Other queries are working fine accept the sum of total and essay contest report. As I said, it worked as of March 5, so don't understand why it all of a sudden doesn't work now. EssayContestTotalsMarch5-03242015082342.pdf. I attached a pdf of the report but don't know if it was able to upload it or not.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It sounds like the table structure is not normalized.

    Would need the dB to analyze the problem.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I see the pdf but it is not useful for analyzing this issue.

    Agree with ssanfu. There should be no reason to annually rename tables in a properly structured database. If you want to keep a db for each year, then archive each db with different name but don't rename objects in the db. Design edits should be rare event in a deployed db.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    sharone is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    8
    I don't rename the queries. Just the name/title of the database, from essay contest 2014 to essay contest 2015, otherwise it won't let me keep the same name for the database, and then changing the main table name. Everything else stays the same. It was how I did it with no issues before. We have since moved to the cloud, but when I printed the report on March 5, we were already on the cloud so that can't be it either, can it? I did have to move it from the cloud server to a One Drive/My documents server because of not responding issues.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why would you even change the main table name?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    sharone is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    8
    I copy the table from year to year and I just change the year behind the table i.e. 2013, 2014, 2015, etc. I guess if I had just kept it generic essay contest with no year it would have been fine. But now if someone opens the table and it says 2014 and we are using it for 2015 entries, it might be confusing. They are used to log entries for that year hence it needed to reflect the current year.

    I copied the 2014 again and changed to 2015 and it seems to be working now. Can't explain I didn't do anything different from what I normally do. I also didn't know you aren't supposed to rename tables, even if copying from one year to the next?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Users should not interact directly with tables and queries, only forms and reports.

    You can rename at your own risk and deal with consequences - as you discovered.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    sharone is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    8
    Hope this comes through, here is the essay contest by district, I put a dummy number of 21 in one district and zeros in the other. totalAllDistricts.pdf

    Actually we have a new database for our members, which is Member Clicks. I only use this database for essay contest entries. So I am the only one who uses the Access database, as well as the only one who has minimal knowledge of Access. So no other users to this database but me.

    And.. this is the first time that I have been copying the file from year to year that this has happened to me. The first essay contest database was created in 2002. I have only copied and renamed the table since that point. I can only wonder if going to the cloud or one drive is causing issues. Access takes 10 minutes after not responding to open on the cloud.

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I also didn't know you aren't supposed to rename tables, even if copying from one year to the next?
    The year is data. In a properly designed dB, the table name would stay the same and there would be a field for the year/date.
    You would filter by year to get 2015 or 2014 entries.

    I NEVER allow anyone to see tables or queries, only forms and reports (as June7 stated).

    I don't rename the queries. Just the name/title of the database, from essay contest 2014 to essay contest 2015, otherwise it won't let me keep the same name for the database, and then changing the main table name. Everything else stays the same.
    Auto-corrupt... uhhh...I mean auto-correct must be enabled. Do you have reports or do you just print the query results?

  12. #12
    sharone is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    8
    I have one main report. The others are queries, district 1 -11 queries, total by district queries, which feeds the essay contest totals by district report, which is a one page document.

    I don't have year data as a column. We get 2,000 plus essays every year. Contest runs from December to March 31. We only need it for contest entries, no need to reference any of the other information after that. With so many teachers entering each year, and only needing the database from one year's information it made more sense to do a yearly database, not a main table with all the years.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Database for each year - fine - just no need to change object names.

    You said 'if someone opens the table' which implied multiple users. Even if you (or your successor) is the only user, still advise to interact with data through forms, at least for routine processes.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    sharone is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    8
    Yes, I meant after me. Sometimes my supervisor might look to see the totals but she usually asks me to print out the report.

  15. #15
    sharone is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    8
    Okay, thanks.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-08-2015, 10:35 AM
  2. Replies: 7
    Last Post: 05-16-2014, 02:25 PM
  3. Select Month from previous selected year
    By k0enf0rNL in forum Access
    Replies: 1
    Last Post: 01-15-2014, 12:14 PM
  4. Previous Year Data Query Expression
    By NotReese in forum Queries
    Replies: 4
    Last Post: 11-22-2013, 04:54 PM
  5. Query Help - calculate variance previous year
    By brtucker in forum Queries
    Replies: 1
    Last Post: 01-31-2013, 05:40 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