How to design a table or table to take an x, y control columns and all the possible array values
in [Tables]
|
From: Blinds Nottingham on 31 Jan 2010 10:35 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: PieterLinden via AccessMonster.com on 31 Jan 2010 10:48 Can you be more specific about exactly what kind of data you plan on putting in this database? Maybe a couple of illustrative examples? Are you talking about dimensions of a shade or something? Can't really answer without further clarification, sorry... Blinds Nottingham wrote: >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 -- Message posted via http://www.accessmonster.com
From: PieterLinden via AccessMonster.com on 31 Jan 2010 10:58 Maybe after my fifth cup of coffee I'll be awake. (not used to that nice BOLD subject line either, I guess!) <g> One way of doing this is to put the columns x and y in separate tables, and then put the two tables in the same query, but don't join the tables. for example... SELECT tblA.x, tblB.y FROM tblA, tblB; this will return all combinations of values from tblA.x and tblB.y. So if tblA has 3 records and tblB has 5, you'll end up with 15 records. So you just turn the select query into an append query or make table query. Then you'd have to update the prices somehow - either manually or with an update query... depends if the price is a function of size... -- Message posted via http://www.accessmonster.com
From: Blinds Nottingham on 31 Jan 2010 11:19 Hi thanks for replying so quickly I can show you what i mean in an excel format but looking at trying to convert this to a database so i can do lookup etc on prices dependant on product and width and drop sizes there is the idea of 3 blinds, 2 the same type but from different suppliers, who have different size for the prices and increments so to show that nothing is standard then an example of a roller blind .. prices are fictitious but eventually want to enter a cost price and then have access or vb dynamically create the end results.. i am a noobie really to this as a developer, but have sorted many database and systems.. done alot of vba in excel and some in access hope this makes things a bit clearer http://www.bradrail.co.uk/uploads/pricelist/pricelistexample.xls and thanks again
From: Blinds Nottingham on 31 Jan 2010 11:35
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 |