Finding multiple records and displaying only one.

[Spac3man]

Hello, I have a database with tables as follows:

Customers -< Orders -< Order Line Items >- Product

...(ignore dots pls)..............v

........................................|

.................................Sales Campaign

Products are sold to customers based on a Sales Campaign. The Sales Campaign number is assigned to each Order. Product prices differ between Sales Campaigns.

One Campaign can have many orders with the same products sold. I need to display a total of any one given part sold based on a given Campaign number and can't seem to visualize the best approach. Any help is greatly appreciated.

[comment]

I assume you meant:

          Sales Campaign

               |

               ^

Customers -< Orders -< Order Line Items >- Product

I need to display a total of any one given part sold based on a given Campaign number

You could simply search the line items table for the given ProductID and the related CampaignID.

[Spac3man]

Finding the records based on a search is the easy part. I'm trying to figure out how to display a single record but with the total quantity ordered.

If 3 customers each order 12 of part 'A', I want to display only one line showing part 'A' Products::Part number and Products::Part Name but with a total Quantity of 36.

If I search in Orders_Line_Items, it will give me every line matching Campaign number and Part number. I only want one line to show

BTW, thanks for your quick response

[comment]

First, add a summary field (Total of Quantity) to your LineItems table (if you haven't already). Add a sub-summary part to the report layout (when sorted by ProductID). Put the product's details and the summary field in this part, and delete the body part.

To show (only) the total quantity of each product in the found set, sort the records by ProductID.

[Spac3man]

Hello Again,

Following what you have said, I do not end up with a complete report showing all products ordered under a given campaign, only one product at a time. I tried a second sub-summary based on campaign_id but for some reason, I lose records in the 'preview' list view. If I view records in 'form' view, they are all there but not in list.

[comment]

Hard to say without seeing the file. Does this work for you?

rep.zip

[Spac3man]

Hi Comment

Sorry for the delay in responding. Had some PC issues.

Your example is what I had. My problem stems from a layout issue. I originally duplicated a layout to keep my GUI identical and then used the Sub-sums just like you have but not all the records would show up. So I created a new layout and started from there and now it works. I don't understand why it does and before it didn't but I know better than to look a gifthorse in the mouth.

Thanks for your guidance, it's been greatly appreciated.

Space.... out

Edited July 30, 2010 by Guest

[Spac3man]

Hi comment

I was looking thru your example to see how you got it to report the summarized total of each product. I can't seem to duplicate your relationship. How did you get it to use the summary field?

[Spac3man]

Well, I've tried and can't figure it out.

A: I can't get the Campaign Report to show a single Product Line with a culumative Quantity for each campaign.

B: I can't get it to show the breakdown of each product based on a campaign number showing all the orders for that product (Campaign Breakdown)

MyLittleDB.zip

[comment]

I am sorry - I may have confused you by not removing the relationship in my demo file. No relationship is required for this.

As for your file, I believe it will work if you sort the records correctly: first by campaign, then by product (i.e. remove the customer from the sort order).