finding a value in a grid of numbers

[Guest]

I need to create a table and form that will search a matrix of prices and
find the correct value depending on two inputs. One of width and length. I
sell blinds and all our pricing is based on price matrices. Width is along
the top and length along the left side with the corresponding values at the
inter sections. For example, If I need the price of a blind that is 31" wide
by 61" long, I would go to the corresponding matrix for that particular type
of blind and then go down the side to 66" (everything between 66 and above
60.1 is the same) and then across the top to 32" (everything from 30.1 to 32
is the same) and the correct price is at the intersection of the row and
column. How can I set up a table and form that can give me this information.
I already have all the matrices in excel and I will want to import them in. I
will build a form where one can enter the product, type of product, width,
length, and quantity, and discount to come up with a sales price for a client.

Any thoughts or suggestons. I am good at excel, however; access is new to me.

Thanks, JLS

 

[Guest]

In a relational database this would be modelled rather differently from a
spreadsheet. You would have four tables Products, Widths, Lengths and
Prices. The first three would each have just one column, defined as the
primary key, Product, Width and Length. The Prices column would have four
columns Product, Width, Length and Price, the first three being foreign keys
referencing the primary keys of the other three tables. Data would be input
into the Prices table via a bound form with combo boxes bound to the Product,
Width and Length fields, each getting their list form the relevant other
table and a text box bound to the Price field.

To produce a matrix from this data, as with your spreadsheet you would use a
crosstab query:

TRANSFORM FIRST(Price) AS UnitPrice
SELECT Product, Length
FROM Prices
GROUP BY Product, Length
PIVOT Width;

The use of the FIRST function here is arbitrary as there is only one price
per product/length/width combination. You could use any aggregate function
in fact.

To calculate prices you can design an unbound form with combo boxes for
Product, Width and Length just as on the bound form, but the combo boxes
would this time be unbound. You would also have unbound text boxes for
Quantity and GrossPrice and Discount.

To calculate the gross price use an expression for the GrossPrice text box's
ControlSource property, referring to the 3 combo boxes and the text box on
the form, which looks up the price from the prices table and multiplies it by
the quantity and discount like this:

