Overflow and group by genre

[Justin]

I am making a DVD database with reports listing the movies. I have
two columns of text boxes listing the titles of the movies on one page of the
report. So when the text gets to the end of a page, instead of stating a new
page, overflow into the next column on the page then when that column is full
go to the next page. For example titles 1-25 in the first column one page 1,
titles 26-50 in the second column on page 1 and titles 51-75 in column one on
page 2.

I have movies Listed by Genrei would like to make a report with all movies
listed my genre and then alphabetcal order. Say there are 9 only movies per
genre. I want the title of the group to be Comedy and then a 3X3 block of the
movie titles. 3 down 3 across. then the next to be documentries., three down
the three across.

 

[Marshall Barton]

I am making a DVD database with reports listing the movies. I have
two columns of text boxes listing the titles of the movies on one page of the
report. So when the text gets to the end of a page, instead of stating a new
page, overflow into the next column on the page then when that column is full
go to the next page. For example titles 1-25 in the first column one page 1,
titles 26-50 in the second column on page 1 and titles 51-75 in column one on
page 2.

I have movies Listed by Genrei would like to make a report with all movies
listed my genre and then alphabetcal order. Say there are 9 only movies per
genre. I want the title of the group to be Comedy and then a 3X3 block of the
movie titles. 3 down 3 across. then the next to be documentries., three down
the three across.

Use Sorting and Grouping (View menu) to specify the grouping
by genre and sorting by title.

 

[Justin]

I cant get the group to go down then across, and stay in the group. is should
look like this. this is with 3 columns

Group 1
1 4 7
2 5 8
3 6 9

Group 2
10 13 16
11 14 17
12 15 18

 

[Marshall Barton]

But that's normal behavior, there should be no problem
getting down then across columns, unless this is in a
subreport.

Maybe you're complaning about the group header being in the
column with the first data item??? If you want the group
header to span the entire page, then the multi column data
will have to go in a subreport. But then it will have to be
across then down and to arrange the data so it looks like it
would if Down then Across worked in subreports requires a
calculated field in the subreport's record source query.

The main report's query would just be:

SELECT DISTINCT genre
FROM thetable

The subreport's record source query would look something
like:

SELECT T.genre, T.title,
numcolumns * (SELECT Count(*)
FROM thetable As X
WHERE X.genre = T.genre
AND X.title < T.title) \
(SELECT Count(*)
FROM thetable As Y
WHERE Y.genre = T.genre)
As ColNum
FROM thetable As T

And the subreport control's Link Master/Chile properties
would be set to the genre field. And the subreport's would
use the ColNum field to sort the data.

 

[Justin]

i have the subreports working great but i still can get it to go down then
across in acending order. like this

title
a c e
b d f

right now it just goes
title
a
b
c
d
e
f

But that's normal behavior, there should be no problem
getting down then across columns, unless this is in a
subreport.

Maybe you're complaning about the group header being in the
column with the first data item??? If you want the group
header to span the entire page, then the multi column data
will have to go in a subreport. But then it will have to be
across then down and to arrange the data so it looks like it
would if Down then Across worked in subreports requires a
calculated field in the subreport's record source query.

The main report's query would just be:

SELECT DISTINCT genre
FROM thetable

The subreport's record source query would look something
like:

SELECT T.genre, T.title,
numcolumns * (SELECT Count(*)
FROM thetable As X
WHERE X.genre = T.genre
AND X.title < T.title) \
(SELECT Count(*)
FROM thetable As Y
WHERE Y.genre = T.genre)
As ColNum
FROM thetable As T

And the subreport control's Link Master/Chile properties
would be set to the genre field. And the subreport's would
use the ColNum field to sort the data.
--
Marsh
MVP [MS Access]

I cant get the group to go down then across, and stay in the group. is should
look like this. this is with 3 columns

Group 1
1 4 7
2 5 8
3 6 9

Group 2
10 13 16
11 14 17
12 15 18

 

[Marshall Barton]

A subreport ***must*** use Across then Down snaking.

To make it appear as if you had specified Down then Across,
the subreport needs to use a record source query like the
one I posted earlier.

If you still don't get what you want, please provide more
information about the subreport's settings and its record
source query.

 

[Justin]

the DB is less then a meg big, can i send it to you jsut so you can see
exactaly what im looking at.

A subreport ***must*** use Across then Down snaking.

To make it appear as if you had specified Down then Across,
the subreport needs to use a record source query like the
one I posted earlier.

If you still don't get what you want, please provide more
information about the subreport's settings and its record
source query.
--
Marsh
MVP [MS Access]

