Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 22 total |
Number of Items |
Sat, Jun 13 2009 4:24 AM | Permanent 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 AM | Permanent Link |
Fernando Dias 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent Link |
Fernando Dias 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 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Fernando
>- It's not easy to change one of the colours only if they are stored combined Oh yes it is Roy Lambert |
Mon, Jun 15 2009 4:21 AM | Permanent 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 PM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 3 | Next Page » | |
Jump to Page: 1 2 3 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |