How to design a table or table to take an x, y control columns andall the possible array values

[Blinds Nottingham]

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

 

[PieterLinden via AccessMonster.com]

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...

 

[PieterLinden via AccessMonster.com]

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...

 

[Blinds Nottingham]

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

 

[Blinds Nottingham]

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

 

[Steve]

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
(e-mail address removed)




message
news:[email protected]...

 

[Piet Linden]

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.

 

[Blinds Nottingham]

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