Is this the right way

[Ian Baker]

I have a db that amongst other things handles stock control on items that
are sold. Currently I hard store the current stock level with each sale
transaction reducing the stock level and each stock received transaction
increasing the stock level. I know this is the wrong way but it's the
fastest way and although it's the fastest way it is prone to a risk of
innacuracy. So to do it the right way and base these values on queries
(calculations rather than hard store) I need to know whether the following
is the best way to achieve the values:
1. Quantity currently on order
2. Quantity currently in stock

I have 4 tables (each one having a quantity field etc):
1. tblPurchaseOrder (records of stock that have been ordered)
2. tblStockReceived (records of stock that have been receive)
3. tblSalesOrder (records of stock that have been sold)
4. tblStockAdjustment (for manual adjustments + or - i.e. breakages etc)

I will need to:
Create a query for each table with each one summing the quantities of each
item
1. qrysumPurchaseOrder
2. qrysumStockReceived
3. qrysumSalesOrder
4. qrysumStockAdjustment

To get the "Quantity currently on order" I would create another query which
contains the summed quantity fields of qrysumPurchaseOrder and
qrysumStockReceived and a calculated field of::
TotalOnOrder = sumPurchaseOrderQty - sumStockReceivedQty

To get the "Quantity currently in stock" I would create another query which
contains the summed quantity fields of qrysumStockReceived,
qrysumStockAdjustment and qrysumSalesOrder and a calculated field of:
TotalInStock = sumStockReceivedQty + sumStockAdjustmentQty -
sumSalesOrderQty

Then for each form I would have to use either or both nested queries
depending on which values I need with the forms normal recordsource.

Is this the best way as I feel it will get rather slow as the db grows?

Thanks

 

[Billy Yao [MSFT]]

Hi Ian,

Thanks for posting in the community.

From your description, I understand that you'd like to achieve the Quantity values of stock
and wonder if your strategy is the best way. I looked into your scenario and found the latter
method you chose could be the right way but not the fastest way for Quantity Accuracy.

Based on my experience, there are limited methods to pursue the best performance of the
queries. However, we can do something to optimize them if possible. I noticed you would go
ahead to use the nested queries, which can bring the performance issue when the
database grows.

Here are my suggestions to your issue:

1. If possible, put the sub summing queries (qrysumPurchaseOrder, qrysumStockReceived,
qrysumSalesOrder, qrysumStockAdjustment) in the Record Source of each form.
Additionally, if expressions are necessary in the Output try to place the final values (Quantity
currently on order, Quantity currently in stock) in a Control Source of a Control (such as text
box) on a form.

2. We should output only the needed fields. When creating a query, return only the fields you
need. If a field doesn't have to be in the SELECT clause, don't add it. In fields used to set
criteria, clear the Show check box if you do not want to display those fields.

3. If use GROUP BY clause, include as few fields as possible. The more fields in the
GROUP BY clause, the longer the query takes to execute. Use the First aggregate function
to help reduce the number of fields required in the GROUP BY clause.

4. To suppress the poor performance when the database grows, compact your database
frequently. This has two performance benefits:

4.1) The Jet database engine uses a cost-based method of optimization. As your database
grows, the optimization scheme may no longer be efficient. It updates the database
statistics and re-optimizes all queries when you compact the database.
4.2) With the growth of your database, it will become fragmented. Compacting database
writes all the data in a table into contiguous pages on the hard disk, improving performance
of sequential scans.

5. Index any field used to set criteria for the query. Queries are able to take advantage of
multiple indexes on a single table, which makes indexing many columns advantageous.
Avoid restrictive query criteria on calculated and non-indexed columns whenever possible.

6. We can also Use the built-in tool "Performance Analyzer" to optimize an Access
database. For more information about how to use the Performance Analyzer in Access, see
the following topic in Access Help:

"Use the Performance Analyzer to optimize an Access database"

For detailed methods to optimize query performance, please apply the performance tuning
principles described in the following article:

209126 ACC2000: How to Optimize Queries in Microsoft Access 2000
http://support.microsoft.com/?id=209126


