[AccessD] SQL Puzzler

Arthur,

I believe that your approach will work.

Thanks for your ideas and insights.

I really appreciate the assistance.

Brad    

~~~~~~~~~~~~~~~~~~~~~~~~~~
 




-----Original Message-----
From: accessd-bounces at databaseadvisors.com on behalf of Arthur Fuller
Sent: Wed 10/17/2012 10:19 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] SQL Puzzler
 
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

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.