VBA to SQL

[antgel]

Hi all,

I'm implementing a database that tracks orders of items, delivery notes, and
invoices. I've tried to implement a dynamic system. What I mean by this is
that when you view an order, the price of each order line is calculated
on-the-fly. The beauty of this is that if a price changes for any reason,
there is no need to go and update every order containing that item.

There is also the (major!) caveat that each customer pays either cost,
retail, or wholesale price, and so the order line price depends on that as
well. Some customers have their own custom price list, and some have a
percentage discount. As this calculation is so complex, I implemented it in
a function:

Function getItemPrice(contactId_p As Long, lItemVariationId_p As Long)_
As Currency

The idea that you pass in the customer and item, and it returns the price
for one item for that customer. I then use this function in a query:

SELECT orderLine.orderLine, contact.contactid, orderLineQuantity.quantity,
getItemPrice([contactid],itemvariation.id) AS itemPrice,
[itemPrice]*[quantity] AS linePrice
FROM (contact INNER JOIN [order] ON contact.contactid = order.customerId)
INNER JOIN (itemVariation INNER JOIN (orderLine INNER JOIN orderLineQuantity
ON orderLine.orderLine = orderLineQuantity.orderLine) ON itemVariation.id =
orderLine.itemRef) ON order.id = orderLine.orderId;

The problem is speed. If I look at a continuous form with, say 20 lines on
it, I can see each record being calculated! Not only does this look crap,
the customer is not happy. Same applies if the form is scrolled, or if a
window on top is closed etc.

