Finding Duplicates within Duplicates Nightmare

[Amapuche]

Hello,

My original problem was getting the following raw data to display colours on one line by style.

Sea Style Colour
01 2225 BLK
01 2225 WHT
01 2225 GRY
02 2225 BLK
02 2225 WHT

With the help of Jim Broadbent I was able to get my data looking like this, Perfect!. There are two tables one for styles and one for style colours tables are joined by season and style. My original question was posted in this forum under the title “Need Help with a complicated (to me) query”

Sea Style Colour
01 2225 BLK WHT GRY
02 3567 BLK WHT GRY
03 2225 BLK WHT GRY
01 3335 BLK WHT GRY
02 5897 BLK WHT GRY
03 3335 BLK WHT GRY
02 3335 BLK WHT GRY
02 2225 BLK WHT GRY

Now my problem is how to separate the duplicate styles from once off styles i.e. a style can appear more than once over more than one season but not in the same season. If I do the following report on just the styles table I get exactly what I want (minus the colours which I need).

1. Select seasons and styles from the style table
2. Group styles in ascending order
3. Summarise the style table and select count & deselect grand total
4. Using the select expert Count (styles) > 1, I suppress the group footer

I get the following data which is great!

Season Style
2225
01 2225
02 2225
03 2225

3335
01 3335
02 3335
03 3335

But when I add the colour table I get the same data that was generated with Jims help because a style will always be greater than one due to the fact that colours are entered individually by style per season in the colour table– hope I’m making sense here!. Hence I can’t find the true duplicates styles by season due to the multiple colours available in a style.

Can anyone help me with this curly one?

Thanks in advance

Mapuche

 

[Ngolem]

sorry but you aren't making your problem very clear

So you want the the report the same as you had before in the form

Style
sea1 color1 color2 color3 ...
sea2 color1 color2 color3 ...

but are you asking to suppress the second row if the colors are the same?? is that what you are asking for now? Jim Broadbent

 

[Amapuche]

Hi Jim,

I want to suppress a style once summarised (as you showed me in the previous example) if it appears only once in the report. So that the report will show me duplicate styles codes only.

Right now the data I get shows all styles regardless of the style summary I place in the report (which is to show only duplicated styles by season as explained above). When the styles table and colour tables are included in the same report the style will always be greater than 1 as colours are entered against styles individually. I am finding this is a really difficult one to explain, sorry If I am fustrating you. Here is an example of the raw data

Style Table Colour Table

Sea Style Sea Style Colour
01 2225 01 2225 BLK
01 3335 01 2225 WHT
02 4445 01 2225 GRY
02 3442 01 3335 BLK
01 3335 WHT
01 3335 GRY
02 2225 BLK

Tell me to go away...if I am too confusing

Thanks,

Mapuche

 

[Ngolem]

No...don't go away ....but it still isn't clear what you want from your example

1. What is the purpose of the Style table...all of the information in it appears in the color table...or are you just showing me part of the Style table...are other fields in the Style table being reported on??

2. Are you just showing me linkages? the Style table is linked to the Color Table by Sea and Style?

3. "When the styles table and colour tables are included in the same report the style will always be greater than 1 as colours are entered against styles individually."

sorry but this doesn't make sense to me.

give me the example again...after answering the above

Give me Style table data and Colour table data

THEN SHOW ME from that data what you want as the output in the report. Jim Broadbent

 

[Amapuche]

Ehhhhh sorry Jim, I will try again and answer your questions in order

1. The style table is the parent, it holds alot of data about a style such as cost, warehouse price, Recommended retail price and so on. A style + season field (primary key) only appear once in this table. So yes and yes, I was only showing you a portion of the style table data, and yes I will include other fields from the style table. I guess I was trying to be susinct and to the point....failing miserably.

2. The style colour table is like a colour look up table. So the individual colours are linked to a style + season. Note: the person who designed this database duplicates the style and season data in the colour lookup table, but not all the data I need to report on such as price for a style.

3. What I am trying to do is to find the duplicate style codes (with the associated price info and colour info) for a season range ie seas1 to seas3.

I tried to summarise the style field to exclude duplicates on the style table first.

