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
|
|||
|
|||
Still Hoping for help with a Query problem
Anyone interested in helping please let me know. I did get a reply (below)
but unfortunately I don't understand it. Still looking, And thanks to all, Don........... Brett, I'm not sure how to reply to your instructions. I replied to your post but it came back as underliverable. Here's my questions Actually I meant 22 products but you're exactly right, one line for each product and the qty for it. I already have a report that shows this data but it doesn't include the date. So I've got a report that shows each product on it's own line and shows the total quantities for each. Now I'd like to be able to show this data in time periods. I don't understand a whole lot of this. There are many features I've never used in Access. I'd like to break this down one or two questions at a time if you don't mind. Questions: You say "make sure you have Sorting & Grouping by Product in your report. I'm not sure what you mean nor do I know how to do that. I can find Sorting & Grouping on the View menu but I'm not sure how to use it or what you want me to do with it. Let's start with that. You're right , I do have lots of questions. Thanks for your help, Don........ Brett Collings [429338]" wrote in message . .. Don, where you say you want to "group" all 162 entries into just the 2 products, I think what you mean is that you want just one line for each product and the Sum() of the Quantities and Defects. Is this correct? If it is, here is a quick suggestion. Make a copy og that Report so that as we play with it, it doesn't matter if we break it. Once you have that feeling of freedom, you'll be a little more adventurous. - Make sure you have Sorting & Grouping by Product in your report - open Group headers and footers (Menu-View-Grou....) - put your controls in the detail section of the group band - Name each control correctly with a simple Naming Convention prefix like txt, num, dte, cbo (Report Design-Properties-Other-Name). The Name of a control MUST be different from the ControlSource. - in the Group footer add a new text box for each of the fields that you want to total and make their controlSource things like Sum(numDefects) or Count(txtDefects). Note that we are using the NAME of the control in the detail band and NOT it's ControlSource. - at this point I go to the trouble on NAMING each of these calculation controls with the prefix "cal" and a descriptive name for it. Once you get into code behind the form, unclear naming like "Text442" really kills you. - click the Details bar, go to it's Properties and mark it invisible Preview the report and all you will see are the totals! The trick is that the detail band information (the 162 records) must be there, you've just made them invisible. Because you have set a grouping for Product, you will get 22 bands displaying totals. Piece of cake huh That's a quick and dirty ... there'll be a lot of tidying up and you'll have more questions ... ask away Brett |
#2
|
|||
|
|||
Don,
I'm still not sure I understand what you are trying to do. Would you give some sample data? So far I know you have 22 products and 30 columns but what does the report look like? Product Qty Widget1 40 Widget2 10 Digit1 15 Digit2 50 Gidget1 33 .. .. .. Item22 13 Are there more columns in the report? I know you want the month/year in there somewhere. What version of access are you using? If you want to send me a cut down version (maybe 500 lines in the table), I have A2K (or A97 is ok). See my email below. Questions: You say "make sure you have Sorting & Grouping by Product in your report. I'm not sure what you mean nor do I know how to do that. I can find Sorting & Grouping on the View menu but I'm not sure how to use it or what you want me to do with it. Grouping is like having unlimited sub reports. Grouping adds more sections to the report. You can sort within the grouping and each group has its own header and footer. In the group header you can put the field that you are grouping by and remove it from the Detail section. Say you have a report that has two sub-reports: This month and last month. Now your boss wants to see this all of this years data by month. You could try and add 10 more sub-reports......well you could try. :0 Or you could get rid of the sub-reports and use grouping. You might have to change the record source a little to get the month/year into separate fields (in the query). Add a group section (header) for the month, put the month field in the group header and the rest of the fields you want to se in the detail section. Set the sorting (if you want) and your report is done! If you wanted to see 5 years of data by year-month, the grouping would be 1) Year field and 2) Month field. Put the year field in the first group header and the month in the second group header. The Detail section gets the rest of the fields you need in the report. Try it on a *copy* of a report...... Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) sanfu at techie dot com replace the at and dot with the symbols & no spaces "Don Sealer" wrote: Anyone interested in helping please let me know. I did get a reply (below) but unfortunately I don't understand it. Still looking, And thanks to all, Don........... Brett, I'm not sure how to reply to your instructions. I replied to your post but it came back as underliverable. Here's my questions Actually I meant 22 products but you're exactly right, one line for each product and the qty for it. I already have a report that shows this data but it doesn't include the date. So I've got a report that shows each product on it's own line and shows the total quantities for each. Now I'd like to be able to show this data in time periods. I don't understand a whole lot of this. There are many features I've never used in Access. I'd like to break this down one or two questions at a time if you don't mind. Questions: You say "make sure you have Sorting & Grouping by Product in your report. I'm not sure what you mean nor do I know how to do that. I can find Sorting & Grouping on the View menu but I'm not sure how to use it or what you want me to do with it. Let's start with that. You're right , I do have lots of questions. Thanks for your help, Don........ |
#3
|
|||
|
|||
Steve,
Let's see if I can do this better than before. I have 22 products that I track quantites built and defects for each product. So I have a report that has 22 rows (one for each product) and about 30 columns (one for each quantity or defect associated with the product). Kinda looks like this: Product Qty Built Defect 1 Defect 2 Defect 3 Defect 4; etc Product1 100 1 2 3 1 Product2 10 0 1 4 2 Product3 121 4 0 5 2 etc I have a report that shows all of this and works very well. What I need to be able to do is show this report for time periods. For instance the month of October. When I do this, because there are multiple entries for each day, my report then has a row for each entry so instead of having a report showing 22 products, one row for each product, I have a report that might have a hundred rows, one for each entry. Product1 for instance may have 5 separate entries. Actually I don't need to show the time period on the report I just need to be able to show the data from specific time periods. Does this explain it any better? Hope so, Thanks for your interest, Don........ "SteveS" sanfu at techie dot com wrote in message ... Don, I'm still not sure I understand what you are trying to do. Would you give some sample data? So far I know you have 22 products and 30 columns but what does the report look like? Product Qty Widget1 40 Widget2 10 Digit1 15 Digit2 50 Gidget1 33 . . . Item22 13 Are there more columns in the report? I know you want the month/year in there somewhere. What version of access are you using? If you want to send me a cut down version (maybe 500 lines in the table), I have A2K (or A97 is ok). See my email below. Questions: You say "make sure you have Sorting & Grouping by Product in your report. I'm not sure what you mean nor do I know how to do that. I can find Sorting & Grouping on the View menu but I'm not sure how to use it or what you want me to do with it. Grouping is like having unlimited sub reports. Grouping adds more sections to the report. You can sort within the grouping and each group has its own header and footer. In the group header you can put the field that you are grouping by and remove it from the Detail section. Say you have a report that has two sub-reports: This month and last month. Now your boss wants to see this all of this years data by month. You could try and add 10 more sub-reports......well you could try. :0 Or you could get rid of the sub-reports and use grouping. You might have to change the record source a little to get the month/year into separate fields (in the query). Add a group section (header) for the month, put the month field in the group header and the rest of the fields you want to se in the detail section. Set the sorting (if you want) and your report is done! If you wanted to see 5 years of data by year-month, the grouping would be 1) Year field and 2) Month field. Put the year field in the first group header and the month in the second group header. The Detail section gets the rest of the fields you need in the report. Try it on a *copy* of a report...... Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) sanfu at techie dot com replace the at and dot with the symbols & no spaces "Don Sealer" wrote: Anyone interested in helping please let me know. I did get a reply (below) but unfortunately I don't understand it. Still looking, And thanks to all, Don........... Brett, I'm not sure how to reply to your instructions. I replied to your post but it came back as underliverable. Here's my questions Actually I meant 22 products but you're exactly right, one line for each product and the qty for it. I already have a report that shows this data but it doesn't include the date. So I've got a report that shows each product on it's own line and shows the total quantities for each. Now I'd like to be able to show this data in time periods. I don't understand a whole lot of this. There are many features I've never used in Access. I'd like to break this down one or two questions at a time if you don't mind. Questions: You say "make sure you have Sorting & Grouping by Product in your report. I'm not sure what you mean nor do I know how to do that. I can find Sorting & Grouping on the View menu but I'm not sure how to use it or what you want me to do with it. Let's start with that. You're right , I do have lots of questions. Thanks for your help, Don........ |
#4
|
|||
|
|||
Steve
You are 3/4's there. Assuming that you have a date field, just add the date field (month) as a group. the results would look something like this October product qty defect1 defect2 abc 10/01 12 1 2 abc 10/10 10 2 1 abc 10/20 5 1 1 xyz 10/12 20 0 1 Use a query to create a date (month) field The query would contain month product date qty defect1 defect2 Your report sort sequence would be month (group) sort product, date You can also play with the grouping ....Month, Product ..to get sub totals by product "Don Sealer" wrote in message news:dv3jd.1055$vH3.228@trndny06... Steve, Let's see if I can do this better than before. I have 22 products that I track quantites built and defects for each product. So I have a report that has 22 rows (one for each product) and about 30 columns (one for each quantity or defect associated with the product). Kinda looks like this: Product Qty Built Defect 1 Defect 2 Defect 3 Defect 4; etc Product1 100 1 2 3 1 Product2 10 0 1 4 2 Product3 121 4 0 5 2 etc I have a report that shows all of this and works very well. What I need to be able to do is show this report for time periods. For instance the month of October. When I do this, because there are multiple entries for each day, my report then has a row for each entry so instead of having a report showing 22 products, one row for each product, I have a report that might have a hundred rows, one for each entry. Product1 for instance may have 5 separate entries. Actually I don't need to show the time period on the report I just need to be able to show the data from specific time periods. Does this explain it any better? Hope so, Thanks for your interest, Don........ "SteveS" sanfu at techie dot com wrote in message ... Don, I'm still not sure I understand what you are trying to do. Would you give some sample data? So far I know you have 22 products and 30 columns but what does the report look like? Product Qty Widget1 40 Widget2 10 Digit1 15 Digit2 50 Gidget1 33 . . . Item22 13 Are there more columns in the report? I know you want the month/year in there somewhere. What version of access are you using? If you want to send me a cut down version (maybe 500 lines in the table), I have A2K (or A97 is ok). See my email below. Questions: You say "make sure you have Sorting & Grouping by Product in your report. I'm not sure what you mean nor do I know how to do that. I can find Sorting & Grouping on the View menu but I'm not sure how to use it or what you want me to do with it. Grouping is like having unlimited sub reports. Grouping adds more sections to the report. You can sort within the grouping and each group has its own header and footer. In the group header you can put the field that you are grouping by and remove it from the Detail section. Say you have a report that has two sub-reports: This month and last month. Now your boss wants to see this all of this years data by month. You could try and add 10 more sub-reports......well you could try. :0 Or you could get rid of the sub-reports and use grouping. You might have to change the record source a little to get the month/year into separate fields (in the query). Add a group section (header) for the month, put the month field in the group header and the rest of the fields you want to se in the detail section. Set the sorting (if you want) and your report is done! If you wanted to see 5 years of data by year-month, the grouping would be 1) Year field and 2) Month field. Put the year field in the first group header and the month in the second group header. The Detail section gets the rest of the fields you need in the report. Try it on a *copy* of a report...... Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) sanfu at techie dot com replace the at and dot with the symbols & no spaces "Don Sealer" wrote: Anyone interested in helping please let me know. I did get a reply (below) but unfortunately I don't understand it. Still looking, And thanks to all, Don........... Brett, I'm not sure how to reply to your instructions. I replied to your post but it came back as underliverable. Here's my questions Actually I meant 22 products but you're exactly right, one line for each product and the qty for it. I already have a report that shows this data but it doesn't include the date. So I've got a report that shows each product on it's own line and shows the total quantities for each. Now I'd like to be able to show this data in time periods. I don't understand a whole lot of this. There are many features I've never used in Access. I'd like to break this down one or two questions at a time if you don't mind. Questions: You say "make sure you have Sorting & Grouping by Product in your report. I'm not sure what you mean nor do I know how to do that. I can find Sorting & Grouping on the View menu but I'm not sure how to use it or what you want me to do with it. Let's start with that. You're right , I do have lots of questions. Thanks for your help, Don........ |
#5
|
|||
|
|||
Don,
Yes, I (think I) understand better. How was the explaination on "Grouping & Sorting"? IMPORTANT: Make sure you are working on a COPY of the database OR a copy of the report and query! OK, here goes........ The record source for the report is a Totals query, right? In design view of the query, the "Totals" button in the toolbar is hi-lighted? You need to add one more field to this query - the date field. 1) In an empty column of the grid, paste the next line, changing "TheDate" to the name of your date field: Period: Format([TheDate],"mm/yyyy") 2) On the design grid, leave the "Total" line to "Group By" for the date column. 3) Save the query. You ARE using a copy, RIGHT??? :- 4) Open the report in design view. 5) Open the Sorting & Grouping dialog box. (Menu/View/Sorting and Grouping) 6) Click in the first row under 'Field/Expression' 7) Select "Period". 8) Change 'Group Header' to YES (look below the grid) 9) Click on the second row under 'Field/Expression' 10) Select "Product" 11) Close the dialog box. Almost done, hang in there... Notice that there is now a section named "Period Header" above the Details section. 12) Put a control (text box) in the Period Header section. Set the record source for the control to "Period" 13) The rest of the fields (Product, Defect1, Defect2,..., Defect30) should be in the Details section already. Save the report. Open it. Is this what you want? Are the numbers right?? Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "Don Sealer" wrote: Steve, Let's see if I can do this better than before. I have 22 products that I track quantites built and defects for each product. So I have a report that has 22 rows (one for each product) and about 30 columns (one for each quantity or defect associated with the product). Kinda looks like this: Product Qty Built Defect 1 Defect 2 Defect 3 Defect 4; etc Product1 100 1 2 3 1 Product2 10 0 1 4 2 Product3 121 4 0 5 2 etc I have a report that shows all of this and works very well. What I need to be able to do is show this report for time periods. For instance the month of October. When I do this, because there are multiple entries for each day, my report then has a row for each entry so instead of having a report showing 22 products, one row for each product, I have a report that might have a hundred rows, one for each entry. Product1 for instance may have 5 separate entries. Actually I don't need to show the time period on the report I just need to be able to show the data from specific time periods. Does this explain it any better? Hope so, Thanks for your interest, Don........ "SteveS" sanfu at techie dot com wrote in message ... Don, I'm still not sure I understand what you are trying to do. Would you give some sample data? So far I know you have 22 products and 30 columns but what does the report look like? Product Qty Widget1 40 Widget2 10 Digit1 15 Digit2 50 Gidget1 33 . . . Item22 13 Are there more columns in the report? I know you want the month/year in there somewhere. What version of access are you using? If you want to send me a cut down version (maybe 500 lines in the table), I have A2K (or A97 is ok). See my email below. Questions: You say "make sure you have Sorting & Grouping by Product in your report. I'm not sure what you mean nor do I know how to do that. I can find Sorting & Grouping on the View menu but I'm not sure how to use it or what you want me to do with it. Grouping is like having unlimited sub reports. Grouping adds more sections to the report. You can sort within the grouping and each group has its own header and footer. In the group header you can put the field that you are grouping by and remove it from the Detail section. Say you have a report that has two sub-reports: This month and last month. Now your boss wants to see this all of this years data by month. You could try and add 10 more sub-reports......well you could try. :0 Or you could get rid of the sub-reports and use grouping. You might have to change the record source a little to get the month/year into separate fields (in the query). Add a group section (header) for the month, put the month field in the group header and the rest of the fields you want to se in the detail section. Set the sorting (if you want) and your report is done! If you wanted to see 5 years of data by year-month, the grouping would be 1) Year field and 2) Month field. Put the year field in the first group header and the month in the second group header. The Detail section gets the rest of the fields you need in the report. Try it on a *copy* of a report...... Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) sanfu at techie dot com replace the at and dot with the symbols & no spaces "Don Sealer" wrote: Anyone interested in helping please let me know. I did get a reply (below) but unfortunately I don't understand it. Still looking, And thanks to all, Don........... Brett, I'm not sure how to reply to your instructions. I replied to your post but it came back as underliverable. Here's my questions Actually I meant 22 products but you're exactly right, one line for each product and the qty for it. I already have a report that shows this data but it doesn't include the date. So I've got a report that shows each product on it's own line and shows the total quantities for each. Now I'd like to be able to show this data in time periods. I don't understand a whole lot of this. There are many features I've never used in Access. I'd like to break this down one or two questions at a time if you don't mind. Questions: You say "make sure you have Sorting & Grouping by Product in your report. I'm not sure what you mean nor do I know how to do that. I can find Sorting & Grouping on the View menu but I'm not sure how to use it or what you want me to do with it. Let's start with that. You're right , I do have lots of questions. Thanks for your help, Don....... |
#6
|
|||
|
|||
That's cool how that works, I think I'm learning something. Acutally it's
not what I was looking for. I also think I should add to my example. Product Qty Built Defect 1 Defect 2 Defect 3 Defect 4; etc Product1 100 1 2 3 1 Product2 10 0 1 4 2 Product3 121 4 0 5 2 etc TOTALS 231 5 3 12 5 Your suggestion grouped everything by month (which may come in handy in the future) but in doing so I lost the bottom line (TOTALS). I was really hoping to be able to somehow, either by query or somewhere in the report, select dates. In some cases it might be a particular month, in other cases it might be a couple of months, still at another time it might only be a week or a couple of days. I'm not sure I'm making myself clear but if you don't mind responding I certainly don't mind your help. Your last suggestion was very helpful, if only for future reference. Thanks again, Don.......... "SteveS" sanfu at techie dot com wrote in message ... Don, Yes, I (think I) understand better. How was the explaination on "Grouping & Sorting"? IMPORTANT: Make sure you are working on a COPY of the database OR a copy of the report and query! OK, here goes........ The record source for the report is a Totals query, right? In design view of the query, the "Totals" button in the toolbar is hi-lighted? You need to add one more field to this query - the date field. 1) In an empty column of the grid, paste the next line, changing "TheDate" to the name of your date field: Period: Format([TheDate],"mm/yyyy") 2) On the design grid, leave the "Total" line to "Group By" for the date column. 3) Save the query. You ARE using a copy, RIGHT??? :- 4) Open the report in design view. 5) Open the Sorting & Grouping dialog box. (Menu/View/Sorting and Grouping) 6) Click in the first row under 'Field/Expression' 7) Select "Period". 8) Change 'Group Header' to YES (look below the grid) 9) Click on the second row under 'Field/Expression' 10) Select "Product" 11) Close the dialog box. Almost done, hang in there... Notice that there is now a section named "Period Header" above the Details section. 12) Put a control (text box) in the Period Header section. Set the record source for the control to "Period" 13) The rest of the fields (Product, Defect1, Defect2,..., Defect30) should be in the Details section already. Save the report. Open it. Is this what you want? Are the numbers right?? Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "Don Sealer" wrote: Steve, Let's see if I can do this better than before. I have 22 products that I track quantites built and defects for each product. So I have a report that has 22 rows (one for each product) and about 30 columns (one for each quantity or defect associated with the product). Kinda looks like this: Product Qty Built Defect 1 Defect 2 Defect 3 Defect 4; etc Product1 100 1 2 3 1 Product2 10 0 1 4 2 Product3 121 4 0 5 2 etc I have a report that shows all of this and works very well. What I need to be able to do is show this report for time periods. For instance the month of October. When I do this, because there are multiple entries for each day, my report then has a row for each entry so instead of having a report showing 22 products, one row for each product, I have a report that might have a hundred rows, one for each entry. Product1 for instance may have 5 separate entries. Actually I don't need to show the time period on the report I just need to be able to show the data from specific time periods. Does this explain it any better? Hope so, Thanks for your interest, Don........ "SteveS" sanfu at techie dot com wrote in message ... Don, I'm still not sure I understand what you are trying to do. Would you give some sample data? So far I know you have 22 products and 30 columns but what does the report look like? Product Qty Widget1 40 Widget2 10 Digit1 15 Digit2 50 Gidget1 33 . . . Item22 13 Are there more columns in the report? I know you want the month/year in there somewhere. What version of access are you using? If you want to send me a cut down version (maybe 500 lines in the table), I have A2K (or A97 is ok). See my email below. Questions: You say "make sure you have Sorting & Grouping by Product in your report. I'm not sure what you mean nor do I know how to do that. I can find Sorting & Grouping on the View menu but I'm not sure how to use it or what you want me to do with it. Grouping is like having unlimited sub reports. Grouping adds more sections to the report. You can sort within the grouping and each group has its own header and footer. In the group header you can put the field that you are grouping by and remove it from the Detail section. Say you have a report that has two sub-reports: This month and last month. Now your boss wants to see this all of this years data by month. You could try and add 10 more sub-reports......well you could try. :0 Or you could get rid of the sub-reports and use grouping. You might have to change the record source a little to get the month/year into separate fields (in the query). Add a group section (header) for the month, put the month field in the group header and the rest of the fields you want to se in the detail section. Set the sorting (if you want) and your report is done! If you wanted to see 5 years of data by year-month, the grouping would be 1) Year field and 2) Month field. Put the year field in the first group header and the month in the second group header. The Detail section gets the rest of the fields you need in the report. Try it on a *copy* of a report...... Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) sanfu at techie dot com replace the at and dot with the symbols & no spaces "Don Sealer" wrote: Anyone interested in helping please let me know. I did get a reply (below) but unfortunately I don't understand it. Still looking, And thanks to all, Don........... Brett, I'm not sure how to reply to your instructions. I replied to your post but it came back as underliverable. Here's my questions Actually I meant 22 products but you're exactly right, one line for each product and the qty for it. I already have a report that shows this data but it doesn't include the date. So I've got a report that shows each product on it's own line and shows the total quantities for each. Now I'd like to be able to show this data in time periods. I don't understand a whole lot of this. There are many features I've never used in Access. I'd like to break this down one or two questions at a time if you don't mind. Questions: You say "make sure you have Sorting & Grouping by Product in your report. I'm not sure what you mean nor do I know how to do that. I can find Sorting & Grouping on the View menu but I'm not sure how to use it or what you want me to do with it. Let's start with that. You're right , I do have lots of questions. Thanks for your help, Don....... |
#7
|
|||
|
|||
I see, said the blind man as he picked up his hammer and saw..... g
OK Don, how are you opening the report? Do you have a form that shows all of the reports and you select one, then click a button? Or is there just a button that opens the form? ------------------------------------------------------------------- The quick and dirty way to select records in a date range: ------------------------------------------------------------------- (You ARE working on a COPY, right?) 1) Make a new copy of the original report and query. 2) Open the query in design view 3) In the top of the window, double click on the date field 4) The date field should have been added to the grid 5) UNCHECK the check mark for that is in the date column (you don't want to see it) 6) In the Criteria row in the date column enter: Between [StartDate] And [EndDate] 7) Save the query (click the floppy disk in the toolbar) 8) Run the query (click on the red exclaimation point in the toolbar) EVERY time you run the query, open a report or form based on the query, you will get dialog boxs asking for a Start date and an End date. ------------------------------------------------------------------- The Not so quick (but better) way to select records in a date range: ------------------------------------------------------------------- If you have a form that you can put two unbound controls (text boxes) on, you can do: validation checks, have default ranges, have the date ranges in the header of the report , .... all kinds of things Let's say you have a simple dB with 3 reports. To open the reports, you have three buttons, one for each report, on a form named 'frmReportMenu'. 1) On this form add two text box controls so the layout looks like this: txtbox1 txtbox2 button 2) Change the name of txtbox1 to StartDate 3) Change the name of txtbox2 to EndDate (You can set the Input Mask and Format later) 4) Open the query in design view. In grid for the Criteria row in the Date column (the one with the check box unchecked), change the criteria to (watch for line wrap): Between [Forms]![frmReportMenu].[StartDate] And [Forms]![frmReportMenu].[EndDate] Don't forget to change frmReportMenu to the name of your form!!!! 5) Save the query and the Report Menu form. Note that there are no changes to the report. The code for the button (if you used a wizard) should open the report. If not, the OnClick event of the button should have: DoCmd.OpenReport "Report1", acPreview where "Report1" should be the name of your report. Done!! ---------------------- To see your report: -Open the report menu or whatever form the button is on -Fill in the start date and end date -Click on the Open Report button -As the report opens, it runs the query -The query sees it has two parameters located on the form frmReportMenu -It gets the dates, the recordset is generated and the report completes opening, displaying the data found (if any). Is this closer to what you want? Clear as mud??? Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "Don Sealer" wrote: That's cool how that works, I think I'm learning something. Acutally it's not what I was looking for. I also think I should add to my example. Product Qty Built Defect 1 Defect 2 Defect 3 Defect 4; etc Product1 100 1 2 3 1 Product2 10 0 1 4 2 Product3 121 4 0 5 2 etc TOTALS 231 5 3 12 5 Your suggestion grouped everything by month (which may come in handy in the future) but in doing so I lost the bottom line (TOTALS). I was really hoping to be able to somehow, either by query or somewhere in the report, select dates. In some cases it might be a particular month, in other cases it might be a couple of months, still at another time it might only be a week or a couple of days. I'm not sure I'm making myself clear but if you don't mind responding I certainly don't mind your help. Your last suggestion was very helpful, if only for future reference. Thanks again, Don.......... "SteveS" sanfu at techie dot com wrote in message ... Don, Yes, I (think I) understand better. How was the explaination on "Grouping & Sorting"? IMPORTANT: Make sure you are working on a COPY of the database OR a copy of the report and query! OK, here goes........ The record source for the report is a Totals query, right? In design view of the query, the "Totals" button in the toolbar is hi-lighted? You need to add one more field to this query - the date field. 1) In an empty column of the grid, paste the next line, changing "TheDate" to the name of your date field: Period: Format([TheDate],"mm/yyyy") 2) On the design grid, leave the "Total" line to "Group By" for the date column. 3) Save the query. You ARE using a copy, RIGHT??? :- 4) Open the report in design view. 5) Open the Sorting & Grouping dialog box. (Menu/View/Sorting and Grouping) 6) Click in the first row under 'Field/Expression' 7) Select "Period". 8) Change 'Group Header' to YES (look below the grid) 9) Click on the second row under 'Field/Expression' 10) Select "Product" 11) Close the dialog box. Almost done, hang in there... Notice that there is now a section named "Period Header" above the Details section. 12) Put a control (text box) in the Period Header section. Set the record source for the control to "Period" 13) The rest of the fields (Product, Defect1, Defect2,..., Defect30) should be in the Details section already. Save the report. Open it. Is this what you want? Are the numbers right?? Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "Don Sealer" wrote: Steve, Let's see if I can do this better than before. I have 22 products that I track quantites built and defects for each product. So I have a report that has 22 rows (one for each product) and about 30 columns (one for each quantity or defect associated with the product). Kinda looks like this: Product Qty Built Defect 1 Defect 2 Defect 3 Defect 4; etc Product1 100 1 2 3 1 Product2 10 0 1 4 2 Product3 121 4 0 5 2 etc I have a report that shows all of this and works very well. What I need to be able to do is show this report for time periods. For instance the month of October. When I do this, because there are multiple entries for each day, my report then has a row for each entry so instead of having a report showing 22 products, one row for each product, I have a report that might have a hundred rows, one for each entry. Product1 for instance may have 5 separate entries. Actually I don't need to show the time period on the report I just need to be able to show the data from specific time periods. Does this explain it any better? Hope so, Thanks for your interest, Don........ "SteveS" sanfu at techie dot com wrote in message ... Don, I'm still not sure I understand what you are trying to do. Would you give some sample data? So far I know you have 22 products and 30 columns but what does the report look like? Product Qty Widget1 40 Widget2 10 Digit1 15 Digit2 50 Gidget1 33 . . . Item22 13 Are there more columns in the report? I know you want the month/year in there somewhere. What version of access are you using? If you want to send me a cut down version (maybe 500 lines in the table), I have A2K (or A97 is ok). See my email below. Questions: You say "make sure you have Sorting & Grouping by Product in your report. I'm not sure what you mean nor do I know how to do that. I can find Sorting & Grouping on the View menu but I'm not sure how to use it or what you want me to do with it. Grouping is like having unlimited sub reports. Grouping adds more sections to the report. You can sort within the grouping and each group has its own header and footer. In the group header you can put the field that you are grouping by and remove it from the Detail section. Say you have a report that has two sub-reports: This month and last month. Now your boss wants to see this all of this years data by month. You could try and add 10 more sub-reports......well you could try. :0 Or you could get rid of the sub-reports and use grouping. You might have to change the record source a little to get the month/year into separate fields (in the query). Add a group section (header) for the month, put the month field in the group header and the rest of the fields you want to se in the detail section. Set the sorting (if you want) and your report is done! If you wanted to see 5 years of data by year-month, the grouping would be 1) Year field and 2) Month field. Put the year field in the first group header and the month in the second group header. The Detail section gets the rest of the fields you need in the report. Try it on a *copy* of a report...... Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) sanfu at techie dot com replace the at and dot with the symbols & no spaces "Don Sealer" wrote: Anyone interested in helping please let me know. I did get a reply (below) but unfortunately I don't understand it. Still looking, And thanks to all, Don........... Brett, I'm not sure how to reply to your instructions. I replied to your post but it came back as underliverable. Here's my questions Actually I meant 22 products but you're exactly right, one line for each product and the qty for it. I already have a report that shows this data but it doesn't include the date. So I've got a report that shows each product on it's own line and shows the total quantities for each. Now I'd like to be able to show this data in time periods. I don't understand a whole lot of this. There are many features I've never used in Access. I'd like to break this down one or two questions at a time if you don't mind. Questions: You say "make sure you have Sorting & Grouping by Product in your report. I'm not sure what you mean nor do I know how to do that. I can find Sorting & Grouping on the View menu but I'm not sure how to use it or what you want me to do with it. Let's start with that. You're right , I do have lots of questions. Thanks for your help, Don....... |
#8
|
|||
|
|||
Steve,
I did this but it returns every record for that time period. I'm really looking to group the time period somehow. In other words I may have 20 entries for one product and 10 entries for another and so on. My report now shows only one entry for each product with accumulated totals for quantities and defects. When I do a start/end date in the query I see all 20 entries for the one product and the 10 entries for the other product and so on again. Another thing came up while I was working with the previous suggestion you gave me. I'm using the "period" idea with sorting and grouping like you suggested. I think this is something I can use most of the time. However there is a formatting problem. Your sorting and grouping suggestion sorts the data by months and shows it in the report. What happens is the individual months come up on parts of the report page. I figured out that it's because I don't always have all of the products being produced each month and so the number of lines change for each month thus the formatting changes in the report. I decided to go into the data and just add an entry for each item that was not produced for the month. That way the report will always have the same amount of lines and the formatting will always be the same. The problem with this is I get an error report that says "Cannot open a form whose underlying query contains a user-defined function that attempts to set or get the form's RecordsetClone Property". I'm not sure what that means but I figured out it's because I'm not entering a quantity into the Total Tested field. Any suggestions on how to get around this? So, I'm still working on how to query a time period and show only one line for each product and all the totals for each product. And now I'm wondering how do I get around the Cannot open a form error. Thanks for your continued interest and help, Don.......... "SteveS" sanfu at techie dot com wrote in message ... I see, said the blind man as he picked up his hammer and saw..... g OK Don, how are you opening the report? Do you have a form that shows all of the reports and you select one, then click a button? Or is there just a button that opens the form? ------------------------------------------------------------------- The quick and dirty way to select records in a date range: ------------------------------------------------------------------- (You ARE working on a COPY, right?) 1) Make a new copy of the original report and query. 2) Open the query in design view 3) In the top of the window, double click on the date field 4) The date field should have been added to the grid 5) UNCHECK the check mark for that is in the date column (you don't want to see it) 6) In the Criteria row in the date column enter: Between [StartDate] And [EndDate] 7) Save the query (click the floppy disk in the toolbar) 8) Run the query (click on the red exclaimation point in the toolbar) EVERY time you run the query, open a report or form based on the query, you will get dialog boxs asking for a Start date and an End date. ------------------------------------------------------------------- The Not so quick (but better) way to select records in a date range: ------------------------------------------------------------------- If you have a form that you can put two unbound controls (text boxes) on, you can do: validation checks, have default ranges, have the date ranges in the header of the report , .... all kinds of things Let's say you have a simple dB with 3 reports. To open the reports, you have three buttons, one for each report, on a form named 'frmReportMenu'. 1) On this form add two text box controls so the layout looks like this: txtbox1 txtbox2 button 2) Change the name of txtbox1 to StartDate 3) Change the name of txtbox2 to EndDate (You can set the Input Mask and Format later) 4) Open the query in design view. In grid for the Criteria row in the Date column (the one with the check box unchecked), change the criteria to (watch for line wrap): Between [Forms]![frmReportMenu].[StartDate] And [Forms]![frmReportMenu].[EndDate] Don't forget to change frmReportMenu to the name of your form!!!! 5) Save the query and the Report Menu form. Note that there are no changes to the report. The code for the button (if you used a wizard) should open the report. If not, the OnClick event of the button should have: DoCmd.OpenReport "Report1", acPreview where "Report1" should be the name of your report. Done!! ---------------------- To see your report: -Open the report menu or whatever form the button is on -Fill in the start date and end date -Click on the Open Report button -As the report opens, it runs the query -The query sees it has two parameters located on the form frmReportMenu -It gets the dates, the recordset is generated and the report completes opening, displaying the data found (if any). Is this closer to what you want? Clear as mud??? Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "Don Sealer" wrote: That's cool how that works, I think I'm learning something. Acutally it's not what I was looking for. I also think I should add to my example. Product Qty Built Defect 1 Defect 2 Defect 3 Defect 4; etc Product1 100 1 2 3 1 Product2 10 0 1 4 2 Product3 121 4 0 5 2 etc TOTALS 231 5 3 12 5 Your suggestion grouped everything by month (which may come in handy in the future) but in doing so I lost the bottom line (TOTALS). I was really hoping to be able to somehow, either by query or somewhere in the report, select dates. In some cases it might be a particular month, in other cases it might be a couple of months, still at another time it might only be a week or a couple of days. I'm not sure I'm making myself clear but if you don't mind responding I certainly don't mind your help. Your last suggestion was very helpful, if only for future reference. Thanks again, Don.......... "SteveS" sanfu at techie dot com wrote in message ... Don, Yes, I (think I) understand better. How was the explaination on "Grouping & Sorting"? IMPORTANT: Make sure you are working on a COPY of the database OR a copy of the report and query! OK, here goes........ The record source for the report is a Totals query, right? In design view of the query, the "Totals" button in the toolbar is hi-lighted? You need to add one more field to this query - the date field. 1) In an empty column of the grid, paste the next line, changing "TheDate" to the name of your date field: Period: Format([TheDate],"mm/yyyy") 2) On the design grid, leave the "Total" line to "Group By" for the date column. 3) Save the query. You ARE using a copy, RIGHT??? :- 4) Open the report in design view. 5) Open the Sorting & Grouping dialog box. (Menu/View/Sorting and Grouping) 6) Click in the first row under 'Field/Expression' 7) Select "Period". 8) Change 'Group Header' to YES (look below the grid) 9) Click on the second row under 'Field/Expression' 10) Select "Product" 11) Close the dialog box. Almost done, hang in there... Notice that there is now a section named "Period Header" above the Details section. 12) Put a control (text box) in the Period Header section. Set the record source for the control to "Period" 13) The rest of the fields (Product, Defect1, Defect2,..., Defect30) should be in the Details section already. Save the report. Open it. Is this what you want? Are the numbers right?? Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "Don Sealer" wrote: Steve, Let's see if I can do this better than before. I have 22 products that I track quantites built and defects for each product. So I have a report that has 22 rows (one for each product) and about 30 columns (one for each quantity or defect associated with the product). Kinda looks like this: Product Qty Built Defect 1 Defect 2 Defect 3 Defect 4; etc Product1 100 1 2 3 1 Product2 10 0 1 4 2 Product3 121 4 0 5 2 etc I have a report that shows all of this and works very well. What I need to be able to do is show this report for time periods. For instance the month of October. When I do this, because there are multiple entries for each day, my report then has a row for each entry so instead of having a report showing 22 products, one row for each product, I have a report that might have a hundred rows, one for each entry. Product1 for instance may have 5 separate entries. Actually I don't need to show the time period on the report I just need to be able to show the data from specific time periods. Does this explain it any better? Hope so, Thanks for your interest, Don........ "SteveS" sanfu at techie dot com wrote in message ... Don, I'm still not sure I understand what you are trying to do. Would you give some sample data? So far I know you have 22 products and 30 columns but what does the report look like? Product Qty Widget1 40 Widget2 10 Digit1 15 Digit2 50 Gidget1 33 . . . Item22 13 Are there more columns in the report? I know you want the month/year in there somewhere. What version of access are you using? If you want to send me a cut down version (maybe 500 lines in the table), I have A2K (or A97 is ok). See my email below. Questions: You say "make sure you have Sorting & Grouping by Product in your report. I'm not sure what you mean nor do I know how to do that. I can find Sorting & Grouping on the View menu but I'm not sure how to use it or what you want me to do with it. Grouping is like having unlimited sub reports. Grouping adds more sections to the report. You can sort within the grouping and each group has its own header and footer. In the group header you can put the field that you are grouping by and remove it from the Detail section. Say you have a report that has two sub-reports: This month and last month. Now your boss wants to see this all of this years data by month. You could try and add 10 more sub-reports......well you could try. :0 Or you could get rid of the sub-reports and use grouping. You might have to change the record source a little to get the month/year into separate fields (in the query). Add a group section (header) for the month, put the month field in the group header and the rest of the fields you want to se in the detail section. Set the sorting (if you want) and your report is done! If you wanted to see 5 years of data by year-month, the grouping would be 1) Year field and 2) Month field. Put the year field in the first group header and the month in the second group header. The Detail section gets the rest of the fields you need in the report. Try it on a *copy* of a report...... Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) sanfu at techie dot com replace the at and dot with the symbols & no spaces "Don Sealer" wrote: Anyone interested in helping please let me know. I did get a reply (below) but unfortunately I don't understand it. Still looking, And thanks to all, Don........... Brett, I'm not sure how to reply to your instructions. I replied to your post but it came back as underliverable. Here's my questions Actually I meant 22 products but you're exactly right, one line for each product and the qty for it. I already have a report that shows this data but it doesn't include the date. So I've got a report that shows each product on it's own line and shows the total quantities for each. Now I'd like to be able to show this data in time periods. I don't understand a whole lot of this. There are many features I've never used in Access. I'd like to break this down one or two questions at a time if you don't mind. Questions: You say "make sure you have Sorting & Grouping by Product in your report. I'm not sure what you mean nor do I know how to do that. I can find Sorting & Grouping on the View menu but I'm not sure how to use it or what you want me to do with it. Let's start with that. You're right , I do have lots of questions. Thanks for your help, Don....... |
#9
|
|||
|
|||
Don,
I think I've got it now (I said hopefully). You want to have only 22 product lines (rows) plus the totals line and the 30 + columns on the report, but you want to be able to select only the records that fall between two dates (inclusive). Seems simple now - I guess my brain wasn't geting enough oxygen - cranial-rectal inversion syndrom ( I think my head was up my backside...grin). It takes two queries: the first query (sub) limits the records by date and the second query (main) totals the found records. The sub-query...... --------------------- Create a new query, add the TABLE the data is in and add the fields: product, TheDate, qtyMade, D1, D2, ..., D30. In the criteria for the date field use either: (watch line wrap) Between [Enter Start Date] And [Enter End Date] or Between [Forms]![frmReportDates].[StartDate] And [Forms]![frmReportDates].[EndDate] The nice thing about using the form to enter dates is that the dates can be added to the report header. The main query...... ---------------------- This query totals the records found in the sub-query. Create another query. Add the SUB-QUERY you created above. Add the fields to the grid: (NOT THE DATE FIELD) product, qtyMade, D1, D2, ....., D30 Click the sigma button on the toolbar (makes this a totals query). Leave the Product column to GROUP BY. Change all the rest of the columns to SUM. Use this query as the record source for the report. ----------------------------- I was also thinking... what if you had a table (tblReportDates) with two fields: S_Date and E_Date, both type Date/Time as the record source for a form (frmReportDates) that has two controls: StartDate (record source S_Date) and EndDate (record source E_Date)? Set the form options: AllowEdits - Yes/ Allow Additions - No Whenever you opened the form, the last entered dates would be there. ----------------------------- Does this give you the report you want? (Hint: the answer I am looking for is Yes - or I will put my tail betwixt the legs and slink away) :-( Steve ----------------------------- Artificial Intelligence is no match for my Natural Stupidity "Don Sealer" wrote: Steve, I did this but it returns every record for that time period. I'm really looking to group the time period somehow. In other words I may have 20 entries for one product and 10 entries for another and so on. My report now shows only one entry for each product with accumulated totals for quantities and defects. When I do a start/end date in the query I see all 20 entries for the one product and the 10 entries for the other product and so on again. Another thing came up while I was working with the previous suggestion you gave me. I'm using the "period" idea with sorting and grouping like you suggested. I think this is something I can use most of the time. However there is a formatting problem. Your sorting and grouping suggestion sorts the data by months and shows it in the report. What happens is the individual months come up on parts of the report page. I figured out that it's because I don't always have all of the products being produced each month and so the number of lines change for each month thus the formatting changes in the report. I decided to go into the data and just add an entry for each item that was not produced for the month. That way the report will always have the same amount of lines and the formatting will always be the same. The problem with this is I get an error report that says "Cannot open a form whose underlying query contains a user-defined function that attempts to set or get the form's RecordsetClone Property". I'm not sure what that means but I figured out it's because I'm not entering a quantity into the Total Tested field. Any suggestions on how to get around this? So, I'm still working on how to query a time period and show only one line for each product and all the totals for each product. And now I'm wondering how do I get around the Cannot open a form error. Thanks for your continued interest and help, Don.......... "SteveS" sanfu at techie dot com wrote in message ... I see, said the blind man as he picked up his hammer and saw..... g OK Don, how are you opening the report? Do you have a form that shows all of the reports and you select one, then click a button? Or is there just a button that opens the form? ------------------------------------------------------------------- The quick and dirty way to select records in a date range: ------------------------------------------------------------------- (You ARE working on a COPY, right?) 1) Make a new copy of the original report and query. 2) Open the query in design view 3) In the top of the window, double click on the date field 4) The date field should have been added to the grid 5) UNCHECK the check mark for that is in the date column (you don't want to see it) 6) In the Criteria row in the date column enter: Between [StartDate] And [EndDate] 7) Save the query (click the floppy disk in the toolbar) 8) Run the query (click on the red exclaimation point in the toolbar) EVERY time you run the query, open a report or form based on the query, you will get dialog boxs asking for a Start date and an End date. ------------------------------------------------------------------- The Not so quick (but better) way to select records in a date range: ------------------------------------------------------------------- If you have a form that you can put two unbound controls (text boxes) on, you can do: validation checks, have default ranges, have the date ranges in the header of the report , .... all kinds of things Let's say you have a simple dB with 3 reports. To open the reports, you have three buttons, one for each report, on a form named 'frmReportMenu'. 1) On this form add two text box controls so the layout looks like this: txtbox1 txtbox2 button 2) Change the name of txtbox1 to StartDate 3) Change the name of txtbox2 to EndDate (You can set the Input Mask and Format later) 4) Open the query in design view. In grid for the Criteria row in the Date column (the one with the check box unchecked), change the criteria to (watch for line wrap): Between [Forms]![frmReportMenu].[StartDate] And [Forms]![frmReportMenu].[EndDate] Don't forget to change frmReportMenu to the name of your form!!!! 5) Save the query and the Report Menu form. Note that there are no changes to the report. The code for the button (if you used a wizard) should open the report. If not, the OnClick event of the button should have: DoCmd.OpenReport "Report1", acPreview where "Report1" should be the name of your report. Done!! ---------------------- To see your report: -Open the report menu or whatever form the button is on -Fill in the start date and end date -Click on the Open Report button -As the report opens, it runs the query -The query sees it has two parameters located on the form frmReportMenu -It gets the dates, the recordset is generated and the report completes opening, displaying the data found (if any). Is this closer to what you want? Clear as mud??? Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "Don Sealer" wrote: That's cool how that works, I think I'm learning something. Acutally it's not what I was looking for. I also think I should add to my example. Product Qty Built Defect 1 Defect 2 Defect 3 Defect 4; etc Product1 100 1 2 3 1 Product2 10 0 1 4 2 Product3 121 4 0 5 2 etc TOTALS 231 5 3 12 5 Your suggestion grouped everything by month (which may come in handy in the future) but in doing so I lost the bottom line (TOTALS). I was really hoping to be able to somehow, either by query or somewhere in the report, select dates. In some cases it might be a particular month, in other cases it might be a couple of months, still at another time it might only be a week or a couple of days. I'm not sure I'm making myself clear but if you don't mind responding I certainly don't mind your help. Your last suggestion was very helpful, if only for future reference. Thanks again, Don.......... "SteveS" sanfu at techie dot com wrote in message ... Don, Yes, I (think I) understand better. How was the explaination on "Grouping & Sorting"? IMPORTANT: Make sure you are working on a COPY of the database OR a copy of the report and query! OK, here goes........ The record source for the report is a Totals query, right? In design view of the query, the "Totals" button in the toolbar is hi-lighted? You need to add one more field to this query - the date field. 1) In an empty column of the grid, paste the next line, changing "TheDate" to the name of your date field: Period: Format([TheDate],"mm/yyyy") 2) On the design grid, leave the "Total" line to "Group By" for the date column. 3) Save the query. You ARE using a copy, RIGHT??? :- 4) Open the report in design view. 5) Open the Sorting & Grouping dialog box. (Menu/View/Sorting and Grouping) 6) Click in the first row under 'Field/Expression' 7) Select "Period". 8) Change 'Group Header' to YES (look below the grid) 9) Click on the second row under 'Field/Expression' 10) Select "Product" 11) Close the dialog box. Almost done, hang in there... Notice that there is now a section named "Period Header" above the Details section. 12) Put a control (text box) in the Period Header section. Set the record source for the control to "Period" 13) The rest of the fields (Product, Defect1, Defect2,..., Defect30) should be in the Details section already. Save the report. Open it. Is this what you want? Are the numbers right?? Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "Don Sealer" wrote: Steve, Let's see if I can do this better than before. I have 22 products that I track quantites built and defects for each product. So I have a report that has 22 rows (one for each product) and about 30 columns (one for each quantity or defect associated with the product). Kinda looks like this: Product Qty Built Defect 1 Defect 2 Defect 3 Defect 4; etc Product1 100 1 2 3 1 Product2 10 0 1 4 2 Product3 121 4 0 5 2 etc I have a report that shows all of this and works very well. What I need to be able to do is show this report for time periods. For instance the month of October. When I do this, because there are multiple entries for each day, my report then has a row for each entry so instead of having a report showing 22 products, one row for each product, I have a report that might have a hundred rows, one for each entry. Product1 for instance may have 5 separate entries. Actually I don't need to show the time period on the report I just need to be able to show the data from specific time periods. Does this explain it any better? Hope so, Thanks for your interest, Don........ "SteveS" sanfu at techie dot com wrote in message ... Don, I'm still not sure I understand what you are trying to do. Would you give some sample data? So far I know you have 22 products and 30 columns but what does the report look like? Product Qty Widget1 40 Widget2 10 Digit1 15 Digit2 50 Gidget1 33 . . . Item22 13 Are there more columns in the report? I know you want the month/year in there somewhere. What version of access are you using? If you want to send me a cut down version (maybe 500 lines in the table), I have A2K (or A97 is ok). See my email below. Questions: You say "make sure you have Sorting & Grouping by Product in your report. I'm not sure what you mean nor do I know how to do that. I can find Sorting & Grouping on the View menu but I'm not sure how to use it or what you want me to do with it. Grouping is like having unlimited sub reports. Grouping adds more sections to the report. You can sort within the grouping and each group has its own header and footer. In the group header you can put the field that you are grouping by and remove it from the Detail section. Say you have a report that has two sub-reports: This month and last month. Now your boss wants to see this all of this years data by month. You could try and add 10 more sub-reports......well you could try. :0 Or you could get rid of the sub-reports and use grouping. You might have to change the record source a little to get the month/year into separate fields (in the query). Add a group section (header) for the month, put the month field in the group header and the rest of the fields you want to se in the detail section. Set the sorting (if you want) and your report is done! If you wanted to see 5 years of data by year-month, the grouping would be 1) Year field and 2) Month field. Put the year field in the first group header and the month in the second group header. The Detail section gets the rest of the fields you need in the report. Try it on a *copy* of a report...... Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) sanfu at techie dot com replace the at and dot with the symbols & no spaces "Don Sealer" wrote: Anyone interested in helping please let me know. I did get a reply (below) but unfortunately I don't understand it. Still looking, And thanks to all, Don........... Brett, I'm not sure how to reply to your instructions. I replied to your post but it came back as underliverable. Here's my questions Actually I meant 22 products but you're exactly right, one line for each product and the qty for it. I already have a report that shows this data but it doesn't include the date. So I've got a report that shows each product on it's own line and shows the total quantities for each. Now I'd like to be able to show this data in time periods. I don't understand a whole lot of this. There are many features I've never used in Access. I'd like to break this down one or two questions at a time if you don't mind. Questions: You say "make sure you have Sorting & Grouping by Product in your report. I'm not sure what you mean nor do I know how to do that. I can find Sorting & Grouping on the View menu but I'm not sure how to use it or what you want me to do with it. Let's start with that. You're right , I do have lots of questions. Thanks for your help, Don....... |
#10
|
|||
|
|||
BINGO!!
It works very well. Thank you so much! I'm sitting here wondering why I couldn't figure this out myself. Anyway thank you again. I will leave you alone now and you can resume your normal life. Also, no need to tuck your tail Thanks again, Don.......... I learned much through this experience "SteveS" sanfu at techie dot com wrote in message ... Don, I think I've got it now (I said hopefully). You want to have only 22 product lines (rows) plus the totals line and the 30 + columns on the report, but you want to be able to select only the records that fall between two dates (inclusive). Seems simple now - I guess my brain wasn't geting enough oxygen - cranial-rectal inversion syndrom ( I think my head was up my backside...grin). It takes two queries: the first query (sub) limits the records by date and the second query (main) totals the found records. The sub-query...... --------------------- Create a new query, add the TABLE the data is in and add the fields: product, TheDate, qtyMade, D1, D2, ..., D30. In the criteria for the date field use either: (watch line wrap) Between [Enter Start Date] And [Enter End Date] or Between [Forms]![frmReportDates].[StartDate] And [Forms]![frmReportDates].[EndDate] The nice thing about using the form to enter dates is that the dates can be added to the report header. The main query...... ---------------------- This query totals the records found in the sub-query. Create another query. Add the SUB-QUERY you created above. Add the fields to the grid: (NOT THE DATE FIELD) product, qtyMade, D1, D2, ....., D30 Click the sigma button on the toolbar (makes this a totals query). Leave the Product column to GROUP BY. Change all the rest of the columns to SUM. Use this query as the record source for the report. ----------------------------- I was also thinking... what if you had a table (tblReportDates) with two fields: S_Date and E_Date, both type Date/Time as the record source for a form (frmReportDates) that has two controls: StartDate (record source S_Date) and EndDate (record source E_Date)? Set the form options: AllowEdits - Yes/ Allow Additions - No Whenever you opened the form, the last entered dates would be there. ----------------------------- Does this give you the report you want? (Hint: the answer I am looking for is Yes - or I will put my tail betwixt the legs and slink away) :-( Steve ----------------------------- Artificial Intelligence is no match for my Natural Stupidity "Don Sealer" wrote: Steve, I did this but it returns every record for that time period. I'm really looking to group the time period somehow. In other words I may have 20 entries for one product and 10 entries for another and so on. My report now shows only one entry for each product with accumulated totals for quantities and defects. When I do a start/end date in the query I see all 20 entries for the one product and the 10 entries for the other product and so on again. Another thing came up while I was working with the previous suggestion you gave me. I'm using the "period" idea with sorting and grouping like you suggested. I think this is something I can use most of the time. However there is a formatting problem. Your sorting and grouping suggestion sorts the data by months and shows it in the report. What happens is the individual months come up on parts of the report page. I figured out that it's because I don't always have all of the products being produced each month and so the number of lines change for each month thus the formatting changes in the report. I decided to go into the data and just add an entry for each item that was not produced for the month. That way the report will always have the same amount of lines and the formatting will always be the same. The problem with this is I get an error report that says "Cannot open a form whose underlying query contains a user-defined function that attempts to set or get the form's RecordsetClone Property". I'm not sure what that means but I figured out it's because I'm not entering a quantity into the Total Tested field. Any suggestions on how to get around this? So, I'm still working on how to query a time period and show only one line for each product and all the totals for each product. And now I'm wondering how do I get around the Cannot open a form error. Thanks for your continued interest and help, Don.......... "SteveS" sanfu at techie dot com wrote in message ... I see, said the blind man as he picked up his hammer and saw..... g OK Don, how are you opening the report? Do you have a form that shows all of the reports and you select one, then click a button? Or is there just a button that opens the form? ------------------------------------------------------------------- The quick and dirty way to select records in a date range: ------------------------------------------------------------------- (You ARE working on a COPY, right?) 1) Make a new copy of the original report and query. 2) Open the query in design view 3) In the top of the window, double click on the date field 4) The date field should have been added to the grid 5) UNCHECK the check mark for that is in the date column (you don't want to see it) 6) In the Criteria row in the date column enter: Between [StartDate] And [EndDate] 7) Save the query (click the floppy disk in the toolbar) 8) Run the query (click on the red exclaimation point in the toolbar) EVERY time you run the query, open a report or form based on the query, you will get dialog boxs asking for a Start date and an End date. ------------------------------------------------------------------- The Not so quick (but better) way to select records in a date range: ------------------------------------------------------------------- If you have a form that you can put two unbound controls (text boxes) on, you can do: validation checks, have default ranges, have the date ranges in the header of the report , .... all kinds of things Let's say you have a simple dB with 3 reports. To open the reports, you have three buttons, one for each report, on a form named 'frmReportMenu'. 1) On this form add two text box controls so the layout looks like this: txtbox1 txtbox2 button 2) Change the name of txtbox1 to StartDate 3) Change the name of txtbox2 to EndDate (You can set the Input Mask and Format later) 4) Open the query in design view. In grid for the Criteria row in the Date column (the one with the check box unchecked), change the criteria to (watch for line wrap): Between [Forms]![frmReportMenu].[StartDate] And [Forms]![frmReportMenu].[EndDate] Don't forget to change frmReportMenu to the name of your form!!!! 5) Save the query and the Report Menu form. Note that there are no changes to the report. The code for the button (if you used a wizard) should open the report. If not, the OnClick event of the button should have: DoCmd.OpenReport "Report1", acPreview where "Report1" should be the name of your report. Done!! ---------------------- To see your report: -Open the report menu or whatever form the button is on -Fill in the start date and end date -Click on the Open Report button -As the report opens, it runs the query -The query sees it has two parameters located on the form frmReportMenu -It gets the dates, the recordset is generated and the report completes opening, displaying the data found (if any). Is this closer to what you want? Clear as mud??? Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "Don Sealer" wrote: That's cool how that works, I think I'm learning something. Acutally it's not what I was looking for. I also think I should add to my example. Product Qty Built Defect 1 Defect 2 Defect 3 Defect 4; etc Product1 100 1 2 3 1 Product2 10 0 1 4 2 Product3 121 4 0 5 2 etc TOTALS 231 5 3 12 5 Your suggestion grouped everything by month (which may come in handy in the future) but in doing so I lost the bottom line (TOTALS). I was really hoping to be able to somehow, either by query or somewhere in the report, select dates. In some cases it might be a particular month, in other cases it might be a couple of months, still at another time it might only be a week or a couple of days. I'm not sure I'm making myself clear but if you don't mind responding I certainly don't mind your help. Your last suggestion was very helpful, if only for future reference. Thanks again, Don.......... "SteveS" sanfu at techie dot com wrote in message ... Don, Yes, I (think I) understand better. How was the explaination on "Grouping & Sorting"? IMPORTANT: Make sure you are working on a COPY of the database OR a copy of the report and query! OK, here goes........ The record source for the report is a Totals query, right? In design view of the query, the "Totals" button in the toolbar is hi-lighted? You need to add one more field to this query - the date field. 1) In an empty column of the grid, paste the next line, changing "TheDate" to the name of your date field: Period: Format([TheDate],"mm/yyyy") 2) On the design grid, leave the "Total" line to "Group By" for the date column. 3) Save the query. You ARE using a copy, RIGHT??? :- 4) Open the report in design view. 5) Open the Sorting & Grouping dialog box. (Menu/View/Sorting and Grouping) 6) Click in the first row under 'Field/Expression' 7) Select "Period". 8) Change 'Group Header' to YES (look below the grid) 9) Click on the second row under 'Field/Expression' 10) Select "Product" 11) Close the dialog box. Almost done, hang in there... Notice that there is now a section named "Period Header" above the Details section. 12) Put a control (text box) in the Period Header section. Set the record source for the control to "Period" 13) The rest of the fields (Product, Defect1, Defect2,..., Defect30) should be in the Details section already. Save the report. Open it. Is this what you want? Are the numbers right?? Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "Don Sealer" wrote: Steve, Let's see if I can do this better than before. I have 22 products that I track quantites built and defects for each product. So I have a report that has 22 rows (one for each product) and about 30 columns (one for each quantity or defect associated with the product). Kinda looks like this: Product Qty Built Defect 1 Defect 2 Defect 3 Defect 4; etc Product1 100 1 2 3 1 Product2 10 0 1 4 2 Product3 121 4 0 5 2 etc I have a report that shows all of this and works very well. What I need to be able to do is show this report for time periods. For instance the month of October. When I do this, because there are multiple entries for each day, my report then has a row for each entry so instead of having a report showing 22 products, one row for each product, I have a report that might have a hundred rows, one for each entry. Product1 for instance may have 5 separate entries. Actually I don't need to show the time period on the report I just need to be able to show the data from specific time periods. Does this explain it any better? Hope so, Thanks for your interest, Don........ "SteveS" sanfu at techie dot com wrote in message ... Don, I'm still not sure I understand what you are trying to do. Would you give some sample data? So far I know you have 22 products and 30 columns but what does the report look like? Product Qty Widget1 40 Widget2 10 Digit1 15 Digit2 50 Gidget1 33 . . . Item22 13 Are there more columns in the report? I know you want the month/year in there somewhere. What version of access are you using? If you want to send me a cut down version (maybe 500 lines in the table), I have A2K (or A97 is ok). See my email below. Questions: You say "make sure you have Sorting & Grouping by Product in your report. I'm not sure what you mean nor do I know how to do that. I can find Sorting & Grouping on the View menu but I'm not sure how to use it or what you want me to do with it. Grouping is like having unlimited sub reports. Grouping adds more sections to the report. You can sort within the grouping and each group has its own header and footer. In the group header you can put the field that you are grouping by and remove it from the Detail section. Say you have a report that has two sub-reports: This month and last month. Now your boss wants to see this all of this years data by month. You could try and add 10 more sub-reports......well you could try. :0 Or you could get rid of the sub-reports and use grouping. You might have to change the record source a little to get the month/year into separate fields (in the query). Add a group section (header) for the month, put the month field in the group header and the rest of the fields you want to se in the detail section. Set the sorting (if you want) and your report is done! If you wanted to see 5 years of data by year-month, the grouping would be 1) Year field and 2) Month field. Put the year field in the first group header and the month in the second group header. The Detail section gets the rest of the fields you need in the report. Try it on a *copy* of a report...... Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) sanfu at techie dot com replace the at and dot with the symbols & no spaces "Don Sealer" wrote: Anyone interested in helping please let me know. I did get a reply (below) but unfortunately I don't understand it. Still looking, And thanks to all, Don........... Brett, I'm not sure how to reply to your instructions. I replied to your post but it came back as underliverable. Here's my questions Actually I meant 22 products but you're exactly right, one line for each product and the qty for it. I already have a report that shows this data but it doesn't include the date. So I've got a report that shows each product on it's own line and shows the total quantities for each. Now I'd like to be able to show this data in time periods. I don't understand a whole lot of this. There are many features I've never used in Access. I'd like to break this down one or two questions at a time if you don't mind. Questions: You say "make sure you have Sorting & Grouping by Product in your report. I'm not sure what you mean nor do I know how to do that. I can find Sorting & Grouping on the View menu but I'm not sure how to use it or what you want me to do with it. Let's start with that. You're right , I do have lots of questions. Thanks for your help, Don....... |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Query problem, trying to make a query run faster? (not as it sound | Sacred | Running & Setting Up Queries | 5 | September 21st, 2004 02:40 PM |
Query Problem: Trying to make a query search faster | Sacred | General Discussion | 2 | September 20th, 2004 04:57 PM |
Query problem, shortening a query can anyone help? | Sacred | Using Forms | 3 | September 20th, 2004 03:53 PM |
Problem with query - no installable ISAM | CarolM | Running & Setting Up Queries | 1 | September 1st, 2004 12:02 PM |
Query Join Problem | Tom | Running & Setting Up Queries | 0 | May 31st, 2004 09:54 AM |