If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Report Populated by a crosstab query
I have a report that is poulated by a cross tab query.
The querry runs off a linked table that is actually an excel spreadsheet that updates automatically every month. The report shows system log ins for the current month and the previous month. The query and report layout has the user's group as the row heading, the month as the column heading and the # of log ins as the sum value. so the output fileds in the report are Group, April, March for a data set of march and april when the table is update for april and may the query works fine but the report is looking for a data field of march that no longer exists. I need the output to look like a crosstab report. Any thoughts. |
#2
|
|||
|
|||
Report Populated by a crosstab query
I would approach this much differently by using "relative" months rather
than semi-hardcoding months. If you want the previous 12 months, create a column heading expression of ColHead:"Mth" & DateDiff("m",[DateField],Date()) This will create columns in the crosstab of "Mth0" to "MthN" where Mth0 is this month and MthN is the oldest previous month. If you set the column headings property to "Mth0","Mth1","Mth2",..."Mth11" you will always get the same number of columns with the same names and they will always display the current and previous 11 months. In your report, you can use text boxes as your column headings with control sources of =DateAdd("m",0,Date()) =DateAdd("m",-1,Date()) =DateAdd("m",-2,Date()) etc This solution results in no coding. -- Duane Hookom MS Access MVP "Michael Noblet" wrote in message ... I have a report that is poulated by a cross tab query. The querry runs off a linked table that is actually an excel spreadsheet that updates automatically every month. The report shows system log ins for the current month and the previous month. The query and report layout has the user's group as the row heading, the month as the column heading and the # of log ins as the sum value. so the output fileds in the report are Group, April, March for a data set of march and april when the table is update for april and may the query works fine but the report is looking for a data field of march that no longer exists. I need the output to look like a crosstab report. Any thoughts. |
#3
|
|||
|
|||
Report Populated by a crosstab query
Duane,
That is a solid solution but I would have to change the way most of my data is presented in my queries to make that work. But I am sure the answer lies along the same lines. In this cross tab query I have 3 data fields. The Group Name, The Log In hour sum and the Month (ie:march). There are only vere 2 months worth of Data. this month and prvious. Each month the data table is overwritten so as of today there would be april and May so the output has only 2 columns that are basically a text field. Any way to make this work without changing this and a couple other queries? -----Original Message----- I would approach this much differently by using "relative" months rather than semi-hardcoding months. If you want the previous 12 months, create a column heading expression of ColHead:"Mth" & DateDiff("m",[DateField],Date()) This will create columns in the crosstab of "Mth0" to "MthN" where Mth0 is this month and MthN is the oldest previous month. If you set the column headings property to "Mth0","Mth1","Mth2",..."Mth11" you will always get the same number of columns with the same names and they will always display the current and previous 11 months. In your report, you can use text boxes as your column headings with control sources of =DateAdd("m",0,Date()) =DateAdd("m",-1,Date()) =DateAdd("m",-2,Date()) etc This solution results in no coding. -- Duane Hookom MS Access MVP "Michael Noblet" wrote in message ... I have a report that is poulated by a cross tab query. The querry runs off a linked table that is actually an excel spreadsheet that updates automatically every month. The report shows system log ins for the current month and the previous month. The query and report layout has the user's group as the row heading, the month as the column heading and the # of log ins as the sum value. so the output fileds in the report are Group, April, March for a data set of march and april when the table is update for april and may the query works fine but the report is looking for a data field of march that no longer exists. I need the output to look like a crosstab report. Any thoughts. . |
#4
|
|||
|
|||
Report Populated by a crosstab query
Are you suggesting your data value of the Month is a text value of the month
name? If so, I would take the time to get this changed to a month number or date. Possibly someone else might have a work-around that works better with your data and current queries. I have a tendency to find a flexible, efficient solution and consistently stick with it. -- Duane Hookom MS Access MVP "Michael Noblet" wrote in message ... Duane, That is a solid solution but I would have to change the way most of my data is presented in my queries to make that work. But I am sure the answer lies along the same lines. In this cross tab query I have 3 data fields. The Group Name, The Log In hour sum and the Month (ie:march). There are only vere 2 months worth of Data. this month and prvious. Each month the data table is overwritten so as of today there would be april and May so the output has only 2 columns that are basically a text field. Any way to make this work without changing this and a couple other queries? -----Original Message----- I would approach this much differently by using "relative" months rather than semi-hardcoding months. If you want the previous 12 months, create a column heading expression of ColHead:"Mth" & DateDiff("m",[DateField],Date()) This will create columns in the crosstab of "Mth0" to "MthN" where Mth0 is this month and MthN is the oldest previous month. If you set the column headings property to "Mth0","Mth1","Mth2",..."Mth11" you will always get the same number of columns with the same names and they will always display the current and previous 11 months. In your report, you can use text boxes as your column headings with control sources of =DateAdd("m",0,Date()) =DateAdd("m",-1,Date()) =DateAdd("m",-2,Date()) etc This solution results in no coding. -- Duane Hookom MS Access MVP "Michael Noblet" wrote in message ... I have a report that is poulated by a cross tab query. The querry runs off a linked table that is actually an excel spreadsheet that updates automatically every month. The report shows system log ins for the current month and the previous month. The query and report layout has the user's group as the row heading, the month as the column heading and the # of log ins as the sum value. so the output fileds in the report are Group, April, March for a data set of march and april when the table is update for april and may the query works fine but the report is looking for a data field of march that no longer exists. I need the output to look like a crosstab report. Any thoughts. . |
Thread Tools | |
Display Modes | |
|
|