Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283

    Not a crosstab but formatting a select query

    Hello everyone,

    I do not believe this is a crosstab as the values are not calculated or anything.


    I have data that comes in like this
    Click image for larger version. 

Name:	1.JPG 
Views:	18 
Size:	36.1 KB 
ID:	30357

    and I would like to format it follow in a query
    Click image for larger version. 

Name:	2.JPG 
Views:	18 
Size:	31.9 KB 
ID:	30358



    Is this even possible?

    Thanks for the help as always

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    not in Access. An excel pivot table may do it.

    Too complex for Crosstab, THO, you could use a 'report' table to run a series of queries that append each day to the table.
    then the report would sum all field to show 1 person / 1 line.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I did a test of Access pivot view on table and that works.

    Also consider:

    Query1: Table1UNION

    SELECT Manager, Rep, Format([Date],"yyyymmdd") & "Login" AS DateCat, Logon AS LogTime FROM Table1
    UNION SELECT Manager, Rep, Format([Date],"yyyymmdd") & "Logout", Logoff FROM Table1;

    Query2

    TRANSFORM First(Table1UNION.LogTime) AS FirstOfLogTime
    SELECT Table1UNION.Manager, Table1UNION.Rep
    FROM Table1UNION
    GROUP BY Table1UNION.Manager, Table1UNION.Rep
    PIVOT Table1UNION.DateCat;


    Date is a reserved word, should not use reserved words as names for anything.
    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.

  4. #4
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    June7 strikes again. You are the master.

    One other question. I see it uses first for the value. Is there a way to set it so it would capture multiple. IN the data above of you look at Dave Rolfe he logged in at 8:09 and out at 10:46. The first captures that one. But he then logged in again on the same day at 10:58 to 4:27 and that is not captured.

    Thanks again for your help on this

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Sorry, did not catch that.

    Requires a sequence identifier for each group.

    SELECT Manager, Rep, Format([Date],"yyyymmdd") & "Login" AS DateCat, Logon AS LogTime, DCount("*","Table1","Manager='" & [Manager] & "' AND Rep='" & [Rep] & "' AND [Date]=#" & [Date] & "# AND Logon<#" & [Logon] & "#")+1 AS Seq FROM Table1
    UNION SELECT Manager, Rep, Format([Date],"yyyymmdd") & "Logout", Logoff, DCount("*","Table1","Manager='" & [Manager] & "' AND Rep='" & [Rep] & "' AND [Date]=#" & [Date] & "# AND Logoff<#" & [Logoff] & "#")+1 FROM Table1;

    TRANSFORM First(Table1UNION.LogTime) AS FirstOfLogTime
    SELECT Table1UNION.Manager, Table1UNION.Rep, Table1UNION.Seq
    FROM Table1UNION
    GROUP BY Table1UNION.Manager, Table1UNION.Rep, Table1UNION.Seq
    PIVOT Table1UNION.DateCat;

    So if you want to use Pivot View, use query not table:
    SELECT Table1.Manager, Table1.Rep, Table1.Logon, Table1.Logoff, Table1.Date,
    DCount("*","Table1","Manager='" & [Manager] & "' AND Rep='" & [Rep] & "' AND [Date]=#" & [Date] & "# AND Logon<#" & [Logon] & "#")+1 AS Seq
    FROM Table1;
    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
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    ok so I created the first query
    Code:
    SELECT Manager, Rep, Format(SI_DATE,"yyyymmdd") & "Login" AS DATECat, Logon AS LogTime, DCount("*","UNION_TABLE","Manager='" & [Manager] & "' AND Rep='" & [Rep] & "' AND SI_DATE=#" & [SI_DATE] & "# AND Logon<#" & [Logon] & "#")+1 AS Seq 
    FROM UNION_TABLE 
    UNION SELECT Manager, Rep, Format(SI_DATE,"yyyymmdd") & "Logout", Logoff, DCount("*","UNION_TABLE","Manager='" & [Manager] & "' AND Rep='" & [Rep] & "' AND SI_DATE=#" & [SI_DATE] & "# AND Logon<#" & [Logon] & "#")+1 
    FROM UNION_TABLE;
    and I get the following error:
    Syntax Error (missing operator) in query expression 'Manager='Murray' AND Rep='Shawna O'Brien' AND SI_DATE=#9/18/2017# AND Logon<#7:44:00AM#'

    And when I click ok another window pops up and says Unknown.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Well, can't UNION the UNION query you are trying to create.

    Change UNION_TABLE to the data table or query name for the first example dataset shown in your first 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.

  8. #8
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    db.zipok so I have all three queries above running and returning data but they each seem to have 1 thing I am looking for but not all the parts. I must be doing something....union of a not created union ....come on

    I have attached a sample db if you could take a look.

    To me the result of the crosstab Step4_qry2 looks good except the values don't seem to be in the right columns.
    It shows
    Rep Seq Logon Logoff
    REP1_FN REP1_LN 1 10:58am 10:46am
    REP1_FN REP1_LN 2 8:09am 4:27pm

    But it should be
    Rep Seq Logon Logoff
    REP1_FN REP1_LN 1 8:09am 10:46am
    REP1_FN REP1_LN 2 10:58am 4:27pm

    Step5_qry3 shows the right results but I can't get them to crosstab

    Sorry for being a pain (again)

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Your date and time data are in text type fields so alpha sort rules apply. If you use text type then need placeholder zeros, like: 08:09am, 04:27pm.

    Really should be date/time type.

    The posted examples show date and time values aligned right so they look like date/time type.
    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
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    db2.zipdb2.zipok I got the times to be 00:00:00 but the data is still not lining up. Attached newer db.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The queries worked for me in my test but I used date/time type fields. Your data is not date/time type, it is text. The time is not going to work as text using AM/PM because the data is subject to alpha sort rules. Will have to format to 24-hour clock time. Remove the AM/PM from the Format().

    Or use CDate() on the date time fields for the MAKE TABLE. Will have to give the Date field a different name. I used LogDate: CDate([Date]).

    Then change the Date name to LogDate in the UNION and use # delimiters in the DCount.

    Why don't you use date/time type fields to begin with? And don't use Date as name.
    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.

  12. #12
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    I used CDate in the format query and got the field to be created as Date/Time but it still did not bring in the first 0 for 8:30AM. So I tried something else. The data itself comes in as text from a flat file. That being said I went back and put the CDate in and now it is working as expected on the test data. I will make the changes to the real data and hope to replicate the results

    Click image for larger version. 

