Results 1 to 4 of 4
  1. #1
    DodgerFH is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2013
    Posts
    2

    Need Query to Pull earliest date with two modfiers

    Hello. I am new to Access and working with a list of data that has the following fields: Ident, BusinessDate, Type. The purpose of my request is to try and find those people (Ident) that the first time they visited my business (BusinessDate) was from a particular promotion (Type.) Basically, if John Doe came in on 1-1-13 without a coupon but he came in on 1-7-13 with a coupon, I would like to have the report state John Doe , 1-7-13.



    Does this make sense? Using the query design view I can get either the first time a guest comes to my buisness or the type of promotion a guest uses. I cannot get the two of them together. Any help would be appreciated.

    Thank you.

    John

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Create a query add all three of your fields

    Click the aggregate query button (greek letter sigma, looks like a capital E on your toolbar/ribbon)
    in your TYPE field put 'coupon'
    on your businessdate field put MINIMUM (MIN)

    This will show you the first date any person came in with a coupon.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Not totally clear what you want. Don't want records that were not coupon visits? What if John Doe also visited 12-14-12 with a coupon?

    Maybe like:

    SELECT Ident, Min(BusinessDate) AS LastCoupon FROM tablename GROUP BY Ident HAVING Type="coupon";
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    DodgerFH is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2013
    Posts
    2

    Thanks!

    Thanks for the help! I had done something similar but this was the right order. Much appreciated!
    John

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Default earliest date and latest
    By Compufreak in forum Access
    Replies: 3
    Last Post: 01-10-2013, 07:17 AM
  2. Replies: 2
    Last Post: 04-27-2012, 05:27 PM
  3. Replies: 15
    Last Post: 04-06-2012, 10:57 AM
  4. Replies: 1
    Last Post: 02-17-2012, 04:43 PM
  5. Selecting Earliest and latest date
    By kstyles in forum Queries
    Replies: 10
    Last Post: 12-31-2010, 03:04 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