MS Access SQL question

March 22, 2016 8:54 PM

I think this should be simple, but I don't use Access very often and my brain isn't working tonight.

I have two tables with a one-to-many relationship. Let's say it's a contact list. I have a table of people, and a table with a brief synopsis of a contact I had with them. The second table has a foreign key for the person table, obviously, and also a date field and a text field. This part works, and it's good for data entry and simple queries like recent activity or a history for a particular person.

What I want to do is have a query that filters the first table to only people who haven't been contacted lately (say, in the last 30 days). I'm thinking something like a max() or dmax() on the date field after the tables are joined?

posted by ctmf to Computers & Internet (14 answers total)

I'm not familiar with Access specifically, but in other databases what you are looking for is Interval and the Where clause.
posted by axismundi at 9:14 PM on March 22, 2016

You don't need SQL, you can do it with the QBE grid. Use date () for today's date.
So less than date()-30 is more than 30 days ago. (Sorry, replace less than with the correct symbol, can't seem to do that here)

If you also wanted people who haven't been contacted at all, you can click on the join properties between the two tables (within the QBE grid) and select an unmatched query.

I am sure someone can give you the SQL if you prefer.
posted by Heloise9 at 9:14 PM on March 22, 2016 [1 favorite]

Just off the top of my head [SQLFiddle]…

SELECT *
  FROM person p
  JOIN contact c ON c.person_id = p.id
 WHERE c.contact_date <= GETDATE() - 30
;

posted by ob1quixote at 9:27 PM on March 22, 2016 [2 favorites]

Best answer: ob1quixote's sql won't work - that will give you every contact that was less than thirty days ago regardless of whether or not they've been contacted more recently.

I don't know Access specifically but you can do a SQL query with a subselect as follows - I'm creating a subselect (which I'm calling r, for recent) selecting every person who HAS been contacted in the last 30 days, and then doing a join that excludes those people (left join and then a where clause that only includes the rows where that table is null.)

SELECT DISTINCT p.person_id
   FROM person p
   LEFT JOIN 
      (SELECT DISTINCT c.person_id 
         FROM contact c 
         WHERE c.contact_date > GETDATE() - 30) r on p.person_id = r.person_id
   WHERE r.person_id IS NULL

posted by brainmouse at 9:46 PM on March 22, 2016

Response by poster: The tricky part is, I'm going to have multiple entries in the contact table for every person, and everyone will eventually have ones far in the past. So if I simply select all the people who have contacts more than 30 days ago, that doesn't mean they don't also have a recent one. I'll just end up selecting every person every time. I need like a "WHERE THERE IS NO contact WITH contact_date > (date() - 30)" type of operator.
posted by ctmf at 9:52 PM on March 22, 2016

Response by poster: On not previewing, brainmouse, that looks plausible. I'll give it a try when I get back to work tomorrow. I know I can type in the SQL directly instead of the point-and-click method.
posted by ctmf at 9:58 PM on March 22, 2016

Best answer: brainmouse's query written in Access syntax. Hopefully person_id is a primary key so the DISTINCT is redundant. Current date in MS Access is obtained with DATEVALUE(Now) (which truncates the time off of the Now function, which is the current date and time. Adding an integer to an MS Access date adds days.

SELECT person_id
FROM person
WHERE person_id NOT IN
    (SELECT person_id FROM contact
    WHERE contact_date > DATEVALUE(Now) - 30)

posted by zixyer at 11:21 PM on March 22, 2016 [1 favorite]

Yeah, I wasn't thinking clearly when I threw that together. At a minimum, I should have had at least one person with more than one row in contact. Here's a version with a better test data set that I think will work a lot better. [SQL Fiddle]

SELECT p.name,
       p.phone,
       c.contact_date,
       c.contact_synopsis
  FROM person p
  JOIN contact c ON c.person_id = p.id
  JOIN (SELECT z.id
          FROM contact z
         WHERE z.contact_date = (SELECT MAX(x.contact_date)
                                   FROM contact x
                                  WHERE x.person_id = z.person_id)
       ) r ON r.id = c.id
 WHERE c.contact_date <= DATEVALUE(Now) - 30       
;

This will show rows that are older than 30 days, but only for the newest row for each person in contact.
posted by ob1quixote at 1:28 AM on March 23, 2016

Depending on what you need this for, you should be aware that, in Access, if you have a query with aggregate functions (like Max()), or one which joins to such a query, it will usually produce a "non-updateable" recordset, which is what it sounds like: a query that only displays records, and which you can't use as the source for a data form. You'd need to make an equivalent query that uses a subquery (like "...WHERE Person.id IN (SELECT Person.id FROM....(some query that gives you the right date range)" if you need a recordset that can be edited.
posted by thelonius at 4:04 AM on March 23, 2016

I use SQL a lot. For the sort of situation you describe, I use an outer join and look for where the join fails:

Select distinct member from member_table
left outer join contact_table on member.mem_num = contact_table.mem_num
where contact_table.date > "some date in the past"
and contact.mem_num = null

Add additional clauses to the WHERE clause as needed to get rid of groups of members you don't want.
posted by SemiSalt at 10:03 AM on March 23, 2016

Oops. Should be like this:

Select distinct member from member_table
left outer join contact_table on member.mem_num = contact_table.mem_num and contact_table.date > "some date in the past"
where contact.mem_num = null
posted by SemiSalt at 7:36 AM on March 25, 2016

Response by poster: Hm, the trick with the outer join and selecting the nulls works (thanks!) in sqlite but for some reason I can't make it work in access. Whittling down the query to the smallest part that doesn't work, it seems I can't make a left outer join work at all, no matter how simple I make it (always returns all nulls in the right-hand table). So it's got to be that I'm doing the syntax wrong and need to figure that out, since it works in sqlite at home.

Thanks for the help with the strategy, I would have never thought of that.
posted by ctmf at 8:52 PM on March 25, 2016

Try "left join" instead of "left outer join" - same exact thing, and some programs are tricky about requiring that "outer" to either be there or not.
posted by brainmouse at 9:21 PM on March 25, 2016

ctmf, be very sure that the fields you are trying to match really are the same. You can have problems with trailing blanks, for example.

My SQL databases are set for "case doesn't matter", but that is a database option, so perhaps in your database it does matter.
posted by SemiSalt at 1:45 PM on March 26, 2016