Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Some queries/reports don't update (2000/9.0.3821 SR1)

    Let's say you have a couple of related tables in a database and that you use queries to create reports. Now suppose you add new records to one or more of the tables. Those new records should automatically appear the next time you run a report, oui? Well, that happens when I open some reports, but not all.

    I've added two new records to a table of continuing education courses. The new courses appear on one set of reports, but they stubbornly refuse to show up on others. I've re-run the queries, but nothing changes. I've studied my properties, but I can't find the source of the problem. I think part of my problem is that I don't know what to look for. Can someone steer me in the right direction?

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Some queries/reports don't update (2000/9.0.3821 SR1)

    I presume you have different queries for different reports.
    Is there some criteria that is not allowing these records to be selected in these queries?
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Some queries/reports don't update (2000/9.0.3821 SR1)

    Have you checked whether they appear when you run the queries directly?

    You mention adding new records to "one or more of the tables". What kind of join do you have between the tables? The default is an inner join which only returns records if there is at least one match on both sides. That issue has "bit" me many times.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Some queries/reports don't update (2000/9.0.3821 SR1)

    Yes--I use different queries to create the individual reports. From the table in question (I'll call it Table A), a query draws data to create what we call training plans for our students. The two new courses I added to the table appear in those training plans just fine. Another query draws from the same table and another related table to create a list of courses (a report) sorted by our in-house course number. This report displays the courses, shows which equipment to use with each course, and gives the lab coordinator some tips on how to start and run each course.

    This latter report is the offending one. Yes, I do have criteria set up to exclude certain records; those criteria filters have worked fine, and there's nothing apparent that would cause the criteria to exclude the new records.

    I'd like to post the database on the forum, but it's too big. Maybe I can create a pared-down version that I can attach.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Some queries/reports don't update (2000/9.0.3821 SR1)

    Yes--that was one of the first things I checked, and no, the new records do not appear in query print preview. As for joins: I've got a one-to-one join on the "HandoutNbr" field, which is identical in both tables.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Some queries/reports don't update (2000/9.0.3821 SR1)

    If the records don't appear in the query, then the problem is with the query, not the report. You'll have to examine the query very closely to see what might be keeping your records out. One approach is to change any inner joins to an outer join and see whether the records show up then.
    Charlotte

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Some queries/reports don't update (2000/9.0.3821 SR1)

    Ah-ha! Thank you, Charlotte! Your reply tipped me off the the source of my troubles. I had created one of the tables involved with a make-table query. That query had pulled information from my "core" table back before I added the new records. I think now I can simply re-run that make-table query, which should make all things right again.

    Gracias! <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •