Combining Query/Report

[aphrael]

Trouble all to help me out with the following. Many thanks in advance.

On Excel



Header -> PIC Stock OUT Stock IN Quantity
01/Oct Truck 1 Warehouse A Warehouse B 100
05/Oct Truck 2 Warehouse A Warehouse C 50
10/Oct Truck 1 Warehouse B Warehouse C 50

Using the simplified excel above, can we derive the following report on access?

Warehouse A Stock OUT Stock IN
01/Oct ........100 / -
05/Oct .........50 / -
Summary ...150 / -
Grand Total.150 / -

Warehouse B Stock OUT Stock IN
01/Oct .........- / 100
05/Oct .......50 / -
Summary ...50 / 100
Grand Total ...- / 50

[June7]

Should be able to, although will be tricky. I expect a UNION query will be involved to rearrange data to look like:

Date	Whse	Out	IN
01/Oct	A	100
01/Oct	B		100
05/Oct	A	50
05/Oct	C		50
10/Oct	B	50
10/Oct	C		50

SELECT PIC, [Stock Out] AS Whse, Quantity AS Out, Null AS IN FROM tablename
UNION SELECT PIC, [Stock IN], Null, Quantity FROM tablename;

Then use report Sorting & Grouping features and aggregate calcs in textboxes.

[aphrael]

hopefully can remain my initial format of stating 1 liner with one quantity but out/in different warehouses. Noted your suggestion works but it creates twice the entries. hoping to minimize lines.

[June7]

Your example shows records grouped by warehouse. There are 2 lines for each date. Only way I know to do that is for each warehouse to have its own record for the transaction.

My solution replicates that example - 2 lines for each date.

Lines are minimized and achieves example provided. Why does it matter how query manipulates data as long as desired output is achieved?

[aphrael]

Hi June, was hoping there's another shortly way to derive the results. Many thanks.