So I would start by creating the following Calculated Field in your Table, to bring the Location and first three characters of the Item into one field (we'll call it Location and Item Code):
which will result in a column as follows:
Location and Item Code
Now, go into the Summary window, click Edit -> Define Summary. (BTW, if you have never created a summary in Monarch before, take some time and look over the online help - Chapter 3 The Summary Window. As its name suggests, Monarch's Summary window takes all the data from the Table window and summarizes it together in some useful way for you.)
Next, Select "count" as your Summary Field and select the following fields as Key fields IN THIS ORDER (from first to last):
Location and Item Code
Then, double click on Location and Item Code under "Key Field." Click on the Matching tab. Click on the following radio buttons:
-- Summary Field
-- is at least
and then type in the number 2 next to that.
What did we do? We are telling Monarch to build a summary showing the data lines in which the Location and Item Code count up to a total of 2 or more (ie, a duplicate). The ones that are not duplicates are rolled up into a separate "All Others" category (you can change the name of this category if you want, BTW).
Finally I recommend clicking on the Display tab next and check off the following boxes:
-- Suppress duplicate values
-- Insert subtotal line when key value changes
-- Insert blank line when key value changes
and click OK all the way out. This is what your Summary should look like:
2100 2 100-1 1
2 100-2 1
All Others 1 100-1 1
1 200-1 1
2 200-4 1
Summary 5 /font[/quote]Let me know how this works for you.
[size="1"][ November 14, 2002, 01:49 PM: Message edited by: Mike Urbonas ][/size]
Does Monarch have a filter that can be set up after the summary? Thanks again. Gary[/b][/quote]Your next question was a complete surprise to me!
Supressing the All Others category is not currently available in Monarch. Most of the time the "All Others" category is useful because the grand total at the bottom provides a "tie" back to the original report and confirms the summary is indeed using all of the report data.
Workarounds include highlighting only the desired portion of the summary, leaving out the All Others category, then copy and paste that portion to Excel, or, if you are automatically exporting the summary into say an Excel workbook, run a VB script to automatically delete the "All Others" part of the summary. Lastly you could save the summary in its entirety as Excel, Access etc. and open that file in Monarch V6 Pro (File -> Open Database command), and you could then define a table filter to remove the All Others lines.
[size="1"][ November 18, 2002, 01:21 PM: Message edited by: Mike Urbonas ][/size]