1. Create new report, select styles table. select season, style, price fields
2. Group on style asc (group #1)
3. Group on season in specified order sea1, sea2, sea3 (group #2)
3. Total on style (count) deselect Add Grand Total
4. Finish
5. At report designer select "select expert"
6. on count of style : is greater than 1
7. Supress Group 1 header, Group 1 footer, group 2 header, group 3 footer.
8. Generate report and I get the following readout.

sea style desc cost rrp

1 22 coat 2.00 4.00
2 22 coat 3.00 6.00
3 22 coat 2.00 3.00

1 33 hat 3.00 5.00
3 33 hat 4.00 6.00

etc precicely what I want except for the colour info

Now when I add the colour table and using your formulas from my previous yelp for help. I get the following results

sea style desc cost RRP colours
1 22 coat 2.00 4.00 BLK BRN WHT GRY
2 22 coat 3.00 6.00 BLK GRY
3 22 coat 2.00 3.00 RED BLK WHT

1 25 scarf 3.00 10.00 RED

3 30 skrit 4.00 6.00 BLK WHT GRY

1 33 hat 3.00 5.00 RED WHT
3 33 hat 4.00 6.00 BLK GRY

the reason I think this is occuring is because the style + season combination now appears more than once in the colour look up table because colours are entered individually against a style code.

I hope this clears up the mud of my previous postings...then again its 2 am here...and you know how it goes...the brain that is.

Thank you very much for bearing with me I truely do appreciate it.

Mapuche.

P.S Table Data below.

Style Table Data

sea style desc cost RRP
1 22 coat 2.00 4.00
2 22 coat 3.00 6.00
3 22 coat 2.00 3.00
1 25 scarf 3.00 10.00
3 30 skrit 4.00 6.00
1 33 hat 3.00 5.00
3 33 hat 4.00 6.00

Colour Table Data
sea style colour
1 22 BLK
1 22 BRN
1 22 GRY
1 22 GRY
2 22 BLK
2 22 GRY
3 22 RED
3 22 BLK
3 22 WHT
1 25 RED
3 30 BLK
3 30 WHT
3 30 GRY
1 33 RED
1 33 WHT
3 33 BLK
3 33 GRY

 

[Ngolem]

got it now....so Scarf and Skirt are not wanted because they are not duplicated in style? Correct?

so if I see this data correctly...you don't want to see the same style in different seasons so try this

(with acknowledgement to MBarron in a previous post)

In the group1 footer place the following summary field (suppressed)

DISTINCTCOUNT({Table.sea},{Table.style})

Highlight this field and in the Selection expert select this field where the DistinctCount is greater than one

remove the other condition with respect to style noted below

5. At report designer select "select expert"
6. on count of style : is greater than 1

That might do it Jim Broadbent

 

[Amapuche]

Hi Jim,

I am going off to bed, so I will check again in our morning...well later on this morning. Thank you very much for your help!

To answer your question

Yes scarf and skirt are not needed because they are not duplicated.

And if I understand your statement correctly

"so if I see this data correctly...you don't want to see the same style in different seasons so try this"

I actually do want to see the same style in different seasons (for auditing purposes).

The only thing I dont want is those styles that are not duplicated.

Well I will try your example tomorrow, and let you know how I go..Thanks again! You have been a great help!

Mapuche.

 

[Ngolem]

I don't understand in this "GOOD" data result

sea style desc cost RRP colours
1 22 coat 2.00 4.00 BLK BRN WHT GRY
2 22 coat 3.00 6.00 BLK GRY
3 22 coat 2.00 3.00 RED BLK WHT

the only duplication here other than description is the fact that the same Style appears for each season Jim Broadbent

 

[Amapuche]

Hi Jim, yes what you are showing me is the "GOOD" data result. I want the data to look like this:

sea style desc cost RRP colours
1 22 coat 2.00 4.00 BLK BRN WHT GRY
2 22 coat 3.00 6.00 BLK GRY
3 22 coat 2.00 3.00 RED BLK WHT

1 33 hat 3.00 5.00 RED WHT
3 33 hat 4.00 6.00 BLK GRY

Not this:

sea style desc cost RRP colours
1 22 coat 2.00 4.00 BLK BRN WHT GRY
2 22 coat 3.00 6.00 BLK GRY
3 22 coat 2.00 3.00 RED BLK WHT

1 25 scarf 3.00 10.00 RED

3 30 skrit 4.00 6.00 BLK WHT GRY

1 33 hat 3.00 5.00 RED WHT
3 33 hat 4.00 6.00 BLK GRY

I want the scarf and skirt not to appear as there are no "duplicates" of these style codes in other seasons.

Mapuche

 

[Ngolem]

WHAT DEFINES A DUPLICATE???

If two seasons can have the same style...What is the duplicate? Jim Broadbent

 

[Amapuche]

Hi Jim,

The report objective is FINDING the duplicates, not removing the duplicates, sorry for confusing you.

I need to find the duplicate style codes over a range of seasons hence I do not want styles that only appear in one season showing up in my report.

My problem (finding duplicates within duplicates nightmare) is that using the formulas you gave me for my original problem I got all the style codes & colours showing up (duplicates + non duplicates, which is perfect for another report) even after adding a count on the styles to be > 1 as explained above. My assumption for why this was occuring was due to the colour look up table as the style count will always be greater than 1 when you link the colour table to the report due to the design of those two tables.

I feel like hiding under a big rock...hehehe.

Thanks for bearing with me though!

 

[Amapuche]

OH OH....
Sorry but my third post on this thread should have read this way....

...... I tried to summarise the style field to exclude NON DUPLICATES on the style table first.

1. Create new report, select styles table. select season, style, price fields
2. Group on style asc (group #1)
3. Group on season in specified order sea1, sea2, sea3 (group #2)
3. Total on style (count) deselect Add Grand Total
4. Finish
5. At report designer select "select expert"
6. on count of style : is greater than 1
7. Supress Group 1 header, Group 1 footer, group 2 header, group 3 footer.
8. Generate report and I get the following readout.

sea style desc cost rrp

1 22 coat 2.00 4.00
2 22 coat 3.00 6.00
3 22 coat 2.00 3.00

1 33 hat 3.00 5.00
3 33 hat 4.00 6.00

etc precicely what I want except for the colour info

 

[Ngolem]

Sorry but I give up....you have not DEFINED A DUPLICATE for me I don't understand what you are looking for (and believe me that is frustrating for me)

"I actually do want to see the same style in different seasons (for auditing purposes). "

The post before this statement by you should give you the the duplicates....IE. More than one season with the same style.

That and the description of the item are the ONLY duplicate criteria I can see. If this isn't the situation then UNLESS you can SPECIFICALLY DEFINE what is a duplicate in measurable terms....I cannot help you....sorry

Jim Broadbent

 

[Amapuche]

Hi Jim,

I misread your post regarding defining a duplicate.

The DUPLICATE is found in the style code field ONLY

Our manufacturing cycle is divided into seasons. Once a season (sea1) is completed the style codes are released and can be used in the next season (sea2, sea3 etc).

Hence there is posibility of the same style code being used for different garments being stored in the warehouse (even through the garments are different, and belong to different seasons).

I am really sorry, its been quite fustrating for me to be unable to explain it to you in words, especially since you have been so patient with me and willing to help, and I write user documentation...god help everyone....hahahah. I'm sure I would be much better at explaining this if I showed you my report in person, pointed to my problem and grunted considering my performance....sigh.

Mapuche....

P.S I want to give you 3 stars for helping!

 

[Amapuche]

Oh I forgot a second P.S.

I seriously don't expect you to respond to this thread again, what I think I will do, is go away write down my requirements so that they make sense and post this question again in 2 weeks, hopefully someone else will make you forget alllllll about me.... .

Thank you

Mapuche.

 

[Ngolem]

one final comment...

sea style desc cost RRP colours
1 22 coat 2.00 4.00 BLK BRN WHT GRY
2 22 coat 3.00 6.00 BLK GRY
3 22 coat 2.00 3.00 RED BLK WHT

if the style is released for reuse with each new season then :

WHY are these 3 items considered duplicates??? Jim Broadbent

 

[Amapuche]

Hi Jim,

I was going to do a Scarlet O'Hara and think about this tomorrow, but I will answer your question before I head off to bed. Speaking of which, I have been dreaming of crystal reports for the last two nights....urgh

To your question.

Even though these items are technically not duplicates, they are in terms of the warehouse stock...

We may manufacture a t-shirt in the following colours pink, green and blue for the summer season and call it style 22.

While the summer season is being manufactured the spring season will be in design, and the spring season style codes are entered into the system (as they are essentially unique keys - season + style) - no manufacturing has occured at this stage for the t-shirt

So in the spring season we design the same t-shirt style in pink, purple and white and call it style 22.

If I had a duplicate style report I would see the following based purely on the style code.

sea1 22 t-shirt pink green blue
sea2 22 t-shirt pink purple white

Now the summer t-shirts may not all sell, say I have 1000 pink t-shirts left, so they are carried over to the spring season...the inventory controller may not physically see the t-shirts but has to manually identify the "duplicate" styles one by one in the system (a very unfriendly system), check the stock on hand for that style and transfer the stock to the next season so at the end of all this

sea1 22 t-shirt pink becomes
sea2 22 t-shirt pink

at the moment identifying these "duplicate" styles is a tedious and time consuming for all involved.

So if I could produce this report to include only "duplicate" styles then it would save the worst step in this process.



Mapuche.

 

[Ngolem]

Sorry but I give up....(and believe me when I say I don't say this often)

I have repeatedly asked you for a way to define the duplicates in some measureable way and I am sorry but your discussions don't show it...only the need for it.

I cannot see from the data you have supplied any way of isolating a "Duplicate"

Perhaps a duplicate is a season/style/single color but you haven't really said that...

But this is my ABSOLUTE last post...sorry I have run out of ideas Jim Broadbent

 

[Amapuche]

Okay I understand,

I thought i did say, a duplicate is based on the style code.

Unfortunately thats all they are basing it on, and all I have to go by

Regards,

Mapuche

 

[dgillz]

Amapuche,

This thread got my attention because of 18 posts, and usually if Jim can't solve it, it can't be solved or it isn't being explained properly.

However, sometimes a second set of eyes is all it takes, hopefully this is one of them.

Why dont you insert a summary count of the field that defines duplicates, which appears to me to be the Style code. Then create a group selection formula where the count of style code must be greater than 1, so all non-duplicates dissapear from the report.

Does this make sense Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com