1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2013 - Count only if date falls within 30 days of field from different table

Discussion in 'Business Applications' started by sdl19, Sep 28, 2014.

Thread Status:
Not open for further replies.
Advertisement
  1. sdl19

    sdl19 Thread Starter

    Joined:
    Sep 28, 2014
    Messages:
    1
    Hello,

    I have been struggling with this for a couple of days.

    Here's the scenario: I have 2 sets of data, one shows repeat calls for a list of customers, the other shows trouble reports for the same set of customers. I want to count how many times each of these customers called within 30 days of the original trouble report.

    I have tried using a nested count Iif expression but I don't have a lot of experience with expressions and apparently am using it incorrectly. What I have tried is something like this:

    =Count(IIf([Contact Date/Time],+30,1))

    Any suggestions or ideas are welcome at this point. Thank you in advance!

    Thanks,

    sdl19
     
  2. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,535
    I would create a 'groupby' query using the DateAdd function for criteria. If this is a report you should be able to link this query to the report query by acct number or something.
    SQL looks like this example
    SELECT Table1.Name2, Count(Table1.ID) AS NameCount
    FROM Table1
    WHERE (((Table1.Date1)>DateAdd('d',-30,Date())))
    GROUP BY Table1.Name2;
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1134501