Results 1 to 6 of 6
  1. #1
    techexpressinc's Avatar
    techexpressinc is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2008
    Location
    Indiana, USA
    Posts
    151

    How to make a query Crash?

    I have two tables with one field name all that is one should be in the other. When this is not true, i want the query to crash, abend not run to a smooth end.



    Reason being to stop the processing of a report that is based on the query.

    Any idea of how I can do this.

    I have attached a picture, as it is worth a thousand words. The name field needs in both tables and being matching. If there is not a match I want the query to crash burn and no report.

    Thx for all and any good ideas. Russ

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,207
    I'm not sure wanting the query to crash is the right way to look at it. I would think with the proper joins, the query would return no records, which you then handle with the No Data event of the report.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    techexpressinc's Avatar
    techexpressinc is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2008
    Location
    Indiana, USA
    Posts
    151

    More info

    More info - This is a billing report. And I only get a once a month complete data load of employees.

    Then, during the month new people might join. I do activity dumps weekly and if any new employees are added I do not know.

    Currently, if the billing/activity reports are run and when a new person is added in the activity, they are not on the table of employees. My query/report runs to a good end, but excludes the new person(s).

    I need the report to not run or have a BIG red flag when a person is on the activity dump but not in the employee file; not just exclude that person from getting paid. This was/is a big problem..

    Thx Russ

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,207
    Okay, how about a join between the tables that makes sure everybody in the activity file shows up, with some sort of flag to highlight the fact that they're missing from the employee file? You would do that by editing the join between those two tables. In the design grid, after right clicking on the join to edit, you'd choose the appropriate "Include all records from..." option. In SQL view, it would entail changing INNER to LEFT or RIGHT as appropriate to their positions relative to each other.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    techexpressinc's Avatar
    techexpressinc is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2008
    Location
    Indiana, USA
    Posts
    151

    Need a Big Flag that is way i wanted an abend

    There are 80 pages of detail i do not want to look month after month for one line being red.

    Most months there will not be a missing person.

    Please take a look at the picture.

    Last month I had the employee on the query "Weekbill of billing activity type base" on the table "dbo_users" But not on "Weekbill staff assign rate". fyi FSW-CR = name.

    That employee then was dropped. No payment record(s) for their activity, no message they were drop. Just one angry client.

    Thx for the help. Russ

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,207
    Fine; you mentioned having a flag. How about creating a unmatched query to find anybody who is in the activity table but not the employee table? You can either use a DCount() on that query and not run the report if it returns any records, or...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Make table query
    By Zahed in forum Queries
    Replies: 1
    Last Post: 11-06-2009, 05:44 AM
  2. How to make A combobox run a query?
    By UnrealEnvy in forum Access
    Replies: 8
    Last Post: 08-17-2009, 11:28 PM
  3. Make Table Query Issue
    By tmcrouse in forum Access
    Replies: 0
    Last Post: 07-23-2009, 03:20 PM
  4. Make a table query
    By Fiona in forum Access
    Replies: 4
    Last Post: 06-25-2009, 11:24 AM

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 - Senior Forums