Re: Still Hoping for help with a Query problem

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Don Sealer (vze2t273_at_verizon.net)
Date: 11/06/04 

Date: Sat, 06 Nov 2004 21:11:20 GMT

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
news:E30FF948-C835-4752-A2CD-7444876E723A@microsoft.com...
> 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
news:056CED86-5377-4EFE-99D9-63A677CEAD39@microsoft.com...
> > > 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.......

Quantcast