Results 1 to 4 of 4
  1. #1
    AccessKook is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    9

    Counting same names between date range and updating field to count


    Hi all,

    I have a db that contains has the following information. ID is the primary key, EventDate is the date on which the event occurred, Name1 and Name2 are the names of the participants in the event and the CountName column is the desired outcome.

    ID EventDate Name1 Name2 CountName
    AA1 01/01/2000 AAA BBB 0
    AA2 31/02/2000 CCC DDD 0
    AA3 10/03/2001 EEE AAA 1
    AA4 15/11/2001 GGG HHH 0
    AA5 20/05/2002 AAA III 2
    AA6 29/08/2003 JJJ KKK 0
    AA7 25/10/2003 BBB LLL 1
    AA8 10/12/2004 AAA MMM 3
    AA9 20/05/2006 AAA NNN 2

    The database contains data about events, let's say form 01/01/2000 until 31/12/2006. I need to count the number of times a name appears in Name1 and Name2 in a five year time period prior to the focal event date. I guess its best to illustrate with an example. Let's look at AAA. On the 01/01/2000 the count is 0 because in there are not prior events for this name in the db. On the 10/03/2001 the count is 1 as the event of the 01/01/2000 was in the five year period before the focal event etc. Note that on the event of the 20/05/2006 the count is 2 as the event on the 01/01/2000 is more then 5 years before the focal event so should not be counted.

    I have been trying to use various combinations of select and update queries but everything seems to lead me to a dead end. Any suggestions as to how to approach this problem would be greatly appreciated.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Part of the difficulty is having to consider two fields in the count. If the names were in one field, this would be easier. One way to deal with this is with a UNION query. UNION will normalize the two fields into one. There is no wizard or designer for UNION, must type or copy/paste into SQL View of query builder.

    SELECT ID, EventDate, Name1 AS PartName, "Name1" AS Source FROM tablename
    UNION SELECT ID, EventDate, Name2, "Name2" FROM tablename;

    Now use the UNION query as data source for summarizing the records. I could only get to work with a DCount() function.

    SELECT ID, EventDate, PartName, Source, DCount("PartName","ParticipantsUnion","PartName='" & [PartName] & "' AND EventDate >#" & DateAdd("yyyy",-5,[EventDate]) & "# AND EventDate<#" & [EventDate] & "#") AS CountPart
    FROM ParticipantsUnion;

    Then pivot the data.

    TRANSFORM First(Query1.PartName) AS FirstOfPartName
    SELECT Query1.ID, Query1.EventDate, Sum(Query1.CountPart) AS [Total Of CountPart]
    FROM Query1
    GROUP BY Query1.ID, Query1.EventDate
    PIVOT Query1.Source;


    Your example has an invalid date - 31/02/2000 does not exist.

    Your dates are not in Access standard of mm/dd/yyyy. This might cause issues. Review http://allenbrowne.com/ser-36.html
    Last edited by June7; 07-16-2013 at 01:58 AM.
    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.

  3. #3
    AccessKook is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    9
    Hi June7,

    that was very quick. Thanks a lot!!! The queries seem to work on my example database. Sorry about the invalid date, I was just typing without putting much thought into it and thanks for point out the date format convention and the link out to me. That was very useful.

    As the original database is quite substantial I have not had the chance to adjust the code and run it on the full dataset because I suspect that running the queries will take quite a bit of time. I will try to post update as to how it went tomorrow and mark the treat as solved if it all goes well.

    Thanks again,
    AccessKook

  4. #4
    AccessKook is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2012
    Posts
    9
    Hi June7, thanks a lot again, everything works perfectly in the full dataset.

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

Similar Threads

  1. Replies: 5
    Last Post: 07-05-2013, 11:16 AM
  2. Updating one date field based on another
    By barryg80 in forum Forms
    Replies: 4
    Last Post: 04-19-2013, 03:17 AM
  3. Replies: 6
    Last Post: 12-11-2012, 09:40 PM
  4. Multiple Field & date range filter
    By mrkandohi001 in forum Reports
    Replies: 6
    Last Post: 01-18-2012, 03:11 AM
  5. Count Dates within Date Range
    By eckert1961 in forum Queries
    Replies: 2
    Last Post: 03-08-2009, 10:58 AM

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