Set DataRange on a pivot table using a field "DateandTime"

[Metplant]

I have almost no experience in ACCESS so am trying to get the system functioning with minimal operator input.

Currently I have managed to modify a database generated by a SCADA system using a query which generates a PIVOT table, grouped by DateAndTime, for all the desired data for Reporting purposes.

I have not been able to set the DateAndTime range of that data; so then a set group of data can be sent as a file to an archive location either as raw data or a report.

Is there a method for setting the DateAndTime range on a PIVOT table.

Field DateAndTime format from database is (dd.mm.yy hh:mm:ss AM/PM)
I was hoping to have an operator input a date (with NO time stamp) and to then fix a time range between 7:00 am and 7:00 pm, then from 7:00 pm to 7:00 am the following day. I think I should be able to do that once I can sort out the DateAndTime "filter"

Current CODE:

TRANSFORM sum(FloatTable.Val) AS Sumval
SELECT FloatTable.DateAndTime
FROM FloatTable INNER JOIN TagTable ON FloatTable.TagIndex=TagTable.TagIndex
GROUP BY FloatTable.DateAndTime
PIVOT TagTable.TagName In ("Report\Blend_RunMinHG",Report\Blend_RunMinLG,Report\Blend_RunMinMAG,Report\BW03_RunMin,Report\BW03_Total,Report\BW03_Total_HG,Report\BW03_Total_LG,);

Responses welcome.

Andrew

[MMcCarthy]

Hi Andrew, try the following:

First create a union query as follows ...

SELECT Val, DateAndTime, "7.00 am to 7.00 pm" As TimeRange
FROM FloatTable
WHERE TimeValue([DateAndTime]) Between #07:00# AND #19:00#
UNION
SELECT Val, DateAndTime, "7.00 pm to 7.00 am" As TimeRange
 FROM FloatTable
 WHERE TimeValue([DateAndTime]) Between #19:00# AND #07:00#
 

For this example I've named the above query 'qry1'

Your crosstab query should now look like ...

TRANSFORM sum(qry1.Val) AS Sumval
SELECT qry1.DateAndTime, qry1.TimeRange
 FROM qry1 INNER JOIN TagTable 
  ON qry1.TagIndex=TagTable.TagIndex
GROUP BY qry1.DateAndTime, qry1.TimeRange
PIVOT TagTable.TagName In ("Report\Blend_RunMinHG",Report\Blend_RunMinLG,Report\Blend_RunMinMAG,Report\BW03_RunMin,Report\BW03_Total,Report\BW03_Total_HG,Report\BW03_Total_LG,);
 

[NeoPa]

I can't claim to understand the whole problem or solution very well, but the first query can be redone as :

SELECT Val,DateAndTime,
       IIf(TimeValue([DateAndTime]) Between #07:00# AND #19:00#, _
           '7.00 am to 7.00 pm', _
           '7.00 pm to 7.00 am') As TimeRange
FROM FloatTable

Thanks for the TimeValue() function btw - I always used Int() :o

[NeoPa]

Having reviewed the whole query it looks like it can be done in one go, using Mary's basic code to work from.
Your CrossTab query should then look like :

TRANSFORM Sum(subQ.Val) AS SumVal
SELECT subQ.DateAndTime,subQ.TimeRange
FROM TagTable INNER JOIN
     (SELECT TagIndex,Val,DateAndTime,
          IIf(TimeValue([DateAndTime])
              Between #07:00# AND #19:00#, _
              '7.00 am to 7.00 pm', _
              '7.00 pm to 7.00 am') As TimeRange
      FROM FloatTable) AS subQ
  ON TagTable.TagIndex=subQ.TagIndex
GROUP BY subQ.DateAndTime, subQ.TimeRange
PIVOT TagTable.TagName In('Report\Blend_RunMinHG',
                          'Report\Blend_RunMinLG',
                          'Report\Blend_RunMinMAG',
                          'Report\BW03_RunMin',
                          'Report\BW03_Total',
                          'Report\BW03_Total_HG',
                          'Report\BW03_Total_LG');