Serialnr query

38 Views

Hello all,

I started working on my serialnr query again, since it's not finished yet.

What my training period speculator wants is an overview of the serialnumbers. He wants the ability to search on a serial number. When you search for it he want to see the result of "who brought the serialnumber in" and to "who is the serialnr sold to"

Currently I only have the "who brought the serialnumber in" part working, but I also want the "sold to part" working. So like this information, but than you can search on the serialnr:

http://www.comm-co.com/images/serienr.jpg

This is my sql code so far:

SELECT T1.IntrSerial, T1.ItemCode, T1.ItemName, T1.WhsCode, T1.CardCode AS 'Leverancier code' , T1.InDate AS ' Inkoopdatum' , T2.CardName AS 'Leverancier',

FROM OSRI T1 LEFT OUTER JOIN OCRD T2 ON T1.CardCode = T2.CardCode

WHERE T1.IntrSerial = '[%0]' FOR BROWSE AND

T2.CardCode IN

( SELECT T3.Direction

FROM SRI1 T3 LEFT OUTER JOIN OCRD T2 ON T3.CardCode = T2.CardCode );

-

T2.CardCode IN

( SELECT T3.Direction

FROM SRI1 T3 LEFT OUTER JOIN OCRD T2 ON T3.CardCode = T2.CardCode );

This part is quite messie and it's far from good, but I've been trying some things out 😊

Anyone who can help me?

Thanks in advance!

Martijn Ivens