Results 1 to 10 of 10
  1. #1
    kaylachris is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2010
    Posts
    21

    Help! Import Excel w/ rows that have same name in column A

    I am attempting to import a spreadsheet to Access that contains redundant data. Here is a sample of what data is downloaded:



    Click image for larger version. 

Name:	ImportedData.png 
Views:	25 
Size:	19.3 KB 
ID:	37651

    I can import the data just fine to a temp table I've created with matching fields. However, I can't seem to figure out the SQL string to give me the results that I want. I had it somewhat working and was able to reduce some of the redunancy but in an attempt to make more subqueries to fix the error I completely destroyed it. Now I can't even seem to figure out how I got that far.

    Code:
    Private Sub cmdImportGrades_Click()  On Error GoTo errHandler
      
      Dim fPath As String: fPath = getFilePath
      Dim strSQL As String
      
      ' Import Data from excel to temp table
      DoCmd.TransferSpreadsheet acImport, , "tbl_temp", fPath, 1, "Grades!"
      
      ' Append to Gradebook
      strSQL = ""
      
      DoCmd.SetWarnings False
      DoCmd.RunSQL
      DoCmd.SetWarnings True
    
    
    errHandler_Exit:
      Exit Sub
    
    
    errHandler:
      Select Case err.Number
        Case 3125
          MsgBox "The file isn't formated correctly."
        Case 2391
          MsgBox "Columns are improperly named"
        Case Else
          MsgBox "something else: " & err.Number
        End Select
        
    End Sub
    Desired Results
    Click image for larger version. 

Name:	FinishedTable.png 
Views:	25 
Size:	24.8 KB 
ID:	37655

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The RunSQL has no parameters. There is no statement to run.

    Your 'desired results' is not a normalized data structure. Why do you want to de-normalize data?

    We do not have enough info to get from A to B. The source data shows names, not student IDs. Where do those column headers come from?
    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
    kaylachris is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2010
    Posts
    21
    I deleted the SQL string before posting because it no longer worked. I started out with just a simple SELECT all FROM tbl query. I had managed to make a subquery in the WHERE class that pulled the Test Names to the top accuratly. When I messed around trying to pull the dates as well I messed up the SQL so much I could no longer even recognize it as an SQL statement. I can't remember even how I got to that point.

    The studentID are pulled from tbl_studentData with a Dlookup that compares last, first and middle names. The downloaded data doesn't include their IDs just names. That part I can easily handle thus I didn't include it. The grades will be stored in a seperate table with a relationship between the two using studentID.

    I've tried to simplify my problem because I wish to understand the process and not just be spoon fed a solution. I've attached a Db I created that similar to the one I'm actually building. My test data for my actual Db contains PII.

    How are my desired results not normalized? One record that contains all the test scores and dates for one student instead of a seperate record for each test the student takes.

    WorkDB.zip

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    #quote How are my desired results not normalized? One record that contains all the test scores and dates for one student instead of a seperate record for each test the student takes.
    #quote
    That is the point, what if you get one or more tests, you will have to add fields to the table and queries and forms....What you want to see is easily achieved by using a cross tab query.
    Cheers,
    Vlad

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    As Vlad says, if you need to add more tests, will have to modify table, query, forms, reports, code to accommodate the new fields. Normalized data should not require design modification for a new item.

    Often, spreadsheets are not normalized, however, in this case it is. Keep the normalized structure and build a CROSSTAB query to output non-normalized. The query can include a JOIN to related tables to pull together info. http://allenbrowne.com/ser-67.html

    Now that is the process. Attempt query and when you need help with it, post the attempt.
    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.

  6. #6
    kaylachris is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2010
    Posts
    21
    Originally I was storing all the test in one record because of the way the course is designed. The course is broken down into six sections with a test for each, plus a final. The only way this would change if the course were completely redesigned. Each class is composed of 20 students or less. My thought process was that it would be more efficent to cycle through 20 records to display data on reports rather than cycling through 140 test records. However, I see the potential of a massive headache of redesigning everything in the event the course does change. Saving minimal processing power isn't worth that headache. Thus, I redesigned my tbl_gradebook to 5 columns: TestID (concatenation of StudentID & TestNum to create unique key), TestNum, StudentID,TestScore, DateTaken.

    The link that you provided was very helpful. I've just about gotten the results that I want. The only issue I'm having now is that my TestScores are displaying as dates instead of numbers. Here's my SQL:

    Code:
    TRANSFORM Max(IIF([FieldName]="Test",CInt(tbl_Gradebook.[TestScore]),tbl_Gradebook.[DateTaken])) AS MaxOfTestScore
    SELECT tbl_Gradebook.[StudentID]
    FROM tblXtabColumns, tbl_Gradebook INNER JOIN tbl_StudentData ON tbl_Gradebook.[StudentID] = tbl_StudentData.[StudentID]
    GROUP BY tbl_Gradebook.[StudentID]
    PIVOT [FieldName] & tbl_Gradebook.[TestNum];
    And this is what it displays:
    Click image for larger version. 

Name:	Almost.png 
Views:	15 
Size:	6.3 KB 
ID:	37675

    TestA01, etc.. should be displaying the actual scores of each test. I tried forcing conversion with CInt but that doesn't work. I've also attemted CONVERT and CAST both of which give me syntax errors. Any ideas how I can get these numbers to properly display?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Reference TestScore field in the Max() expression. Don't even include DateTaken in the query.

    If you want the output shown in your original post, review Allen's example for CROSSTAB with Multiple Sets of Values.
    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.

  8. #8
    kaylachris is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2010
    Posts
    21
    I followed the CROSSTAB with multiple values which is how I arrived with what I have now. The problem is that in the example both outputs are of integer type. I need to output one int and one date field. I believe I'll just go back to my original plan and have separate pivot queries to pull dates and scores then combine them in a select. Was just hopeing there was a more efficient means of doing this.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If you have just dates in each column then you could not have followed Allen's instructions. However, two CROSSTABs and then join them is probably just as efficient.
    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.

  10. #10
    kaylachris is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2010
    Posts
    21
    It wasn't dates in both columns, it was just how it was being displayed. If I changed my Transform statement I could either get both fields to display as dates or both display as numbers.
    If I made the Transform statement to MAX all my data woud be displayed as dates I.E. 1/24/1900 = 24; dates displayed correctly
    If I made the transform statement to SUM it would display the numbers correctly.. but then display the dates in their equvielent serial number format I.E. 11/15/2018 = 43419
    I couldn't figure out how to display the dates as dates and the numbers as numbers at the same time.
    The two CROSSTAB and combining them works.. just seems rather sloppy to me. It will be something I'll revist on different push of the Db. For now I just need to get it up and running.

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

Similar Threads

  1. Replies: 1
    Last Post: 02-26-2018, 10:57 AM
  2. Import Data from Excel returns blank rows
    By mari_hitz in forum Import/Export Data
    Replies: 1
    Last Post: 04-12-2017, 09:35 AM
  3. How to remove blank rows when I import from excel
    By timpepu in forum Import/Export Data
    Replies: 3
    Last Post: 09-14-2015, 04:04 PM
  4. Replies: 12
    Last Post: 10-08-2014, 02:35 PM
  5. Replies: 5
    Last Post: 12-01-2011, 05:38 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