Results 1 to 5 of 5
  1. #1
    hym1988 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    2

    Red face Access SQL Query to split sheet into two tables

    I wonder if any one could help me with this case:

    Exampled data
    Click image for larger version. 

Name:	Untitled.png 
Views:	13 
Size:	20.0 KB 
ID:	42220



    And I would like to split them into two separate tables/sheets

    Click image for larger version. 

Name:	Untitled2.png 
Views:	13 
Size:	54.3 KB 
ID:	42221

    Splitting criteria for table 1:

    Any contact (email) that their second-to-last record contains data (Event ID)


    Splitting criteria for table 2:
    Any contact (email) that: have only ONE row OR their last two records are empty

    By last - I mean the oldest

    Any one can help with SQL query?

    M.

    EDIT://
    SplitTestData.zip
    Last edited by hym1988; 06-18-2020 at 08:53 AM.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Can you post a db with the table and its data for us to play with.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    hym1988 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2020
    Posts
    2
    I have added zip with db to first post, thank you.

  4. #4
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    Any contact (email) that their second-to-last record contains data (Event ID)
    you can find their second to last email in two steps:

    1. sort in date order, ascending, latest at the top, and run a TOP query to find the TOP 2 dates for each sender.
    2. run an AGGREGATE query on these two to find Min(date) and that will be the second to last one they sent. check to see if EventID is Null, and you're done.

    Any contact (email) that: have only ONE row OR their last two records are empty
    you already have their last two emails, from the above solution. check EventID for those.
    to see if they have only one row, you just run an AGGREGATE query, if Count(Email) = 1, then it's 1.


    that's your basic strategy,


    good luck with your project,


    Cottonshirt

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Also keep in mind that if your two resultant tables are mutual exclusive you only need to get the easiest of the two then use a unmatched query between the original and the one you got to get the second.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 6
    Last Post: 05-18-2020, 04:02 PM
  2. Replies: 7
    Last Post: 10-27-2016, 06:04 AM
  3. Split access querry in several excell sheet
    By wcedeno in forum Queries
    Replies: 3
    Last Post: 05-26-2011, 01:31 PM
  4. Replies: 1
    Last Post: 04-17-2011, 04:16 PM
  5. Replies: 4
    Last Post: 10-07-2010, 04:45 PM

Tags for this Thread

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