Grouping like colors 1

[mhcruella]

My Question:
Is there a way to group jobs that have like colors?

Background:
My database has each job with a list of inserts for that month. Each insert has a color. A color can be used for more than one insert in a job. For example, job #1 has 20 inserts, but only the colors blue and green for one month. On a monthly basis I want to be able to list all the jobs that get the same color inserts. There are about 25 different colors that can be used and a job can consist of any conbination of those 25 colors. I did a crosstab query and was able to get the colors listed for each job, but that was in job number order. Someone has to manually go through and group the jobs that have similar colors.

Any ideas? Thanks.

 

[pdldavis]

Hi, I'd think you would about have to have another field to store a unique color identifier for the colors used and then use that ID to sort your jobs.

I put a couple unbound check boxes for colors on a form with a button. Assign a value to each check box. Select the checkboxes for the colors used and create a string to store as an ID:

Private Sub Command12_Click()

Dim Red
Dim Green
Dim Result

Select Case Me.ckRed
Case Is = -1
Red = "Rd,"
Case Is = 0
Red = ""
End Select

Select Case Me.ckGreen
Case Is = -1
Green = "Grn,"
Case Is = 0
Green = ""
End Select


Result = Red & Green
MsgBox Result

Me.txtJobColorSelection = Result
End Sub

Something along those lines would be a bit cumbersome but it should work.

 

[mhcruella]

I'm not sure I understand. Do you mean to group the names of the colors into a string and then do a group by? So I would have bluewhitegreen?

I want to be able to group by the colors in the job.

Job #1 Job #2 Job #3 Job #4
blue red green white
white green blue blue
green red green

I want to be able to have a list that shows that Job #1 and Job #4 should be grouped together. I can get the above information using the crosstab query, but I don't know how to group it once there.

 

[pdldavis]

Well, yes...that's what I would do unless someone else has a better idea. From what I understand you are saying, you want to group jobs that use the same colors and it can be any number of colors. I don't know that you can group the way you are describing using a cross tab. I rarely use cross tabs, so I may be wrong about that.

Say you have two jobs with red,white, green and two jobs with yellow and red. If you created a unique string, an ID for that color combination and stored it with the job, then sorting and grouping would be a no brainer. It can be letters, numbers, whatever you like.

I have the same problem in a different field. I may have a table that has 3000 electrical cables that get routed throughout a construction job. This comes over as a flat file and I have to convert it. Construction wants to pull (route) all the cables that share the same routing. There are 20 possible combinations of ways to route, so what I am looking for are routes that match.

The way I finally came up with something that worked was to assign a unique ID to each combination of routes and paste that next to the cable. That way I could easily group all the cables for a particular pull. I've got it automated to where it plugs in the ID's when I import the file.

If there's an easier way, I'm all for it.

It is an interesting problem.

 

[MichaelRed]

Listing the actual db/table structure would be helpful, at least to me. I do not understand wheather the "Job" is a seperate table or a record, or even multiple records within a table.


As an aside, colors in vb(a) are represented as a long (numeric), so there is no actual need to create a seperate list of relatioships between the name and an arbitrary valu to sort them by.

e.g. as few examples:

? vbred
255

? vbgreen
65280

? vbyellow
65535

? vbwhite
16777215

? vbblack
0

? vbblue
16711680
MyRed = 127
MyGrn = 128
MyBku = 127


MyColor = RGB(MyRed, MyGrn, MyBlu)
? MyColor
32895

Other functions to manipulate 'colors' are available, and many others have been created and posted in varirous fora.



MichaelRed
mlred@verizon.net

 

[willir]

Would you consider going back to your design?

I suepct the best way to resolve the problem is to setup a many-to-many relationship between colour inserts and job.

A table along the lines of

tblJobInserts
JobID - foreign key to job
ColourCode - foreign key to colour insert

primary key is JobID + Colourcode

This way you easily see the colours used for a job, and just as easily see what jobs are used for colour.

If I am way off base... shoot me.

Richard

 

[mhcruella]

pdldavis,

I think your idea is very helpful. I can't quite figure out how to go about writing it. I have 25 colors that would lead to 25! color combinations, right?