And, it's compounded. When I want order totals, and later invoice totals
(where by virtue of joins, the invoice references the order lines, and there
is a loooong wait.

I've decided that conceptually this _must_ be do-able in Jet SQL, and I hope
it would be much faster. The function opens up to 6 ADO Recordsets, which
I've heard is rather slow. Only problem is, I have no idea how to set up
the queries.

It might help to understand my data structures if I point out some of my
tables and _some_ relevant fields:

Contact(id, contactTypeId) - this field determines customer's price "type".

ContactType(id, description) e.g. (1,cost, 2,wholesale 3, retail).

ItemCommon(article, wholesalePrice, costPrice, retailPrice) - this table
contains the prices.

CustomPriceList(contactId, articleId, customPrice) - if the contact id is in
here - they pay customPrice.

Any help is appreciated as I'm tearing my hair out here! I hope I haven't
turned off all the SQL gurus by going into too much detail...

I should also point out that I've been working on this for months and I
wouldn't be surprised if there's a simple, elegant, fast solution.

Antony

 

[John Viescas]

Antony-

First question: If you're using a JET mdb database, then why are you using
ADO?

Next question: If the "decision" about price is based on information from
additional columns in Contact or Contact Type, why not include those in the
query and pass them to the function? Without seeing the code, it's tough to
imagine why you need to open 6 recordsets to solve it. It would seem that
one probe to the CustomPriceList table should do it if you have all the
other info.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

Hi all,

I'm implementing a database that tracks orders of items, delivery notes, and
invoices. I've tried to implement a dynamic system. What I mean by this is
that when you view an order, the price of each order line is calculated
on-the-fly. The beauty of this is that if a price changes for any reason,
there is no need to go and update every order containing that item.

There is also the (major!) caveat that each customer pays either cost,
retail, or wholesale price, and so the order line price depends on that as
well. Some customers have their own custom price list, and some have a
percentage discount. As this calculation is so complex, I implemented it in
a function:

Function getItemPrice(contactId_p As Long, lItemVariationId_p As Long)_
As Currency

The idea that you pass in the customer and item, and it returns the price
for one item for that customer. I then use this function in a query:

SELECT orderLine.orderLine, contact.contactid, orderLineQuantity.quantity,
getItemPrice([contactid],itemvariation.id) AS itemPrice,
[itemPrice]*[quantity] AS linePrice
FROM (contact INNER JOIN [order] ON contact.contactid = order.customerId)
INNER JOIN (itemVariation INNER JOIN (orderLine INNER JOIN orderLineQuantity
ON orderLine.orderLine = orderLineQuantity.orderLine) ON itemVariation.id =
orderLine.itemRef) ON order.id = orderLine.orderId;

The problem is speed. If I look at a continuous form with, say 20 lines on
it, I can see each record being calculated! Not only does this look crap,
the customer is not happy. Same applies if the form is scrolled, or if a
window on top is closed etc.

And, it's compounded. When I want order totals, and later invoice totals
(where by virtue of joins, the invoice references the order lines, and there
is a loooong wait.

I've decided that conceptually this _must_ be do-able in Jet SQL, and I hope
it would be much faster. The function opens up to 6 ADO Recordsets, which
I've heard is rather slow. Only problem is, I have no idea how to set up
the queries.

It might help to understand my data structures if I point out some of my
tables and _some_ relevant fields:

Contact(id, contactTypeId) - this field determines customer's price "type".

ContactType(id, description) e.g. (1,cost, 2,wholesale 3, retail).

ItemCommon(article, wholesalePrice, costPrice, retailPrice) - this table
contains the prices.

CustomPriceList(contactId, articleId, customPrice) - if the contact id is in
here - they pay customPrice.

Any help is appreciated as I'm tearing my hair out here! I hope I haven't
turned off all the SQL gurus by going into too much detail...

I should also point out that I've been working on this for months and I
wouldn't be surprised if there's a simple, elegant, fast solution.

Antony

 

[Joe Fallon]

"when you view an order, the price of each order line is calculated
on-the-fly. The beauty of this is that if a price changes for any reason,
there is no need to go and update every order containing that item."

I fail to understand why this is a thing of "beauty" and not your worst
nightmare.
For example if the price of a widget is $1.00 and it is ordered on Oct 1,
2003 then shouldn't report about the purchase of all widgets show $1.00?
(Yes. It should.)

So if you change the price of widgets to $2.00 on Nov 1, 2003 then the price
that was on the old order should NOT change.

Historical pricing is one of the cases where you break the rules of database
design.
The rule says to only store a value like price once.
But historical pricing requires you to keep the price intact at the time of
the transaction so you must store the price on each transaction.


--
Joe Fallon
Access MVP

Hi all,

I'm implementing a database that tracks orders of items, delivery notes, and
invoices. I've tried to implement a dynamic system. What I mean by this is
that when you view an order, the price of each order line is calculated
on-the-fly. The beauty of this is that if a price changes for any reason,
there is no need to go and update every order containing that item.

There is also the (major!) caveat that each customer pays either cost,
retail, or wholesale price, and so the order line price depends on that as
well. Some customers have their own custom price list, and some have a
percentage discount. As this calculation is so complex, I implemented it in
a function:

Function getItemPrice(contactId_p As Long, lItemVariationId_p As Long)_
As Currency

The idea that you pass in the customer and item, and it returns the price
for one item for that customer. I then use this function in a query:

SELECT orderLine.orderLine, contact.contactid, orderLineQuantity.quantity,
getItemPrice([contactid],itemvariation.id) AS itemPrice,
[itemPrice]*[quantity] AS linePrice
FROM (contact INNER JOIN [order] ON contact.contactid = order.customerId)
INNER JOIN (itemVariation INNER JOIN (orderLine INNER JOIN orderLineQuantity
ON orderLine.orderLine = orderLineQuantity.orderLine) ON itemVariation.id =
orderLine.itemRef) ON order.id = orderLine.orderId;

The problem is speed. If I look at a continuous form with, say 20 lines on
it, I can see each record being calculated! Not only does this look crap,
the customer is not happy. Same applies if the form is scrolled, or if a
window on top is closed etc.

And, it's compounded. When I want order totals, and later invoice totals
(where by virtue of joins, the invoice references the order lines, and there
is a loooong wait.

I've decided that conceptually this _must_ be do-able in Jet SQL, and I hope
it would be much faster. The function opens up to 6 ADO Recordsets, which
I've heard is rather slow. Only problem is, I have no idea how to set up
the queries.

It might help to understand my data structures if I point out some of my
tables and _some_ relevant fields:

Contact(id, contactTypeId) - this field determines customer's price "type".

ContactType(id, description) e.g. (1,cost, 2,wholesale 3, retail).

ItemCommon(article, wholesalePrice, costPrice, retailPrice) - this table
contains the prices.

CustomPriceList(contactId, articleId, customPrice) - if the contact id is in
here - they pay customPrice.

Any help is appreciated as I'm tearing my hair out here! I hope I haven't
turned off all the SQL gurus by going into too much detail...

I should also point out that I've been working on this for months and I
wouldn't be surprised if there's a simple, elegant, fast solution.

Antony

 

[Michel Walsh]

Hi,


Or you store the date, "effective since", for each basic price. Sure,
then, you have to make a special lookup on the prices taking into account
the latest "effective since" among those occurring "before" the date of the
bill, for each implied items... More work, but doing so, you then know that
1$ was the right price in October, even if it is now 2$ at the time you
actually "check" for a bill accuracy (tax audit, or whatever). And so, but
only as accessory, it reduces the size of information to be stored.



Vanderghast, Access MVP

"when you view an order, the price of each order line is calculated
on-the-fly. The beauty of this is that if a price changes for any reason,
there is no need to go and update every order containing that item."

I fail to understand why this is a thing of "beauty" and not your worst
nightmare.
For example if the price of a widget is $1.00 and it is ordered on Oct 1,
2003 then shouldn't report about the purchase of all widgets show $1.00?
(Yes. It should.)

So if you change the price of widgets to $2.00 on Nov 1, 2003 then the price
that was on the old order should NOT change.

Historical pricing is one of the cases where you break the rules of database
design.
The rule says to only store a value like price once.
But historical pricing requires you to keep the price intact at the time of
the transaction so you must store the price on each transaction.


--
Joe Fallon
Access MVP

Hi all,

I'm implementing a database that tracks orders of items, delivery notes, and
invoices. I've tried to implement a dynamic system. What I mean by

this

is
that when you view an order, the price of each order line is calculated
on-the-fly. The beauty of this is that if a price changes for any reason,
there is no need to go and update every order containing that item.

There is also the (major!) caveat that each customer pays either cost,
retail, or wholesale price, and so the order line price depends on that as
well. Some customers have their own custom price list, and some have a
percentage discount. As this calculation is so complex, I implemented

it

in
a function:

Function getItemPrice(contactId_p As Long, lItemVariationId_p As Long)_
As Currency

The idea that you pass in the customer and item, and it returns the price
for one item for that customer. I then use this function in a query:

SELECT orderLine.orderLine, contact.contactid, orderLineQuantity.quantity,
getItemPrice([contactid],itemvariation.id) AS itemPrice,
[itemPrice]*[quantity] AS linePrice
FROM (contact INNER JOIN [order] ON contact.contactid = order.customerId)
INNER JOIN (itemVariation INNER JOIN (orderLine INNER JOIN orderLineQuantity
ON orderLine.orderLine = orderLineQuantity.orderLine) ON

itemVariation.id

=
orderLine.itemRef) ON order.id = orderLine.orderId;

The problem is speed. If I look at a continuous form with, say 20 lines on
it, I can see each record being calculated! Not only does this look crap,
the customer is not happy. Same applies if the form is scrolled, or if a
window on top is closed etc.

And, it's compounded. When I want order totals, and later invoice totals
(where by virtue of joins, the invoice references the order lines, and there
is a loooong wait.

I've decided that conceptually this _must_ be do-able in Jet SQL, and I hope
it would be much faster. The function opens up to 6 ADO Recordsets, which
I've heard is rather slow. Only problem is, I have no idea how to set up
the queries.

It might help to understand my data structures if I point out some of my
tables and _some_ relevant fields:

Contact(id, contactTypeId) - this field determines customer's price "type".

ContactType(id, description) e.g. (1,cost, 2,wholesale 3, retail).

ItemCommon(article, wholesalePrice, costPrice, retailPrice) - this table
contains the prices.

CustomPriceList(contactId, articleId, customPrice) - if the contact id

is

in
here - they pay customPrice.

Any help is appreciated as I'm tearing my hair out here! I hope I haven't
turned off all the SQL gurus by going into too much detail...

I should also point out that I've been working on this for months and I
wouldn't be surprised if there's a simple, elegant, fast solution.

Antony

 

[Joe Fallon]

I actually used the effective date solution in one of my very first Access
apps.
It was a struggle to "invent" it. But I was pretty happy with my result.

I later learned about the other technique and can see some advatages to it
like less complex and faster queries.

But they both work.
--
Joe Fallon
Access MVP

Hi,


Or you store the date, "effective since", for each basic price. Sure,
then, you have to make a special lookup on the prices taking into account
the latest "effective since" among those occurring "before" the date of the
bill, for each implied items... More work, but doing so, you then know that
1$ was the right price in October, even if it is now 2$ at the time you
actually "check" for a bill accuracy (tax audit, or whatever). And so, but
only as accessory, it reduces the size of information to be stored.



Vanderghast, Access MVP

"when you view an order, the price of each order line is calculated
on-the-fly. The beauty of this is that if a price changes for any reason,
there is no need to go and update every order containing that item."

I fail to understand why this is a thing of "beauty" and not your worst
nightmare.
For example if the price of a widget is $1.00 and it is ordered on Oct 1,
2003 then shouldn't report about the purchase of all widgets show $1.00?
(Yes. It should.)

So if you change the price of widgets to $2.00 on Nov 1, 2003 then the price
that was on the old order should NOT change.

Historical pricing is one of the cases where you break the rules of database
design.
The rule says to only store a value like price once.
But historical pricing requires you to keep the price intact at the time of
the transaction so you must store the price on each transaction.


--
Joe Fallon
Access MVP



notes,
and this

that

as

well. Some customers have their own custom price list, and some have a
percentage discount. As this calculation is so complex, I implemented

it

in
a function:

Function getItemPrice(contactId_p As Long, lItemVariationId_p As Long)_
As Currency

The idea that you pass in the customer and item, and it returns the price
for one item for that customer. I then use this function in a query:

SELECT orderLine.orderLine, contact.contactid, orderLineQuantity.quantity,
getItemPrice([contactid],itemvariation.id) AS itemPrice,
[itemPrice]*[quantity] AS linePrice
FROM (contact INNER JOIN [order] ON contact.contactid = order.customerId)
INNER JOIN (itemVariation INNER JOIN (orderLine INNER JOIN orderLineQuantity
ON orderLine.orderLine = orderLineQuantity.orderLine) ON

itemVariation.id

=
orderLine.itemRef) ON order.id = orderLine.orderId;

The problem is speed. If I look at a continuous form with, say 20

lines
on

it, I can see each record being calculated! Not only does this look crap,
the customer is not happy. Same applies if the form is scrolled, or

if

a I
hope is