Does that answer your question Ian? Please apply my suggestions above and let me know
if this lights you on the correct way. Please notice that there is possibly no best way but
better one to balance between performance and strategy. If there is anything more I can do
to assist you, please feel free to post it in the group.

Thanks again for participating in our newsgroup!

Best regards,

Billy Yao
Microsoft Online Support

 

[Ian Baker]

Hi Billy
Thanks for your response.

1. If possible, put the sub summing queries (qrysumPurchaseOrder, qrysumStockReceived,
qrysumSalesOrder, qrysumStockAdjustment) in the Record Source of each form.
Additionally, if expressions are necessary in the Output try to place the final values (Quantity
currently on order, Quantity currently in stock) in a Control Source of a Control (such as text
box) on a form.

- To perhaps clarify further I have several forms (one called Items) that
amongst other controls like [ItemName], [ItemDescription] have 2 locked
controls called [QtyInStock] and [QtyOnOrder]. Placing a totalled query in
the forms RecordSource will not allow updates to the other fields on the
form. To overcome this the only way I think is to convert the 2 text boxes
of [QtyInStock] and [QtyOnOrder] to list boxes and set their rowsource to
each totalled nested query.

2. We should output only the needed fields. When creating a query, return only the fields you
need. If a field doesn't have to be in the SELECT clause, don't add it. In fields used to set
criteria, clear the Show check box if you do not want to display those

fields.
- Each of the totalled queries would take the same format as:
SELECT [Stock Adjustment].Code, Sum([Stock Adjustment].Quantity) AS
TotalAdjustment
FROM [Stock Adjustment]
GROUP BY [Stock Adjustment].Code;

- Then the nested query to get say the total units on order would be:
SELECT Nz([TotalOrdered])+Nz([TotalReceived]) AS UnitsOnOrder
FROM ([Stock Control] LEFT JOIN xTotalStockReceived ON [Stock Control].Code
= xTotalStockReceived.Code) LEFT JOIN xTotalStockOrdered ON [Stock
Control].Code = xTotalStockOrdered.Code
WHERE ((([Stock Control].Code)=[ctlCode]));
- The only question here is I presume the criteria is defined as I have done
in the nested query?

3. If use GROUP BY clause, include as few fields as possible. The more fields in the
GROUP BY clause, the longer the query takes to execute. Use the First aggregate function
to help reduce the number of fields required in the GROUP BY clause.

- I think this is covered in the above

4. To suppress the poor performance when the database grows, compact your

database
- I use the CompactOnClose for the FE and a user fired function to compact
the BE which first checks that no one else is logged on.

5. Index any field used to set criteria for the query.

All the

 and [Quantity] fields in all tables are indexed.

So I think I have done the best possible outcome (I say with reservation)
--
Regards
Ian
-
[QUOTE=""Billy Yao [MSFT]""]
Hi Ian,

Thanks for posting in the community.

From your description, I understand that you'd like to achieve the Quantity values of stock
and wonder if your strategy is the best way. I looked into your scenario and found the latter
method you chose could be the right way but not the fastest way for Quantity Accuracy.

Based on my experience, there are limited methods to pursue the best performance of the
queries. However, we can do something to optimize them if possible. I noticed you would go
ahead to use the nested queries, which can bring the performance issue when the
database grows.

Here are my suggestions to your issue:

1. If possible, put the sub summing queries (qrysumPurchaseOrder, qrysumStockReceived,
qrysumSalesOrder, qrysumStockAdjustment) in the Record Source of each form.
Additionally, if expressions are necessary in the Output try to place the final values (Quantity
currently on order, Quantity currently in stock) in a Control Source of a Control (such as text
box) on a form.

2. We should output only the needed fields. When creating a query, return only the fields you
need. If a field doesn't have to be in the SELECT clause, don't add it. In fields used to set
criteria, clear the Show check box if you do not want to display those fields.

3. If use GROUP BY clause, include as few fields as possible. The more fields in the
GROUP BY clause, the longer the query takes to execute. Use the First aggregate function
to help reduce the number of fields required in the GROUP BY clause.

