1

I use this forum all the time for VBA help but this is the first time I have to post something myself.

I am trying to make a report that provides a summary of various alarms stored in Access. I want to provide a simple Count of each alarm, each day. I have used some SQL queries but not really any Access. I took the fact that Access can do Pivot tables from Access itself. If there is a better way, please let me know.

Set CommandQuery.activeConnection = conn
commandQuery.CommandText = _
   "TRANSFORM Count(FixAlarms.[Alm_NativeTimeLast]) AS CountOfAlm_NativeTimeLast " & _
   "SELECT FixAlarms.Alm_Tagname, FixAlarms.Alm_Desc " & _
   "FROM FixAlarms " & _
   "WHERE ((FixAlarms.Alm_Tagname) <> """")) AND FixAlarms.Alm_NativeTimeIn > CellTime " & _
   "GROUP BY FixAlarms.[Alm_Tagname], FixAlarms.Alm_Descr " & _
   "PIVOT Format([Alm_NativeTimeIn],""Short Date"")"
rec.Open commandQuery

This is the code I am using. I had to retype it, so please forgive any typo. It does most of what I want but it does not give me any indication of what day each column is. I need a header on each column in case there were no alarms one day. I think the answer lies within the IN part of the PIVOT but I can't get it to work without syntax errors. I thought all I had to do was add on 

PIVOT Format([Alm_NativeTimeIn],""Short Date"") IN 01/20/15"

Please help if you can.

Thanks.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Furbs
  • 49
  • 5

3 Answers3

0

In order to get the records for all day, even those where there were no activity you need to create these days. The simplest way to do so in access is to use a set of UNION statements to create a fake table for the days similar to this:

SELECT #2015-01-20# as dt FROM dual
UNION ALL
SELECT #2015-01-21# as dt FROM dual
UNION ALL
SELECT #2015-01-22# as dt FROM dual

If you try the above query in Access it will not work, as there is no table called dual. You will have to create it. Check this SO question.

After you created the above query you can LEFT JOIN it with the source table. 

TRANSFORM Count(FixAlarms.[Alm_NativeTimeLast]) AS CountOfAlm_NativeTimeLast 
SELECT FixAlarms.Alm_Tagname, FixAlarms.Alm_Desc 
FROM 
(SELECT #2015-01-20# as dt FROM dual
UNION ALL
SELECT #2015-01-21# as dt FROM dual
UNION ALL
SELECT #2015-01-22# as dt FROM dual) as dates LEFT JOIN
FixAlarms ON DateValue(FixAlarms.[Alm_NativeTimeIn]) = dates.dt
WHERE ((FixAlarms.Alm_Tagname) <> """")) AND FixAlarms.Alm_NativeTimeIn > CellTime 
GROUP BY FixAlarms.[Alm_Tagname], FixAlarms.Alm_Descr 
PIVOT Format(dates.dt, 'Short Date')

EDIT: I must add that this is not the only way of achieving it. Another way is to use a Numbers table. Create a table called Numbers with a single numeric column n and fill it with numbers 0 to 100 (depends on the maximum number of days you wish to include into your query). Then your query for the dates will be:

SELECT DateAdd('d', n, #2015-01-20#) as dt FROM numbers where n < 30;

And the resulting query will be:

TRANSFORM Count(FixAlarms.[Alm_NativeTimeLast]) AS CountOfAlm_NativeTimeLast 
SELECT FixAlarms.Alm_Tagname, FixAlarms.Alm_Desc 
FROM 
(SELECT DateAdd('d', n, #2015-01-20#) as dt FROM numbers where n < 30) as dates LEFT JOIN
FixAlarms ON DateValue(FixAlarms.[Alm_NativeTimeIn]) = dates.dt
WHERE ((FixAlarms.Alm_Tagname) <> """")) AND FixAlarms.Alm_NativeTimeIn > CellTime 
GROUP BY FixAlarms.[Alm_Tagname], FixAlarms.Alm_Descr 
PIVOT Format(dates.dt, 'Short Date')
Community
  • 1
  • 1
cha
  • 10,301
  • 1
  • 18
  • 26
  • Cha, thanks for your answer. I dont fully understand why I have to make another column when I already have a Date column to use. Really I would be okay with not having blank columns as long as I had a header for each column. Then I can just write a macro to place them and sort them. I don't understand why the query has column names when Access does it, but when I copy it to VBA it does not. Maybe this is some connection setting I am missing? – Furbs Jan 28 '15 at 00:42
  • If you do not have data for all dates in a range then this data will not be in the select statement. As a result, when you TRANSFORM this select statement the columns will not be created resulting in gaps. by using the LEFT JOIN you make it sure that at least one row is created to the missing dates. Just try the above queries ans see if they produce the desired result – cha Jan 28 '15 at 00:47
0

When using PIVOT columnName IN (ValueList) ValueList is

  1. In parentheses
  2. In quotes
  3. Comma separated

So you're

PIVOT Format([Alm_NativeTimeIn],""Short Date"") IN 01/20/15"

Needs to become

PIVOT Format([Alm_NativeTimeIn],""Short Date"") IN (""01/20/15"")

With that said, this will not filter your records using PIVOTS in IN statement. You need to use the WHERE clause still.

If the end goal is to represent your data left to right then this will work. It will be a lot of extra work to make this work as a report though because your controls will not be bound to predictable columns. The Column names will change for different parameters.

You could leave this as a traditional query (not pivoted) and have a much easier time reporting it. If you are showing users the grid directly or exporting to Excel then this is not a problem.

Brad
  • 11,934
  • 4
  • 45
  • 73
0

So, I just wanted to add a header to my pivot table that would tell me what date the particular column was for. The part of the code that I did not show was that I was using a rec.getrows to move all of my data into a simpler array variable. While this had all the data from Access, it did not have any headers to inform me what was a tagname, what was a description, and what was which date.

I found that in the recordset itself under fields.item(n) there was a Name attribute. This name told me where the column data came from or the date of the data. Using this and a simple day(date) function, I was able to make my monthly report summarizing all of the alarms.

Thanks for your help guys, but I either was not clear in my description of the problem or it was being over thought.

Furbs
  • 49
  • 5