0

I was wondering if the solution to the ticket at the following link was ever found Pivot on multiple fields and export from Access.

I am trying to get results from an access table in the following format where: V1 (value 1) is a number and V2 (value 2) is timestamp (hh:mm:ss).

Basically the report pulls for current month so eventually there would be a day at the top for every day of the month and a corresponding Number and time value for each day.

               April 1          April 2          April 3
Manager      V1   V2         V1      V2         V1      V2
John Doe     4   5:43:12      1    0:56:32      2    3:15:12

It is an elapsed time. I have been looking at Allen Browne's post and I am most of the way there I think. I created two tables: 1) for V1 which is a number of dials; and 2) for V2 which is total talk time. I then used Allen Browne's method but I cannot get the format of the time to be in hh:mm:ss. It is that way in the base table but whatever I try it always seems to just show 1 digit. 

TRANSFORM Sum(IIf([FldName]="DIALS",Val([DIALS].[DIALS]),
              Val(Format([T‌​ALKTIME].[TT],"hh:nn‌​:ss")))) AS TheValue 
SELECT TALKTIME.Manager, DIALS.TW_Program_Code, 
       [DIALS]![VSE_FirstName] & " " & [DIALS]![VSE_Surname_Name] AS REP 
FROM tblXtabColumns, TALKTIME 
INNER JOIN DIALS 
ON (TALKTIME.TW_Program_Code = DIALS.TW_Program_Code) 
AND (TALKTIME.VSE_FirstName = DIALS.VSE_FirstName) 
AND (TALKTIME.VSE_Surname_Name = DIALS.VSE_Surname_Name) 
AND (TALKTIME.Period = DIALS.Period) AND (TALKTIME.[Manager] = DIALS.[Manager])
WHERE (((Month([TALKTIME].[Period])) = Month(Now())-1)) 
GROUP BY TALKTIME.Manager, DIALS.TW_Program_Code, 
         [DIALS]![VSE_FirstName] & " " & [DIALS]![VSE_Surname_Name] 
PIVOT [DIALS].[Period] & " " & [FldName];
Community
  • 1
  • 1
Mindbender
  • 41
  • 1
  • 6
  • Why are you using Val() function on the elapsed time? Purpose of Val() function is to return a number (integer or short, no punctuation and no alpha). – June7 May 08 '17 at 18:10
  • I have just been googling and trying things. I am lost to be honest. And if I don't put anything I get a data Type Mismatch error – Mindbender May 08 '17 at 18:15
  • Can't SUM a text value. If the elapsed time is saved as seconds then you should sum the seconds without formatting to h:n:s. Do formatting after all the arithmetic calcs are done. Don't Format within that Iif(). – June7 May 08 '17 at 18:28
  • Really, best place to do formatting is in textbox on form or report. However, building a stable report based on CROSSTAB is not easy. – June7 May 08 '17 at 18:36
  • is it possible to format the elapsed time columns of each output excel file once they have been created using vba from access? Something like - in folder Temp - format any column in any workbook that contains TT in the column header probably reaching at this point :) – Mindbender May 09 '17 at 12:57
  • You want to export to Excel? Possibly, but not finding intrinsic function in Excel so would likely be a more complex custom function. Topic for another question. Or don't export the CROSSTAB directly. Build another query using the CROSSTAB and apply formatting in that 'final' manipulation for export. – June7 May 09 '17 at 18:32

1 Answers1

0

Is that time of day or elapsed time?

Review http://allenbrowne.com/ser-67.html#MultipleValues. Cannot have multi-line headers. The date would have to be concatenated with the field that provides the V1 and V2 header. Is there even a field that can be used to provide the "V1" and "V2" headers? If not, calculate it. One way:

SELECT *, DCount("*","Table1","Manager='" & [Manager] & "' AND ID <" & [ID])+1 AS Seq FROM Table1 WHERE Month([datefield]) = 4 AND Year([datefield])=2017;

Now use that query in CROSSTAB. Either build 2 CROSSTAB queries and join them or use Allen Browne approach. Example CROSSTAB to pivot only the "V1" data.

TRANSFORM First(Query1.Reading) AS DailyReading SELECT Query1.Manager FROM Query1 GROUP BY Query1.Manager PIVOT Format([ReadDate],"mmm d") & " : " & [Seq] & "Reading";

June7
  • 19,874
  • 8
  • 24
  • 34
  • It is an elapsed time. I have been looking at Allen Browne's post and I am most of the way there I think. I created 2 tables ( 1 for V1 which is a number of dials) and a second for V2 (which is total talk time) I then used Allen Browne's method but I cannot get the format of the time to be in hh:mm:ss. It is that way in the base table but whatever I try it always seems to just show 1 digit. – Mindbender May 08 '17 at 17:51
  • Is the elapsed time in a date/time field? Possibly use Format() to stabilize. Format([fieldname],"hh:mm:ss"). Should probably edit your original post to show the SQL there then delete the two comments. – June7 May 08 '17 at 17:56
  • It gets created as a short text field this is what formats the field from seconds to hh:mm:ss and it ends up in a table as short text TT: Format(Sum([CUST_TT])*Val("0.000694444444")/60,"hh:nn:ss") – Mindbender May 08 '17 at 18:02
  • If I remove the TT: Format(Sum([CUST_TT])*Val("0.000694444444")/60,"hh:nn:ss") from the initial make table query and leave it as seconds (what the data comes in as) the query returns what I want it to except it shows the elapsed time as seconds and not in the hh:mm:ss format. Can the step to return the data in the Transform step also format it? `TRANSFORM Sum(IIf([FldName]="DIALS",CDbl([DIALS].[DIALS]),CDbl(([TALKTIME].[CUST_TT])))) AS TheValue` – Mindbender May 08 '17 at 18:43
  • I think not with Allen's all-in-one query approach. – June7 May 08 '17 at 18:46
  • thank you for your time and patience. I appreciate it – Mindbender May 08 '17 at 19:24