Need help with subreports in Access 2003 1

[dawnd3]

I am having trouble getting columns to work correctly in a sub report. Basically, in the report header of the main report for a meeting I have a subreport that lists the attendees for the meeting. They want the attendees to be down then across since they are grouped within the subreport. The problem is that when I set the subreport object to "Can Grow" it takes away the column format. I need it to be able to grow as more or less attendees are listed but I also need it to space out evenly. i.e. if there are 9 attendees, it should have 3 columns of 3 names. If I make the subreport object larger it fills in the first column before it moves to the next. If I set Can Grow in the detail of the subreport it still doesn't work. Please help me get the proper combination of settings.

Thank you,

Dawn

http://www.calliopeconsulting.net

 

[dhookom]

The method that I use is to create a value/column in the subreport's record source that can be used to sort across then down but look like down then across. This is usually done by creating a sequence value (numbering the records) and using Int() and Mod calculations.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dawnd3]

Duane, your reply is much appreciated but must be over my head because my first thought was "HUH?". LOL Anyway, can you explain further? I am not sure what Int() and Mod calculations are and how/where I would use them in this situation.

Thanks!!!

Dawn

http://www.calliopeconsulting.net

 

[dhookom]

Assuming you want to get the Employees table in Northwind to display Across then Down but "appear" to be displaying Down then Across (by last name). You would need to create a column like:

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

Then create a 3 column report that displays Across then Down. Use [Col] as the first level of sorting and then the Last Name field.

Even though the report sorts across then down, it will appear down then across by last name.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dhookom]

I'm not sure the above reply is correct. Assuming you want to print the Products table in Northwind in alpha order displayed in three columns down then across. You can create a query like:

SELECT Products.ProductName, DCount("*","Products","ProductName <""" & [ProductName] & """") Mod (DCount("*","Products")\3+1)+DCount("*","Products","ProductName <""" & [ProductName] & """")*0.001 AS AtD2DtA
FROM Products
ORDER BY Products.ProductName;

Then set your report to print across then down and use the AtD2DtA column/field as the primary sorting and grouping level.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dhookom]

I put a sample file that prints across then down in three columns but it looks like it is down then across at

http://www.Access.hookom.net/samples/threeequalcolumns.zip.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dawnd3]

Duane, Thank you! I haven't tried it out on my database yet, but I am sure that actually seeing an example will be super helpful to me. I am a more visual person. Only problem is that here I can't give you more than a star and a thanks to show my appreciation.

Take Care,

Dawn

http://www.calliopeconsulting.net

 

[dawnd3]

Duane, you are a genius! Thanks again!!!

I do have another question though. I actually had a grouping for this report so I used your code on the field I was grouping by. When I set that field as the header and tell it to keep together whole group, it still has stray child records going to the next column. I thought that since we were doing the columns a little differently that it might have messed up that setting. Any ideas?

Thanks

Dawn

http://www.calliopeconsulting.net

 

[dawnd3]

Actually Duane, now that I take a closer look the grouping isn't working correctly. Names are listed under the incorrect grouping. I guess I forgot to mention in the beginning that I was grouping attendees by company name. (How do I do the embarrassed emoticon?)

Dawn

http://www.calliopeconsulting.net

 

[dawnd3]

Duane, I still think you are a genius but I did find another issue. For some reason mine isn't being put in alphabetical order. Here is the query results with your code:

Company AtD2DtA
ACE 0
CCCSD 14.014
CSI 1.021
ENGEO 6.026
ERS 7.027
HSE 13.033
JHCS 14.034
KF 15.035
LC 16.036
MI 18.038
VFP 10.05
VFP 10.05
WBLC 12.052

And here is the code I used to create a new field:

AtD2DtA: DCount("*","Names","Company <""" & [Company] & """")Mod DCount("*","Company")\3+1)+DCount("*","Names","Company <""" & [Company] & """")*0.001

Names being the name of the table, Company the field.

Did I do something wrong?

Thanks

Dawn

http://www.calliopeconsulting.net

 

[dhookom]

Do you have a table or query named "Company"? Is your [Company] field just the abbreviation? Apparently you have duplicate values in your Company field. I'm not sure how that will work unless you add another field inside your where clause in the DCount().

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dawnd3]

No, this is a poorly written database (not mine!) The Company name is just in the "Names" table and yes there are duplicates. That is the field that I am grouping by. There are just abbreviations, no full names.

Any ideas?

Thanks

Dawn

http://www.calliopeconsulting.net