Subtracting Totals in a Business Database

[Evon]

Hi, I need some help as I have been battling with this problem for over a week. I consider myself at the intermediary stage and do not use much coding in my programs.

I have set up this database here for my new sole trader business. In it I enter data of my purchases and my gross sales. I have generated 2 queries both based on the same tables. 1 calculates the purchases and the other the sales.

I want to be able to subtract the purchases from the sales so that I can see my profit at any point in time. As simple as it sounds, I have not been able to achieve this.

The complication is brought on I think from the fact that I am using one table for both Purchasing and Sales. However, I use a filtering method to separate the two.

I have included all the queries and the data so anyone willing to assist will see what I was trying to do.

 

[jdraw]

You mention PurchaseOrder in your Customer table, but I see no purchase order anywhere. You also mention Invoice in your reports but I see no Invoice entity.

Normally you would have

Customer-->Order--->OrderDetail<----Product<-- maybe--Suppliers

I would not expect to see ProductId or PurchaseOrderId in a Customer table.
Your use of Product1, Product2, Product3... indicates a lack of Normalization. This will make info retrieval, manipulation and maintenance most difficult.

I think you have a database structure issue that is the basis for your issue.

Watch the videos in post #4 at
http://www.access-programmers.co.uk/....php?p=1217446 for more info on data structure/modelling.

 

[Evon]

Yes jdraw, that is the trick. I want to enter data only once and use the same table (Products) to generate the Quotation to my clients, The Purchases made from my Suppliers, and the Invoices & Delivery/Receipts to my clients. And I have achieved that so far.

Additionally, I now want to be able to calculate a simple profit-and-loss by subtracting the sales from the purchases.

I have already created a query to calculate the purchases called "Purchase Query" and another query for my sales called "Product Query" Also, I created 2 individual Reports from those 2 queries called "PurchaseReport" & "Revenue".
I would like to create a 3rd. query called "Profit" to show the difference between both reports. (Which so far I am unable to do and is here beggin' for some help)

Admittedly, I know there is some clutter in the database as a result of various experiments I had carried out, but please don't let that distract you from the main objective. The clutter can be cleared up quite easily once I find the solution for this issue.

I thought it might be helpful if you are able to see what I have been trying so far, so that is another reason for not cleaning up yet. I also read "The Ten Commandments" Sticky and know that I am guilty of a few more sins also, but hope that since these were committed in my zeal, I will be forgiven by the gods.
Anyway, thanks for trying to help.