i have the subreports working great but i still can get it to go down then
across in acending order. like this

title
a c e
b d f

right now it just goes
title
a
b
c
d
e
f


"Marshall Barton" wrote

 

[Marshall Barton]

No, don't send it. I get paid for doing that kind of work
and I'm too busy to get to it in any reasonable amount of
time.

You should try to explain what you've done and what the
results were as I requested.

Are my suggestions confusing you so you don't know what I'm
talking about or are you having trouble translating what I
said to your situation?

 

[Justin]

Here is the info.
All the information is in a table called tblmain
The querys read from that table to get it into genre
I have reports made for every genre that I put into one report so they
become sub reports.
I want to it to look like its across then down to fill up horizontally
first, but it needs to go down then across, like to diagram I posted earlier.

No, don't send it. I get paid for doing that kind of work
and I'm too busy to get to it in any reasonable amount of
time.

You should try to explain what you've done and what the
results were as I requested.

Are my suggestions confusing you so you don't know what I'm
talking about or are you having trouble translating what I
said to your situation?
--
Marsh
MVP [MS Access]

the DB is less then a meg big, can i send it to you jsut so you can see
exactaly what im looking at.

 

[Marshall Barton]

Here is the info.
All the information is in a table called tblmain
The querys read from that table to get it into genre
I have reports made for every genre that I put into one report so they
become sub reports.
I want to it to look like its across then down to fill up horizontally
first, but it needs to go down then across, like to diagram I posted earlier.

I understand all that, except maybe the part about a report
for each genre.

I really need for you to explain what you tried as the
record source query and the results that you got.

Let's try to simplify the issue by isolating things to just
a genre report. Try using the query I posted earlier and
post back with the query and what the report displayed.

 

[Justin]

ok we can call it just one report, i understand what you are saying there.i
do not understand how to use the record source query. i used to know how to
program in that but have forgotem it. i had someone that know how to code sql
look at it and he wanst sure how to use it either. do i just copy and paste
it?

 

[Marshall Barton]

Create a new query with nothing in it, switch to SQL view
and Paste my query on place of whatever's already there.
Then go through it replacing the tabl and field names that I
used with the real names you are using. When you think it's
correct, switch to sheet view to check if it actually runs.
After it works, save the query with an appropriate name.

Finally, set the genre report's RecordSource property with
the name of the query, make sure the report's snaking is set
to Across then Down and save the report.

Run the report and post back with the actual query you used
and a description of the report results.

While you're doing that, I will double check the query's
logic to make sure it does what I think it will.

 

[Marshall Barton]

Well, it seems that I was confusing two ways of doing this.
The query's logic is what it's supposed to be for one
approach. Unfortunately my arrangement for the subreport
was for another approach.

Using the query I posted earlier, you would need to leave
the genre report with one column, but place two copies of it
side by side on the main report. Also, add two text boxes
named txtCol1 and txtCol2. Set their control source
expressions to =0 and =1 respectively. The Link Master
property would be Genre,txtCol1 and the Link Child property
would be the Genre,ColNum for the left subreport. Similar
for the right subreport using txtCol2.

A second approach for just two columns, you can leave the
genre report with two columns (Across then Down) and only
one copy on the main report. This approach needs a
different query for the record source:
SELECT T.Genre, T.title,
(SELECT 2 * Count(*)
FROM table As X
WHERE X.Genre = T.Genre
And X.title < T.title
) As RR,
(SELECT Count(*)
FROM table
) As Recs,
2*((Recs+1)\2)+1 As Halfway,
RR Mod Halfway As ColNum
FROM table As T

The Link Master/Child properties would be set to Genre. If
the subreport has Grouping, then this query will require
that the subqueries be replaced with DCount (example below).

A third approach that will work for two or more columns
would use this kind of query:

SELECT T.Genre, T.title,
NumCols * DCount("*", "table", "Genre = " & T.Genre &
" And title < """ & T.title & """") \
DCount("*", "table", "Genre = " & T.Genre) As ColNum
FROM table As T

In this arrangement, the subreport would be set to NumCols
columns, but this time set the snaking to Down then Across.
Set the subreport to group on the ColNum field with a group
footer (which can be zero height) and sort on the title
field. Set the Group footer section's New Row or Col
property to After Section. The Link Master/Child properties
are set to the Genre field.

Don't feel too bad if you find this confusing, because it
really is confusing. Just keep trying to understand it and
sooner or later it will become less murky.

I sure hope you can get this working because I have got to
go out and get more aspirin for the headache this issue has
caused.