Solved Stock Summary Issue in crosstab query (1 Viewer)

[ahmad_rmh]

I am making a stock summary in crosstab query. The criteria is as under;

1. Current Stock should have to be the net present stock, i.e, (Opening+Purchases-Transfers)

2. Branches stock should have to be only current month transfers.

how to get this.

db file is attached herewith.

kindly suggest

 

[plog]

Your query is producing a result, so I can't see what the issue is. But I do know what will help--show me what results you expect.

Run your query, paste the results into Excel then change the ones that are incorrect and post that file back here. That will allow me to see what you expect it to return and help you get there.

 

[ahmad_rmh]

Your query is producing a result, so I can't see what the issue is. But I do know what will help--show me what results you expect.

Run your query, paste the results into Excel then change the ones that are incorrect and post that file back here. That will allow me to see what you expect it to return and help you get there.

Yes, Current stock result is ok, but the branches transfers should have to be shown only current month transfers but it's including the previous month transfers.

 

[plog]

You quoted my post which leads me to believe you read it, but didn't provide what I requested.

 

[ahmad_rmh]

You quoted my post which leads me to believe you read it, but didn't provide what I requested.

I have attached updated db file with only item for sample. Excel file is not attachable with me. I have attached pdf

 

[plog]

You're not helping me help you. I need to see what data you are starting with and what data you expect to end up with. Trying to walk me through it isn't helpful. I need you to do less work than you actually are.

Give me a database with sample data, then based on that data give me what you expect the query you are building to look like. No words needed, not step by step instructions, just data--starting and expected results based on starting data.

 

[ahmad_rmh]

You're not helping me help you. I need to see what data you are starting with and what data you expect to end up with. Trying to walk me through it isn't helpful. I need you to do less work than you actually are.

Give me a database with sample data, then based on that data give me what you expect the query you are building to look like. No words needed, not step by step instructions, just data--starting and expected results based on starting data.

ok, got it,
1. sample db file is attahced herewith.
2. pdf is attached for the expected results.

 

[plog]

That's perfect. Since you want calculations based on different timeframes (all and current month) it will require some subqueries. I've attached a database that gets you 95% of the way there. Here's what each query does:

sub1 - gets total stock for each item

sub2 - gets total current month transactions for each item and branch. This will automatically update to the current month each month. Which also means if you want January totals in February you will need to make an adjustment in this query.

sub3 - combines them together

To get your finished product you would make another query based on sub3 and cross tab it.

 

[ahmad_rmh]

That's perfect. Since you want calculations based on different timeframes (all and current month) it will require some subqueries. I've attached a database that gets you 95% of the way there. Here's what each query does:

sub1 - gets total stock for each item

sub2 - gets total current month transactions for each item and branch. This will automatically update to the current month each month. Which also means if you want January totals in February you will need to make an adjustment in this query.

sub3 - combines them together

To get your finished product you would make another query based on sub3 and cross tab it.

Thanks plog, done,