Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    No Records (WIN XP/Access 97)

    I would have sworn I've done this before, but cannot find it anywhere.

    tblABC - 4 fields
    RecNum
    REP
    DateWorked
    Phone

    tblREPNAMES
    RecNum
    REP
    REPNAME

    I want to write a query prompting the user for the Date Worked (I know how to do
    that !!) and have the results shows all REPs with NO records for that date.

    Any help is always appreciated.

    Thank you.

    Michael Abrams

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: No Records (WIN XP/Access 97)

    Most people use the query wizard to do this - but it's pretty easy on your own.

    Create a query with both tables in the query and do a join from RecNum in tblREPNAMES to RecNum in tblABC. Then click on the join line and select the option that says show all records in tblREPNAMES and matching records in tblABC. Then put the date constraint on DateWorked, and put a constraint of Is Null on RecNum in tblABC. That should do the trick.
    Wendell

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: No Records (WIN XP/Access 97)

    Sorry - I didn't look at your names very carefully either. I presume the REP field is a key that you can join on so the join should be between those two fields in the two tables.
    Wendell

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Records (WIN XP/Access 97)

    I apologize for my speedy initial posting - The tblREPNAMES is strictly a lookup table, therefore the
    RECNUM in that table refers to the record number in tblREPNAMES. It is not associated with the
    RECNUM in tblABC.

    Basically, I need the opposite of this:
    SELECT [tbl_Rep Names].[REP NAME], tblABC.DATE_WORKED
    FROM tblABC RIGHT JOIN [tbl_Rep Names] ON tblABC.REP = [tbl_Rep Names].REP
    GROUP BY [tbl_Rep Names].[REP NAME], tblABC.DATE_WORKED
    HAVING (((tblABC.DATE_WORKED)=[ENTER DATE]));

    Sorry for misleading you.

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Records (WIN XP/Access 97)

    SELECT DISTINCTROW [tbl_Rep Names].[REP NAME], tblABC.REP, tblABC.DATE_WORKED
    FROM tblABC RIGHT JOIN [tbl_Rep Names] ON tblABC.REP = [tbl_Rep Names].REP
    WHERE (((tblABC.REP) Is Null));

    This gives me everyone in the tblREPNAME table that has NO records at all in the tblABC.

    When I add the date parameter, it gives me no records when there should be.

    SELECT DISTINCTROW [tbl_Rep Names].[REP NAME], tblABC.REP, tblABC.DATE_WORKED
    FROM tblABC RIGHT JOIN [tbl_Rep Names] ON tblABC.REP = [tbl_Rep Names].REP
    WHERE (((tblABC.REP) Is Null) AND ((tblABC.DATE_WORKED)=[Enter Date]));

    I am sooo close ?

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: No Records (WIN XP/Access 97)

    Under normal circumstances that should work. However in this case you may need to do a query that gets just the records for the day you want from tblABC, and then use that query in the join to tbl_Rep Names. (You can use queries like you would normally use a table when constructing a query.) So in the second SQL statement, replace tblABC with the name of the query, and it should work.
    Wendell

  7. #7
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Records (WIN XP/Access 97)

    It works fine using two queries.

    For some reason, my brain always stops at one query.
    I really need to expand that part of my thought process.

    Thank you Wendell for sharing !!

    Michael

Posting Permissions

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