Product Database

Question

Hi

I have access database with three tables, the first one is called ProductTb and has the fields ProductID as AutoNumber and ProductName as short Text as shown with figure below

The second table called PurchaseTb and has the following fields: PurchaseID as AutoNumber, ProductID as Number, PurchaseQty as Number, PurchasePrice as Number, P_Payment  as Number, PurchaseDate as Date and P_InvoiceNo as Number as shown with figure below

The Third table called SaleTb and has the following fields: SaleID as AutoNumber, ProductID as Number, SaleQty as Number, SalePrice as Number, S_Payment  as Number, SaleDate as Date and S_InvoiceNo as Number as shown with figure below

I made a relationships between tables as shown with figure below

I would like to create a continuous form with three fields, the first field will show ProductNames (ProductName), the second field will show Product Numbers (ProductID), the third field will show the difference between the sum of PurchaseQty for a specific product and the sum of SaleQty of the same product. The example below shows the form

Product Name                              Product Number                     Difference

Custard                                                       1                                           2

Dalya                                                           2                                           0

Fish                                                              3                                           3

Biscuit                                                         4                                          11

Thanks for help in advance.

Best Regards

Answer 1

You need to create a query that joins your all three tables . Join Sales and Purchase to Product on ProductID. Add the ProductID, ProductName columns from Products and the SalesQty and PurchaseQty fields. Turn the query into a Group By query, Group on ProductID and ProductName, and Sum the two Quantity fields. Finally add a column to calc the difference:

Difference: Sum(ProductQty)-Sum(SalesQty)

Then use that query as the Recordsource of your Continuous form.

Note: this might be better as a report rather than a form since you won't be able to edit the data.

Answer 2

You need to use an Outer Join. This may be a good reason to do the sepoarate tables so you only need to use the Outer Join in the third table.

Answer 3

Hi

Thank you for your answer. I will try to do it in one query. I have tried to create a form based on my third query it shows all the product names, product numbers, and difference between PurchaseQty and SaleQty but the form does not show all product names, I mean the products that have not  PurchaseQty or SaleQty or both. I would like the form will shows all the product names, product numbers and if there is not PurchaseQty or SaleQty leaves the StoreAvailable empty.

The figure below shows my form

The figure below shows ProductTb with all Product Names

Thanks for your help in advance.

Best Regards

Answer 4

You don't need to do the separate Sales and Purchases queries. You can do it all in one query. But there is nothing wrong in the separate queries and you may be able to use them elsewhere.

Answer 5

Hi

Thank you for your answer, it works, I have created three queries.

 The first query called StorePurchaseQry

SELECT PurchaseTb.ProductID, Sum(PurchaseTb.PurchaseQty) AS SumOfPurchaseQty

FROM PurchaseTb

GROUP BY PurchaseTb.ProductID;

The Second query called StoreSaleQry

SELECT ProductID, Sum(SaleTb.SaleQty) AS SumOfSaleQty

FROM SaleTb

GROUP BY ProductID;

The Third query called StoreQtyQry

SELECT ProductTb.ProductID, ProductTb.ProductName, StorePurchaseQry.SumOfPurchaseQty, StoreSaleQry.SumOfSaleQty, [SumOfPurchaseQty]-[SumOfSaleQty] AS StoreAvailabel

FROM (ProductTb INNER JOIN StoreSaleQry ON ProductTb.[ProductID] = StoreSaleQry.[ProductID]) INNER JOIN StorePurchaseQry ON ProductTb.[ProductID] = StorePurchaseQry.[ProductID]

ORDER BY ProductTb.ProductName;

I will use the third query for building my first form.

Best Regards