4. To suppress the poor performance when the database grows, compact your database
frequently. This has two performance benefits:

4.1) The Jet database engine uses a cost-based method of optimization. As your database
grows, the optimization scheme may no longer be efficient. It updates the database
statistics and re-optimizes all queries when you compact the database.
4.2) With the growth of your database, it will become fragmented. Compacting database
writes all the data in a table into contiguous pages on the hard disk, improving performance
of sequential scans.

5. Index any field used to set criteria for the query. Queries are able to take advantage of
multiple indexes on a single table, which makes indexing many columns advantageous.
Avoid restrictive query criteria on calculated and non-indexed columns whenever possible.

6. We can also Use the built-in tool "Performance Analyzer" to optimize an Access
database. For more information about how to use the Performance Analyzer in Access, see
the following topic in Access Help:

"Use the Performance Analyzer to optimize an Access database"

For detailed methods to optimize query performance, please apply the performance tuning
principles described in the following article:

209126 ACC2000: How to Optimize Queries in Microsoft Access 2000
http://support.microsoft.com/?id=209126


Does that answer your question Ian? Please apply my suggestions above and let me know
if this lights you on the correct way. Please notice that there is possibly no best way but
better one to balance between performance and strategy. If there is anything more I can do
to assist you, please feel free to post it in the group.

Thanks again for participating in our newsgroup!

Best regards,

Billy Yao
Microsoft Online Support[/QUOTE]

 

[Billy Yao [MSFT]]

Perfect Ian!!

That's really cool to know more about your strategies and methods to pursue the better
performance. Shocked to see you've already have applied all my suggestions

It's OK to convert those 2 text boxes to list boxes and set their rowsource to each totalled
nested query. Additioally, all seems good on your queries tuning and indexes usage. I think
there is no reason to produce a poor outcome with these completely optimization!

If possible, I always use "Performance Analyzer" to optimize an Access database after I made
lot of operations on it. Therefore, I shared this with you in my previous post.

Thanks for sharing me your good methods and exchanging our opinions on this issue. If there
is anything more I can do to assist you, please feel freel to let me know.

Best regards,

Billy Yao
Microsoft Online Support

 

[Ian Baker]

Thanks Billy
Everything seems to work ok although there are some headaches when trying to
use the "UnitsInStock" for a field in a continous updateable form.

Thanks again for your help!

 

[Billy Yao [MSFT]]

Dear Ian,

Thank you for your update!

I'm glad that everything works fine on your side. As to the "UnitsInStock" in
your continuous form, you didn't introduce it and I have few ideas till now.
Do you mean you met with the problem of all the same value on the
"UnitsInStock" on your continuous form, or something else?

I'm willing to be of assistance on your new problem and prefer your
specifying what it is. In the meanwhile, I performed further researching and
found out some related KBs. Please check them and see if they make
sense on your issue:

208961 ACC2000: Changing Control Properties Affects All Records in a
Form
http://support.microsoft.com/?id=208961

208502 ACC2000: How to Enumerate Selected Form Records
http://support.microsoft.com/?id=208502

If there is anything more I can do to assist you, please feel free to post it in
the group.

Best regards,

Billy Yao
Microsoft Online Support

 

[Ian Baker]

Hi Billy
As always, thanks for your help.
The problem is I have a continous form that displays all the products and
allows the user to enter the quantity they wish to order of each product
into a field QtyToOrder. Each row also needs to display the
TotalUnitsInStock and TotalUnitsOnOrder. Placing the totalled queries for
these two fields in the forms recordsource naturally prevents input into the
QtyToOrder field as the recordset is not updateable. I have tried DLookups
for each of the fields but the form either takes to long to fully load
(several seconds with only 4 test records on a P4 3gig + 1gig ram machine)
or Access 2003 just mysteriously closes leaving the ldb file open. I have
tried making another field ProductCode a combo box with the totalled queries
in there and the two fields reference the combo box columns but that again
is far to slow.