Please help with VB - Access query

[Seba Sama]

Hi!

I'm working on a query that stuck me for some hours. I have 4 tables that I need to query to get a result that I need to populate a flexgrid with.
Table Transactions contains (tID, data, clientID, operation) that I need.
Table TransactionsContent contains some columns that I need to sum conditionally.
(tID, productID, palletNumber, boxesNumber, qty, price) that I need
Table Clients contains identification data for clients, need to retrieve NAME
Table pallets contains (paletID, paletState). paletState is for condition.

table Transactions column operation is 'in' or 'out' depends of the operation. This is joined with table TransactionsContent witch contains in or out data as pallet number, number of boxes, qty, price as specified by table Transactions (column operation).

I need to multi join for getting the data like

productID
paletID
data - the date of receiving
(sum(boxesNumber in)-sum(boxesNumber out))
(sum(qty in)-sum(qty out))
price - price of acquisition
clientName

My grid will show smth like:

productID   pallet  date   boxes   qty   acquisitionPrice  client
1              1     ...    150     1500    1.45           X
1              4     ...    100     1000    1.52           y
1              4     ...     50      500    1.48           Z

I tried

SELECT T.productID, (SUM(IIF(T.operation='IN', T.operation,0))-SUM(IIF(T.operation='OUT',T.operation,0)) AS boxes etc

I used MIN(data) for the acquisition date, as the date of receiving is always previous of that of sell.
But how can I doto get the price of acquisition (operation 'IN') as I need to use it in GROUP BY clause? It returns 2 rows, one with the sum(IN) and one with -sum(out). I can't get MIN(price) because sometimes you need to get rid of stuff and sell at a lower price that you bought...

I have tried even create separate tables for IN and OUT but the results are in different columns....

Any advice is highly appreciated!

IF(anyoneCanHelp, 'Thanks in advance','I will break smth')

Seba Sama

[vb5prgrmr]

Use M$ Access's query design tools to create your query. Once you have it showing the information that you want, then goto sql view and copy it from there into your vb6 application. It really is one of the best tools out there for creating the more complex queries...

Good Luck

[Seba Sama]

Already tried. The problem is that I can't get it working. Let me show it by example.

Transactions table

ID	data	              clientID	InOut	processed	
1	6/16/2010 12:37:04 PM	42	IN	0	
2	6/16/2010 12:55:45 PM	5	OUT	1

TransactionsContent table

transID	productIDX	boxes	qty	prezUnit	palet
1	  20	        100	500	0.56		15	
1	  20	         50	250	0.56		15	
1	  18	         80	145	0.48		16	
2	  20	        100	500	0.68    	15

This is my query that WORKS

SELECT q1.pal, q1.boxes, q1.qta, q1.minofdata, c.nome
FROM [SELECT Tc.paletInternal AS pal, Tc.productIDX AS prdidx, (Sum(IIf(t.inout='IN',Tc.boxes,0))-Sum(IIf(t.inout='OUT',tc.boxes,0))) AS boxes, (Sum(IIf(t.inout='IN',Tc.qty,0))-Sum(IIf(t.inout='OUT',tc.qty,0))) AS qta, Min(t.data) AS MinOfdata
FROM Transactions AS t INNER JOIN TransactionsContent AS Tc ON t.ID = Tc.transID
GROUP BY Tc.paletInternal, Tc.productIDX]. AS Q1 INNER JOIN (Transactions AS tr INNER JOIN clienti AS c ON tr.clientID=c.ID_Cliente) ON q1.minofdata= tr.data
WHERE (tr.inout='in')

Result is

pal	boxes	qta	minofdata	        nome
15	50	250	6/16/2010 12:37:04 PM	Cacciatore Angelo
16	80	145	6/16/2010 12:37:04 PM	Cacciatore Angelo

But with this result set I need a last column that shows the acquisition price like:

pal	boxes	qta	minofdata	        nome                 price
15	50	250	6/16/2010 12:37:04 PM	Cacciatore Angelo    0.48
16	80	145	6/16/2010 12:37:04 PM	Cacciatore Angelo    0.56

I'm stuck on this multi join, I can't figure it out.

[vb5prgrmr]

so then adding...

SELECT q1.pal, q1.boxes, q1.qta, q1.minofdata, c.nome, tc.prezUnit ...

does not work? Because yeah, this is a complex query...

You might want to search for SQL forums or access sql forums like the ones over at tek-tips and other places if no one else pipes up...

Good Luck