Complex Grouping for Report (1 Viewer)

ss6857 · Jul 12, 2011

Hello and thank you in advance.
I have search the forum and have found people with similar issues, but I couldn't find an answer that could help me.. I'm sorry if I'm reposting an already asked question.

I am attempting to create a pricing report. I have different products and different prices for these products depending on the level. Example: Level one is orders under $500, Level 2 is between $500 and $1000, and Level 3 is over $1000.

I would like the report to look like this:

..............L1...L2...L3
Product 1 $50 $40 $30
Product 2 $45 $40 $35
Product 3 $70 $50 $30
Product 4 $45 $35 $25
Product 5 $60 $55 $50

Whenever I group it (either on level or product), the product appear 3 times. Like:

Level one
..Product 1 price
..Product 2 price etc
Level two
..Product 1 price
..Product 2 price etc
Level three
..Product 1 price
..Product 2 price etc

Or:

Product 1 Level one price
Product 1 Level two price
Product 1 Level three price
etc

Any suggestions? Sorry if this is so complex.. I thought it would be easier if I had visual examples...

Side question: can you use code to create reports? is it advised?

Beetle · Jul 12, 2011

I would like the report to look like this:

..............L1...L2...L3
Product 1 $50 $40 $30
Product 2 $45 $40 $35
Product 3 $70 $50 $30
Product 4 $45 $35 $25
Product 5 $60 $55 $50

There's nothing really complex about getting an output like that on a report. What is your table structure? Do you have a table for Price Levels, or how are those determined?

ss6857 · Jul 13, 2011

I do have a price levels table but it just defines the level with a corresponding number. I have a master price table that has the number and the level description along with the products and their prices. I linked all the subtables I made in Access in SQL and then linked the master table back into Access. Now I'm using that table to do my reports.

Beetle · Jul 13, 2011

...and then linked the master table back into Access. Now I'm using that table to do my reports.

You're using what table, the master table? And what do you mean by "linked..back into Access"? Are you using linked tables to a non Access backend like SQL Server? Is the data being imported from some external source?

Usually, you don't use a table as the record source for a report. You create a query returning the necessary fields from two or more related tables and you use the query as the record source.

ss6857 · Jul 14, 2011

Yes, I'm using sql server. The information isn't imported, I did that because the goal is to export the information.

I've only worked with Access a couple times and it was mostly data entry stuff, so I'm not sure how to work my way around it yet.

Beetle · Jul 14, 2011

I can't be specific without knowing your table structure, but basically you would want to create a query that returns only the data you need for the report. The query would involve the Products table, plus whatever table (or tables) are necessary to determine the Price levels and the amounts for each level. You would most likely use ProductID to join the tables in the query. The goal would be to have the query output look like;

Product.....L1...L2...L3
Product 1 $50 $40 $30
Product 2 $45 $40 $35
Product 3 $70 $50 $30
Product 4 $45 $35 $25
Product 5 $60 $55 $50

Then base your report on that query.

ss6857 · Jul 18, 2011

Ok great. I figured that part out. I made a query based off the master price table and its good. But now I have run into a different problem... In my report I have grouped based on the "category" of product and there is a price break after that. But every page does not include all of the pricing levels. (there are 6 levels total, 3 is the most used on one page) I was wondering if there is a way to delete, or not show the labels and text boxes. Is there a way to create an expression like (iif(sum([Level1])=0, delete column, do nothing) and would this work for each group?

Beetle · Jul 19, 2011

I thought we were talking about pricing levels. In other words, something like;

You buy 1-5 products, they are $50 each.
You buy 5-10 they are $40 each.
You buy 10 0or more they are $30 each.

I'm not sure why a pricing level would ever be $0. Or are we now talking about actual product sales? If that's the case then I would think that your report would be based on a query of a ProductSales table of some type and that products with no sales (i.e. $0) would not be in the record set in the first place, so you would not need to try and "hide" them in the report.

Maybe I'm missing something?

ss6857 · Jul 19, 2011

You're right except for the category of products. So let say my products are foods. So my first category is fruit, so all the apples, bananas, oranges, etc go by the price level you explained above. But the vegetables go at a different price. So fruit is <5 is $1 each, but <5 for Vegetables is $.75 each. And after each category there is a page break.
For a little more clarity, the entire report is the customer I sell my product to. It's a price sheet for my customer to look at before they purchase.

The problem is, on my "fruit" page, the levels for vegetables are showing up. Those levels are all zeros because the fruit is not included in vegetable pricing.

Beetle · Jul 19, 2011

Can you post a more detailed description of your table structure and relationships? In a properly normalized structure the pricing levels for Fruits should not be showing up in the Vegetables category. Something like;

TblCategories
**************
CategoryID (Primary Key)
CategoryName

tblProducts
************
ProductID (PK)
CategoryID (Foreign Key to tblCategories)
ProductName

tblPriceLevels
**************
LevelID(PK)
CategoryID (FK to tblCategories)
PriceLevel
Price

ss6857 · Jul 21, 2011

I use the master price table to create a query.. Directly from the table I have the customer name, category, product, weight, etc... and then I have "Price1: Sum(IIf([dbo_qry_MasterPriceTable]![Level]=1,[dbo_qry_MasterPriceTable]![Price],0))" and I have that for all the price levels.

Complex Grouping for Report (1 Viewer)

ss6857

Registered User.

Beetle

Duly Registered Boozer

ss6857

Registered User.

Beetle

Duly Registered Boozer

ss6857

Registered User.

Beetle

Duly Registered Boozer

ss6857

Registered User.

Beetle

Duly Registered Boozer

ss6857

Registered User.

Beetle

Duly Registered Boozer

ss6857

Registered User.

Similar threads

Users who are viewing this thread