Would I just concantinate the color names and write that to a table, then skip the color combination if it is already written to the table? Is this what you mean?

 

[pdldavis]

Hi, what I suggested works if the record is structured as a flat file, where you have the job and 25 color fields in one row. This is not the usual way of doing things.

What williR is suggesting would be appropriate with normal db design. I still don't know though how you would obtain a grouping using the correct method.

You would have to have an exact match of colors used for one job with an exact match of colors in another job to obtain the grouping you want. Maybe some kind of recordset iteration?

At any rate, describing your table structures would be beneficial in coming up with a solution.

 

[willir]

As an FYI, what I am suggesting is...

tblJobInserts
JobID - foreign key to job
ColourCode - foreign key to colour insert

Then...
[tt]
JobID ColourCode
1 blu1
1 wht2
1 grn1
2 red3
2 grn1
2 red1
3 wht1
3 blu1
3 grn1
4 blu2
4 wht1
4 red3
[/tt]

Using this approach, you can generate your results just as easy by JobID as by ColourCode. There is a many-to-many relationship between colour and job - a job has many colours, and a colour can be included on many jobs.

I have embelished a bit by using colour codes - different shades of blue, red, etc. You would have a colour table too.

ColourTbl
ColourCode - primary key
ColourDescritpion


I guess it might seem odd to break the job into subsets - one subset for each colour, but it does make analysis more easy.

Here is one Cross Tab...
TRANSFORM Count(JobInsertTbl.ColourCode) AS CountOfColourCode
SELECT JobInsertTbl.JobID
FROM JobInsertTbl
GROUP BY JobInsertTbl.JobID
PIVOT JobInsertTbl.ColourCode;

Here is another...
TRANSFORM Count(JobInsertTbl.JobID) AS CountOfJobID
SELECT JobInsertTbl.ColourCode
FROM JobInsertTbl
GROUP BY JobInsertTbl.ColourCode
PIVOT JobInsertTbl.ColourCode;

A simple Count / Group by aggregate may also be useful...
SELECT JobInsertTbl.ColourCode, Count(JobInsertTbl.JobID) AS CountOfJobID
FROM JobInsertTbl
GROUP BY JobInsertTbl.ColourCode;


Richard

 

[MichaelRed]

Willr's suggestion make more sense, but I still do not see the need to create artificial 'values' for the color codes when they already exist. VB uses a numeric code for colors which is widely recognized and fairly well documented in the 'official' documentation as well as thoroughly explored with examples in the public domain. My brief examples aside, These (Tek-Tips) fora have several excellent threads with procedures for maniplulting the various color ingredients, and illustrating their use. format, etc.




MichaelRed
mlred@verizon.net

 

[pdldavis]

Richard, thanks for the primer on cross tabs. The question remains I think, how do you group these results to display the records whose color combinations match? That's the $20.00 question. Below Jobs 1 & 2 have the same color combo, as do 4 and 5. I would want a report that has:

Color Inserts 1: JobID 1,2
Color Inserts 2: JobID 4,5
Color Inserts 3: JobID 3

and so on. I think that is what mbCruella is after.

JobID 1 2 4 5 6
1 1 1 1
2 1 1 1
3 1 1
4 1 1 1
5 1 1 1

This is the problem I ran into with my own cable routing dilemma and why I kept the original table as a flat file. I could not group the results without inserting another identifier.

 

[mhcruella]

pdldavis has the question correct. I have no problem extracting the colors inside of a job. The $20.00 question is grouping them.

We have a 7 pocket collator in production. One color goes into each pocker. Instead of having the operator take out the colors and switch the pockets around, we want to be able to group jobs by color. To change out all the pockets ( 7 colors) takes about 20 minutes of down time (setup time). We want to group the jobs so that the number of pockets that need to be changed can be kept at a minimum so that the down time is kept to a minimum.

Tables

tblEnvColors
EnvColorID -primary key
EnvColorDescript

tblJobs
Job#
Envdate
EnvSeqNo
EnvColor

The Job#, Envdate and EnvSeqNo are a composite primary key. I don't want any job to have a duplicate date and seqno. The seqno designates the order of the envelopes (colors) if they fall on the same Envdate.

 

[mhcruella]

