Query To Define Outstanding Issued Document

[Guest]

I do not really know anything about SQL and would like to know is the
following is possible?:

I currenly have a ODBC link to our Drawing Database. The table is called
dbo_DRAWMOVE. Within this table the are the following fields:
Drawing
Copies_Moved
Issue_Type
Move_Issue_Ref

When a drawing is issued our the qty issued shows as a positive in
'Copies_Moved', and when drawings are returned the qty shows as a negative in
'Copies_Moved'.
Each transaction as an 'Issue_Type' which are as follow:
employee = 1
works order = 2
workshop = 3
others = 4
And against each one of these transaction a 'Move_Issue_Ref' is given as
follows:
1 = employee clock number
2 = works order number
3 = WC
4 = (blank)

What I am wanting to do is report ALL drawings that are currently issued and
to what/whom ie:

Works Order No *****
Employee *****

If 1 drawing for drawing ABCD is issued to works order 1234 than returned
the table will show 2 transactions as follows:

ABCD 1 2 1234
ABCD -1 2 1234

Therefore the query would not result anything as the drawing had been return
but if 2 drawings had been issued but only 1 had been retuned the transations
would be as follows:

ABCD 2 2 1234
ABCD -1 2 1234

Therefore the query would result:

ABCD 1 2 1234

Hope you can understand this.

Regards

 

[Michel Walsh]

Sounds that something like:


SELECT Drawing, Issue_Type, Move_Issued_Ref
FROM myTable
GROUP BY Drawing, Issue_Type, Move_Issued_Ref
HAVING SUM(Copies_Moved) <> 0


or, graphically, click on the Summation button on the toolbar, a new line,
Total, appear in the grid, bring the 3 fields :
Drawing, Issue_Type, Move_Issued_Ref
in the grid, keep the proposed GroupBy, bring Copies_moved in the grid,
change the proposed GroupBy to SUM and, in the same column, but at the
criteria line, type <>0


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Easy when you know how.

Much appreciated.

Regards
The Rook