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

    Exception Reporting

    Hey Everyone,
    Looking for the most efficient way to identify Data in a column that is unexpected (i.e. UI Typo). Right now I think this is going to be incredibly manual in that I will need to eliminate correct entries in the WHERE clause.

    Any ideas or logic that may make this less manual?

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Create a table with all allowed value. then you can find the exception easily by matching them.

  3. #3
    shexe is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    71
    So essentially doing a vlookup and populating a report with #N/A returns?

  4. #4
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    are you working in Excel?

    A left join query will work in Access

  5. #5
    shexe is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    71
    The report is in excel but connected to Access to reflect realtime updates. Everything in the excel sheet is programmed in SQL.

  6. #6
    shexe is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    71
    Closed that a little too soon. The left JOIN only provide records that match to the Allowed table. I am looking for items that do not match. Am I missing something here?

  7. #7
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    exchange the place of two tables:
    select * from table1 left join table2 ...
    select * from table2 left join table1....

    or change left join to right join.
    select * from table1 left join table2 ...
    select * from table1 right join table2 ...

  8. #8
    shexe is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    71
    Both suggestions are just eliminating the Records that do not match. I am trying to eliminate the records that do match. Is it possible that using a JOIN, no matter what table it is matching from is going to provide this?

    I think the complexity lies in the fact that the table I am matching to has known values and I am looking for the unknown. Keep in mind I cannot create a table of the unknown as this is essentially a safety net for typo's and human error, the possibilities are really endless.

  9. #9
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Oh, I forgot.

    select table2.* from table2 left join table1 on table1.key=table2.key where table1.key is null

    will show records in table2 but not in table1.

  10. #10
    shexe is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    71
    Tried running through with the suggested SQL and I am still not receiving the needed results. Is there any other way to comapre tables and report on non-match?

  11. #11
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Please provide your table name and field names and brief description of table, as well as your query,.

  12. #12
    shexe is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    71
    IT_Staff_Allocation_2010 - Table is fed by an Excel template (UI)
    FieldNames TemplateOwner,Project,BudgetWorkbook,ResourceName,Correction,January,February,March,April,May,June,July,August,September,October,November,December,UpdatedBy,UpdatedDateTime

    BudgetWorkbookAllowed - only 1 field.
    FieldNames BudgetWorkbook

    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 BudgetWorkbookAllowed ON [BudgetWorkbookAllowed].BudgetWorkbook=[IT_Staff_Allocation_2010].BudgetWorkbook
    WHERE [BudgetWorkbookAllowed].BudgetWorkbook=null;


    Essentially what I am trying to build is a query that compares the 2 tables on BudgetWorkbook and only displays the BudgetWorkbook from IT_Staff_Allocation_2010 that does not match.

    Please keep in mind I am in a development environment and the live data for BudgetWorkbookAllowed is much larger than the table provided.

  13. #13
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    WHERE [BudgetWorkbookAllowed].BudgetWorkbook=null
    change it to following and try again, you must get it.
    WHERE [BudgetWorkbookAllowed].BudgetWorkbook is null

  14. #14
    shexe is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    71
    Thanks! Works like a charm!

    Why did the = not work?

  15. #15
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    should not use compare to a null value.
    no =, no >, no <, no <>.
    only is null or is not null.

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

Similar Threads

  1. Reporting Issue
    By watzmann in forum Access
    Replies: 1
    Last Post: 08-30-2010, 08:16 PM
  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. Question on Reporting
    By jbarrum in forum Access
    Replies: 20
    Last Post: 01-14-2010, 02:05 PM
  4. Dynamic reporting
    By pushpm in forum Programming
    Replies: 0
    Last Post: 04-22-2009, 12:45 PM
  5. Rookie needs help with basic reporting
    By James Rousselle in forum Reports
    Replies: 0
    Last Post: 03-01-2007, 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