MsAccess freezing
- Thread starter INFOS
- Start date
- Local time
- Today, 07:59
- Joined
- Feb 28, 2001
- Messages
- 30,806
I see so many problems that I am reluctant to advise you how to put a "fix" that will handle your problem. Your system is so non-normalized that it is almost like a spreadsheet, but of course you cannot use a spreadsheet because you have too many rows. WAY too many rows.
In table Supplys, you show quantity in and quantity out. Your description suggests that the same item from two vendors will (or at least COULD) have two different prices. You want to put instantaneous pricing on something based on what you have in stock, yet I see no way to unify this given the structure you have.
First, you have a MAJOR table design problem. Second, you have a business rules issue that is going to be a real bear to disentangle from what you are trying to do.
The quantity-in and quantity-out stuff being summarized in your supply table is going to give you permanent migraines the longer you leave it this way. Your record-keeping is going to be a headache. Your product inventory is going to be inaccurate. You are going to run into so many table locking issues that even WINDOWS will have a headache.
The non-uniform pricing is going to be an issue because you have to identify the prices of the stuff you actually sell. In other words, this is a problem in short-term spot-pricing based on inventory on-hand at the moment of the invoice transaction. And even "hourly" pricing is going to be questionable as a way to compute prices. How do you know that the inventory you sent out was at the higher (or lower) price of the stock on hand? This potentially leads to fluctuations in inventory value that must be a total bookkeeping nightmare.
I think part of the problem is that this is SO badly denormalized that I have trouble finding a place to start. With a properly normalized DB, this might (repeat MIGHT) be tractable. With the design as-is, anything I would suggest would be a band-aid on a blow-out patch on a piece of duct tape. I have as helpful an attitude as anyone on the forum, but to be painfully honest, this is such a complicated problem that it is out of my league to straighten out in anything less than hours of analysis. And my boss only allows me to do this sort of stuff when I don't have any hot issues on the burner.
The "right" way to do this involves at least the following tables:
1. Supply items - a list of inventory by description codes
2. Suppliers - a list of places where you get given items.
3. Purchase Orders - a list of numbered purchase orders showing when you bought something. Relationship: Many POs to one supplier.
4. Purchase Details - the line items of the purchase orders - which is your "real" SUPPLY_IN information. Spot-purchase prices can go here, maybe. Relationship: Many details to one PO. Many details to one Supply Item.
5. Customers - a list of people to whom you sell your stuff
6. Invoices - a list of numbered invoices showing when you sold something. Relationship: Many Invoices to one customer.
7. Invoice Details - the line items of the invoices - which is your "real" SUPPLY_OUT information. Spot-invoice prices go here, maybe. Relationship: Many details to one Invoice. Many details to one Supply Item.
Further complications could occur, such as partial shipment deliveries and the like, and I won't bother you with that at the moment. The point is, your two tables should be not less than SEVEN tables to normalize, and even there, I'm not going to swear that this is right.
NOW - how do you deal with this? HERE is your business model issue. Do you assume that the price of a product depends on what is in stock even though the prices vary for the same product from different suppliers? If so, then you have to choose a model for stock removal. "Oldest first" is the usual case for spot-price models, but I won't make that assumption.
This is getting deep and I have another project to hit. Think about this before going too much farther with your design. I would seriously consider hiring a profession inventory DB expert for this rather than doing this yourself.
In table Supplys, you show quantity in and quantity out. Your description suggests that the same item from two vendors will (or at least COULD) have two different prices. You want to put instantaneous pricing on something based on what you have in stock, yet I see no way to unify this given the structure you have.
First, you have a MAJOR table design problem. Second, you have a business rules issue that is going to be a real bear to disentangle from what you are trying to do.
The quantity-in and quantity-out stuff being summarized in your supply table is going to give you permanent migraines the longer you leave it this way. Your record-keeping is going to be a headache. Your product inventory is going to be inaccurate. You are going to run into so many table locking issues that even WINDOWS will have a headache.
The non-uniform pricing is going to be an issue because you have to identify the prices of the stuff you actually sell. In other words, this is a problem in short-term spot-pricing based on inventory on-hand at the moment of the invoice transaction. And even "hourly" pricing is going to be questionable as a way to compute prices. How do you know that the inventory you sent out was at the higher (or lower) price of the stock on hand? This potentially leads to fluctuations in inventory value that must be a total bookkeeping nightmare.
I think part of the problem is that this is SO badly denormalized that I have trouble finding a place to start. With a properly normalized DB, this might (repeat MIGHT) be tractable. With the design as-is, anything I would suggest would be a band-aid on a blow-out patch on a piece of duct tape. I have as helpful an attitude as anyone on the forum, but to be painfully honest, this is such a complicated problem that it is out of my league to straighten out in anything less than hours of analysis. And my boss only allows me to do this sort of stuff when I don't have any hot issues on the burner.
The "right" way to do this involves at least the following tables:
1. Supply items - a list of inventory by description codes
2. Suppliers - a list of places where you get given items.
3. Purchase Orders - a list of numbered purchase orders showing when you bought something. Relationship: Many POs to one supplier.
4. Purchase Details - the line items of the purchase orders - which is your "real" SUPPLY_IN information. Spot-purchase prices can go here, maybe. Relationship: Many details to one PO. Many details to one Supply Item.
5. Customers - a list of people to whom you sell your stuff
6. Invoices - a list of numbered invoices showing when you sold something. Relationship: Many Invoices to one customer.
7. Invoice Details - the line items of the invoices - which is your "real" SUPPLY_OUT information. Spot-invoice prices go here, maybe. Relationship: Many details to one Invoice. Many details to one Supply Item.
Further complications could occur, such as partial shipment deliveries and the like, and I won't bother you with that at the moment. The point is, your two tables should be not less than SEVEN tables to normalize, and even there, I'm not going to swear that this is right.
NOW - how do you deal with this? HERE is your business model issue. Do you assume that the price of a product depends on what is in stock even though the prices vary for the same product from different suppliers? If so, then you have to choose a model for stock removal. "Oldest first" is the usual case for spot-price models, but I won't make that assumption.
This is getting deep and I have another project to hit. Think about this before going too much farther with your design. I would seriously consider hiring a profession inventory DB expert for this rather than doing this yourself.
Reply for the DOCMAN
Thank You for Yours effort - I have taken You a lot of text lines.
All this time I'm trying to simplify problem as much as possible so I'm not explaining to You my whole database (because I think it is irelevant).
You have designed a little database:
1. Supply items
2. Suppliers
3. Purchase Orders
4. Purchase Details
5. Customers
6. Invoices
7. Invoice Details
I have all this in my database but I have 50 tables more - I can't explain all database here.
What is important is that I need average cost prices for the articles sold to a customer (and they are in the table INVOICE DETAILS). When I make Invoice I have all the articles in this table and now I need to update one field in this table with average cost price for every article. I don't want "oldest first" method (it is FIFO - first in first out method). I don't want LIFO method (last in last out). I want average price method - average price in the moment of sale. To calculate average price I must store informations about cost of supllyed goods and cost of goods that are sold. So I can see how many articles (quantity) are left in the warehouse and how big cost value for theese articles is. I have used here - table suplys for that (I repeat - I have tryed to simplify the example) - actually I use separate table for storing cost values of goods that are coming in (supplys) and coming out (invoices). That table I use to calculate actuall average price and i need to store this value in table "invoice details". But how???? I use code that I run on a form! It is disaster for the memory resource. I asked You what else can I do. I still don't see other way.
Generally question is: HOW TO UPDATE (not append Louie!) RECORDS IN TABLEa WITH SUMMARY DATA FROM THE TABLEb? TableA is one to many related with tableB.
You gave me solution to make temporary table first and then run the update query. This is not good solution because I need to sum whole tableA (when I'm making temporary table) - and tableA has to many records. It is faster with runing code on a form when tableB do not have to many records (this table has from 1 to 10000 records - it is changing from invoice to invoice -and tableB has allways a lot of records).
OR TELL ME THIS: CAN I SUM RECORDS IN TABLEb (when I make temporary table)- BUT NOT ALL THE RECORDS - JUST RECORDS WITH "FIELDX" that is equall to "FIELDX" from TABLEA" (invoice details)???
Thank You for Yours effort - I have taken You a lot of text lines.
All this time I'm trying to simplify problem as much as possible so I'm not explaining to You my whole database (because I think it is irelevant).
You have designed a little database:
1. Supply items
2. Suppliers
3. Purchase Orders
4. Purchase Details
5. Customers
6. Invoices
7. Invoice Details
I have all this in my database but I have 50 tables more - I can't explain all database here.
What is important is that I need average cost prices for the articles sold to a customer (and they are in the table INVOICE DETAILS). When I make Invoice I have all the articles in this table and now I need to update one field in this table with average cost price for every article. I don't want "oldest first" method (it is FIFO - first in first out method). I don't want LIFO method (last in last out). I want average price method - average price in the moment of sale. To calculate average price I must store informations about cost of supllyed goods and cost of goods that are sold. So I can see how many articles (quantity) are left in the warehouse and how big cost value for theese articles is. I have used here - table suplys for that (I repeat - I have tryed to simplify the example) - actually I use separate table for storing cost values of goods that are coming in (supplys) and coming out (invoices). That table I use to calculate actuall average price and i need to store this value in table "invoice details". But how???? I use code that I run on a form! It is disaster for the memory resource. I asked You what else can I do. I still don't see other way.
Generally question is: HOW TO UPDATE (not append Louie!) RECORDS IN TABLEa WITH SUMMARY DATA FROM THE TABLEb? TableA is one to many related with tableB.
You gave me solution to make temporary table first and then run the update query. This is not good solution because I need to sum whole tableA (when I'm making temporary table) - and tableA has to many records. It is faster with runing code on a form when tableB do not have to many records (this table has from 1 to 10000 records - it is changing from invoice to invoice -and tableB has allways a lot of records).
OR TELL ME THIS: CAN I SUM RECORDS IN TABLEb (when I make temporary table)- BUT NOT ALL THE RECORDS - JUST RECORDS WITH "FIELDX" that is equall to "FIELDX" from TABLEA" (invoice details)???
- Local time
- Today, 07:59
- Joined
- Feb 28, 2001
- Messages
- 30,806
A summation query can have criteria just like an ordinary SELECT query. In the drop-down that selects Total or Average or Max, there is a Where option so you can limit what you look at. And an Update query could be written based on such a summation query as its data source.
As a side note, INFOS, when you describe a problem and oversimplify it as you did, you invite confusion in those trying to help. I'm not upset with you because you have a difficult problem in spot pricing. Difficult problems, by their very nature, ARE harder to explain. But in the future, you'll get somewhere faster by not totally oversimplifying things quite so much.
I'm going to diverge from the Access issue to give you a different approach. It is not easy. It is not a short answer. But it is absolutely mandatory as a part of the solution to your problem.
Find a dry-erase board, some suitable markers for it, and some sticky-note pads. At least a box worth, not just a pad or two. Draw out the significant parts of your table structure as separate tables. "Populate" the tables by writing "data" on the sticky pads. (I suggest it this way because all these items can be used again later in your business - even the leftover sticky note pads.)
Now, BY HAND, walk through the process of finding the spot price you need to charge. Follow the info in the tables that leads to the data you need in order to compute that price. The issue, as always when doing anything complex, is the "Old programmer's rule:" If you cannot do it on paper, you will NEVER EVER get it right in Access.
Doing it this way will let you SEE the relationships of your database to your actual inventory. Without that visualization, you will get precisely nowhere.
Now, you might realize, for example, that (in contradiction to the way this is normally done), you need to actually keep a separate record for each individual item of inventory (holding the price in that item) in order to know when it is sold - so that you won't use its price in subsequent sales.
I.e.
tblInventoryItem
ItemID, FK to master catalog item
POID, FK to purchase order where this was bought
SupplID, FK to where you got it
Cost, actual per-unit cost of this item
ItemCommit, Yes/No - Yes when item is purchased, No once item is sold.
InvID, FK to invoice where this was sold.
Now, bear with me...
When an item comes in, create ONE RECORD PER ITEM showing that this item
is NOW available (not yet committed). Mark the item as not available (committed) when you sell it. In between, the average cost for any item in your catalog is just the average cost for any available (not committed) item with a given ItemID.
OR... when filling an order, write some VBA to search this table in order of the POID field (which should be implicitly in date order, perhaps...) for items with no invoice number. If you want 10 items, take the first 10 items from this table and average their cost. Mark the item as part of the invoice. Search only items with no Invoice ID for pricing.
If you commit the invoice, go back and set the ItemCommit flag to Yes. At that point, you are done with the sale. If you cancel the invoice, you have the invoice number in the table to back out. Clear the number, and the item is back in inventory. That way, you ALWAYS have the exact price of the item available to you.
When it comes time to do DB maintenance, you can run erase queries to get rid of all items that were committed. Perhaps you can roll up those items to a separate table. I leave that detail to you.
This viewpoint might not be practical either, but then, you don't have a trivial problem. I hope your computer has a LOT of horsepower, 'cause nothing you can do is going to completely reduce the difficulty of your situation.
Also, maybe it isn't a solution now, but down the road you might wish to consider a more robust back end solution. Like SQL Server or ORACLE or one of the many other workable Access BE solutions. Because Access is not going to like handling big databases like this could get.
Reply for DOCMAN
"If you want 10 items, take the first 10 items from this table and average their cost. Mark the item as part of the invoice. Search only items with no Invoice ID for pricing."
I don't want first 10 items - I want 5 items (not just any 5 items - but items with same IDs) that are currently in other table (query) - invoice details.
"If you want 10 items, take the first 10 items from this table and average their cost. Mark the item as part of the invoice. Search only items with no Invoice ID for pricing."
I don't want first 10 items - I want 5 items (not just any 5 items - but items with same IDs) that are currently in other table (query) - invoice details.
Similar threads
