|
| 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
January 8th, 2010, 04:28 PM
posted to microsoft.public.access.queries,microsoft.public.access.reports,microsoft.public.access
|
|||
|
|||
Help with a Monthly Report Design and Layout
I have a bit of a challenge in front of me with a requested monthly report
that will translate a list of departments that have not approved their monthly numbers in our databases. Query: SELECT tblMonthlyData.DEPTCODE, tblMonthlyData.FACILITY, tblMonthlyData.Month FROM tblMonthlyData WHERE (((tblMonthlyData.Approved)=No)) GROUP BY tblMonthlyData.DEPTCODE, tblMonthlyData.FACILITY, tblMonthlyData.Month HAVING (((tblMonthlyData.Month)DateSerial(Year(Date()),M onth(Date())-1,1))) ORDER BY tblMonthlyData.DEPTCODE, tblMonthlyData.Month DESC; Generates a list of departments without approvals: DEPTCODE FACILITY Month 3CSE S 11/01/2009 3CSE S 10/01/2009 3CSE S 09/01/2009 3CSE S 08/01/2009 3CSW S 11/01/2009 3CSW S 10/01/2009 3CSW S 09/01/2009 3CSW S 08/01/2009 3CSW S 10/01/2009 4CCL S 11/01/2009 4CCL S 10/01/2009 4CCL S 09/01/2009 4CCL W 09/01/2009 4CCL S 08/01/2009 ... Desired Output Layout: DEPTCODE FAC 12/2009 11/2009 10/2009 09/2009 ... 3CSE S NO YES YES YES ... 3CSW S NO YES YES YES ... 4CCL S NO YES YES YES ... 4CCL W NO NO YES NO ... Would it be smarter to create a table for this layout, or can a report be created that is smart enough to adjust the column headings on a monthly basis? How can I best approach this? Thank you in advance for your time and help with this. -- MJ 
|
|
#2
January 8th, 2010, 05:05 PM
posted to microsoft.public.access.queries,microsoft.public.access.reports,microsoft.public.access
|
|||
|
|||
|
Help with a Monthly Report Design and Layout
I would take a look at the dynamic monthly crosstab report solution at
http://www.tek-tips.com/faqs.cfm?fid=5466. -- Duane Hookom Microsoft Access MVP "MJ" wrote: I have a bit of a challenge in front of me with a requested monthly report that will translate a list of departments that have not approved their monthly numbers in our databases. Query: SELECT tblMonthlyData.DEPTCODE, tblMonthlyData.FACILITY, tblMonthlyData.Month FROM tblMonthlyData WHERE (((tblMonthlyData.Approved)=No)) GROUP BY tblMonthlyData.DEPTCODE, tblMonthlyData.FACILITY, tblMonthlyData.Month HAVING (((tblMonthlyData.Month)DateSerial(Year(Date()),M onth(Date())-1,1))) ORDER BY tblMonthlyData.DEPTCODE, tblMonthlyData.Month DESC; Generates a list of departments without approvals: DEPTCODE FACILITY Month 3CSE S 11/01/2009 3CSE S 10/01/2009 3CSE S 09/01/2009 3CSE S 08/01/2009 3CSW S 11/01/2009 3CSW S 10/01/2009 3CSW S 09/01/2009 3CSW S 08/01/2009 3CSW S 10/01/2009 4CCL S 11/01/2009 4CCL S 10/01/2009 4CCL S 09/01/2009 4CCL W 09/01/2009 4CCL S 08/01/2009 ... Desired Output Layout: DEPTCODE FAC 12/2009 11/2009 10/2009 09/2009 ... 3CSE S NO YES YES YES ... 3CSW S NO YES YES YES ... 4CCL S NO YES YES YES ... 4CCL W NO NO YES NO ... Would it be smarter to create a table for this layout, or can a report be created that is smart enough to adjust the column headings on a monthly basis? How can I best approach this? Thank you in advance for your time and help with this. -- MJ
|
|
#3
January 8th, 2010, 06:13 PM
posted to microsoft.public.access.queries,microsoft.public.access.reports,microsoft.public.access
|
|||
|
|||
|
Help with a Monthly Report Design and Layout
Duane, I fully expected you to be the first to reply on this one and you came
through! I am a little embarrassed about it, because within a minute of the time I submitted this question I was already looking at some of your previous crosstab solutions and suggestions. The crosstab definitely was the answer to to question. Thanks again for your inputs. -- MJ "Duane Hookom" wrote: I would take a look at the dynamic monthly crosstab report solution at http://www.tek-tips.com/faqs.cfm?fid=5466. -- Duane Hookom Microsoft Access MVP "MJ" wrote: I have a bit of a challenge in front of me with a requested monthly report that will translate a list of departments that have not approved their monthly numbers in our databases. Query: SELECT tblMonthlyData.DEPTCODE, tblMonthlyData.FACILITY, tblMonthlyData.Month FROM tblMonthlyData WHERE (((tblMonthlyData.Approved)=No)) GROUP BY tblMonthlyData.DEPTCODE, tblMonthlyData.FACILITY, tblMonthlyData.Month HAVING (((tblMonthlyData.Month)DateSerial(Year(Date()),M onth(Date())-1,1))) ORDER BY tblMonthlyData.DEPTCODE, tblMonthlyData.Month DESC; Generates a list of departments without approvals: DEPTCODE FACILITY Month 3CSE S 11/01/2009 3CSE S 10/01/2009 3CSE S 09/01/2009 3CSE S 08/01/2009 3CSW S 11/01/2009 3CSW S 10/01/2009 3CSW S 09/01/2009 3CSW S 08/01/2009 3CSW S 10/01/2009 4CCL S 11/01/2009 4CCL S 10/01/2009 4CCL S 09/01/2009 4CCL W 09/01/2009 4CCL S 08/01/2009 ... Desired Output Layout: DEPTCODE FAC 12/2009 11/2009 10/2009 09/2009 ... 3CSE S NO YES YES YES ... 3CSW S NO YES YES YES ... 4CCL S NO YES YES YES ... 4CCL W NO NO YES NO ... Would it be smarter to create a table for this layout, or can a report be created that is smart enough to adjust the column headings on a monthly basis? How can I best approach this? Thank you in advance for your time and help with this. -- MJ
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
