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 |
#21
|
|||
|
|||
SELECT Statement in a Report
Is this enough information, or do you need more?
Let me know... Thanks. "chickalina" wrote: Here is the table information: tbl_Ideas_Bank IdeaID (primary key) Description Structural Jurisdiction BenefitType (ETR Only, Cash Only, or ETR &Cash) tbl_Quarter IdeaID (dual primary key with Qtrenddate) QtrEndDate (this is the Quarter end Date) QuarterValue (These are the values to calculate with) "John Spencer" wrote: If you are using a label then you have to set the label's caption. for 3. lbl_Year1.Caption=forms![frm_customreports]![ControlNameon frm_customreports] +0 As for 4, you really need to post the SQL statement. so we have some idea of the column names,etc. I keep thinking that you are trying use a query directly as the source for a control. If you are that does not work. What columns do you want to Add up horizontally? Across the page can probably be handled in the reports source query, or with vba in the report's detail section. What columns do you want to add up vertically? Depending on what you specifically want to do here, this could involve a sub-report or just some calculated controls on the report. As for using the form for input into the query. Here is a Simple Select (not a crosstab) example Parameters Forms!frm_CustomReports!StartYear Short; SELECT a,b,c,d FROM SomeTable WHERE SomeDateField Between DateSerial(Forms!frm_CustomReports!StartYear,1,1) and DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31) A crosstab might look like the following and it would if I got all the syntax correctly give you a count of c for each year and a grand total for the year for each combination of a, b, and d. Parameters Forms!frm_CustomReports!StartYear Short; Transform Count(c) as CountEm SELECT a,b,d, , Sum(C) as ThisLineTotal FROM SomeTable WHERE SomeDateField Between DateSerial(Forms!frm_CustomReports!StartYear,1,1) and DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31) GROUP BY a, b, d PIVOT 'Year' & (Year(SomeDateField) - Forms!frm_CustomReports!StartYear) In ('Year0', 'Year1', 'Year2', 'Year3', 'Year4') "chickalina" wrote in message ... Disregard Previous.... I meant that I know that you are all helping... I didn't realize you were volunteers... It's late... 3. I put the VBA code in the header and changed it to: lbl_Year1=forms![frm_customreports]!HERE'S THE SCREW UP +0 txtControlYear1 = Forms![FormGetYear]![TxtYearNumberControl] + 0 etc. etc. etc. I can't figure out what to put here... it's a form where you pick the year start from cboYear and click on the Preview Report button... the above code is in the header section... the first label is lbl_Year1. Can I use a label? or should I make it a text box...? And 4: What do I use for the select statement in the Control Source for totaling for the year by Idea in the detail section? the query is [qry_STRAPforReport Query] and the field names are "qtrendddate" and "quartervalue". I'm guessing, the SELECT statement would pull the year from the same screen where you pick the start date? so it would collect all fields ending in 2007? And then... the characteristics is another field in tbl_ideas_bank that needs to be subtotaled on the bottom.... separate from the year total. I guess I mean it would be broken down by the three choices from a combo box for 2007 for each country. I hope this makes better sense. Thanks again. |
#22
|
|||
|
|||
SELECT Statement in a Report
Is there anything anyone can do? I'm in desperate need of help.
"chickalina" wrote: Is this enough information, or do you need more? Let me know... Thanks. "chickalina" wrote: Here is the table information: tbl_Ideas_Bank IdeaID (primary key) Description Structural Jurisdiction BenefitType (ETR Only, Cash Only, or ETR &Cash) tbl_Quarter IdeaID (dual primary key with Qtrenddate) QtrEndDate (this is the Quarter end Date) QuarterValue (These are the values to calculate with) "John Spencer" wrote: If you are using a label then you have to set the label's caption. for 3. lbl_Year1.Caption=forms![frm_customreports]![ControlNameon frm_customreports] +0 As for 4, you really need to post the SQL statement. so we have some idea of the column names,etc. I keep thinking that you are trying use a query directly as the source for a control. If you are that does not work. What columns do you want to Add up horizontally? Across the page can probably be handled in the reports source query, or with vba in the report's detail section. What columns do you want to add up vertically? Depending on what you specifically want to do here, this could involve a sub-report or just some calculated controls on the report. As for using the form for input into the query. Here is a Simple Select (not a crosstab) example Parameters Forms!frm_CustomReports!StartYear Short; SELECT a,b,c,d FROM SomeTable WHERE SomeDateField Between DateSerial(Forms!frm_CustomReports!StartYear,1,1) and DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31) A crosstab might look like the following and it would if I got all the syntax correctly give you a count of c for each year and a grand total for the year for each combination of a, b, and d. Parameters Forms!frm_CustomReports!StartYear Short; Transform Count(c) as CountEm SELECT a,b,d, , Sum(C) as ThisLineTotal FROM SomeTable WHERE SomeDateField Between DateSerial(Forms!frm_CustomReports!StartYear,1,1) and DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31) GROUP BY a, b, d PIVOT 'Year' & (Year(SomeDateField) - Forms!frm_CustomReports!StartYear) In ('Year0', 'Year1', 'Year2', 'Year3', 'Year4') "chickalina" wrote in message ... Disregard Previous.... I meant that I know that you are all helping... I didn't realize you were volunteers... It's late... 3. I put the VBA code in the header and changed it to: lbl_Year1=forms![frm_customreports]!HERE'S THE SCREW UP +0 txtControlYear1 = Forms![FormGetYear]![TxtYearNumberControl] + 0 etc. etc. etc. I can't figure out what to put here... it's a form where you pick the year start from cboYear and click on the Preview Report button... the above code is in the header section... the first label is lbl_Year1. Can I use a label? or should I make it a text box...? And 4: What do I use for the select statement in the Control Source for totaling for the year by Idea in the detail section? the query is [qry_STRAPforReport Query] and the field names are "qtrendddate" and "quartervalue". I'm guessing, the SELECT statement would pull the year from the same screen where you pick the start date? so it would collect all fields ending in 2007? And then... the characteristics is another field in tbl_ideas_bank that needs to be subtotaled on the bottom.... separate from the year total. I guess I mean it would be broken down by the three choices from a combo box for 2007 for each country. I hope this makes better sense. Thanks again. |
#23
|
|||
|
|||
SELECT Statement in a Report
If you are still looking for a method to display the results of a crosstab
of moving window of years, look at the dynamice monthly crosstab report solution at http://www.tek-tips.com/faqs.cfm?fid=5466. You should be able to easily change from month to year. -- Duane Hookom MS Access MVP "chickalina" wrote in message ... Is there anything anyone can do? I'm in desperate need of help. "chickalina" wrote: Is this enough information, or do you need more? Let me know... Thanks. "chickalina" wrote: Here is the table information: tbl_Ideas_Bank IdeaID (primary key) Description Structural Jurisdiction BenefitType (ETR Only, Cash Only, or ETR &Cash) tbl_Quarter IdeaID (dual primary key with Qtrenddate) QtrEndDate (this is the Quarter end Date) QuarterValue (These are the values to calculate with) "John Spencer" wrote: If you are using a label then you have to set the label's caption. for 3. lbl_Year1.Caption=forms![frm_customreports]![ControlNameon frm_customreports] +0 As for 4, you really need to post the SQL statement. so we have some idea of the column names,etc. I keep thinking that you are trying use a query directly as the source for a control. If you are that does not work. What columns do you want to Add up horizontally? Across the page can probably be handled in the reports source query, or with vba in the report's detail section. What columns do you want to add up vertically? Depending on what you specifically want to do here, this could involve a sub-report or just some calculated controls on the report. As for using the form for input into the query. Here is a Simple Select (not a crosstab) example Parameters Forms!frm_CustomReports!StartYear Short; SELECT a,b,c,d FROM SomeTable WHERE SomeDateField Between DateSerial(Forms!frm_CustomReports!StartYear,1,1) and DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31) A crosstab might look like the following and it would if I got all the syntax correctly give you a count of c for each year and a grand total for the year for each combination of a, b, and d. Parameters Forms!frm_CustomReports!StartYear Short; Transform Count(c) as CountEm SELECT a,b,d, , Sum(C) as ThisLineTotal FROM SomeTable WHERE SomeDateField Between DateSerial(Forms!frm_CustomReports!StartYear,1,1) and DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31) GROUP BY a, b, d PIVOT 'Year' & (Year(SomeDateField) - Forms!frm_CustomReports!StartYear) In ('Year0', 'Year1', 'Year2', 'Year3', 'Year4') "chickalina" wrote in message ... Disregard Previous.... I meant that I know that you are all helping... I didn't realize you were volunteers... It's late... 3. I put the VBA code in the header and changed it to: lbl_Year1=forms![frm_customreports]!HERE'S THE SCREW UP +0 txtControlYear1 = Forms![FormGetYear]![TxtYearNumberControl] + 0 etc. etc. etc. I can't figure out what to put here... it's a form where you pick the year start from cboYear and click on the Preview Report button... the above code is in the header section... the first label is lbl_Year1. Can I use a label? or should I make it a text box...? And 4: What do I use for the select statement in the Control Source for totaling for the year by Idea in the detail section? the query is [qry_STRAPforReport Query] and the field names are "qtrendddate" and "quartervalue". I'm guessing, the SELECT statement would pull the year from the same screen where you pick the start date? so it would collect all fields ending in 2007? And then... the characteristics is another field in tbl_ideas_bank that needs to be subtotaled on the bottom.... separate from the year total. I guess I mean it would be broken down by the three choices from a combo box for 2007 for each country. I hope this makes better sense. Thanks again. |
#24
|
|||
|
|||
SELECT Statement in a Report
Sorry, I haven't responded but I've been busy and have not been able to take
an extended period to examine your problem. At this point, I just don't understand what you are attempting to do. One thing that bothers me is you keep saying you are using a query (a select statement) in the footer of the report. That makes no sense to me. You cannot use a query in a footer and you can't use it as the source of a control. I will try to find some time this afternoon to look at your problem. In the meantime, please copy and paste the SQL statement you are using as the report's record source. And try to explain what you are attempting to accomplish in the report's footer (not the page footer). To make sense of this, I am probably going to have to print out all the postings in this thread and then ask additional questions. "chickalina" wrote in message ... Is there anything anyone can do? I'm in desperate need of help. "chickalina" wrote: |
#25
|
|||
|
|||
SELECT Statement in a Report
Hi John,
I have a report that "should" show the yearly sum for an idea for 5 years. I want the user to be able to choose the start year. At the bottom of the report (whereever you say the text boxes should go), I need totals for each of three different fields (all ideas fall into one of the three). I created a crosstab query that gives me yearly sums, but then you can't break that down into the three sections, and I can't figure out a way for a user prompt for the start year. Should I be using a Select Query for the source and then SELECT statements in the boxes to pull the information? Thanks. M "John Spencer" wrote: Sorry, I haven't responded but I've been busy and have not been able to take an extended period to examine your problem. At this point, I just don't understand what you are attempting to do. One thing that bothers me is you keep saying you are using a query (a select statement) in the footer of the report. That makes no sense to me. You cannot use a query in a footer and you can't use it as the source of a control. I will try to find some time this afternoon to look at your problem. In the meantime, please copy and paste the SQL statement you are using as the report's record source. And try to explain what you are attempting to accomplish in the report's footer (not the page footer). To make sense of this, I am probably going to have to print out all the postings in this thread and then ask additional questions. "chickalina" wrote in message ... Is there anything anyone can do? I'm in desperate need of help. "chickalina" wrote: |
#26
|
|||
|
|||
SELECT Statement in a Report
Is there a way to do this in a report?
"Duane Hookom" wrote: If you are still looking for a method to display the results of a crosstab of moving window of years, look at the dynamice monthly crosstab report solution at http://www.tek-tips.com/faqs.cfm?fid=5466. You should be able to easily change from month to year. -- Duane Hookom MS Access MVP "chickalina" wrote in message ... Is there anything anyone can do? I'm in desperate need of help. "chickalina" wrote: Is this enough information, or do you need more? Let me know... Thanks. "chickalina" wrote: Here is the table information: tbl_Ideas_Bank IdeaID (primary key) Description Structural Jurisdiction BenefitType (ETR Only, Cash Only, or ETR &Cash) tbl_Quarter IdeaID (dual primary key with Qtrenddate) QtrEndDate (this is the Quarter end Date) QuarterValue (These are the values to calculate with) "John Spencer" wrote: If you are using a label then you have to set the label's caption. for 3. lbl_Year1.Caption=forms![frm_customreports]![ControlNameon frm_customreports] +0 As for 4, you really need to post the SQL statement. so we have some idea of the column names,etc. I keep thinking that you are trying use a query directly as the source for a control. If you are that does not work. What columns do you want to Add up horizontally? Across the page can probably be handled in the reports source query, or with vba in the report's detail section. What columns do you want to add up vertically? Depending on what you specifically want to do here, this could involve a sub-report or just some calculated controls on the report. As for using the form for input into the query. Here is a Simple Select (not a crosstab) example Parameters Forms!frm_CustomReports!StartYear Short; SELECT a,b,c,d FROM SomeTable WHERE SomeDateField Between DateSerial(Forms!frm_CustomReports!StartYear,1,1) and DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31) A crosstab might look like the following and it would if I got all the syntax correctly give you a count of c for each year and a grand total for the year for each combination of a, b, and d. Parameters Forms!frm_CustomReports!StartYear Short; Transform Count(c) as CountEm SELECT a,b,d, , Sum(C) as ThisLineTotal FROM SomeTable WHERE SomeDateField Between DateSerial(Forms!frm_CustomReports!StartYear,1,1) and DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31) GROUP BY a, b, d PIVOT 'Year' & (Year(SomeDateField) - Forms!frm_CustomReports!StartYear) In ('Year0', 'Year1', 'Year2', 'Year3', 'Year4') "chickalina" wrote in message ... Disregard Previous.... I meant that I know that you are all helping... I didn't realize you were volunteers... It's late... 3. I put the VBA code in the header and changed it to: lbl_Year1=forms![frm_customreports]!HERE'S THE SCREW UP +0 txtControlYear1 = Forms![FormGetYear]![TxtYearNumberControl] + 0 etc. etc. etc. I can't figure out what to put here... it's a form where you pick the year start from cboYear and click on the Preview Report button... the above code is in the header section... the first label is lbl_Year1. Can I use a label? or should I make it a text box...? And 4: What do I use for the select statement in the Control Source for totaling for the year by Idea in the detail section? the query is [qry_STRAPforReport Query] and the field names are "qtrendddate" and "quartervalue". I'm guessing, the SELECT statement would pull the year from the same screen where you pick the start date? so it would collect all fields ending in 2007? And then... the characteristics is another field in tbl_ideas_bank that needs to be subtotaled on the bottom.... separate from the year total. I guess I mean it would be broken down by the three choices from a combo box for 2007 for each country. I hope this makes better sense. Thanks again. |
#27
|
|||
|
|||
SELECT Statement in a Report
Can you use a subreport in your "At the bottom of the report" (assuming
Report Footer Section). This subreport could be your crosstab or any other query. -- Duane Hookom MS Access MVP "chickalina" wrote in message ... Hi John, I have a report that "should" show the yearly sum for an idea for 5 years. I want the user to be able to choose the start year. At the bottom of the report (whereever you say the text boxes should go), I need totals for each of three different fields (all ideas fall into one of the three). I created a crosstab query that gives me yearly sums, but then you can't break that down into the three sections, and I can't figure out a way for a user prompt for the start year. Should I be using a Select Query for the source and then SELECT statements in the boxes to pull the information? Thanks. M "John Spencer" wrote: Sorry, I haven't responded but I've been busy and have not been able to take an extended period to examine your problem. At this point, I just don't understand what you are attempting to do. One thing that bothers me is you keep saying you are using a query (a select statement) in the footer of the report. That makes no sense to me. You cannot use a query in a footer and you can't use it as the source of a control. I will try to find some time this afternoon to look at your problem. In the meantime, please copy and paste the SQL statement you are using as the report's record source. And try to explain what you are attempting to accomplish in the report's footer (not the page footer). To make sense of this, I am probably going to have to print out all the postings in this thread and then ask additional questions. "chickalina" wrote in message ... Is there anything anyone can do? I'm in desperate need of help. "chickalina" wrote: |
#28
|
|||
|
|||
SELECT Statement in a Report
The title is "Dynamic Monthly Crosstab Report ". It describes how to create
a report. -- Duane Hookom MS Access MVP "chickalina" wrote in message ... Is there a way to do this in a report? "Duane Hookom" wrote: If you are still looking for a method to display the results of a crosstab of moving window of years, look at the dynamice monthly crosstab report solution at http://www.tek-tips.com/faqs.cfm?fid=5466. You should be able to easily change from month to year. -- Duane Hookom MS Access MVP "chickalina" wrote in message ... Is there anything anyone can do? I'm in desperate need of help. "chickalina" wrote: Is this enough information, or do you need more? Let me know... Thanks. "chickalina" wrote: Here is the table information: tbl_Ideas_Bank IdeaID (primary key) Description Structural Jurisdiction BenefitType (ETR Only, Cash Only, or ETR &Cash) tbl_Quarter IdeaID (dual primary key with Qtrenddate) QtrEndDate (this is the Quarter end Date) QuarterValue (These are the values to calculate with) "John Spencer" wrote: If you are using a label then you have to set the label's caption. for 3. lbl_Year1.Caption=forms![frm_customreports]![ControlNameon frm_customreports] +0 As for 4, you really need to post the SQL statement. so we have some idea of the column names,etc. I keep thinking that you are trying use a query directly as the source for a control. If you are that does not work. What columns do you want to Add up horizontally? Across the page can probably be handled in the reports source query, or with vba in the report's detail section. What columns do you want to add up vertically? Depending on what you specifically want to do here, this could involve a sub-report or just some calculated controls on the report. As for using the form for input into the query. Here is a Simple Select (not a crosstab) example Parameters Forms!frm_CustomReports!StartYear Short; SELECT a,b,c,d FROM SomeTable WHERE SomeDateField Between DateSerial(Forms!frm_CustomReports!StartYear,1,1) and DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31) A crosstab might look like the following and it would if I got all the syntax correctly give you a count of c for each year and a grand total for the year for each combination of a, b, and d. Parameters Forms!frm_CustomReports!StartYear Short; Transform Count(c) as CountEm SELECT a,b,d, , Sum(C) as ThisLineTotal FROM SomeTable WHERE SomeDateField Between DateSerial(Forms!frm_CustomReports!StartYear,1,1) and DateSerial(Forms!frm_CustomReports!StartYear+4,12, 31) GROUP BY a, b, d PIVOT 'Year' & (Year(SomeDateField) - Forms!frm_CustomReports!StartYear) In ('Year0', 'Year1', 'Year2', 'Year3', 'Year4') "chickalina" wrote in message ... Disregard Previous.... I meant that I know that you are all helping... I didn't realize you were volunteers... It's late... 3. I put the VBA code in the header and changed it to: lbl_Year1=forms![frm_customreports]!HERE'S THE SCREW UP +0 txtControlYear1 = Forms![FormGetYear]![TxtYearNumberControl] + 0 etc. etc. etc. I can't figure out what to put here... it's a form where you pick the year start from cboYear and click on the Preview Report button... the above code is in the header section... the first label is lbl_Year1. Can I use a label? or should I make it a text box...? And 4: What do I use for the select statement in the Control Source for totaling for the year by Idea in the detail section? the query is [qry_STRAPforReport Query] and the field names are "qtrendddate" and "quartervalue". I'm guessing, the SELECT statement would pull the year from the same screen where you pick the start date? so it would collect all fields ending in 2007? And then... the characteristics is another field in tbl_ideas_bank that needs to be subtotaled on the bottom.... separate from the year total. I guess I mean it would be broken down by the three choices from a combo box for 2007 for each country. I hope this makes better sense. Thanks again. |
#29
|
|||
|
|||
SELECT Statement in a Report
I could... I have had nothing but problems when I tried.
"Duane Hookom" wrote: Can you use a subreport in your "At the bottom of the report" (assuming Report Footer Section). This subreport could be your crosstab or any other query. -- Duane Hookom MS Access MVP "chickalina" wrote in message ... Hi John, I have a report that "should" show the yearly sum for an idea for 5 years. I want the user to be able to choose the start year. At the bottom of the report (whereever you say the text boxes should go), I need totals for each of three different fields (all ideas fall into one of the three). I created a crosstab query that gives me yearly sums, but then you can't break that down into the three sections, and I can't figure out a way for a user prompt for the start year. Should I be using a Select Query for the source and then SELECT statements in the boxes to pull the information? Thanks. M "John Spencer" wrote: Sorry, I haven't responded but I've been busy and have not been able to take an extended period to examine your problem. At this point, I just don't understand what you are attempting to do. One thing that bothers me is you keep saying you are using a query (a select statement) in the footer of the report. That makes no sense to me. You cannot use a query in a footer and you can't use it as the source of a control. I will try to find some time this afternoon to look at your problem. In the meantime, please copy and paste the SQL statement you are using as the report's record source. And try to explain what you are attempting to accomplish in the report's footer (not the page footer). To make sense of this, I am probably going to have to print out all the postings in this thread and then ask additional questions. "chickalina" wrote in message ... Is there anything anyone can do? I'm in desperate need of help. "chickalina" wrote: |
#30
|
|||
|
|||
SELECT Statement in a Report
All right, I'm doing the Query|Parameter thing... I've got the
"frm_CustomReports" (the form where the beginning year is selected), the labels at the top of the page work with: lbl_Year1.Caption = Forms![frm_CustomReports]![txtYear] + 0 lbl_Year2.Caption = Forms![frm_CustomReports]![txtYear] + 1 lbl_Year3.Caption = Forms![frm_CustomReports]![txtYear] + 2 lbl_Year4.Caption = Forms![frm_CustomReports]![txtYear] + 3 lbl_Year5.Caption = Forms![frm_CustomReports]![txtYear] + 4 The problem is getting the text boxes in the detail section to work. Here's the code from the report's Record Source: PARAMETERS forms!frm_CustomSearch!cboYear DateTime; SELECT "Year1" & DateDiff("y",[qtrenddate],[forms]![frm_customreports]![txt_year]) AS Expr1, [Year1] AS Expr2, [Year2] AS Expr3, [Year3] AS Expr4, [Year4] AS Expr5, [Year5] AS Expr6 FROM tbl_ReserveAmounts; "Duane Hookom" wrote: Can you use a subreport in your "At the bottom of the report" (assuming Report Footer Section). This subreport could be your crosstab or any other query. -- Duane Hookom MS Access MVP "chickalina" wrote in message ... Hi John, I have a report that "should" show the yearly sum for an idea for 5 years. I want the user to be able to choose the start year. At the bottom of the report (whereever you say the text boxes should go), I need totals for each of three different fields (all ideas fall into one of the three). I created a crosstab query that gives me yearly sums, but then you can't break that down into the three sections, and I can't figure out a way for a user prompt for the start year. Should I be using a Select Query for the source and then SELECT statements in the boxes to pull the information? Thanks. M "John Spencer" wrote: Sorry, I haven't responded but I've been busy and have not been able to take an extended period to examine your problem. At this point, I just don't understand what you are attempting to do. One thing that bothers me is you keep saying you are using a query (a select statement) in the footer of the report. That makes no sense to me. You cannot use a query in a footer and you can't use it as the source of a control. I will try to find some time this afternoon to look at your problem. In the meantime, please copy and paste the SQL statement you are using as the report's record source. And try to explain what you are attempting to accomplish in the report's footer (not the page footer). To make sense of this, I am probably going to have to print out all the postings in this thread and then ask additional questions. "chickalina" wrote in message ... Is there anything anyone can do? I'm in desperate need of help. "chickalina" wrote: |
Thread Tools | |
Display Modes | |
|
|