Name:	Capture.JPG 
Views:	10 
Size:	20.6 KB 
ID:	30408

    Thanks again for all the help. I know I must frustrate you. Is there any online training you would suggest where I could get grounded in this more? It would be nice to have more foundation and knowledge to be better able to build things from the ground up, as it seems the demands are always more than basic level stuff.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    When converted to date/time, placeholder zeros not needed.

    Look at http://www.rogersaccesslibrary.com/

    Any decent introductory tutorial book should be beneficial.

    I never went through any tutorials - I had a 1-on-1 tutor to get me started. Since then learning has just been researching specific issues. I remember the days I discovered Nz() and UNION ...
    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.

  14. #14
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    could you think of any reason why the steps throw the same error above (Syntax Error (missing operator) in query expression 'Manager='Murray' AND Rep='Shawna O'Brien' AND SI_DATE=#9/18/2017# AND Logon<#7:44:00AM#') when I used a linked table in the make table.

    I have replicated it and if I covert it to a local table it works fine.

    a 1-on-1 tutor would be awesome

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    It might be the apostrophe in O'Brien. Apostrophe is special character in SQL. Force special character to be recognized as normal text by 'escaping', done by doubling the character.

    Replace([somefield], "'", "''")

    This should not be an issue in Access query object and I don't see how could make difference if the table is local or linked. I put O'Brien in a record and cannot replicate the issue with the db you provided.

    If constructing SQL statement in VBA, might need to escape the character.

    Post your complete SQL.
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-14-2014, 01:17 PM
  2. Conditional Formatting Report from Crosstab Query
    By zmster2033 in forum Reports
    Replies: 1
    Last Post: 01-27-2014, 02:54 PM
  3. Replies: 3
    Last Post: 07-23-2013, 02:53 PM
  4. Replies: 1
    Last Post: 07-30-2010, 10:28 AM
  5. Conditional formatting in crosstab report
    By squirrelmaster in forum Reports
    Replies: 0
    Last Post: 06-24-2010, 06:31 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