 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / November 2004How do I set up a report using dates as my report header?
I need to print a report showing the following: Name 8/26 8/27 8/28 8/29 8/30 Jane H S H H Bob H H H S Don S H H H Total H 2 2 3 2 Total S 1 1 1 I can print the report sorting by date and giving me a count on my variables but How do I print this sorting the dates going across the page and keeping the data with the Name field. I will be using a Beginning date and Ending date for the report. Thanks for all your help Robin Copying my response from your similar question in another thread... Substitute dates for months in this solution. Try not to use "absolute" column headings for dates. You could possibly use "relative" dates. For instance: Form: frmA Text Box: txtEndDate Table: tblSales Field: SaleDate You want to show 12 months of sales in columns of a crosstab report. Set the with menuing: Query|Parameter Forms!frmA!txtEndDate Date/Time Use this expression for your Column Headings: ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate) This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the same month as the ending date on your form. Mth1 is the previous month etc. Set your queries Column Headings property to: "Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11" Build your report based on these "relative" months. If you need column labels in your report, use text boxes with control sources of: =DateAdd("m",0,Forms!frmA!txtEndDate) =DateAdd("m",-1,Forms!frmA!txtEndDate) =DateAdd("m",-2,Forms!frmA!txtEndDate) =DateAdd("m",-3,Forms!frmA!txtEndDate) ... This solution requires no code and will run fairly quickly.  SignatureDuane Hookom MS Access MVP > I need to print a report showing the following: > [quoted text clipped - 14 lines] > > Robin Duane, I am still a little lost on how to do the report. Here is my goal. The report is to print students who are ordering Hot lunches(H) or Salads (S). The students order their lunches everyday. Now I need to produce a report that will print out the students name in one column and the order date in the other columns. Under the order date field it will print what ever the student ordered (H or S). I will total the number of H or S for each date. I'm sorry if I made this confusing. The first report that I designed listed the students in date order going down the page. I need it to look more like a spreadsheet report with dates going across the page. Thanks again for all your help Robin > Copying my response from your similar question in another thread... > Substitute dates for months in this solution. [quoted text clipped - 47 lines] > > > > Robin Could you share your current table structure with actual table and field names as well as some records? I also need to know how many date columns you expect to display and if these are always based on the current date. SignatureDuane Hookom MS Access MVP -- > Duane, > I am still a little lost on how to do the report. Here is my goal. The [quoted text clipped - 68 lines] >> > >> > Robin Hello, Table: Lunch Fields: LunchID AutoNumbering StudentName Text LunchType Text Date Number Grade Number Records: LunchID Student Name Grade Date LunchType 1 Joe Black 1 10/11/04 H 2 Joe Black 1 10/12/04 H 3 Joe Black 1 10/13/04 H 4 Joe Black 1 10/14/04 H 5 Joe Black 1 10/15/04 H 6 Joe Black 1 10/18/04 S 7 Joe Black 1 10/19/04 S 8 Kyle Hampton 2 10/11/04 H 9 Kyle Hampton 2 10/13/04 H 10 Kyle Hampton 2 10/14/04 S 11 Kyle Hampton 2 10/18/04 H I will be using 5 date columns (M T W TH F). I want to sort the report by grade and then list the students in alpha order with the dates going across the page along with the students name. I will put in a beginning date and ending date. That way I will be able to print a weekly report based on the days I entered. At the end of the report I will have a total of how many H or S I have per date. A total for each column. If this is not enough information please let me know. Thanks, Robin > Could you share your current table structure with actual table and field > names as well as some records? I also need to know how many date columns you [quoted text clipped - 72 lines] > >> > > >> > Robin Use my previous response but change month stuff to date/day stuff. If you always want 5 days then use only the ending date since the beginning date can be calculated. You column headings would use and expression like ColHead: "D" & DateDiff("D",[Date],[Forms]![frmDates]![txtEndDate]) Set the column headings property to D0,D1,D2,..D4 Change some of the other stuff from months to days. SignatureDuane Hookom MS Access MVP > Hello, > [quoted text clipped - 108 lines] > > >> > > > >> > Robin Duane, I am sorry, but I am still having trouble. I went to the crosstab query and clicked on the field Date and typed the expression in the column heading space. This is what I typed: ColHead: "D" & DateDiff("D",[Date],[Forms]![frmDates]![txtEndDate]). After I type that in, I try to open the query to check the data before I go any further. I get an error msg. Syntax error (missing operator) in query expression. What am I'm doing wrong. My input form name is called Lunch. I have listed previously the field names that are in the table. Am I'm using the right names in the above expression for the columning headings and also did I type it in the right place in the qurey? Thanks, Robin > Use my previous response but change month stuff to date/day stuff. If you > always want 5 days then use only the ending date since the beginning date [quoted text clipped - 130 lines] > > > >> > > > > >> > Robin Do you have a form open named frmDates with a text box named txtEndDate? Can you paste the SQL of your current query into a reply? SignatureDuane Hookom MS Access MVP > Duane, > [quoted text clipped - 148 lines] > > > > >> > > > > > >> > Robin Hello, This is my second time posting this message. It gave me errors during the first posting and was worried that it did not go through. Here is the SQL you requested I hope this helps. TRANSFORM Count([Student Roster for Lunch form].LunchType) AS [The Value] SELECT [Student Roster for Lunch form].Grade, [Student Roster for Lunch form].[Student Name], [Student Roster for Lunch form].LunchType, Count([Student Roster for Lunch form].LunchType) AS [Total Of LunchType] FROM [Student Roster for Lunch form] GROUP BY [Student Roster for Lunch form].Grade, [Student Roster for Lunch form].[Student Name], [Student Roster for Lunch form].LunchType PIVOT [Student Roster for Lunch form].Day; The input form name is Lunch. I enter a date in the field called Day in the input form. Did I set this up wrong? Thanks, Robin > Do you have a form open named frmDates with a text box named txtEndDate? Can > you paste the SQL of your current query into a reply? [quoted text clipped - 178 lines] > > > > > >> > > > > > > >> > Robin This doesn't look at all like the original table structure you provided. Do you actually use the word "Form" in a table or query name. This is so confusing I can't begin to understand this (at my age). What is your form name and ending date text box that controls/filters your crosstab? SignatureDuane Hookom MS Access MVP > Hello, > [quoted text clipped - 201 lines] > > > > > > >> > > > > > > > >> > Robin Duane, I have not added a filter for the beginning date and ending date. I was going to add Between [StartDate] And [EndDate] to the query design view in the Day field after I had finished the report layout. But it sounds like I need to something a little different. When creating the query I combined two tables. That query was called Student Roster for Lunch form(I should have used a different name). When I created the crosstab query I used the query that I just had set up and the crosstab query is named Student Roster for Lunch form_Crosstab1. SQL statement is from this crosstab query. The Form name (input form ) is called Lunch. It is a very basic input form. It consist of the following fields: Day, LunchID, Student Name, LunchType Did I confuse you more? Sorry for all the mess, please get me out of it. Robin > This doesn't look at all like the original table structure you provided. Do > you actually use the word "Form" in a table or query name. This is so [quoted text clipped - 235 lines] > > > > > > > >> > > > > > > > > >> > Robin My solution depends on two things, 1) your table with the appropriate fields 2) your text box on a form which is used to compare with a date field from your table I would suggest that you don't any further with this solution until you have a form with a text box to enter the ending or beginning date. SignatureDuane Hookom MS Access MVP > Duane, > [quoted text clipped - 229 lines] > > > > > > > > >> ... > > > > > > > > >> This solution requires no code and will run fairly quickly. news:DAA1A067-865C-477C-A41D-E20DFCAC81F9@microsoft.com... > > > > > > > > >> > I need to print a report showing the following: > > > > > > > > >> > [quoted text clipped - 23 lines] > > > > > > > > >> > > > > > > > > > >> > Robin Duane, I did as you said. I have set up a new form called frmReportSelect to compare the date fields. The fields are called txtStartDate and txtEndDate. I actually did some research and found http://allenbrowne.com/tips.html/ website which I found very useful. The textboxes are unbound in the new form that I created. I am having a better understanding on what you were telling me earlier. After I set up the form and textfields I went back to my crosstab query. Before I changed or added anything to the query I did a preview of the data. Everything looks good. The dates are going across the page with correct information under the dates. The next step was to be able to change the date field column heading to D0.D1,D2,D3,D4 in the properties section of the date field in the query. I then continued to add the expression to the crietiera section "D" & DateDiff("D",[Day],[Forms]![frmReportSelect]![txtEndDate]) I then went to the query/Parameter and added this Forms!frmReportSelect!txtEndDate Date/Time After I made all these changes I did a preview of my data and came up with nothing. If I go back and delete what I add to the column headings in the query I get the data back to the original way with the dates going across the page with the correct information under the dates. Now my question is why does the query not work when I add the D0,D1,D2,,,to the column heading properties? My dates will always be changing so when I do a report I will need to reflect the change on my dates. Thanks Robin Thanks Robin > My solution depends on two things, > 1) your table with the appropriate fields [quoted text clipped - 315 lines] > > > > > > > > > >> > > > > > > > > > > >> > Robin You need to create the Column Headings field as ColHead:"D" & DateDiff("D",[Day],[Forms]![frmReportSelect]![txtEndDate]) This does not go in your criteria. SignatureDuane Hookom MS Access MVP -- > Duane, > [quoted text clipped - 405 lines] >> > > > > > > > > >> > >> > > > > > > > > >> > Robin Duane, I think I need just a little more help. I set up the Column heading as you said. The way I did this was in the value section of the crosstab query I changed the following to read as: Field: ColHead:"D" & DateDiff("D",[Day],[Forms]![frmReportSelect]![txtEndDate]) Table: Total: Expression Crosstab: Value Sort: Critera Now the only problem is when the column prints across the page it puts D0,D1,D2,,D3,D4 as my column heading, which is good But the letter D is printed under the column headings, Not the field data that I need. Could you please give me a little more help. Thanks, Robin > You need to create the Column Headings field as > ColHead:"D" & DateDiff("D",[Day],[Forms]![frmReportSelect]![txtEndDate]) [quoted text clipped - 410 lines] > >> > > > > > > > > >> > > >> > > > > > > > > >> > Robin I'm not sure why you used the Column Heading expression as the Value. Your value is what would display in the "grid" of the crosstab. I expect this would be First of [LunchType]. SignatureDuane Hookom MS Access MVP > Duane, > [quoted text clipped - 209 lines] > > >> > > > > > > D0,D1,D2,..D4 > > >> > > > > > > Change some of the other stuff from months to days. news:53EA0C29-635A-490C-8269-CF798F45BE28@microsoft.com... > > >> > > > > > > > Hello, > > >> > > > > > > > [quoted text clipped - 81 lines] > > >> current > > >> > > date. news:D365621F-1113-4AFE-8094-1A11ED5177BD@microsoft.com... > > >> > > > > > > > > > Duane, > > >> > > > > > > > > > I am still a little lost on how to do the report. Here [quoted text clipped - 135 lines] > > >> > > > > > > > > >> > > > >> > > > > > > > > >> > Robin I want to thank you for guiding me in the right direction. I have finally figured out what to do. The report runs great. Thanks, Robin > I'm not sure why you used the Column Heading expression as the Value. Your > value is what would display in the "grid" of the crosstab. I expect this [quoted text clipped - 501 lines] > > > >> > > > > > > > > >> > > > > >> > > > > > > > > >> > Robin |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.