Still Hoping for help with a Query problem

[Don Sealer]

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........

 

[Guest]

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

 

[Don Sealer]

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........

 

[Will Sellers]

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

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........

 

[Guest]

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

 

[Don Sealer]

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.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..........

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.)

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: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.

 

[Guest]

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.)

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.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..........

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.)

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: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.

 

[Don Sealer]

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..........

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.)

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..........

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.)



:

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

:

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.......

 

[Guest]

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

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..........

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.)

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.)



:

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


:

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.......

 

[Don Sealer]

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

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

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..........

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.

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.)


:

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.)



:

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


:

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.......

 

[Don Sealer]

One final thing Steve, how would I show the dates in the report header?
Don............

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

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..........

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.

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.)


:

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.)



:

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


:

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.......

 

[Guest]

Are you using a form to enter the start and end dates?

In the header of the report you could put two unbound text boxes. Change the
label for the first text box to "Between". Set the Control Source for the
text box to the text box on the form that has the start date:

= Forms!frmName.StartDate

Set the label for the second text bot to "and" and the Control Source to the
end date:

= Forms!frmName.EndDate

Adjust the spacing and you are done. Remember, the form where you entered
the dates must be open.

Another way is to create an unbound text box and make the width as wide as
the report. Set the "Text Align" property of the text box to "Center". The
Control Source would be:

= "Between " & Forms!frmName.StartDate & " and " & Forms!frmName.EndDate

In both cases you could format the date using:

Format(Forms!frmName.StartDate, "mmm, d, yyyy")

Change frmName to the name of your form and the names of the controls to
whatever you named them.


If you used a pop-up parameter box (did not use a form to get the dates),
you could use the DMax() and DMin() functions to get the max and min dates
from the recordset, substituting the DMax() function for
"Forms!frnName.controlName" in the Control Source of the text boxes.

You could also write a custom function to get the dates from a recordset and
put them in the header.

Fun, huh?

Steve

 

[Don Sealer]

Steve,
I tried option 1. It doesn't seem to recognize where it should look. I get
a "name" error. Here's what I entered into the control source.
=[Forms]![shift form].[startdate] (same thing for the other text box only
enddate)
Access inserted the brackets. Shift Form is the name of the my form.
I know I'm not understand what I'm supposed to enter but I tried several
different formats but can't seen to get it right.
Don..........

 

[Guest]

Don,

You verified the spelling and the form [shift form] is open? Just
checking.... <g>

If you open the form [shift form] and enter dates, then open the query (or
sub-query)from the main database window, does the query run and have data?

If the query runs without error, open the sub-query and look at the criteria
row. The two controls in the report header should refer to the same
parameters that are in the sub-query.

Make sure the two controls (textboxes) in the *report (or page) header* are
named something other than StartDate and EndDate - maybe Date1 and Date2 or
Text 20 and Text21???

I just tried changing the name of the start date on the test form I made and
I got the #Name error. So it looks like the text box on the report cannot
find a control on the form [shift form] named StartDate/EndDate.

Check the spelling and the names of the controls, again. I know you did,
but I can't check it, so you have to... <g>


Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve,
I tried option 1. It doesn't seem to recognize where it should look. I get
a "name" error. Here's what I entered into the control source.
=[Forms]![shift form].[startdate] (same thing for the other text box only
enddate)
Access inserted the brackets. Shift Form is the name of the my form.
I know I'm not understand what I'm supposed to enter but I tried several
different formats but can't seen to get it right.
Don..........

 

[Don Sealer]

Steve,
Not sure I'm getting this one, it may be over my head. I did however
finally get the date into the Report Header but it didn't work like I hoped.
I have one table (shift table), two queries (date sub query & main
query), and one report (Lreport). In order to make things work like you
suggested I had to create two additional fields in my table (start date &
end date). I then added them to the "date sub query". I then created two
new text boxes in my form (start date & end date). The control source for
these was "Date Sub Query/Start Date and End Date". I then inputted the
dates into the Shift Form's new text boxes that I wanted to show on my
report. I left the form open and then opened the Lreport using the same
dates. It did put the dates into the report header but it seems a long way
to go to get this result. I'd have to always input the dates I wanted on
the report into the two text boxes in the form first, always remember to
leave the form open, then type in the same dates in the report pop up box.
Maybe I'm doing something wrong, which wouldn't surprise me. I thought
this might be something fairly simple but I'm guessing it's not.
Out of all of your suggestions to accomplish this goal I thought this
was the only one I'd have a shot at getting done. The others use functions
I'm not very famliar with.
Any other ideas???
You've been a great help and I certainly don't want to overstay or overuse
your rescources.
Thanks for all your help and interest,
Don............

Don,

You verified the spelling and the form [shift form] is open? Just
checking.... <g>

If you open the form [shift form] and enter dates, then open the query (or
sub-query)from the main database window, does the query run and have data?

If the query runs without error, open the sub-query and look at the criteria
row. The two controls in the report header should refer to the same
parameters that are in the sub-query.

Make sure the two controls (textboxes) in the *report (or page) header* are
named something other than StartDate and EndDate - maybe Date1 and Date2 or
Text 20 and Text21???

