Results 1 to 3 of 3
  1. #1
    jezicat14 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    2

    Prepping data for a cross tab

    Hello, I'm brand new. I'm looking to create a query to prep some data for a cross tab.
    I have a file of students who took two certificate programs and it is structured like this.

    StudentID Fname Lname Certificate CertDate
    101 John Smith Marketing 2/1/2017
    101 John Smith Accounting 5/4/2016
    102 Jane Price Design 3/1/2015
    102 Jane Price Project Management 3/1/2015



    I would like to tack on a number column with a value for each certificate so I have values that I can convert to column headers in a crosstab. Date would be nice to use for the order (determining which gets tagged CertificateOne or CertificateTwo), but it isn't necessary, just as long as I can get the two certificates for each student on one row. It may also be a problem when the CertDates are the same.

    StudentID Fname Lname Certificate CertDate CertificateNumber
    101 John Smith Marketing 2/1/2016 CertificateOne
    101 John Smith Accounting 5/4/2016 CertificateTwo
    102 Jane Price Design 3/1/2015 CertificateOne
    102 Jane Price Project Management 3/1/2015 CertificateTwo

    Thanks in advance for any direction.
    jezi

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You want to do a 'ranking' within each student's set of records. This can be difficult and queries perform slowly. Review http://allenbrowne.com/ranking.html

    Try a DCount().

    SELECT *, DCount("*", "tablename","StudentID=" & [StudentID] & " AND CertDate<#" & [CertDate] & "#") + 1 AS CertNum FROM tablename;

    Now use that query as source for the CROSSTAB. Review http://allenbrowne.com/ser-67.html
    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
    jezicat14 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    2
    Thanks. Dcount() is not one I am familiar with. I will check it out.

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

Similar Threads

  1. Cross Referencing two sets of data?
    By Dinger045 in forum Access
    Replies: 1
    Last Post: 10-14-2016, 10:26 AM
  2. Help! Transposing Data via Cross Tab Query?
    By William McKinley in forum Queries
    Replies: 3
    Last Post: 03-03-2015, 10:45 AM
  3. Replies: 1
    Last Post: 06-18-2011, 10:00 AM
  4. Replies: 0
    Last Post: 07-20-2010, 12:44 PM
  5. Cross check data from 2 sources
    By Zukster in forum Queries
    Replies: 7
    Last Post: 09-22-2009, 10:54 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