[AccessD] SQL Puzzler

Stuart McLachlan stuart at lexacorp.com.pg
Wed Oct 17 15:29:22 CDT 2012


As I understand Brad's requirement is more like the folloing slight amendment to Arthur's 
code.

Trim$(IIf(InStr([Description],"Artwork")>0,"Art ","") &
IIf(InStr([Description],"Photo")>0,"Pho ","") &
IIf(InStr([Description],"Customer-Service")>0,"CS ","")) AS
[Attachments]

Which would return the following fields for the three records:
Pho CS
CS
Art Pho

-- 
Stuart

On 17 Oct 2012 at 11:19, Arthur Fuller wrote:

> Brad,
> 
> Just so I'd have some data to work with, I added OrderDate to the
> SalesOrder table, with SalesOrderID being ANPK. Then I added a few rows to
> the Attachments table:
> 
> 
> *SalesOrders*  SalesOrderID OrderDate   1 16/10/2012  2 17/10/2012  3
> 18/10/2012  4 18/10/2012  *Attachments*  AttachmentID SalesOrderID
> Description   1 1 Photo, Customer-Service  2 2 Customer-Service  3 3 Artwork,
> Photo
> Then I created the following query:
> 
> <vba>
> SELECT
>     SalesOrders.SalesOrderID,
>     SalesOrders.OrderDate,
>     IIf(InStr([Description],"Artwork")>0,"Y","") AS Artwork,
>     IIf(InStr([Description],"Photo")>0,"Y","") AS Photo,
>     IIf(InStr([Description],"Customer-Service")>0,"Y","") AS
> [Customer-Service]
> FROM
>     SalesOrders LEFT JOIN Attachments
>     ON SalesOrders.SalesOrderID = Attachments.SalesOrderID;
> </vba>
> 
> Which results in this output:
> 
> *qrySalesByAttachmentType*  SalesOrderID OrderDate Artwork Photo
> Customer-Service   1 16/10/2012
> Y Y  2 17/10/2012
> 
> Y  3 18/10/2012 Y Y
>  4 18/10/2012
> 
> 
> 
> Is this what you had in mind? If so, there you go. If you need to tweak it
> a tad, at least you've got a place to begin.
> 
> Arthur
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 




More information about the AccessD mailing list