Question Database design to calculate profit for individual sold items (1 Viewer)

[asonofTheAlmighty]

I am trying to design a database to keep records for a literature distribution organization. I have the basic design figured out patterned after the Northwind database wherever applicable. I am now trying to create reports to track Purchases, Sales, AND PROFIT by Customer and/or by Product for any chosen time period. I have a query called "Closed Purchases" with fields of PurchaseID, PurchaseDetailsID, Product, Qty, ActualCost, and ArrivalDate. (These are captions not the actual fields names.) I also have a query called "Closed Sales" with fields of SalesID, SalesDetailsID, Product (actual field is od_ProductID which is stored as a number but pulls the Product Title from the Products table), Qty, Selling Price, and OrderDate. Based on the assumption that oldest inventory is automatically sold first, I would like to know the Profit for every item sold based on the ActualCost paid for THAT PARTICULAR item.

How I did this was to create a new query called "Closed Sales Individualized" that broke all the sales down into individual items. In other words, what had been a Product of "BookTitle1" with a Qty of 40 would now be 40 individual records of "BookTitle1" numbered 1 through 40.

I then made a new query called "Closed Sales Individualized Sorted a-z" that pulled all the fields from the last one and added a new ID field by concatenating the Product, OrderDate, SalesID, SalesDetailsID, and Rank fields. I then sorted by this new field. So now it looked something like: 40 records of "BookTitle1" numbered 1-40; 32 records of "BookTitle1" (but of a newer Date) numbered 1-32; 25 records of "BookTitle2" numbered 1-25; 24 more records of "BookTitle2" (with a newer Date) numbered 1-24; etc.

Next, I made a new query called "Sales Ranked" that pulled just the Product and UniqueID fields from the last query. To this one I added a field called Rank (that included a subquery with Count function) that numbered each Product as a group: so now it was 40 records of "BookTitle1" of "Date1" followed by 32 records of "BookTitle1" of "Date2" now numbered 1-72 (instead of 1-40 and 1-32).... So all items of a Product are now grouped together and ordered by the order in which they were sold.

I then made a string of similar queries for "Closed Purchases". Next, I took the "Sales Ranked" query and the "Purchases Ranked" query and combined them into a query called "Sales matched to Purchases" that joined the two queries wherever the Product and Rank were identical. From this I made a new query called "Sales/Purchases Summary" that used the ID from the sales side of the "Sales matched to Purchases" query to pull the rest of the details from "Closed Sales Individualized Sorted a-z" and the ID from the purchases side to pull the wanted details from "Closed Purchases Individualized Sorted a-z". To this query I added a field to calculate the Profit. I now have a query that shows every Sale with its Selling Price and with its Actual Cost (and Profit) based on the order of sale compared to the order of purchase.

The only problem: it runs very slowly! The last query takes more than a full minute to run even though there are only 262 individual sold items. The bottleneck is in the "Sales Ranked" and "Purchases Ranked" queries. The function that is used to rank the records is very intensive and slow. Here is the SQL of the "Sales Ranked" query:

SELECT [Closed Sales Individualized Sorted a-z].ID, a.od_ProductID, IIf([a]![od_ProductID]=[Closed Sales Individualized Sorted a-z]![od_ProductID],(SELECT COUNT(ID) FROM [Closed Sales Individualized Sorted a-z] WHERE (ID < a!ID) and (a!od_ProductID = od_ProductID)),"")+1 AS RANK
FROM [Closed Sales Individualized Sorted a-z] AS a INNER JOIN [Closed Sales Individualized Sorted a-z] ON a.ID = [Closed Sales Individualized Sorted a-z].ID
ORDER BY [Closed Sales Individualized Sorted a-z].ID;

So, someone surely has a better idea how to accomplish what I am trying to do. Is there a better way to rank the records so that they can be joined. Or is there a common way of accomplishing this type of report completely different from the way I set out to do it. After all, I expect that there are many companies that keep track of the actual cost paid for each of the 12 copies of Item1 that they sold today even when 5 of those copies were part of a purchase 13 months ago and the other 7 part of a purchase (of a higher price) only 8 months ago.

I hope you can make sense of this. If not, please ask for clarification. (I understand very little of VBA coding and a very limited amount of SQL. I prefer to work in Design View and only switch to SQL to make final adjustments that Design View cannot do. So hopefully there is a way to do this with my limited understanding. ???) Thank you very much for your help. I have been working on this Report alone for more that two days.

 

[Simon_MT]

Have you thought about evaluating the cost for each sale and storing it in the table. What you are trying to do retroactively determine the cost when it would be easier to do at the time of sale, particularly as the cost can be variable.

Simon

 

[spikepl]

I'd recommend that you stop for a moment to think: you are implementing a complex mechanism which I am not sure is necessary. Google cost of goods sold (purchased), inventory management, FIFO and LIFO accounting etc - you are not the only one in the world facing variable cost of goods, and inventory management is a discipline in its own right.

Besides, if you are doing this for fun then you can do what you please. However, if you are doing this for the purpose of actual accounting, then you should run the chosen method past the accountants before coding anything to verify what is recognized by the tax people.

 

[asonofTheAlmighty]

Thank you. I will try more Google searching. I knew there had to be others facing the same situations but I just didn't know the right terminology to search for. So thank you for those suggestions.

Concerning evaluating the cost at the time of sale: I would like for the cost of goods sold to be figured on a FIFO basis even if I don't enter the sales in the order that they occurred. So if I enter a sale with a date of March 25 and then another sale with a date of February 25, I want the March 25 sale to reconsider the cost that it was assigned. If you can make sense of what I mean.

 

[asonofTheAlmighty]

It has now been several months and although my database is finally up and running, I continue to make improvements to it. Here is the conclusion I came to:

I did a lot of Google searching and finally came to the conclusion that to do this in the "right" way is beyond my coding capabilities. So I opted to perfect the method that I already had. And I did get it to run much faster.

The method that I used to number the rows is described in Microsoft KB94397. This method is very fast but not very flexible. #1 you can't base another query on it—the row numbers change as you scroll up and down the results. #2 you can't do any grouping—its just sequential numbering.

So, I wrote a MakeTable query that used the dynamic counter to assign every row in "Closed Sales Individualized" a row number and create a table from the results.

Next, another query pulls just the quantity and the ProductID from "Closed Sales Individualized", groups the results by ProductID, and totals the quantity. Then another query takes these results, divides them out into one item per row again (This is done with an SQL from clause like this:"FROM ClosedSales4a INNER JOIN Individualizer ON ClosedSales4a.Qty >= Individualizer.ID". Individualizer is a table that has one field—ID—containing a list of numbers 1 through the-highest-qty-that-will-be-encountered.) and uses the dynamic counter to number the rows. This query then creates a table from the results. (Both of these queries were sorted the same way.)

Next, a third MakeTable query joins the two tables by matching their row numbers.

A similar string of queries is made for Purchases. Then I wrote a VBA function to run all these MakeTable queries in the right order and delete all but the final tables. It also runs some queries to add indexes to the final tables and it beeps when it is done. Then a query can pull results from the two final tables, joining them on Product and Rank and then figure Profit.

I placed a button on the form used to launch reports, that, when pressed, runs the VBA function.

Now I hope you can make sense of all this. It sounds complicated; and it does take a while to write. But the final result is a MUCH faster method than before to figure profit. With over 8,000 Purchases and 4,000 Sales, when I click the button, it finishes in 6 seconds. The query that figures profit runs almost instantaneously.

 

[spikepl]

Good of you to post back