Brad Marks
BradM at blackforestltd.com
Wed Oct 17 10:49:00 CDT 2012
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.