Results 1 to 3 of 3
  1. #1
    VAer is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2016
    Location
    USA
    Posts
    163

    How to transpose access table like this?

    Is it possible to build table relationship to display table like attached screenshot?

    Thanks.

    Click image for larger version. 

Name:	Access Table Transpose.jpg 
Views:	25 
Size:	180.1 KB 
ID:	43041

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your score table should not need first name/last name since you already have this data in the user table

    you don't need the datatable unless you want to list specific days even when there is no score, the format can be calculated from the date in your score table.

    In access your format calculation would be

    format(testdate,"dd-mmm-yy") & "/" & weekdayname(weekday(testdate()),True) & iif(holiday is not null,"(H)")

    based on this query

    select *,format(testdate,"dd-mmm-yy") & "/" & weekdayname(weekday(testdate()),True) & iif(holiday is not null,"(H)") dateHdr
    FROM (usertable left join scoretable on usertable.ID=scoretable.ID) left join holidaytable ON scoretable.testdate=holidaytable.holiday

    You would use a crosstab query to display the data horizontally - ID, firstname, last name would be row headers, dateHdr would be column header and use first(score) as your value

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    some other observations
    - all of your tables should have a pk field
    - Date is a reserved word and should not be used for objects. So is Weekday IIRC.
    - ID is an ambiguous name for a field, especially when you're going to end up referencing more than one at the same time at some point
    - you don't need holiday field in data if you can join these tables on the date field

    Personally I would advise to abandon this idea. I believe there is a limit of how many fields you can transpose, and it seems quite likely that with enough dates you will hit that limit. Besides, if what I wanted is a spreadsheet, then I'd pull/pull the data into Excel and use that instead. I would not try to make a spreadsheet out of a database - much like trying to make a dog out of a cat I'd say. They're both animals, but that's the end of the similarity.

    EDIT - forgot to mention that you don't need to recreate weekday values when you have a date. The WeekdayName function would give you that out of your date rather than repeating pieces of your values.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Transpose table
    By HananJanaby in forum Access
    Replies: 4
    Last Post: 04-28-2019, 12:03 PM
  2. How to Partially Transpose A Table
    By jrfost in forum Queries
    Replies: 9
    Last Post: 01-15-2019, 06:11 PM
  3. Transpose table
    By jabarlee in forum Queries
    Replies: 5
    Last Post: 07-27-2018, 07:52 AM
  4. Transpose a table using query in Access
    By Skhaliq in forum Access
    Replies: 1
    Last Post: 03-22-2018, 01:02 PM
  5. Transpose Data of single table
    By Atif Mahmood in forum Access
    Replies: 4
    Last Post: 05-05-2016, 04:50 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