Solved QryStockSummary

[ahmad_rmh]

Kindly suggest the solution of repeating items in qryStockSummary. I have attached the database file.


Thanks

 

[jdraw]

ahmad_rmh,

How about providing a description in simple, non database terms of what you are trying to do?
It is much easier to work from a requirement, than to adjust code for which we have no context and could misunderstand/guess your intent.

 

[ahmad_rmh]

Thanks Jack for the response. I have explained the requirement of the query.

I have made query of stock summary as per the following requirements.

1. Opening Stock: closing stock upto the end of the last month would be the opening stock of current month.
2. Purchases: Current month purchases.
3. Transfers: Current month transfers to the branches warehouse.
4. Adjustment: Current month stock adjustment
5. Stock Summary: Opening + Purchases -Transfers (+/-) Adjustment
6. Monthly Avg Usage: Current month usage / No of days of Current month

As the same item is received two to three times, the query shows multiple times of the same item. Kindly suggest the solution.

 

[ahmad_rmh]

Thanks Pat, yes transactions table is splitted into two because we are receiving more than one item on a single invoice and transferring more than 10 or 20 items on a single transfer note.

tblTransactionsMain and tblTransactions.

My database is designed in the scenario as per the attached screenshot of northwind database relationship.

1. tblTransactions as like orders detail
2. tblTransactionsMain as like orders
3. Entities as like customers.

Kindly suggest the solution.

 

[ahmad_rmh]

Thanks Pat, I have solved through UNION and CROSSTAB query.