Results 1 to 12 of 12
  1. #1
    shexe is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    71

    Exception Reporting - Continued

    Good Morning -
    I have a query that uses a LEFT JOIN to identify typos and errors in a field that is populated by front end users. Essentially, the query ensures that no unknowns are skewing reports.



    So far, the query logic works fine, except it is not picking out the possibility of the field being keyed with a space after the data. For instance, the query identifies (where underscore=space) _BITN306 and BITN_306 but not BITN306_.

    Is anyone aware of why the last example isn't being identified by the query? Also, any recommendations on how to possibly solve for this?

    Thanks.

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    how did you identify _BITN306 and BITN_306, may I have a look?

  3. #3
    shexe is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    71
    BITN_306 and _BITN306 are identified b/c they are not on the table that the query is being joined on.

    Below is the query.
    SELECT IT_Staff_Allocation_2010.TemplateOwner, IT_Staff_Allocation_2010.Project, IT_Staff_Allocation_2010.BudgetWorkbook, IT_Staff_Allocation_2010.ResourceName, IT_Staff_Allocation_2010.January, IT_Staff_Allocation_2010.February, IT_Staff_Allocation_2010.March, IT_Staff_Allocation_2010.April, IT_Staff_Allocation_2010.May, IT_Staff_Allocation_2010.June, IT_Staff_Allocation_2010.July, IT_Staff_Allocation_2010.August, IT_Staff_Allocation_2010.September, IT_Staff_Allocation_2010.October, IT_Staff_Allocation_2010.November, IT_Staff_Allocation_2010.December

    FROM IT_Staff_Allocation_2010 LEFT JOIN Budget_Workbook_Allowed ON IT_Staff_Allocation_2010.BudgetWorkbook = Budget_Workbook_Allowed.BudgetWorkbook

    WHERE (((Budget_Workbook_Allowed.BudgetWorkbook) Is Null));

  4. #4
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    BITN306_ must be in table [IT_Staff_Allocation_2010],right?

    is BITN306_ in the table [Budget_Workbook_Allowed]?

  5. #5
    shexe is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    71
    BITN306_ is not in either table. The query is designed to identify budget workbooks that do not exist (user error). For instance, BITN306 does exist (no spaces) but any variation of that (spaces) should populate from the WHERE clause.

    In short, I am matching a Front End user template to a table of known workbooks and identifying any mismatch.

  6. #6
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    I am not sure what do you want to do.
    But I don't think you can get it (BITN306_) if it is not in either table.

    would you please explain why do you need BITN306_ even it not in table [IT_Staff_Allocation_2010]?

  7. #7
    shexe is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    71
    The key to this query is that it identifies workbooks not on either table. I need to be able to identify instances including spaces because downstream reports are being affected.

    If the workbook does not match, the reports will not include that line item. The problem with that lies in the fact that the reports are used monthly to forecast a business areas budget.

  8. #8
    shexe is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    71
    Continued from post above.

    The Exception query allows me to identify either typos or new projects that may not be in my table, which means they also do not have the necessary report created downstream. This is an issue as we are losing track of department spend.

  9. #9
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    you need another similar query to identify those in [[Budget_Workbook_Allowed]] but not in [[IT_Staff_Allocation_2010].

  10. #10
    shexe is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    71
    FROM Budget_Workbook_Allowed LEFT JOIN IT_Staff_Allocation_2010?

  11. #11
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    yes.
    do not forget to change a little bit in where clause.

  12. #12
    shexe is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    71
    This is a nice double check to make sure that all needed workbooks are included, but it is not yielding the BITN306_ that I am need identified.

    I am wondering why access isn't picking up the space after the data in the match. Doesn't seem to be an issue with spaces within or before data.

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

Similar Threads

  1. Exception Reporting
    By shexe in forum Queries
    Replies: 16
    Last Post: 09-09-2010, 09:14 AM
  2. Exception When Executing In Java But Runs In MS Access DB Manually
    By vasuresh.kishor@gmail.com in forum Access
    Replies: 0
    Last Post: 03-11-2010, 12:22 AM
  3. print continued at bottom of report
    By ecpike in forum Reports
    Replies: 2
    Last Post: 02-25-2010, 02:50 PM
  4. Question on Reporting
    By jbarrum in forum Access
    Replies: 20
    Last Post: 01-14-2010, 02:05 PM
  5. Dynamic reporting
    By pushpm in forum Programming
    Replies: 0
    Last Post: 04-22-2009, 12:45 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