Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 22 total
Thread Number of Items
Sat, Jun 13 2009 4:24 AMPermanent Link

"Kerry Cain"
I would like to build a program where I could record our production - split
into various fields eg Size, Type and Main Colour and Trim Colour. - so
that I could query the database and determine some idea of requirements
based on previous stats.

My problem however is that we manufacture the product from 14 main colours
with trim from 13 colours  - ie theoretically up to  14 * 13 = 182
combinations

In practice this would most likely be approx. 60 or 70.

I would like to be able to determine that we made eg   44 Red/Yellow
                                                                            
       35 Blue/ Red
                                                                            
       32 Black / White  etc for perhaps the top 5 to 10 colour
combinations for the week/ month etc
in descending order by number.

If anyone could give me some idea as to how I could do this I would be
greatly appreciated.

I'm using D5 and 3.19 DBISAM.

thanks again Kerry

Sat, Jun 13 2009 7:23 AMPermanent Link

Fernando Dias

Team Elevate Team Elevate

Kerry,

I think that what you want is not difficult to get using an SQL "SELECT ...
GROUP BY" statement, but I can't be more specific not knowing the structure of
the tables. Can you give us some more information about the structure of the
tables where you will insert the production data?

--
Fernando Dias
[Team Elevate]
Sat, Jun 13 2009 7:53 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Kerry


I'm not sure from your post just what level of help you're looking for. It could be at the sort of level that Fernando's assuming ie you have your tables defined and you're just not sure how to query them or it could be you need help with defining the table structures , forecasting algorithms etc.

Can you give a bit more info please.

Roy Lambert [Team Elevate]
Sun, Jun 14 2009 1:59 AMPermanent Link

"Kerry Cain"
Thankyou Roy and Fernando for your replys to my question.

I haven't actually got to designing the table yet but the following would be
OK

Field                Type            Desc.
Auto
Name                String        Name of product
Size                    S              Product can be made in 3 sizes
Group                S                Name of group of products  - possibly
optional
Main Colour       S                Name of colour - up to 14 colours
Trim Colour        S                Name of trim colour - up to 13 colours -
this could be blank if main and trim are the same
DateMade            D            Date of production
DateSold            D               Date of sale
PriceMade            Curr        Price of product made
PriceSold            Curr          Price of product sold.
NoMade            I                Number made
NoSold                I              Number Sold

I think the above would be all I would need in my table.
Just a bit of background

the product is PVC horsegear, bridles, reins, halters, harness etc and a
typical order would be for a

   Full size, Blue (Main colour) with Red trim * 6 halters
   Cob size, Blue with Yellow trim * 3 halters
  Pony size, Black with White trim * 2   bridles  etc

I can split them up into their various groups - halters, bridles etc and
even sizes - but my problem, and I must admit I haven't got a clue where to
start -
is being able to split them up into their colour combinations.

I would like to be able to say that for that period we made x number of
blue/red Full sized halters, x number of blue/yellow etc for possibly the
top 10
items so that I can say this period lets get ahead and make these extra.

Any suggestions would be greatly appreciated
Thanking you

Kerry





Sun, Jun 14 2009 4:26 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Kerry


OK this is where you need to start thinking about what you're going to do with the information.

Your original post was as an aid to production scheduling and if that's the only reason the simplest (not the best but the simplest) is to have a field which combines the colours with a separator (eg Red:Blue or Green:Pink) either instead of or as well as the two separate fields.

If you want to use it as an aid to materials purchase then you want to keep the two fields separate to allow easier calculations.

Having said that its easy enough to keep the fields separate and combine either using Delphi or SQL for reportage eg (and obviously untested)

SELECT MainColour+':'+TrimColour AS Colours, Size, SUM(NumberSold) FROM Production WHERE DateSold BETWEEN date1 AND date2 GROUP BY Colours,Size

One possible problem is I don't know if V3 will support this V4 does.

Roy Lambert
Sun, Jun 14 2009 6:04 AMPermanent Link

