Results 1 to 5 of 5
  1. #1
    DC CS is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    Jan 2023
    Location
    Vaalpark, South Africa
    Posts
    63

    SQL Statement to retrieve data

    Hi all,
    I have an MS Access 365 database with a table that stores ID Numbers, Facilitator ID Numbers, Dates, etc.
    When the user captures the data, each student can have more that 1 date (one to many), so the data is stored in the table as follows:

    StudID, Course, StartDate, Enddate
    1234, 123, 02-01-2023, 02-01-2023
    1234, 123, 07-01-2023, 07-01-2023
    1234, 123, 16-01-2023, 16-01-2023
    1111, 123, 02-01-2023, 02-01-2023
    1111, 123, 07-01-2023, 07-01-2023
    1111, 123, 16-01-2023, 16-01-2023
    2222, 123, 02-01-2023, 02-01-2023


    2222, 123, 07-01-2023, 07-01-2023
    2222, 123, 16-01-2023, 16-01-2023

    All 9 records are being written to the table. All that differs, is the StudID numbers.

    Using SQL statement "SELECT * FROM Tbl_Transaction.... WHERE Course = '123'" results all 9 records to be retrieved.
    All data I require, is the 3 different startdates and the 3 different enddates. I am sorting the data by StudID and then by EndDate.

    Is there perhaps a SQL statement that I can use just to retrieve the 3 dates from the table? Maybe a conditional statement?

    As always, much appreciated.

    Thanks
    Deon

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    All data I require, is the 3 different startdates and the 3 different enddates.
    Code:
    SELECT Dccs.StartDate, Dccs.Enddate
    FROM Dccs
    GROUP BY Dccs.StartDate, Dccs.Enddate;

  3. #3
    DC CS is offline Advanced Beginner
    Windows 11 Office 365
    Join Date
    Jan 2023
    Location
    Vaalpark, South Africa
    Posts
    63
    Thanks Orange. Yes, I did do it this way, but as soon as I add the StudID to the SELECT Statement, all 9 records are retrieved.
    I thought there may be a SQL statement that I can use to include the StudID without retrieving al 9 records.

    Thanks
    Deon

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Use that sql as a subquery?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  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
    Include StudID but instead of having Group By in the Totals row use Min or Max (or First or Last).

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

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

Similar Threads

  1. Retrieve Data From 2 x SQL Statements
    By DMT Dave in forum Access
    Replies: 11
    Last Post: 06-18-2022, 12:41 AM
  2. What is quicker way to retrieve data
    By adnancanada in forum Queries
    Replies: 14
    Last Post: 03-02-2017, 09:46 AM
  3. using SQL in VBA to retrieve data
    By sovereign in forum Access
    Replies: 8
    Last Post: 08-25-2015, 10:17 AM
  4. Replies: 21
    Last Post: 06-27-2014, 07:33 AM
  5. Retrieve Data Too Slow
    By BGF in forum Programming
    Replies: 8
    Last Post: 09-08-2010, 04:28 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