Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report--All Records Table 1; True/False Table 2 (Access 2000)

    My boss wants a report that shows all clients (500+) in Table 1 and an indication of whether or not the clients have been contacted. In Table 2 only clients that have been contacted appear. (The tables related by client code.)

    Table 1 has

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report--All Records Table 1; True/False Table 2 (Access 2000)

    You'll need to make a query for this report...

    <UL><LI>Table 1 needs to be Left Joined with Table 2 on Table1.ClientCode = Table2.ClientCode (this will select all Clients in Table 1)
    <LI>Then add both ClientCode fields to the query design... (Table 1's ClientCode will always have a value in it... but Table 2's ClientCode field will be Null if that client doesn't exist in Table 2)
    <LI>Then create a calulated field such as... fldContacted: IIf(IsNull(<table2>.[ClientCode]),"False", "True")[/list]This should give you a list of all clients with a True or False value for whether they've been contacted or not...
    Obviously you'll have to add any other fields you want to see... and use your own names for the tables and fields... <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    HTH

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Report--All Records Table 1; True/False Table 2 (Access 2000)

    The next step will be that your boss wants to know the latest time someone was contacted as well as how many times the client was contacted over a period of time.

    You could do this with two tables. Table 1 stores clients. Table 2 stores contact information such as date contacted and is linked to table 1 as a one to many. When you run your report, create a query to get clients from table one and dates contacted in table two. Group the data by client and max date to get the last time a client was contacted. You could use another query to count the time contacted over a period of time.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report--All Records Table 1; True/False Table 2 (Access 2000)

    Oops.... It didn't show the Table2 name in the square brackets...
    Okay... I'll use "tbl2" for this purpose...

    The calculated field syntax was supposed to look like this...

    fldExists: IIf(IsNull([tbl2].[ClientCode], "False", "True)

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report--All Records Table 1; True/False Table 2 (Access 2000)

    Many thanks for the quick response. I tried the query with the IIf expression field as suggested, but I must be doing something wrong, as the query results still only reflect those clients that are included in BOTH tables and thus, the expression field reflects "True" for all records in this partial list. We need to see ALL clients from the "tbl Clients" and then see a field beside each client that indicates "True" or "False" as having been contacted. Those clients in "tbl Clients" that do not appear in "tbl Client Visits" should be reflected with a "false" in the expression field. The primary key field in both tables is named CODE. Thanks for clarifying anything I may have misunderstood.

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report--All Records Table 1; True/False Table 2 (Access 2000)

    I understood that...

    Hang on... I'll try make a quick sample database... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report--All Records Table 1; True/False Table 2 (Access 2000)

    Off topic: some text in square brackets gets interpreted as HTML tags - such as <!t><!/t> and <!t><!/t> to create bold text. There are some unexpected side effects of this: for instance, <!t>[Table]<!/t> is used to create a table in a post, but if the rest of the code for the table is lacking, it will just disappear. In fact <!t>[Table followed by anything between the brackets]<!/t> will disappear.

  8. #8
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report--All Records Table 1; True/False Table 2 (Access 2000)

    k... Here it is... You'll see two tables... and one query... It shows the Left join and the calculated field...
    In the results you'll notice that Jim Jones has a False next to his CODE because he's not been contacted (ie there is no record for him in table 2)

    HTH
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Attached Files Attached Files

  9. #9
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report--All Records Table 1; True/False Table 2 (Access 2000)

    Thanks Hans! I figured it was something to do with the Table tag... <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

  10. #10
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report--All Records Table 1; True/False Table 2 (Access 2000)

    Many thanks, Trudi, for sending the attachment with example of the query. I saw immediately what I was doing wrong--it had been so long since I've had to change the default join in queries that I didn't even think of it (even though you did say a "left" join, and silly me just switched the one table to the left side!). Anyway, the query now works exactly as needed, and I really appreciate your help and the prompt response! Again, many, many thanks...Mary

Posting Permissions

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