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 |
#11
|
|||
|
|||
SELECT Statement in a Report
Does this make more sense? I'm looking forward to having closure on this... I
have to apply it to something else and we're looking to get this database finished and start working on it. Thanks. "chickalina" wrote: Maybe my note was cryptic? I'll try again... Page Header : Title of the report. Labels for Year 1, Year 2, etc. CountryID Header: This is so the report sorts by Country, but the Country name is in the Page header so it repeats at the top of the page in case there is more than one page in each country. Structural Header: This is the second sort level. Details: Idea Description and the Totals for each idea by year. Structural Footer: Totals by year for all the ideas in that country. Page Footer: Page number and current date. I am trying to do this in a report. Let me see if I have this correct: 1. I do not need the crosstab query I've created, I should use the tables that store the information. 2. for the label name in the page header I should use for the 5 years: Year 1, Year 2, etc. and the actual years would appear when a start date is typed. I should use the "Select" code in a text box "row source" forthe calculations in the detail section. The determining start date will be entered in a "pop up" form before the report is open. This will also be code. 3. I would like to see at the bottom of each country a total by [benefittype]: some ideas are cash, some are ETR and some are both. I need these three line items at the bottom of the page per country. This can be done with a select statement, but in which section? This is what I don't get. I've tried the code in the details section, page header, page footer, you name it. It doesn't work. I checked and rechecked the code. So, if this is correct, what code should I be using? I hope this is more clear then before and you can help. Thanks for your help John, I appreciate it! M |
#12
|
|||
|
|||
SELECT Statement in a Report
Maybe my second explanation is not good enough? That's why I'm getting no
response? "chickalina" wrote: Maybe my note was cryptic? I'll try again... Page Header : Title of the report. Labels for Year 1, Year 2, etc. CountryID Header: This is so the report sorts by Country, but the Country name is in the Page header so it repeats at the top of the page in case there is more than one page in each country. Structural Header: This is the second sort level. Details: Idea Description and the Totals for each idea by year. Structural Footer: Totals by year for all the ideas in that country. Page Footer: Page number and current date. I am trying to do this in a report. Let me see if I have this correct: 1. I do not need the crosstab query I've created, I should use the tables that store the information. 2. for the label name in the page header I should use for the 5 years: Year 1, Year 2, etc. and the actual years would appear when a start date is typed. I should use the "Select" code in a text box "row source" forthe calculations in the detail section. The determining start date will be entered in a "pop up" form before the report is open. This will also be code. 3. I would like to see at the bottom of each country a total by [benefittype]: some ideas are cash, some are ETR and some are both. I need these three line items at the bottom of the page per country. This can be done with a select statement, but in which section? This is what I don't get. I've tried the code in the details section, page header, page footer, you name it. It doesn't work. I checked and rechecked the code. So, if this is correct, what code should I be using? I hope this is more clear then before and you can help. Thanks for your help John, I appreciate it! M |
#13
|
|||
|
|||
SELECT Statement in a Report
Are there any other websites I can check in the interim for answers? This
newsgroup thing is slow in responding and I only have a limited time to finish this database. Thanks. "chickalina" wrote: Maybe my second explanation is not good enough? That's why I'm getting no response? "chickalina" wrote: Maybe my note was cryptic? I'll try again... Page Header : Title of the report. Labels for Year 1, Year 2, etc. CountryID Header: This is so the report sorts by Country, but the Country name is in the Page header so it repeats at the top of the page in case there is more than one page in each country. Structural Header: This is the second sort level. Details: Idea Description and the Totals for each idea by year. Structural Footer: Totals by year for all the ideas in that country. Page Footer: Page number and current date. I am trying to do this in a report. Let me see if I have this correct: 1. I do not need the crosstab query I've created, I should use the tables that store the information. 2. for the label name in the page header I should use for the 5 years: Year 1, Year 2, etc. and the actual years would appear when a start date is typed. I should use the "Select" code in a text box "row source" forthe calculations in the detail section. The determining start date will be entered in a "pop up" form before the report is open. This will also be code. 3. I would like to see at the bottom of each country a total by [benefittype]: some ideas are cash, some are ETR and some are both. I need these three line items at the bottom of the page per country. This can be done with a select statement, but in which section? This is what I don't get. I've tried the code in the details section, page header, page footer, you name it. It doesn't work. I checked and rechecked the code. So, if this is correct, what code should I be using? I hope this is more clear then before and you can help. Thanks for your help John, I appreciate it! M |
#14
|
|||
|
|||
SELECT Statement in a Report
RESPONSES in line. Sorry, but as a volunteer, I can't always get on line to
answer further questions. chickalina wrote: I am trying to do this in a report. Let me see if I have this correct: 1. I do not need the crosstab query I've created. No, you probably do need the crosstab as the source of the report, but it sounds as if you may need to modify it so you can specify your period start and the names of the year columns. 2. for the "name" for the 5 years will be Year 1, Year 2, etc. and they will be in the page header. and take the year from the calculations in the detail section. You would have unbound controls in the report's page header or in a group header for the column labels. You would use a VBA formula in the format event of the relevant section to assign the value to the control. Assuming that you were getting the start year from a form, you could use something like the following to assign the value to the control txtControlYear1 = Forms![FormGetYear]![TxtYearNumberControl] + 0 txtControlYear2 = Forms![FormGetYear]![TxtYearNumberControl] + 1 txtControlYear3 = Forms![FormGetYear]![TxtYearNumberControl] + 2 txtControlYear4 = Forms![FormGetYear]![TxtYearNumberControl] + 3 txtControlYear5 = Forms![FormGetYear]![TxtYearNumberControl] + 4 3. The details section will hold the actual calculations for every idea, with a select statement, by year. No, the details section would be the fields (columns) from the crosstab 4. In order to calculate certain characteristics (some ideas, some are cash, some are both) at the end of each country, this can be done with a select statement in which section... this is what I don't get. I've tried the code in the details section, page header, page footer, you name it. It doesn't work. I checked and rechecked the code. And here you have lost me. Your sample showed Idea and a Count(?) of the idea. Now you are introducing characteristics and cash? So, if I am correct... where do I find all this glorious code! Thanks for your help John, I appreciate it! M "John Spencer" wrote: You seem to be asking multiple questions at once. I would suggest that we try to solve one question at a time. 1) Post the SQL of the crosstab query for specific advice on how you might modify it to get just five years. If you want to vary the number of years then we will hope that Duane Hookom is around and can point you to some sample databases on how to do this. 2) Instead of hard coding the years, use relative column names like Year1, Year2, Year3, Year4, and Year5. You can use code on a form or in a report to change the captions on labels or the control source of unbound text controls to show the year numbers. 3) If you have the start year and you are always going to have 5 years, you don't need to ask for the end year. Plus if you are limiting the report to 5 years and the user enters 2005 to 2014 they are going to be confused when they only get 2005 to 2009 data in the report. Are you doing this on a form or are you doing this in a report? "chickalina" wrote in message ... John, Also, I have a form that looks like thiswhich is only supposed to be a 5 year projection) Country Local 2007 2008 2009 2010 2011 Total Idea 5 5 5 5 5 25 State Idea 4 4 4 4 4 16 The yearly totals are taken from a Crosstab Query, but the years are hard coded. If I want 2012 included, I have to go back into the query and add that column. How can this be done automatically? Because I want to create a pop up form for the report where they can put a start and end date so the report only shows the 5 year window. How do I set up the report fields? OR, is this too involved? P.S. this is also the same report where I'm putting the SELECT statement in the footer. "chickalina" wrote: John, Thanks for the help... I'm going rename Value to QtrValue or something like that... but for now, the code still does not work. Might that be because of the placement on the report? It's in the footer now to give apply this equation to each section. I've tried with the brackets and without. Thanks. "John Spencer" wrote: It looks as if you have ther brackets incorrectly placed. SELECT [tbl_Ideas_Bank].[IdeaID] , [tbl_Ideas_Bank].[BenefitType] , [tbl_Quarter].[QtrEndDate] , [tbl_Quarter].[Value] FROM [tbl_Ideas_Bank], [ tbl_Quarter] WHERE ((([tbl_Ideas_Bank].[BenefitType])="ETR & Cash") AND ((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#))); And since you really don't need the brackets becuase your table and field names contain no spaces. That could be rewritten as SELECT tbl_Ideas_Bank.IdeaID , tbl_Ideas_Bank.BenefitType , tbl_Quarter.QtrEndDate , tbl_Quarter.Value FROM tbl_Ideas_Bank, tbl_Quarter WHERE tbl_Ideas_Bank.BenefitType="ETR & Cash" AND tbl_Quarter.QtrEndDate Between #1/1/2007# And #12/31/2007# I would be wary of using "Value" as a field name, since it is a reserved word in Access, but you are probably OK in the query. Elsewhere the use of the word Value could cause naming conflicts since most controls have a value property. "chickalina" wrote in message ... What's wrong with this code? It keeps giving me the ?Error message, and when I try to run the report, it says there's a problem. It runs in the query though. SELECT [tbl_Ideas_Bank.IdeaID]![ tbl_Ideas_Bank.BenefitType], [tbl_Quarter.QtrEndDate],[ tbl_Quarter.Value] FROM [tbl_Ideas_Bank],[ tbl_Quarter] WHERE (((tbl_Ideas_Bank.BenefitType)="ETR & Cash") AND ((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#))); Thanks. |
#15
|
|||
|
|||
SELECT Statement in a Report
John,
I apologize, I know that you are all volunteers... I didn't realize you were all volunteers... Thanks for the help... I'll try what you've suggested. M "John Spencer" wrote: RESPONSES in line. Sorry, but as a volunteer, I can't always get on line to answer further questions. chickalina wrote: I am trying to do this in a report. Let me see if I have this correct: 1. I do not need the crosstab query I've created. No, you probably do need the crosstab as the source of the report, but it sounds as if you may need to modify it so you can specify your period start and the names of the year columns. 2. for the "name" for the 5 years will be Year 1, Year 2, etc. and they will be in the page header. and take the year from the calculations in the detail section. You would have unbound controls in the report's page header or in a group header for the column labels. You would use a VBA formula in the format event of the relevant section to assign the value to the control. Assuming that you were getting the start year from a form, you could use something like the following to assign the value to the control txtControlYear1 = Forms![FormGetYear]![TxtYearNumberControl] + 0 txtControlYear2 = Forms![FormGetYear]![TxtYearNumberControl] + 1 txtControlYear3 = Forms![FormGetYear]![TxtYearNumberControl] + 2 txtControlYear4 = Forms![FormGetYear]![TxtYearNumberControl] + 3 txtControlYear5 = Forms![FormGetYear]![TxtYearNumberControl] + 4 3. The details section will hold the actual calculations for every idea, with a select statement, by year. No, the details section would be the fields (columns) from the crosstab 4. In order to calculate certain characteristics (some ideas, some are cash, some are both) at the end of each country, this can be done with a select statement in which section... this is what I don't get. I've tried the code in the details section, page header, page footer, you name it. It doesn't work. I checked and rechecked the code. And here you have lost me. Your sample showed Idea and a Count(?) of the idea. Now you are introducing characteristics and cash? So, if I am correct... where do I find all this glorious code! Thanks for your help John, I appreciate it! M "John Spencer" wrote: You seem to be asking multiple questions at once. I would suggest that we try to solve one question at a time. 1) Post the SQL of the crosstab query for specific advice on how you might modify it to get just five years. If you want to vary the number of years then we will hope that Duane Hookom is around and can point you to some sample databases on how to do this. 2) Instead of hard coding the years, use relative column names like Year1, Year2, Year3, Year4, and Year5. You can use code on a form or in a report to change the captions on labels or the control source of unbound text controls to show the year numbers. 3) If you have the start year and you are always going to have 5 years, you don't need to ask for the end year. Plus if you are limiting the report to 5 years and the user enters 2005 to 2014 they are going to be confused when they only get 2005 to 2009 data in the report. Are you doing this on a form or are you doing this in a report? "chickalina" wrote in message ... John, Also, I have a form that looks like thiswhich is only supposed to be a 5 year projection) Country Local 2007 2008 2009 2010 2011 Total Idea 5 5 5 5 5 25 State Idea 4 4 4 4 4 16 The yearly totals are taken from a Crosstab Query, but the years are hard coded. If I want 2012 included, I have to go back into the query and add that column. How can this be done automatically? Because I want to create a pop up form for the report where they can put a start and end date so the report only shows the 5 year window. How do I set up the report fields? OR, is this too involved? P.S. this is also the same report where I'm putting the SELECT statement in the footer. "chickalina" wrote: John, Thanks for the help... I'm going rename Value to QtrValue or something like that... but for now, the code still does not work. Might that be because of the placement on the report? It's in the footer now to give apply this equation to each section. I've tried with the brackets and without. Thanks. "John Spencer" wrote: It looks as if you have ther brackets incorrectly placed. SELECT [tbl_Ideas_Bank].[IdeaID] , [tbl_Ideas_Bank].[BenefitType] , [tbl_Quarter].[QtrEndDate] , [tbl_Quarter].[Value] FROM [tbl_Ideas_Bank], [ tbl_Quarter] WHERE ((([tbl_Ideas_Bank].[BenefitType])="ETR & Cash") AND ((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#))); And since you really don't need the brackets becuase your table and field names contain no spaces. That could be rewritten as SELECT tbl_Ideas_Bank.IdeaID , tbl_Ideas_Bank.BenefitType , tbl_Quarter.QtrEndDate , tbl_Quarter.Value FROM tbl_Ideas_Bank, tbl_Quarter WHERE tbl_Ideas_Bank.BenefitType="ETR & Cash" AND tbl_Quarter.QtrEndDate Between #1/1/2007# And #12/31/2007# I would be wary of using "Value" as a field name, since it is a reserved word in Access, but you are probably OK in the query. Elsewhere the use of the word Value could cause naming conflicts since most controls have a value property. "chickalina" wrote in message ... What's wrong with this code? It keeps giving me the ?Error message, and when I try to run the report, it says there's a problem. It runs in the query though. SELECT [tbl_Ideas_Bank.IdeaID]![ tbl_Ideas_Bank.BenefitType], [tbl_Quarter.QtrEndDate],[ tbl_Quarter.Value] FROM [tbl_Ideas_Bank],[ tbl_Quarter] WHERE (((tbl_Ideas_Bank.BenefitType)="ETR & Cash") AND ((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#))); Thanks. |
#16
|
|||
|
|||
SELECT Statement in a Report
I meant that I know that you are all helping... I didn't realise 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 combo box...? And 4: What do I use for the select statement in the Control Source for totaling for the year by Idea? 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 fields for 2007 for each country. I hope this is making sense. Thanks again. "John Spencer" wrote: RESPONSES in line. Sorry, but as a volunteer, I can't always get on line to answer further questions. chickalina wrote: I am trying to do this in a report. Let me see if I have this correct: 1. I do not need the crosstab query I've created. No, you probably do need the crosstab as the source of the report, but it sounds as if you may need to modify it so you can specify your period start and the names of the year columns. 2. for the "name" for the 5 years will be Year 1, Year 2, etc. and they will be in the page header. and take the year from the calculations in the detail section. You would have unbound controls in the report's page header or in a group header for the column labels. You would use a VBA formula in the format event of the relevant section to assign the value to the control. Assuming that you were getting the start year from a form, you could use something like the following to assign the value to the control txtControlYear1 = Forms![FormGetYear]![TxtYearNumberControl] + 0 txtControlYear2 = Forms![FormGetYear]![TxtYearNumberControl] + 1 txtControlYear3 = Forms![FormGetYear]![TxtYearNumberControl] + 2 txtControlYear4 = Forms![FormGetYear]![TxtYearNumberControl] + 3 txtControlYear5 = Forms![FormGetYear]![TxtYearNumberControl] + 4 3. The details section will hold the actual calculations for every idea, with a select statement, by year. No, the details section would be the fields (columns) from the crosstab 4. In order to calculate certain characteristics (some ideas, some are cash, some are both) at the end of each country, this can be done with a select statement in which section... this is what I don't get. I've tried the code in the details section, page header, page footer, you name it. It doesn't work. I checked and rechecked the code. And here you have lost me. Your sample showed Idea and a Count(?) of the idea. Now you are introducing characteristics and cash? So, if I am correct... where do I find all this glorious code! Thanks for your help John, I appreciate it! M "John Spencer" wrote: You seem to be asking multiple questions at once. I would suggest that we try to solve one question at a time. 1) Post the SQL of the crosstab query for specific advice on how you might modify it to get just five years. If you want to vary the number of years then we will hope that Duane Hookom is around and can point you to some sample databases on how to do this. 2) Instead of hard coding the years, use relative column names like Year1, Year2, Year3, Year4, and Year5. You can use code on a form or in a report to change the captions on labels or the control source of unbound text controls to show the year numbers. 3) If you have the start year and you are always going to have 5 years, you don't need to ask for the end year. Plus if you are limiting the report to 5 years and the user enters 2005 to 2014 they are going to be confused when they only get 2005 to 2009 data in the report. Are you doing this on a form or are you doing this in a report? "chickalina" wrote in message ... John, Also, I have a form that looks like thiswhich is only supposed to be a 5 year projection) Country Local 2007 2008 2009 2010 2011 Total Idea 5 5 5 5 5 25 State Idea 4 4 4 4 4 16 The yearly totals are taken from a Crosstab Query, but the years are hard coded. If I want 2012 included, I have to go back into the query and add that column. How can this be done automatically? Because I want to create a pop up form for the report where they can put a start and end date so the report only shows the 5 year window. How do I set up the report fields? OR, is this too involved? P.S. this is also the same report where I'm putting the SELECT statement in the footer. "chickalina" wrote: John, Thanks for the help... I'm going rename Value to QtrValue or something like that... but for now, the code still does not work. Might that be because of the placement on the report? It's in the footer now to give apply this equation to each section. I've tried with the brackets and without. Thanks. "John Spencer" wrote: It looks as if you have ther brackets incorrectly placed. SELECT [tbl_Ideas_Bank].[IdeaID] , [tbl_Ideas_Bank].[BenefitType] , [tbl_Quarter].[QtrEndDate] , [tbl_Quarter].[Value] FROM [tbl_Ideas_Bank], [ tbl_Quarter] WHERE ((([tbl_Ideas_Bank].[BenefitType])="ETR & Cash") AND ((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#))); And since you really don't need the brackets becuase your table and field names contain no spaces. That could be rewritten as SELECT tbl_Ideas_Bank.IdeaID , tbl_Ideas_Bank.BenefitType , tbl_Quarter.QtrEndDate , tbl_Quarter.Value FROM tbl_Ideas_Bank, tbl_Quarter WHERE tbl_Ideas_Bank.BenefitType="ETR & Cash" AND tbl_Quarter.QtrEndDate Between #1/1/2007# And #12/31/2007# I would be wary of using "Value" as a field name, since it is a reserved word in Access, but you are probably OK in the query. Elsewhere the use of the word Value could cause naming conflicts since most controls have a value property. "chickalina" wrote in message ... What's wrong with this code? It keeps giving me the ?Error message, and when I try to run the report, it says there's a problem. It runs in the query though. SELECT [tbl_Ideas_Bank.IdeaID]![ tbl_Ideas_Bank.BenefitType], [tbl_Quarter.QtrEndDate],[ tbl_Quarter.Value] FROM [tbl_Ideas_Bank],[ tbl_Quarter] WHERE (((tbl_Ideas_Bank.BenefitType)="ETR & Cash") AND ((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#))); Thanks. |
#17
|
|||
|
|||
SELECT Statement in a Report
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. |
#18
|
|||
|
|||
SELECT Statement in a Report
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. |
#19
|
|||
|
|||
SELECT Statement in a Report
Hi John...
3. Done... this worked perfectly!!! 4. The SELECT statement I am using (in the footer) is the following: SELECT [tbl_Ideas_Bank].[IdeaID], [tbl_Ideas_Bank].[BenefitType], [tbl_Quarter].[QtrEndDate], [tbl_Quarter].[Value] FROM [tbl_Ideas_Bank], [ tbl_Quarter] WHERE ((([tbl_Ideas_Bank].[BenefitType])="ETR & Cash") AND ((tbl_Quarter.QtrEndDate) Between #1/1/2007# And #12/31/2007#))); BUT... if we can use the [frm_CustomReports.cboYear] combo box, where I just pick the year.... that would be even better... Here's what the form ultimately should look like: Australia Structural Year1 Year2 Year3 Year4 Year5 Total Idea 1 5 5 5 5 5 25 Idea 2 2 2 2 2 2 10 Structural total 7 7 7 7 7 35 Non-Structural Year1 Year2 Year3 Year4 Year5 Total Idea 1 4 4 4 4 4 20 Idea 2 1 1 1 1 1 5 Non-Struct. tot 5 5 5 5 5 25 ETR 5 5 5 5 5 25 Cash 4 4 4 4 4 20 ETR & Cash 3 3 3 3 3 15 Australia Total 12 12 12 12 12 60 Each idea would fall under one of these three categories... each country starts on a new page. I hope this makes sense... "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. |
#20
|
|||
|
|||
SELECT Statement in a Report
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. |
Thread Tools | |
Display Modes | |
|
|