If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to design a table or table to take an x, y control columns andall the possible array values
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 |
#2
|
|||
|
|||
How to design a table or table to take an x, y control columns andall the possible array values
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 |
#3
|
|||
|
|||
How to design a table or table to take an x, y control columns andall the possible array values
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 |
#4
|
|||
|
|||
How to design a table or table to take an x, y control columnsand all the possible array values
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/pr...istexample.xls and thanks again |
#5
|
|||
|
|||
How to design a table or table to take an x, y control columnsand all the possible array values
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,pric e 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 |
#7
|
|||
|
|||
How to design a table or table to take an x, y control columnsand all the possible array values
On Jan 31, 10:35*am, Blinds Nottingham
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,pric e 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. |
#8
|
|||
|
|||
How to design a table or table to take an x, y control columnsand all the possible array values
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 |
Thread Tools | |
Display Modes | |
|
|