coding a report 1

[scroce]

I have no idea where to begin. Tek-tips has always come through in the past - so here's my question:

Have a customer report generated from a saved query that look like this:

County 1

Sales Rep 1
Customer 1 Product 1
Customer 1 Product 2
Customer 1 Product 3
Customer 2 Product 2
Customer 3 Product 1
Customer 3 Product 3
Sales Rep 2
Customer 4 Product 1
Customer 4 Product 2
Customer 4 Product 3
Customer 5 Product 2
County 2

Sales Rep 1
Customer 1 Product 1
Customer 1 Product 2
Customer 1 Product 3
Customer 2 Product 2
Customer 3 Product 1
Customer 3 Product 3
Sales Rep 3
Customer 4 Product 1
Customer 4 Product 2
Customer 4 Product 3
Customer 5 Product 2

This report is unwieldy - We need it to look like this:

County 1

Sales Rep 1
Customer 1 Product 1, Product 2, Product 3
Customer 2 Product 2
Customer 3 Product 1, Product 3
Sales Rep 2
Customer 4 Product 1, Product 2, Product 3
Customer 5 Product 2
County 2

Sales Rep 1
Customer 1 Product 1, Product 2, Product 3
Customer 2 Product 2
Customer 3 Product 1, Product 3
Sales Rep 3
Customer 4 Product 1, Product 2, Product 3
Customer 5 Product 2

but I just really don't have any idea of where to begin, or what objects to manipulate with code.

Can anyone point me in the right direction?

Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...

 

[beetee]

Where to begin is probably a cross tab query, that can generate a view with product as the column headings, and a different customer on each row.

I'm thinking you'll want to make a subreport for the crosstab query, and add it to the group-by section for sales rep; but I could be wrong.

If you need more help getting started, don't hesitate to ask. Additionally, perhaps someone out there has a better solution.

 

[danvlas]

Check thread181-341439
I posted a function to 'transpose' values in a column.

HTH






Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[beetee]

Some thoughts on the two methods:

There are some advantages to each approach (crosstab vs. VBA) in report generation:

Crosstab:
produces an easy to read table
doesn't require VBA
VBA:
can deal with any number of products (e.g. pivotted items)
often produces a more concise report.

 

[scroce]

bee tee,

OK - closer.....so far I've tried you're crosstab suggestion which presents what seems to be a workable result - but I think there still needs to be code involved. I was able to get a report that shows the following.

product1 product2 product3
County1

Sales Rep1
Client1 1 1 1
Client2 1 1
Sales Rep2
Client3 1 1 1
Client4 1 1


Which solved the repeating aspect of the client names, but now since the crosstab forces you to use a calculated value, I used CountofProduct. But I don't want those 1's there, I want the product name separated by commas.

So I'm trying to develop some code that will say, if txtProduct1=1 then lblProduct.Caption="Product1"

However, for some reason vba is not allowing me to access the .value property of my objects.

I'm used to coding forms - i guess they don't work the same way.





Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...

 

[beetee]

After trying it out, forget the crosstab. You can't really make a sub-report based on a crosstab query to solve this problem; it wants to hard-wire the column names into the report; I was hoping to just show the products associated with each salesperson. Use Daniel's approach.

 

[scroce]

DanVlas,

I wanted to thank you for your help. Your post on Thread181-341439 helped me arrive to the solution of my problem. I'm going to put a star for you.

Thank heaven for tek-tips.

Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...