=(DLookup("Price", "Prices", "Product = """ & [cboProduct] & """ And Length
= " & [cboLength] & " And Width = " & [cboWidth]) * [txtQuantity])) * (1 -
([txtDiscount]/100))

This assumes that the Product field is text data type, hence the wrapping of
the value in quotes) and Length and Width fields are number data types. I've
also assumed that the discount is entered as a percentage, so if 10 is
entered the price will be multiplied by (1 – (10/100)), i.e. 0.9.

To import the existing data from Excel I'd imagine you'd have to pivot it in
Excel first to get it into a format to match the Prices table, but I'm not an
Excel expert by any stretch of the imagination, so you probably are more
familiar with this than I am. Once you have the Prices table set up from
the Excel data you can easily append data from it into the other tables with
append queries. As the columns in those are their primary keys only one
instance of the duplicate values from the Prices table will be added to each
of the other tables when you run the append queries. You can then set up the
relationships between Prices and the other tables to enforce referential
integrity.

 

[Guest]

Ken,

Thanks a million. Now I just have to learn what most of the things you have
told me mean. I get the drift, however; I don't know the terminology yet.
Just to make sure I have the complete answer, here are a few more things that
I neglected to include in my original post. When pricing a blind or shade, we
measure to the 1/8". The price grids use "spreads" you might say for the
prcies. For instance a blind that is 31" wide and 72" long is the same price
as one that is 31 1/4" wide and 72 3/4" long. This is because the width axis
is in increments (i.e. 30, 36, 42, 48, 54, etc. as is the length axis 36, 42,
60, 66, etc. so all widths from 30 to 35 7/8 are the same as are lengths from
60 to 65 7/8, etc. Would your explanatioj still apply in this case? Are there
any other refinements I would need to make to make it fit in this model?

We need to have the 1/8"'s inorder to then place the order with the
manufacturer. We only want to input the data once. That is the whole idea.

Thanks again for your quick and thorough answer.

Jayson Smith

In a relational database this would be modelled rather differently from a
spreadsheet. You would have four tables Products, Widths, Lengths and
Prices. The first three would each have just one column, defined as the
primary key, Product, Width and Length. The Prices column would have four
columns Product, Width, Length and Price, the first three being foreign keys
referencing the primary keys of the other three tables. Data would be input
into the Prices table via a bound form with combo boxes bound to the Product,
Width and Length fields, each getting their list form the relevant other
table and a text box bound to the Price field.

To produce a matrix from this data, as with your spreadsheet you would use a
crosstab query:

TRANSFORM FIRST(Price) AS UnitPrice
SELECT Product, Length
FROM Prices
GROUP BY Product, Length
PIVOT Width;

The use of the FIRST function here is arbitrary as there is only one price
per product/length/width combination. You could use any aggregate function
in fact.

To calculate prices you can design an unbound form with combo boxes for
Product, Width and Length just as on the bound form, but the combo boxes
would this time be unbound. You would also have unbound text boxes for
Quantity and GrossPrice and Discount.

To calculate the gross price use an expression for the GrossPrice text box's
ControlSource property, referring to the 3 combo boxes and the text box on
the form, which looks up the price from the prices table and multiplies it by
the quantity and discount like this:

=(DLookup("Price", "Prices", "Product = """ & [cboProduct] & """ And Length
= " & [cboLength] & " And Width = " & [cboWidth]) * [txtQuantity])) * (1 -
([txtDiscount]/100))

This assumes that the Product field is text data type, hence the wrapping of
the value in quotes) and Length and Width fields are number data types. I've
also assumed that the discount is entered as a percentage, so if 10 is
entered the price will be multiplied by (1 – (10/100)), i.e. 0.9.

To import the existing data from Excel I'd imagine you'd have to pivot it in
Excel first to get it into a format to match the Prices table, but I'm not an
Excel expert by any stretch of the imagination, so you probably are more
familiar with this than I am. Once you have the Prices table set up from
the Excel data you can easily append data from it into the other tables with
append queries. As the columns in those are their primary keys only one
instance of the duplicate values from the Prices table will be added to each
of the other tables when you run the append queries. You can then set up the
relationships between Prices and the other tables to enforce referential
integrity.

I need to create a table and form that will search a matrix of prices and
find the correct value depending on two inputs. One of width and length. I
sell blinds and all our pricing is based on price matrices. Width is along
the top and length along the left side with the corresponding values at the
inter sections. For example, If I need the price of a blind that is 31" wide
by 61" long, I would go to the corresponding matrix for that particular type
of blind and then go down the side to 66" (everything between 66 and above
60.1 is the same) and then across the top to 32" (everything from 30.1 to 32
is the same) and the correct price is at the intersection of the row and
column. How can I set up a table and form that can give me this information.
I already have all the matrices in excel and I will want to import them in. I
will build a form where one can enter the product, type of product, width,
length, and quantity, and discount to come up with a sales price for a client.

Any thoughts or suggestons. I am good at excel, however; access is new to me.

Thanks, JLS

 

[Guest]

To cater for this I'd recommend expanding the Widths and Lengths tables so
that all the 1/8" dimensions are stored, though this would be as decimal
values of course, not fractions, so 31 1/8" would be 31.125 and so on. So
you can see the fractions in the combo boxes rather than the decimal values
create a table Fractions with two columns Decimal and Fraction, so the rows
in the table would be:

0.125 1/8
0.25 1/4
0.375 3/8
0.5 1/2
and so on to:
0.875 7/8

The RowSource for the combo boxes on the form to select the length or width
would join this to the Lengths or Widths table, e.g. for Lengths:

SELECT Length, INT(Length) & " " & Fraction AS FractionalLength
FROM Lengths INNER JOIN Fractions
ON Lengths.Length - INT(Lengths.Length) = Fractions.Decimal
ORDER BY Length;

So you just see the fractional value in the combo box you'd set its
properties like this:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches)

The exact second dimension of the ColumnWidths property is not crucial, but
the first one must be zero to hide the first column. This hidden column is
the bound column so when you select a fractional value from the list the
value of the combo box is actually the decimal equivalent.

In the Prices table replace the Length and Width columns by four columns,
LowerLength, HigherLength, LowerWidth and HigherWidth. These would define
the range for each price so you might have a row with width values of 30 and
30.875 (30" to 30 7/8"). The criteria for searching would now look for
values within the ranges (spreads):

=(DLookup("Price", "Prices", "Product = """ & [cboProduct] & """ And " &
[cboLength] & " Between LowerLength And HigherLength And " & [cboWidth] & "
Between HigherWidth And LowerWidth")) * [txtDiscount] * (1 -
([txtDiscount]/100))

You don't need to worry about the 1/8"s being in order. A table in a
relational database is a set, which has no intrinsic order. You order the
rows on the fly via the ORDER BY clause in a query, as in the one above for
the RowSource property. The rows can be entered into the table in any order,
so inserting a ne2wone at a later stage is no problem; it will appear in its
correct position in a query's result set.

 

[Guest]

Thanks again Ken. I purchased the Maran Illustrated Access 2003 book to help
me learn the terminology. You just saved me hours of trial and error.

To cater for this I'd recommend expanding the Widths and Lengths tables so
that all the 1/8" dimensions are stored, though this would be as decimal
values of course, not fractions, so 31 1/8" would be 31.125 and so on. So
you can see the fractions in the combo boxes rather than the decimal values
create a table Fractions with two columns Decimal and Fraction, so the rows
in the table would be:

0.125 1/8
0.25 1/4
0.375 3/8
0.5 1/2
and so on to:
0.875 7/8

The RowSource for the combo boxes on the form to select the length or width
would join this to the Lengths or Widths table, e.g. for Lengths:

SELECT Length, INT(Length) & " " & Fraction AS FractionalLength
FROM Lengths INNER JOIN Fractions
ON Lengths.Length - INT(Lengths.Length) = Fractions.Decimal
ORDER BY Length;

So you just see the fractional value in the combo box you'd set its
properties like this:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches)

The exact second dimension of the ColumnWidths property is not crucial, but
the first one must be zero to hide the first column. This hidden column is
the bound column so when you select a fractional value from the list the
value of the combo box is actually the decimal equivalent.

In the Prices table replace the Length and Width columns by four columns,
LowerLength, HigherLength, LowerWidth and HigherWidth. These would define
the range for each price so you might have a row with width values of 30 and
30.875 (30" to 30 7/8"). The criteria for searching would now look for
values within the ranges (spreads):

=(DLookup("Price", "Prices", "Product = """ & [cboProduct] & """ And " &
[cboLength] & " Between LowerLength And HigherLength And " & [cboWidth] & "
Between HigherWidth And LowerWidth")) * [txtDiscount] * (1 -
([txtDiscount]/100))

You don't need to worry about the 1/8"s being in order. A table in a
relational database is a set, which has no intrinsic order. You order the
rows on the fly via the ORDER BY clause in a query, as in the one above for
the RowSource property. The rows can be entered into the table in any order,
so inserting a ne2wone at a later stage is no problem; it will appear in its
correct position in a query's result set.

Ken,

Thanks a million. Now I just have to learn what most of the things you have
told me mean. I get the drift, however; I don't know the terminology yet.
Just to make sure I have the complete answer, here are a few more things that
I neglected to include in my original post. When pricing a blind or shade, we
measure to the 1/8". The price grids use "spreads" you might say for the
prcies. For instance a blind that is 31" wide and 72" long is the same price
as one that is 31 1/4" wide and 72 3/4" long. This is because the width axis
is in increments (i.e. 30, 36, 42, 48, 54, etc. as is the length axis 36, 42,
60, 66, etc. so all widths from 30 to 35 7/8 are the same as are lengths from
60 to 65 7/8, etc. Would your explanatioj still apply in this case? Are there
any other refinements I would need to make to make it fit in this model?

We need to have the 1/8"'s inorder to then place the order with the
manufacturer. We only want to input the data once. That is the whole idea.

Thanks again for your quick and thorough answer.

Jayson Smith

 

[Guest]

Ken,

You have been extremely helpful. Thank you. Another question please. I have
about 500 individual products each has a possible combination of length and
width of about 400. Would it be best to enter this info in Excel and then
transfer the data into access? Most of this info I already have in grid
formation in Excel. I would just have to create a macro to juggle the info
into a suitable format and then transfer it into access. What do you think?
Otherwise it will take a very very long time to input the thousands of
price/width/length combinations for all of the prodcts.

Also, I had already created a "product" table prior to contacting you and it
includes a product id, vendor id, category id and a few other columns. Will
this work for the form you have suggested or do I need to start over. If I
do, how can I use the info that is already in the table I've made so I don't
have to type everything in again?

Thanks again,

Jayson

To cater for this I'd recommend expanding the Widths and Lengths tables so
that all the 1/8" dimensions are stored, though this would be as decimal
values of course, not fractions, so 31 1/8" would be 31.125 and so on. So
you can see the fractions in the combo boxes rather than the decimal values
create a table Fractions with two columns Decimal and Fraction, so the rows
in the table would be:

0.125 1/8
0.25 1/4
0.375 3/8
0.5 1/2
and so on to:
0.875 7/8

The RowSource for the combo boxes on the form to select the length or width
would join this to the Lengths or Widths table, e.g. for Lengths:

SELECT Length, INT(Length) & " " & Fraction AS FractionalLength
FROM Lengths INNER JOIN Fractions
ON Lengths.Length - INT(Lengths.Length) = Fractions.Decimal
ORDER BY Length;

So you just see the fractional value in the combo box you'd set its
properties like this:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches)

The exact second dimension of the ColumnWidths property is not crucial, but
the first one must be zero to hide the first column. This hidden column is
the bound column so when you select a fractional value from the list the
value of the combo box is actually the decimal equivalent.

In the Prices table replace the Length and Width columns by four columns,
LowerLength, HigherLength, LowerWidth and HigherWidth. These would define
the range for each price so you might have a row with width values of 30 and
30.875 (30" to 30 7/8"). The criteria for searching would now look for
values within the ranges (spreads):

=(DLookup("Price", "Prices", "Product = """ & [cboProduct] & """ And " &
[cboLength] & " Between LowerLength And HigherLength And " & [cboWidth] & "
Between HigherWidth And LowerWidth")) * [txtDiscount] * (1 -
([txtDiscount]/100))

You don't need to worry about the 1/8"s being in order. A table in a
relational database is a set, which has no intrinsic order. You order the
rows on the fly via the ORDER BY clause in a query, as in the one above for
the RowSource property. The rows can be entered into the table in any order,
so inserting a ne2wone at a later stage is no problem; it will appear in its
correct position in a query's result set.

Ken,

Thanks a million. Now I just have to learn what most of the things you have
told me mean. I get the drift, however; I don't know the terminology yet.
Just to make sure I have the complete answer, here are a few more things that
I neglected to include in my original post. When pricing a blind or shade, we
measure to the 1/8". The price grids use "spreads" you might say for the
prcies. For instance a blind that is 31" wide and 72" long is the same price
as one that is 31 1/4" wide and 72 3/4" long. This is because the width axis
is in increments (i.e. 30, 36, 42, 48, 54, etc. as is the length axis 36, 42,
60, 66, etc. so all widths from 30 to 35 7/8 are the same as are lengths from
60 to 65 7/8, etc. Would your explanatioj still apply in this case? Are there
any other refinements I would need to make to make it fit in this model?

We need to have the 1/8"'s inorder to then place the order with the
manufacturer. We only want to input the data once. That is the whole idea.

Thanks again for your quick and thorough answer.

Jayson Smith

 

[Guest]

Provided you can get the data in Excel into columns which match the columns
in the Access table it’s a simple task to import it into Access. I'd suggest
you copy the relevant columns into a new Excel workbook for simplicity. In
Access I'd set up the table structure first, then from the File|Get External
Data|Link menu item on the main Access menu bar create a link to it in
Access. It will then appear as a linked table in the database window in
Access and you can easily create an Append query to append the data from it
into your local Access table.

Your existing Product table should work fine with the unbound 'pricing'
form. If you want to see multiple columns form the product table (possibly
along with columns from related tables) I'd suggest using a list box rather
than a combo box as you can then see all the columns simultaneously. Say you
wanted to list the Product ID from Product and the Vendor Name from a Vendors
table (incidentally its usually recommended that tables have plural names and
fields singular names) the RowSource of the list box would be:

SELECT [Product ID], [Vendor Name]
FROM Product INNER JOIN Vendors
ON Product.[Vendor ID] = Vendors.[Vendor ID]
ORDER BY ProductID;

As this list box has two columns you set its ColumnCount property to 2 and
its ColumnWidths property to something appropriate like 2cm;4cm (or
equivalent in inches in your case – we use the metric system here in Europe).
So long as the BoundColumn property of the list box is 1 when you select an
item in it its Value will be the selected Product ID, so when you refer to
the list box in the expression for the control which gives you the price it’s
the Product ID value its getting. Strictly speaking this is because the
Value property of a control is its default property, and can thus be omitted,
so when you refer to the control by name you are actually referring to its
Value property.

Even if you don't want to show more than one column you might find a list
box more suitable for your purposes. They behave exactly like a combo box in
a case like this so the choice is yours.

 

[Guest]

Wow!

Thanks a mil. again. I'll get to work and let you know how it goes.

Jayson

Provided you can get the data in Excel into columns which match the columns
in the Access table it’s a simple task to import it into Access. I'd suggest
you copy the relevant columns into a new Excel workbook for simplicity. In
Access I'd set up the table structure first, then from the File|Get External
Data|Link menu item on the main Access menu bar create a link to it in
Access. It will then appear as a linked table in the database window in
Access and you can easily create an Append query to append the data from it
into your local Access table.

Your existing Product table should work fine with the unbound 'pricing'
form. If you want to see multiple columns form the product table (possibly
along with columns from related tables) I'd suggest using a list box rather
than a combo box as you can then see all the columns simultaneously. Say you
wanted to list the Product ID from Product and the Vendor Name from a Vendors
table (incidentally its usually recommended that tables have plural names and
fields singular names) the RowSource of the list box would be:

SELECT [Product ID], [Vendor Name]
FROM Product INNER JOIN Vendors
ON Product.[Vendor ID] = Vendors.[Vendor ID]
ORDER BY ProductID;

As this list box has two columns you set its ColumnCount property to 2 and
its ColumnWidths property to something appropriate like 2cm;4cm (or
equivalent in inches in your case – we use the metric system here in Europe).
So long as the BoundColumn property of the list box is 1 when you select an
item in it its Value will be the selected Product ID, so when you refer to
the list box in the expression for the control which gives you the price it’s
the Product ID value its getting. Strictly speaking this is because the
Value property of a control is its default property, and can thus be omitted,
so when you refer to the control by name you are actually referring to its
Value property.

Even if you don't want to show more than one column you might find a list
box more suitable for your purposes. They behave exactly like a combo box in
a case like this so the choice is yours.

Ken,

You have been extremely helpful. Thank you. Another question please. I have
about 500 individual products each has a possible combination of length and
width of about 400. Would it be best to enter this info in Excel and then
transfer the data into access? Most of this info I already have in grid
formation in Excel. I would just have to create a macro to juggle the info
into a suitable format and then transfer it into access. What do you think?
Otherwise it will take a very very long time to input the thousands of
price/width/length combinations for all of the prodcts.

Also, I had already created a "product" table prior to contacting you and it
includes a product id, vendor id, category id and a few other columns. Will
this work for the form you have suggested or do I need to start over. If I
do, how can I use the info that is already in the table I've made so I don't
have to type everything in again?

Thanks again,

Jayson