Fernando Dias

Team Elevate Team Elevate

Roy, Kerry

<< Having said that its easy enough to keep the fields separate and combine
either using Delphi or SQL for reportage eg (and obviously untested)>>

I would go with separated fields, for many reasons:
- As Roy said, it's easy to combine them at any time, if needed
- It permits search and join with the fields separated and/or combined
- It's not easy to change one of the colours only if they are stored combined

<<SELECT MainColour+':'+TrimColour AS Colours, Size, SUM(NumberSold) FROM
Production WHERE DateSold BETWEEN date1 AND date2 GROUP BY Colours,Size
One possible problem is I don't know if V3 will support this V4 does.>>

It will, no problem with that.


--
Fernando Dias
[Team Elevate]
Sun, Jun 14 2009 9:32 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Fernando

>- It's not easy to change one of the colours only if they are stored combined

Oh yes it is Smiley

Roy Lambert
Mon, Jun 15 2009 4:21 AMPermanent Link

"Kerry Cain"
Hi Roy and Fernando

Thanks very much for your help - for some reason I forgot about using SQL

Will have a go in the near future.
Once again thanks

Kerry

Wed, Jun 17 2009 12:55 PMPermanent Link

David Puett
Oh no, no, no.  Get rid of most of those string fields.  Size, Group, and
both colors should all be integers referencing other tables.

Here are a couple sources about data normalization

http://databases.about.com/od/specificproducts/a/normalization.htm

http://en.wikipedia.org/wiki/Database_normalization

It is a little more work in setting everything up but it makes things so
much simpler later.


On 6/14/09 1:58 AM, in article
0E655DD6-1690-418F-93EA-9C98952B3BC7@news.elevatesoft.com, "Kerry Cain"
<cooper@members.bordernet.com.au> wrote:

> Thankyou Roy and Fernando for your replys to my question.
>
> I haven't actually got to designing the table yet but the following would be
> OK
>
> Field                Type            Desc.
> Auto
> Name                String        Name of product
> Size                    S              Product can be made in 3 sizes
> Group                S                Name of group of products  - possibly
> optional
> Main Colour       S                Name of colour - up to 14 colours
> Trim Colour        S                Name of trim colour - up to 13 colours -
> this could be blank if main and trim are the same
> DateMade            D            Date of production
> DateSold            D               Date of sale
> PriceMade            Curr        Price of product made
> PriceSold            Curr          Price of product sold.
> NoMade            I                Number made
> NoSold                I              Number Sold
>
> I think the above would be all I would need in my table.
> Just a bit of background
>
> the product is PVC horsegear, bridles, reins, halters, harness etc and a
> typical order would be for a
>
>     Full size, Blue (Main colour) with Red trim * 6 halters
>     Cob size, Blue with Yellow trim * 3 halters
>    Pony size, Black with White trim * 2   bridles  etc
>
> I can split them up into their various groups - halters, bridles etc and
> even sizes - but my problem, and I must admit I haven't got a clue where to
> start -
> is being able to split them up into their colour combinations.
>
> I would like to be able to say that for that period we made x number of
> blue/red Full sized halters, x number of blue/yellow etc for possibly the
> top 10
> items so that I can say this period lets get ahead and make these extra.
>
> Any suggestions would be greatly appreciated
> Thanking you
>
> Kerry
>
>
>
>
>
>

Thu, Jun 18 2009 1:43 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

David

>Oh no, no, no. Get rid of most of those string fields. Size, Group, and
>both colors should all be integers referencing other tables.

That is normalisation gone berserk. It will not make things simpler later but more complex. Joins or calculated fields will be required to display data in human comprehensible form (unless their staff become used to colour 47 rather than red). In a big complex database where the data can be manipulated by many programs there may (note I say may not will) be some justification for this approach but in a small company which isn't massively IT sophisticated its neither required nor suitable.

Roy Lambert
Page 1 of 3Next Page »
Jump to Page:  1 2 3
Image