how do you make "down, then across" columns in a subreport?

  • Thread starter Thread starter rick allison
  • Start date Start date
R

rick allison

From a 2004 posting from Duane Hookom...
--------------------------
Assuming you have a list of names Abe, Bart, Chris, Darrin, Edward, Frank
that you want to appear in a two-column subreport as:
Abe Darrin
Bart Edward
Chris Frank
Rather than:
Abe Bart
Chris Darrin
Edward Frank
You would need to create a sequencing value in your query/record source with
values like:
1 Abe
2 Darrin
3 Bart
4 Edward
5 Chris
6 Frank
 
Use a ranking in a group query like this --
SELECT Q.Group, Q.Item_no, Q.Points, (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

Try this --
SELECT Q.FName, (SELECT Count(*) FROM YourTable AS Q1 WHERE Q.FName >=
Q1.FName) AS Sequence
FROM YourTable AS Q
ORDER BY Q.FName;
 
Thanks but not quite...

I have no idea what to do with the first query.

The second query resulted in (I like how it sequenced it, that's good to
know)
1 Abe
2 Bart
3 Chris
4 Darrin
5 Edward
6 FrankBut what I need is
1 Abe
2 Darrin
3 Bart
4 Edward
5 Chris
6 Frank

So when the columns in a subreport print it shows as...
1 Abe 2 Darrin
3 Bart 4 Edward
5 Chris 6 Frank

Notice A,B,C in column 1 and D,E,F in column 2

Rick

KARL DEWEY said:
Use a ranking in a group query like this --
SELECT Q.Group, Q.Item_no, Q.Points, (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

Try this --
SELECT Q.FName, (SELECT Count(*) FROM YourTable AS Q1 WHERE Q.FName >=
Q1.FName) AS Sequence
FROM YourTable AS Q
ORDER BY Q.FName;

--
Build a little, test a little.


rick allison said:
From a 2004 posting from Duane Hookom...
--------------------------
Assuming you have a list of names Abe, Bart, Chris, Darrin, Edward, Frank
that you want to appear in a two-column subreport as:
Abe Darrin
Bart Edward
Chris Frank
Rather than:
Abe Bart
Chris Darrin
Edward Frank
You would need to create a sequencing value in your query/record source
with
values like:
1 Abe
2 Darrin
3 Bart
4 Edward
5 Chris
6 Frank
--------------------------
I like the idea. How in the heck do you create a query/record source to
do
that?

Rick
Click to expand...
Click to expand...
 
You use Print Setup to specify columns and either "down, then across" or
"across, then down". And that works very nicely when the multicolumn Report
is the main Report.

The problem you have encountered is that in formatting a Report in a
Subreport Control, Access has difficulty determining where you want a "page"
of that Report in a Subreport Control to end... so the only reliable,
effective option is to use "Across, then Down".

It may be possible to jump through some flaming hoops and code lots of VBA
to handle this; but I imagine that I'd take one look at it and decide that
"Down, then Across" in a Subreport just isn't worth the trouble.

Larry Linson
Microsoft Office Access MVP
 
I did an example with the Employee table in Northwind. My report's record
source query is:

SELECT DCount("*","Employees","LastName <""" & [LastName] & """") Mod 3 AS
Col, Employees.*
FROM Employees;

In the sorting and grouping, I sorted first by Col and then by LastName. My
three column report that displayed across then down actually prints like:

Buchanan Dodsworth Leverling
Callahan Fuller Peacock
Davolio King Suyama

It looks like down then across but the Page->Setup->Columns is Across, then
Down.

--
Duane Hookom
Microsoft Access MVP


rick allison said:
Thanks but not quite...

I have no idea what to do with the first query.

The second query resulted in (I like how it sequenced it, that's good to
know)
1 Abe
2 Bart
3 Chris
4 Darrin
5 Edward
6 FrankBut what I need is
1 Abe
2 Darrin
3 Bart
4 Edward
5 Chris
6 Frank

So when the columns in a subreport print it shows as...
1 Abe 2 Darrin
3 Bart 4 Edward
5 Chris 6 Frank

Notice A,B,C in column 1 and D,E,F in column 2

Rick

KARL DEWEY said:
Use a ranking in a group query like this --
SELECT Q.Group, Q.Item_no, Q.Points, (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

Try this --
SELECT Q.FName, (SELECT Count(*) FROM YourTable AS Q1 WHERE Q.FName >=
Q1.FName) AS Sequence
FROM YourTable AS Q
ORDER BY Q.FName;

--
Build a little, test a little.


rick allison said:
From a 2004 posting from Duane Hookom...
--------------------------
Assuming you have a list of names Abe, Bart, Chris, Darrin, Edward, Frank
that you want to appear in a two-column subreport as:
Abe Darrin
Bart Edward
Chris Frank
Rather than:
Abe Bart
Chris Darrin
Edward Frank
You would need to create a sequencing value in your query/record source
with
values like:
1 Abe
2 Darrin
3 Bart
4 Edward
5 Chris
6 Frank
--------------------------
I like the idea. How in the heck do you create a query/record source to
do
that?

Rick
Click to expand...
Click to expand...
Click to expand...
 
Duane,

Perfect!! I modified it a little because I wanted two columns.

SELECT DCount("*","tblTest","Name <""" & [Name] & """") Mod
DCount("*","tblTest")/2 AS Col, tblTest.*
FROM tblTest;

My table tblTest has one field in it "name" and I put in names starting with
Abe, Bart, etc... for each letter of the alphabet. I event put in Bart and
Bert to make sure it would work.
Results...

Abe John
Bart Kelly
Bert Larry
Chris Mike
Darrin Nike
Edward Otto
Frank Paul
George Query
Henry Rick
Ike

Works for odd or even number of rows. The key was to make the MOD based on
half of the total number of rows in the table.

You're the best!!

Rick


Duane Hookom said:
I did an example with the Employee table in Northwind. My report's record
source query is:

SELECT DCount("*","Employees","LastName <""" & [LastName] & """") Mod 3 AS
Col, Employees.*
FROM Employees;

In the sorting and grouping, I sorted first by Col and then by LastName.
My
three column report that displayed across then down actually prints like:

Buchanan Dodsworth Leverling
Callahan Fuller Peacock
Davolio King Suyama

It looks like down then across but the Page->Setup->Columns is Across,
then
Down.

--
Duane Hookom
Microsoft Access MVP


rick allison said:
Thanks but not quite...

I have no idea what to do with the first query.

The second query resulted in (I like how it sequenced it, that's good
to
know)
1 Abe
2 Bart
3 Chris
4 Darrin
5 Edward
6 FrankBut what I need is
1 Abe
2 Darrin
3 Bart
4 Edward
5 Chris
6 Frank

So when the columns in a subreport print it shows as...
1 Abe 2 Darrin
3 Bart 4 Edward
5 Chris 6 Frank

Notice A,B,C in column 1 and D,E,F in column 2

Rick

KARL DEWEY said:
Use a ranking in a group query like this --
SELECT Q.Group, Q.Item_no, Q.Points, (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

Try this --
SELECT Q.FName, (SELECT Count(*) FROM YourTable AS Q1 WHERE Q.FName >=
Q1.FName) AS Sequence
FROM YourTable AS Q
ORDER BY Q.FName;

--
Build a little, test a little.


:

From a 2004 posting from Duane Hookom...
--------------------------
Assuming you have a list of names Abe, Bart, Chris, Darrin, Edward,
Frank
that you want to appear in a two-column subreport as:
Abe Darrin
Bart Edward
Chris Frank
Rather than:
Abe Bart
Chris Darrin
Edward Frank
You would need to create a sequencing value in your query/record
source
with
values like:
1 Abe
2 Darrin
3 Bart
4 Edward
5 Chris
6 Frank
--------------------------
I like the idea. How in the heck do you create a query/record source
to
do
that?

Rick
Click to expand...
Click to expand...
Click to expand...
 
I'm not sure this is the right group but here goes.

I did what Duane suggested. It works however it can take up to two minutes
before the report is displayed.

Does anyone have any idea how I can speed up the query or the report? The
number of rows in the result is 300.

If I run just the DCount it takes some time. That's the part perhaps where
some modifications could help speed things up.

DCount('*','qryscribesheets',' [qrySelectDogsInEventEntry].[TitleID]= 7and
EventDateID=64 and ArmBandNumber<' & [ArmBandNumber]) Mod
DCount('*','qryscribesheets','TitleID= 7 and EventDateID=64')/2

Thanks,

Rick

Here's the entire query, it's complicated. The database is normalized. The
query is slow but put the query together with the report and two minutes to
complete happens.

strSQL = "SELECT DCount('*','qryscribesheets','
[qrySelectDogsInEventEntry].[TitleID]=" & frm!cboSelectTitle & _
" and EventDateID=" & frm!cboSelectEventDate & " and " & _
"ArmBandNumber<' & [qrySelectArmBandNumbers].[ArmBandNumber]) Mod
DCount('*','qryscribesheets','TitleID=" & frm!cboSelectTitle & " " & _
"and EventDateID=64')/2 AS col, qrySelectDogsInEvent.DogID,
qrySelectDogsInEvent.CallName, " & _
"qrySelectDogsInEventEntry.TitleEventDateID, tblEventDate.EventDate,
qrySelectTitle.ClassName, " & _
"qrySelectTitle.LevelName, qrySelectTitle.DivisionName,
qrySelectDogsInEventEntry.JumpHeightCD, " & _
"qrySelectDogsInEventEntry.JumpHeightinInches,
qrySelectArmBandNumbers.ArmBandNumber, " & _
"qrySelectDogsInEvent.BreedName, qrySelectDogsInEventEntry.MovedUp,
qrySelectTitle.CatalogSortOrder, " & _
"qrySelectDogsInEvent.HandlerFirstName & ' ' & [HandlerLastName] AS
HandlerName, " & _
"tblEventDate.EventDateID, qrySelectDogsInEventEntry.TitleID,
tblEventDate.EventDateDescription " & _
"FROM qrySelectArmBandNumbers INNER JOIN (((tblEventDate INNER JOIN
qrySelectDogsInEventEntry ON " & _
"tblEventDate.EventDateID = qrySelectDogsInEventEntry.EventDateID)
INNER JOIN qrySelectDogsInEvent ON " & _
"qrySelectDogsInEventEntry.DogID = qrySelectDogsInEvent.DogID) INNER
JOIN qrySelectTitle ON " & _
"qrySelectDogsInEventEntry.TitleID = qrySelectTitle.TitleID) ON
qrySelectArmBandNumbers.DogID = " & _
"qrySelectDogsInEvent.DogID " & _
"WHERE tblEventDate.EventDateID=" & frm!cboSelectEventDate & " AND
qrySelectDogsInEventEntry.TitleID=" & frm!cboSelectTitle & " " & _
"ORDER BY qrySelectArmBandNumbers.CatalogSortOrder,
qrySelectDogsInEventEntry.JumpHeightinInches, " & _
"qrySelectDogsInEventEntry.MovedUp DESC ,
qrySelectArmBandNumbers.ArmBandNumber, tblEventDate.EventDate"


Duane Hookom said:
I did an example with the Employee table in Northwind. My report's record
source query is:

SELECT DCount("*","Employees","LastName <""" & [LastName] & """") Mod 3 AS
Col, Employees.*
FROM Employees;

In the sorting and grouping, I sorted first by Col and then by LastName.
My
three column report that displayed across then down actually prints like:

Buchanan Dodsworth Leverling
Callahan Fuller Peacock
Davolio King Suyama

It looks like down then across but the Page->Setup->Columns is Across,
then
Down.

--
Duane Hookom
Microsoft Access MVP


rick allison said:
Thanks but not quite...

I have no idea what to do with the first query.

The second query resulted in (I like how it sequenced it, that's good
to
know)
1 Abe
2 Bart
3 Chris
4 Darrin
5 Edward
6 FrankBut what I need is
1 Abe
2 Darrin
3 Bart
4 Edward
5 Chris
6 Frank

So when the columns in a subreport print it shows as...
1 Abe 2 Darrin
3 Bart 4 Edward
5 Chris 6 Frank

Notice A,B,C in column 1 and D,E,F in column 2

Rick

KARL DEWEY said:
Use a ranking in a group query like this --
SELECT Q.Group, Q.Item_no, Q.Points, (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

Try this --
SELECT Q.FName, (SELECT Count(*) FROM YourTable AS Q1 WHERE Q.FName >=
Q1.FName) AS Sequence
FROM YourTable AS Q
ORDER BY Q.FName;

--
Build a little, test a little.


:

From a 2004 posting from Duane Hookom...
--------------------------
Assuming you have a list of names Abe, Bart, Chris, Darrin, Edward,
Frank
that you want to appear in a two-column subreport as:
Abe Darrin
Bart Edward
Chris Frank
Rather than:
Abe Bart
Chris Darrin
Edward Frank
You would need to create a sequencing value in your query/record
source
with
values like:
1 Abe
2 Darrin
3 Bart
4 Edward
5 Chris
6 Frank
--------------------------
I like the idea. How in the heck do you create a query/record source
to
do
that?

Rick
Click to expand...
Click to expand...
Click to expand...
 
Back
Top