Query Help

  • Thread starter Thread starter Don Sealer
  • Start date Start date
D

Don Sealer

I need some help. I have a database that right now has about 3000 entries.
The database tracks products and the quantities and defects associated with
the products. There are about 22 products and about 30 columns tracking
defects and quantities. I have a report that shows all this data and lists
each product (22) and all the quantities and defects associated with each
product. This is a one page report.
What I'd like to do is be able to show specific records by date and still
only show the totals for the 22 products. For instance, I want to show all
the data for January 04. There are 162 individual records for that month.
When I query my database for that month the report shows all 162 records.
I'd really like it to group everything for that time period.
I hope this explains my problem and I hope someone can tell me how to make
this work.
Thanks,
Don.......
 
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


I need some help. I have a database that right now has about 3000 entries.
The database tracks products and the quantities and defects associated with
the products. There are about 22 products and about 30 columns tracking
defects and quantities. I have a report that shows all this data and lists
each product (22) and all the quantities and defects associated with each
product. This is a one page report.
What I'd like to do is be able to show specific records by date and still
only show the totals for the 22 products. For instance, I want to show all
the data for January 04. There are 162 individual records for that month.
When I query my database for that month the report shows all 162 records.
I'd really like it to group everything for that time period.
I hope this explains my problem and I hope someone can tell me how to make
this work.
Thanks,
Don.......
Click to expand...

Cheers,
Brett
 
Back
Top