I just tried changing the name of the start date on the test form I made and
I got the #Name error. So it looks like the text box on the report cannot
find a control on the form [shift form] named StartDate/EndDate.

Check the spelling and the names of the controls, again. I know you did,
but I can't check it, so you have to... <g>


Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve,
I tried option 1. It doesn't seem to recognize where it should look. I get
a "name" error. Here's what I entered into the control source.
=[Forms]![shift form].[startdate] (same thing for the other text box only
enddate)
Access inserted the brackets. Shift Form is the name of the my form.
I know I'm not understand what I'm supposed to enter but I tried several
different formats but can't seen to get it right.
Don..........

Are you using a form to enter the start and end dates?

In the header of the report you could put two unbound text boxes.

Change
the

label for the first text box to "Between". Set the Control Source for the
text box to the text box on the form that has the start date:

= Forms!frmName.StartDate

Set the label for the second text bot to "and" and the Control Source

to
the

end date:

= Forms!frmName.EndDate

Adjust the spacing and you are done. Remember, the form where you entered
the dates must be open.

Another way is to create an unbound text box and make the width as wide as
the report. Set the "Text Align" property of the text box to "Center". The
Control Source would be:

= "Between " & Forms!frmName.StartDate & " and " & Forms!frmName.EndDate

In both cases you could format the date using:

Format(Forms!frmName.StartDate, "mmm, d, yyyy")

Change frmName to the name of your form and the names of the controls to
whatever you named them.


If you used a pop-up parameter box (did not use a form to get the dates),
you could use the DMax() and DMin() functions to get the max and min dates
from the recordset, substituting the DMax() function for
"Forms!frnName.controlName" in the Control Source of the text boxes.

You could also write a custom function to get the dates from a

recordset
and

put them in the header.

Fun, huh?

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

One final thing Steve, how would I show the dates in the report header?
Don............

 

[SteveS]

Don,

Sorry for taking so long to get back to you. I had to reload windows (again!)
..... I "play" with software and hardware and sometimes (alot actually) I do
things I can't recover from. Hence the downtime and rebuild/reload.

What version of access are you using?


You shouldn't (don't) need to add the two fields to the table. Just two unbound
text boxes (StartDate and EndDate) on a form - the main menu (switchboard,
whatever you call it) would work. Then reference those two controls for the
query and the title in the header.

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve,
Not sure I'm getting this one, it may be over my head. I did however
finally get the date into the Report Header but it didn't work like I hoped.
I have one table (shift table), two queries (date sub query & main
query), and one report (Lreport). In order to make things work like you
suggested I had to create two additional fields in my table (start date &
end date). I then added them to the "date sub query". I then created two
new text boxes in my form (start date & end date). The control source for
these was "Date Sub Query/Start Date and End Date". I then inputted the
dates into the Shift Form's new text boxes that I wanted to show on my
report. I left the form open and then opened the Lreport using the same
dates. It did put the dates into the report header but it seems a long way
to go to get this result. I'd have to always input the dates I wanted on
the report into the two text boxes in the form first, always remember to
leave the form open, then type in the same dates in the report pop up box.
Maybe I'm doing something wrong, which wouldn't surprise me. I thought
this might be something fairly simple but I'm guessing it's not.
Out of all of your suggestions to accomplish this goal I thought this
was the only one I'd have a shot at getting done. The others use functions
I'm not very famliar with.
Any other ideas???
You've been a great help and I certainly don't want to overstay or overuse
your rescources.
Thanks for all your help and interest,
Don............

Don,

You verified the spelling and the form [shift form] is open? Just
checking.... <g>

If you open the form [shift form] and enter dates, then open the query (or
sub-query)from the main database window, does the query run and have data?

If the query runs without error, open the sub-query and look at the
criteria

row. The two controls in the report header should refer to the same
parameters that are in the sub-query.

Make sure the two controls (textboxes) in the *report (or page) header*
are

named something other than StartDate and EndDate - maybe Date1 and Date2
or

Text 20 and Text21???

I just tried changing the name of the start date on the test form I made
and

I got the #Name error. So it looks like the text box on the report cannot
find a control on the form [shift form] named StartDate/EndDate.

Check the spelling and the names of the controls, again. I know you did,
but I can't check it, so you have to... <g>


Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)



:

Steve,
I tried option 1. It doesn't seem to recognize where it should look. I

get

a "name" error. Here's what I entered into the control source.
=[Forms]![shift form].[startdate] (same thing for the other text box

only

enddate)
Access inserted the brackets. Shift Form is the name of the my form.
I know I'm not understand what I'm supposed to enter but I tried several
different formats but can't seen to get it right.
Don..........

"SteveS" <sanfu at techie dot com> wrote in message

Are you using a form to enter the start and end dates?

In the header of the report you could put two unbound text boxes.

Change

the

label for the first text box to "Between". Set the Control Source for

the

text box to the text box on the form that has the start date:

= Forms!frmName.StartDate

Set the label for the second text bot to "and" and the Control Source

to

the

end date:

= Forms!frmName.EndDate

Adjust the spacing and you are done. Remember, the form where you

entered

the dates must be open.

Another way is to create an unbound text box and make the width as

wide as

The

Forms!frmName.EndDate

to

dates),

dates

recordset

header?