Is this the right way

  • Thread starter Thread starter Ian Baker
  • Start date Start date
I

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
 
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
 
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.
Click to expand...
- 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
Click to expand...
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.
Click to expand...
- I think this is covered in the above
4. To suppress the poor performance when the database grows, compact your
Click to expand...
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.
Click to expand...
All the
Code: 
 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]
 
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
 
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!
 
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
 
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.
 
Hello Ian,

Thank you for you update! The additional issue seems to be a
performance issue on the continuous form.

I'n not 100% sure what results in the slow performance. Based on my
experience, however, the number of the Controls might influence the form
loading, expecially on the continuous form which will diaplay load all the
controls at the same time.

Please check how many controls are loading on the continuous form. If the
number is large than 50, we may consider not use the continuous form. On
the other hand, if you are using the continuous form as the subform, please
follow the performance tuning method in the KB below:

209113 ACC2000: Tips for Improving Subform Performance
http://support.microsoft.com/?id=209113

Ian, please note that the performance issue is somewhat hard to
troubleshoot in the newsgroup. If there is anything more I can still do to
assist you, please feel free to let me know. I will try my best to help you as
far as I can. Thanks for your understanding!

Best regards,

Billy Yao
Microsoft Online Support
 
Hi Billy
There are 12 fields in the source with 10 fields being displayed. I have had
to create a hidden combo box and place the two totalled queries in that
(TotalUnitsInStock and TotalUnitsOnOrder) and two fields on the subform
having their controlsource =ctlStockTotal.Column(1) and
=ctlStockTotal.Column(2). Rather messy way of doing it.

The performance issue I have been able to ascertain is directly linked to
A2003 as the sub works fine on my AccessXP box that is only an AMD 2.1 with
512 Ram where the 2003 version is on a more powerful box P4 3.0 with 1g of
Ram. I have seen other posts where using A2003 that Access just ups and
closes leaving just the ldb file open.

Thanks for you help Billy although I am seriously considering giving A2003
away and going back to XP.
 
Hi Ian,

Thank you for your feedback!

I'm really not sure what causes this performance issue, but I think at the start
the problem was casued by the compositive factors (such as nested
queries, continue forms, the number of controls and the subforms).

The machine environment and Access Version is indeed beyond my mind.
Hopefully, the problemance is good on your AccessXP box. It comes to me
that there is a known issue called "LDB locking", which fixed by a persistent
recordset connection. This may make sense but I'm unsure of it.

Please read the following article and see if it addresses your problem:
http://www.granite.ab.ca/access/performancefaq.htm

The article also mentions something about the second user trying to delete
the lock (LDB) file while the first user is holding onto it, and trying the delete
many times before silently failing and returning the data successfully.

That's all what I can do for you Ian. I appreciate your efforts troubleshooting
this issue with me and it's really nice to cooperate with you. I hope that the
knows issue "LDB Locking" will make sense on your side. Thank you very
much for your feedback and understanding!

With much gratitude,

Best regards,

Billy Yao
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

**************************************
The Third Party Disclaimer
**************************************
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or information
found there. There are inherent dangers in the use of any software found on
the Internet, and Microsoft cautions you to make sure that you completely
understand the risk before retrieving any software from the Internet.
 
Back
Top