Results 1 to 15 of 15
  1. #1
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30

    Crosstab or Join Query to cast data

    I inherited a database that had tables structured so they were like spreadsheets instead of being normalized. I've fixed that with a new table design, but would like to design a query so that it looks like it did previously. What we are doing is going to several Areas and recording the Time and a Count of people. Before, the database had Area1, Time1, Count1, Area2, Time2, Count2, etc. Here, is a second table for the Area, Time, Count.

    Here are the table relationships and a crosstab query that I tried. The Crosstab cannot incorporate times and I'm not sure how to proceed. Other types of joins didn't work for me.
    Click image for larger version. 

Name:	CrossTabDes.png 
Views:	32 
Size:	142.4 KB 
ID:	33752 which looks like Click image for larger version. 

Name:	crosstab_tbl.png 
Views:	30 
Size:	66.5 KB 
ID:	33753



    I would like it to look like this:
    Click image for larger version. 

Name:	cast.png 
Views:	32 
Size:	51.8 KB 
ID:	33754
    I could probably do this in R, but my users don't use R so I want them to be able to view it in Access.

    Thanks for any help you can provide. Hopefully I can keep up with the code!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you can use multiple crosstabs (one for each column) and then join them on date and port. Not sure what you mean by 'The Crosstab cannot incorporate times'

    or you could use a pivot query if appropriate for your users

  3. #3
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    Agree with Ajax, what do you mean it doesn't incorporate Time?
    Is your data a pivot where there is only 1 record? i.e. Do you need to average or sum the time?
    You can only have one Column Heading and one Value at a time in a cross-tab so you can't have both. If you want the outcome to "LOOK" like what it used to, you can concatenate the fields:

    For the Value field: Expr1: ([Area_Effort] & " - " & [Effort_Time])
    Then make it the Min or Max.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    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.

  5. #5
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30
    I checked out the link June7 posted and read more about joins, but I think I'm still confused about the basics of joins.

    I created two crosstab queries:
    1) qry_AreaEffort
    Row Heading: Survey_Date
    Column Heading: Area_Effort
    Value: Effort_Count (Sum; though there is only 1 value)
    Where: PortID_FK = 24 (though I would like to choose this at the join, if possible)
    Click image for larger version. 

Name:	qxt_Effort.png 
Views:	26 
Size:	91.8 KB 
ID:	33827

    2) qry_AreaTime
    Row Heading: Survey_Date
    Column Heading: Area_Effort
    Value: Effort_Time (Max, though only 1 time)
    Where: PortID_FK = 24
    Click image for larger version. 

