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 |
#20
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|