Calculated column in your report

[Kbrad32]

I came across the posting by Duane Hookom:

You can create a calculated column in your reports record source such
that when it is printed across then down, it looks like it is down then
across.
For instance if you want two columns and have 30 records you would
create a column that numbers the records
1
16
2
17
3
18
...

Duane Hookom
MS Access MVP

I am curious on how to get this to work. I have the calculated column
in my report by I'm not sure if I am doing it right. My running sum is
over group....is that correct or is there someone who can explain in
more detail?

Thanks,

Kbrad32

 

[Duane Hookom]

You don't create a column in the report. You need to create the column in
your report's record source query. For instance a three column report for the
employees table in Northwind that is set display across then down. The
following is the Record Source query:

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

The report is sorted first by [Col] and then by [LastName]. It appears to be
set up to display down then across while it is actually across then down.

 

[Kbrad32]

Thank you for your explanation.

You don't create a column in the report. You need to create the column in
your report's record source query. For instance a three column report for the
employees table in Northwind that is set display across then down. The
following is the Record Source query:

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

The report is sorted first by [Col] and then by [LastName]. It appears to be
set up to display down then across while it is actually across then down.

I came across the posting by Duane Hookom:

[quoted text clipped - 22 lines]

Kbrad32

 

[Kbrad32 via AccessMonster.com]

I tried the example, but when I use the query statement as the record source
and set the report to Across, Down it doesn't change to look like Down and
Across....so I am not sure what I am doing wrong.

Thank you for your explanation.

You don't create a column in the report. You need to create the column in
your report's record source query. For instance a three column report for the

[quoted text clipped - 13 lines]

 

[Duane Hookom]

What did you use in the Sorting and Grouping level control sources?

--
Duane Hookom
Microsoft Access MVP

I tried the example, but when I use the query statement as the record source
and set the report to Across, Down it doesn't change to look like Down and
Across....so I am not sure what I am doing wrong.

Thank you for your explanation.

You don't create a column in the report. You need to create the column in
your report's record source query. For instance a three column report for the

[quoted text clipped - 13 lines]

Kbrad32

 

[Kbrad32 via AccessMonster.com]

Per your example, I was to sort the [COL] first and then [LastName], but I
was sure if it should sort on each value or Prefix Characters? I will keep
working on it....hopefully before I pull my hair out.

Thanks,

What did you use in the Sorting and Grouping level control sources?

I tried the example, but when I use the query statement as the record source
and set the report to Across, Down it doesn't change to look like Down and

[quoted text clipped - 7 lines]

 

[Duane Hookom]

It's not an easy to create calculation in the query. I usually need to mess
around with the COL calculation until I get it right.

If you struggle too much with this, you might want to come back with your
SQL view of your record source.

--
Duane Hookom
Microsoft Access MVP

Per your example, I was to sort the [COL] first and then [LastName], but I
was sure if it should sort on each value or Prefix Characters? I will keep
working on it....hopefully before I pull my hair out.

Thanks,

What did you use in the Sorting and Grouping level control sources?

I tried the example, but when I use the query statement as the record source
and set the report to Across, Down it doesn't change to look like Down and

[quoted text clipped - 7 lines]

Kbrad32

 

[Kbrad32 via AccessMonster.com]

I tried changing the interval #'s in the sorting and grouping that work on a
few of the records.

Here is my SQL that I am currently using:

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

When the query is ran the COL column has only 0,1,2....then it repeats
starting back at 0 is there a way to alter the numbers?

It's not an easy to create calculation in the query. I usually need to mess
around with the COL calculation until I get it right.

If you struggle too much with this, you might want to come back with your
SQL view of your record source.

Per your example, I was to sort the [COL] first and then [LastName], but I
was sure if it should sort on each value or Prefix Characters? I will keep

[quoted text clipped - 9 lines]

 

[Duane Hookom]

I did another test with the Products table in Northwinds. Assuming you want 3
columns, this is the SQL view of the record source:
SELECT DCount("*","Products","ProductName <""" & [ProductName] & """") Mod
(DCount("*","Products")/3) AS Col, Products.*
FROM Products
ORDER BY Products.ProductName;

I think this should work for you:
SELECT DCount("*","tblIngredients","Ingredient <""" & [Ingredient] & """")
MOD (DCount("*","tblIngredients")/3) AS Col, tblIngredients.*
FROM tblIngredients;

--
Duane Hookom
Microsoft Access MVP

I tried changing the interval #'s in the sorting and grouping that work on a
few of the records.

Here is my SQL that I am currently using:

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

When the query is ran the COL column has only 0,1,2....then it repeats
starting back at 0 is there a way to alter the numbers?

It's not an easy to create calculation in the query. I usually need to mess
around with the COL calculation until I get it right.

If you struggle too much with this, you might want to come back with your
SQL view of your record source.

Per your example, I was to sort the [COL] first and then [LastName], but I
was sure if it should sort on each value or Prefix Characters? I will keep

[quoted text clipped - 9 lines]

Kbrad32

 

[Kbrad32 via AccessMonster.com]

Thanks for your help on this...I was actually trying to make 2 columns. Can I
change the 3 to 2? Also with the new SQL do I still need the Sorting and
Grouping set to what on these examples?

Thanks again

I did another test with the Products table in Northwinds. Assuming you want 3
columns, this is the SQL view of the record source:
SELECT DCount("*","Products","ProductName <""" & [ProductName] & """") Mod
(DCount("*","Products")/3) AS Col, Products.*
FROM Products
ORDER BY Products.ProductName;

I think this should work for you:
SELECT DCount("*","tblIngredients","Ingredient <""" & [Ingredient] & """")
MOD (DCount("*","tblIngredients")/3) AS Col, tblIngredients.*
FROM tblIngredients;

I tried changing the interval #'s in the sorting and grouping that work on a
few of the records.

[quoted text clipped - 19 lines]

 

[Duane Hookom]

Try it. Change the 3 to 2 and set the sorting and grouping to Col and then
Ingredient.

--
Duane Hookom
Microsoft Access MVP

Thanks for your help on this...I was actually trying to make 2 columns. Can I
change the 3 to 2? Also with the new SQL do I still need the Sorting and
Grouping set to what on these examples?

Thanks again

I did another test with the Products table in Northwinds. Assuming you want 3
columns, this is the SQL view of the record source:
SELECT DCount("*","Products","ProductName <""" & [ProductName] & """") Mod
(DCount("*","Products")/3) AS Col, Products.*
FROM Products
ORDER BY Products.ProductName;

I think this should work for you:
SELECT DCount("*","tblIngredients","Ingredient <""" & [Ingredient] & """")
MOD (DCount("*","tblIngredients")/3) AS Col, tblIngredients.*
FROM tblIngredients;

I tried changing the interval #'s in the sorting and grouping that work on a
few of the records.

[quoted text clipped - 19 lines]

Kbrad32

 

[Kbrad32 via AccessMonster.com]

I have changed the 3 to a two and it still not coming out the way it should,
so I think I need to step back and give my brain a rest. If I every get to
work I will send a response. Thank you for your insight.

Try it. Change the 3 to 2 and set the sorting and grouping to Col and then
Ingredient.

Thanks for your help on this...I was actually trying to make 2 columns. Can I
change the 3 to 2? Also with the new SQL do I still need the Sorting and

[quoted text clipped - 19 lines]