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.
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.
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
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.
ok so I created the first query
and I get the following error: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;
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.
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.
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)
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.
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.
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
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.
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.
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
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.