Name:	qxt_Time.png 
Views:	26 
Size:	106.7 KB 
ID:	33828

    I created a new query with these two crosstab queries and linked them via an Inner Join (I think): "Only include rows where the joined fields from both tables are equal." I suppose if one did not record either a time or a count then the records would not be selected, correct?

    Then, I went into SQL view and typed this in:

    SELECT [qry_AreaEffort].[Survey_Date], [qry_AreaEffort].[Effort_Count] AS [Count], [qry_AreaTime].[Effort_Time] AS [Time]
    FROM (([qry_AreaEffort]
    INNER JOIN [qry_AreaTime]
    ON [qry_AreaEffort].[Survey_Date]=[qry_EffortTime].[Survey_Date])
    ORDER BY [qry_AreaEffort].[Survey_Date];

    I get an error with the syntax for inner join. I'm still not sure when to use [] or just Name.Field. Where am I not executing this correctly? Also, not sure if I can select WHERE port value here or if that needs to be chosen already in the two crosstab queries.

    Also, I can create a select query with Survey_Date and all the fields from the two crosstab queries, but the names are difficult to read (e.g. qry_AreaEffort.BB instead of BB).

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    There is an unpaired (. Remove it. Why did you manually build the SQL instead of using the Designer?

    The INNER JOIN should work just as well since both CROSSTAB should have the same Survey_Date values.

    Use [ ] when names have spaces, punctuation/special characters (underscore only exception) or are reserved words. Best to avoid those.
    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.

  7. #7
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30
    Thanks for your feedback.

    Quote Originally Posted by June7 View Post
    Why did you manually build the SQL instead of using the Designer?
    Do you mean the Design tab in Design View as the "Designer"?
    I don't know how to deal create this join using Create > Query Design (ie what it looks like in "Field" as a expression).

    I removed the extra "(" and tried it without the [] but still get the error "..does not recognize 'qry_AreaEffort.Effort_Count' as a valid field name or expression."

    Here is the code that I'm using in SQL view.

    SELECT qry_AreaTime.Survey_Date, qry_AreaEffort.Effort_Count AS [COUNT], qry_AreaTime.Effort_Time AS [Time]
    FROM qry_AreaTime INNER JOIN qry_AreaEffort ON qry_AreaTime.Survey_Date = qry_AreaEffort.Survey_Date;

    This is what the queries with the join look like:

    Click image for larger version. 

Name:	join.png 
Views:	23 
Size:	60.9 KB 
ID:	33833

    Sorry. I don't know what it isn't showing images as thumbnails and keeping deleted ones.
    Attached Thumbnails Attached Thumbnails join.png  

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    your qry_areaTime does not have a field called Effort_Time, hence the error
    Your next error will be about the same field name in the other query

    Under Survey_Date you should have Group By, not Expression
    COUNT is a reserved word, it just needs the name of your field (which also does not exist) - and instead of Expression, use Count

    No idea what you are trying to do with the third column

    So something like

    SELECT qry_AreaTime.Survey_Date, Count(qry_AreaEffort.Effort_Count), Sum(qry_AreaTime.Effort_Time)
    FROM qry_AreaTime INNER JOIN qry_AreaEffort ON qry_AreaTime.Survey_Date = qry_AreaEffort.Survey_Date
    GROUP BY qry_AreaTime.Survey_Date;

    But as stated above, those fields don't exist in your queries - you have BB, CP etc

    EDIT: just realised you are perhaps not trying to group the data but see it side by side. If so, remove the group by's (click on the sigma -totals button - on the ribbon) and drag down the fields you want to see - comments about field names still apply
    Last edited by CJ_London; 05-01-2018 at 04:19 PM.

  9. #9
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30
    I've not made myself clear. If you look at my original post, there is an example of what I want the data to look like. The date is grouped by Date (rows) with the counts and times as columns for each Area (e.g. Area1 (value Count1), Area2 (Time1), Area2 (Count2), Area2 (Time2)

    I thought I had to create two crosstab queries that cast the data for count and times for each area, respectively. Now I don't know how to join them, because as you pointed out, the resulting fields from the two crosstab queries are the areas.

    I revisited the link that June7 posted as another approach that I couldn't get to work, but maybe this is the missing piece. I created tbl_XColumns that has two values "People" and "sTime". http://allenbrowne.com/ser-67.html#MultipleValues

    This is the code I tried to modify based on the code in the example from the link. I get a syntax error with the IIf statement.


    TRANSFORM IIf(tbl_XColumns.FieldName="People", Sum(tbl_Effort.Effort_Count), Max(tbl_Effort.Effort_Time])) AS TheValue
    SELECT tbl_Main.Survey_Date
    FROM tbl_XColumns, tbl_Main INNER JOIN tbl_Effort
    ON tbl_Main.MainID_PK = tbl_Effort.MainID_FK
    WHERE (tbl_Main.Port = "24")
    GROUP BY tbl_Main.Survey_Date
    PIVOT tbl_XColumns.FieldName & tbl_Effort.Area_Effort);

    See above posts for tables, their fields, and relationships.
    Thanks for sticking with me.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Provide sample data. In the Advanced Post Editor can copy/paste from Access table into post.
    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.

  11. #11
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30

    Practice data

    Here is test db with practice data.
    clam_db_test.zip

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I have no problem building query to join your crosstabs:

    SELECT qry_AreaEffort.Survey_Date, qry_AreaTime.BB, qry_AreaTime.BWN, qry_AreaTime.CP, qry_AreaTime.IF, qry_AreaTime.MB, qry_AreaTime.RF, qry_AreaEffort.BB, qry_AreaEffort.BWN, qry_AreaEffort.CP, qry_AreaEffort.IF, qry_AreaEffort.MB, qry_AreaEffort.RF
    FROM qry_AreaTime INNER JOIN qry_AreaEffort ON qry_AreaTime.Survey_Date = qry_AreaEffort.Survey_Date;
    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.

  13. #13
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30
    Hi June7. Thanks for your efforts. I mentioned earlier that when I tried this, the field names are difficult to navigate (i.e. qry_AreaX in front of codes). I recreated the queries to be called "Area" and "sTime" and that made it more readable. The problem is that I need to create this for each Port (which is selected in the crosstab queries right now). I was hoping to get the method to work using the link that you posted, but haven't figured out how to get it to work.

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Remove the filter criteria from the crosstabs. Make PortId_FK a second RowHeading. Compound join the two crosstabs.

    SELECT Area.Survey_Date, Area.PortID_FK, Area.BB, Area.BO, Area.BWN, Area.CFT, Area.CL, Time.BB, Time.BO, Time.BWN, Time.CFT, Time.CL, Time.CP, Time.GF, Time.HC, Time.IF, Time.JP, Time.MB, Time.NC, Time.RF, Time.RV, Time.SZ, Time.WR, Area.CP, Area.GF, Area.HC, Area.IF, Area.JP, Area.MB, Area.NC, Area.RF, Area.RV, Area.SZ, Area.WR
    FROM [Time] INNER JOIN Area ON (Time.Survey_Date = Area.Survey_Date) AND (Time.PortID_FK = Area.PortID_FK);
    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.

  15. #15
    chelonidae is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2017
    Posts
    30
    Of course! Create a second row heading. I tried it and then added a WHERE statement to select the Port. It gives me only the dates when that Port was sampled, but still shows all of the area codes. I'm thinking I need to add some sort of conditional statement in the crosstabs that will select Areas based on Port. I have tbl_Port linked to luAreas for a form. Perhaps I need to start there. This was a good step forward for me though.

    SELECT Area.Survey_Date, Area.PortID_FK, Area.BB, Time.BB, Area.BO, Time.BO, Area.BWN, Time.BWN, Area.CFT,Time.CFT, Area.CL, Time.CL, Area.CP, Time.CP, Area.GF, Time.GF, Area.HC, Time.HC, Area.IF, Time.IF, Area.JP, Time.JP, Area.MB, Time.MB, Area.NC,Time.NC, Area.RF, Time.RF, Area.RV, Time.RV, Area.SZ, Time.SZ, Area.WR,Time.WR
    FROM [Time] INNER JOIN Area ON (Time.Survey_Date = Area.Survey_Date) AND (Time.PortID_FK = Area.PortID_FK)
    WHERE Area.PortID_FK = 24;

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

Similar Threads

  1. Left Join with Crosstab query
    By FL0XN0X in forum Access
    Replies: 13
    Last Post: 03-05-2018, 04:22 PM
  2. How to Make More than one Inner Join on Crosstab Query
    By RAJESHKUMAR R in forum Queries
    Replies: 6
    Last Post: 03-02-2018, 10:04 PM
  3. UPDATE query with CAST function
    By hsavignac in forum Queries
    Replies: 1
    Last Post: 12-07-2015, 12:22 PM
  4. Union query to join two crosstab queries
    By racefan91 in forum Queries
    Replies: 5
    Last Post: 09-26-2013, 10:24 AM
  5. Crosstab Query Prompting twice for data?
    By AccessFreak in forum Queries
    Replies: 5
    Last Post: 01-07-2011, 10:38 AM

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