MichaelRed - I am having difficulty finding the other threads you mentioned that deal with color issues. Can you please list a few?

 

[pdldavis]

That's why I don't think a cross tab will work. A simple count would not be sufficient because you need to identify the colors and then find those records where there is an exact match. The job number is of secondary importance. I'm still thinking recordset.

 

[pdldavis]

I added an extra ID field - InsertID to the jobs table and to the colors table for selecting colors usedd for a job. I included a form with some unbound check boxes on it for several colors. There's a button on it that concantenates into a string the checked values. That gets inserted into the jobs InsertID field.

There is a subform for the colors selected. On the afterupdate of a combobox selection for colors, that same string gets inserted into the the InsertID field in the colors table.

That way, you can query for exact color matches and group them by the concatenated string.

It's kind of clunky, but it works.

 

[mhcruella]

I got the colors sorted!

I used a recordset and used a priority field to sort by the envelope colors that are used the most. I also used the 1st three colors to use a group by. This way white-blue-yellow-cherry will fall under the same grouping as white-blue-yellow-green.

I used a Do loop so that I could go through each record.

With rstColorSet
        rstColorSet.MoveLast
        rstColorSet.MoveFirst
        
        Do While (rstColorSet.AbsolutePosition + 1) > 0
            NewTrackNum = Format(rstColorSet!MMNo, "0000") & Format(rstColorSet!periodcode, "00") & Right(rstColorSet!ScheduleYear, 1) & rstColorSet!MlgType
            'Test to see if the MMNo or the Mlgtype have changed
            If OldTrackNum = NewTrackNum Then
                intCountInsert = intCountInsert + rstColorSet!numaddrinserts
                strColorSet = strColorSet & "-" & Format(rstColorSet!ColorNum, "0000")
                strColorSetDescrip = strColorSetDescrip & "-" & rstColorSet!envcolor
                If intNumColors < 3 Then
                    strColorGroup = strColorGroup & "-" & Format(rstColorSet!ColorNum, "0000")
                    strColorGroupDescrip = strColorGroupDescrip & "-" & rstColorSet!envcolor
                End If
                intNumColors = intNumColors + 1
            Else
            'if we are dealing with a new MMNo or MlgType add the 'old' value to the tables before updating them
                If OldTrackNum > 0 Then
                  IDNum = IDNum + 1
                  'add the values to the tblColorSet
                  rstColorSettbl.AddNew
                    rstColorSettbl!ColorSetID = IDNum
                    rstColorSettbl!colorset = strColorSet
                    rstColorSettbl!colorsetdescrip = strColorSetDescrip
                    rstColorSettbl!ColorGroup = strColorGroup
                    rstColorSettbl!ColorGroupDescrip = strColorGroupDescrip
                  rstColorSettbl.Update
                  
                  'add values to the tblColorSetByJob
                  rstColorSetByJob.AddNew
                    rstColorSetByJob!MMNo = rstColorSet!MMNo
                    rstColorSetByJob!MlgType = rstColorSet!MlgType
                    rstColorSetByJob!Period = rstColorSet!periodcode
                    rstColorSetByJob!SchedYear = rstColorSet!ScheduleYear
                    rstColorSetByJob!JobColorSet = strColorSet
                    If intCountInsert > 18 Then
                        rstColorSetByJob!RunType = "Manual"
                    Else
                        rstColorSetByJob!RunType = "Auto"
                    End If
                    rstColorSetByJob!AddrInserts = intCountInsert
                    rstColorSetByJob!NumOfColors = intNumColors
                  rstColorSetByJob.Update
                End If
                  
                  'Update and initialize values for new mailing
                  OldTrackNum = NewTrackNum
                  intCountInsert = rstColorSet!numaddrinserts
                  strColorSet = Format(rstColorSet!ColorNum, "0000")
                  strColorSetDescrip = rstColorSet!envcolor
                  strColorGroup = Format(rstColorSet!ColorNum, "0000")
                  strColorGroupDescrip = rstColorSet!envcolor
                  intNumColors = 1
                
             End If
                
            rstColorSet.MoveNext
        Loop
    End With

The trackNo is just a way to distinquish jobs from each other. There is probably an easier way, but this one worked. Let me know your thoughts.