How to design a table or table to take an x, y control columns and all the possible array values
in [Tables]
|
From: Steve on 31 Jan 2010 14:02 Let's start with the following for discussion. Provide your comments and we can modify as needed. TblSupplier SipplierID <supplier fields> TblProduct ProductID SupplierID ProductName MaxWidth StartWidth WidthIncrement MaxDrop StartDrop DropIncrement TblProductSizePrice ProductSizePriceID ProductID ProductWidth ProductDrop Price For data entry to TblProductSizePrice, you would use MaxWidth, StartWidth and WidthIncrement to get the ProductWidth entries You would use MaxDrop, StartDropand DropIncrement to get the ProductDrop entries. For your invoices you would use ProductSizePriceID to get the item price. Steve santus(a)penn.com "Blinds Nottingham" <bradrail.blinds.awnings(a)googlemail.com> wrote in message news:d6e3bc07-9631-4c11-b9cd-034d3ccd8e2c(a)g1g2000yqi.googlegroups.com... > Hi > > Looking for some guidance on how to design.. then manipulate a price > grid structure in a database > > the first part of the project will to design a data table or tables > which i can see ideas on how to do it but not too sure the best way > > Each product that is supplied has its only price table and its own > sizes always variants for each product to product > > Ie Roller Blinds has a max width of 3 meters starting at 400mm then at > an increment of 200mm 400,600,800,1000 ... etc becomes the grid column > headings then the drop row heading may be 1800,2200,2600 > where a vertical blind may have a max width of 5m starting at 600m and > increments of 250 thus 850,1100,1350 etc and the drop might be > 400,800,1200 > > where i am trying to describe the constant increment in x and y value > no one product may ever be the same > > then for ever width x drop there is a price making a data / look up > table / grid > > i presume i could make a table which said > > productid , product name, width, drop, price > > is this going to be the best way... in consideration the next step is > to look how i can display a input form which can step through each of > the x and y to enter a price which make me think that the price may > need to be in a different table and linked to the width and drop .. or > is that overkill > > comments welcome on concepts and ideas... also if some one know enough > about a way of creating an input grid so they user can enter width and > drop increments then show a grid with heading and column and then edit > directly the price in i would be gratefull the only grid i have seem > see to be table grids rather than an 2d array type of grid. > > thanks > > Nick >
From: Piet Linden on 31 Jan 2010 14:05 On Jan 31, 10:35 am, Blinds Nottingham <bradrail.blinds.awni...(a)googlemail.com> wrote: > just read your coffee induce thought just before i posted.. > > the x and y are related to the product.. and the price is related to > the x and y or a selected product > > hence the thought of > > productgridid,productid,productx,producty > > this would have to have the product reference and the productx and > producty entered manually.. didnt think for the 20 enteries the (x * > y) worth splitting again in to productgridxid,productid,productx and > productgridyid,productid,producty > then as u said dont join them select them out of each table then get > the corrosponding prices from a third table, and then populate an > array i presume for manipulation > > pricegridid,productid,productidxid,productyid,price > > so combining the productgird and the x and y and the pricegrid price > for each of the x and y then list them > > i am glad its making you scratch your head > > generally rule is ... that once a product is set up, that the sizes > wont change only the prices for all x and y's and since the price and > constant increment it becomes a bum ache... > > looking forward to your idea etc Sounds like you might want to create a deliberate cartesian product and turn it into an append query. Say you have tables that contain all the values for X and Y. You could create a cross-product of the two tables and turn it into an append query, which would do most of the grunt work for you. I'll make up a simple example... Say I have two models of car, "Model A" and "Model T", and they can come in several colors: Red, Orange, Yellow, etc. If I create a table of Colors CREATE TABLE Colors( ColorName TEXT(20) PRIMARY KEY ); and then I create a table of Models CREATE TABLE Car( Model TEXT(20) PRIMARY KEY ) If I create the following query, I will get all possible combinations of (Car.Model, Colors.ColorName) SELECT Car.Model, Colors.ColorName FROM Car, Colors ORDER BY Car.Model, Colors.ColorName; Note that there is NO join between the two tables. That's on purpose. If you take that and turn it into a make-table query, you'll get a table of all the possible values... Hope that gets you started.
From: Blinds Nottingham on 2 Feb 2010 13:45
thank you to all who have give me a really good start point on this .. will have to sit down now and have a go at testing them out.. does any one know of a plugin which will allow me to display the data as a grid and enter the price rather having to keep steping through each record.. i know i probably need to ask in the forms area .. once again thanks for all the input it is greatly appreciated |