[AccessD] SQL Puzzler

Brad Marks BradM at blackforestltd.com
Wed Oct 17 08:40:37 CDT 2012


All,

I recently received a user request and have started work on an SQL statement to meet this request.  After several hours of work on the SQL Statement, I am starting to pull my hair out.

This request involves a purchased software package.  We cannot change the definition of the tables.

I am using Access 2007 via ODBC to pull data from this purchased package to create a number of reports.

The system has a Sales Order table with one Sales Order per row.

Connected to the Sales Order table is the "Attachments" table.  This table stores the Sales Order ID, a description of the attachment, and the path to the attachment.

In the "description of the attachment" field there are key words which describe the "type" of attachment (artwork, photo, customer-service).

Currently there is a report that shows various Sales Order fields (one line on the report per Sales Order).  Included on the report, is an indicator to show if there are any attachments for a given Sales Order.  This is all working nicely.

Now, there is a need to indicate which "type" of attachments are connected to specific Sales Orders (still keeping with one report line per Sales Order).



By using a "Switch" and a "Like" in an SQL statement like this -

Switch([DESCRIPT] Like "*Photo*","Photo",True,"") AS Photo 

I can obtain a field that returns the word "Photo" if this word is found in the description.




(I also have similar code for artwork and customer-service)


The catch is that this approach returns one record for each attachment.  Now I need to somehow summarize or combine the returned rows (one summary row per Sales Order in order to achieve one line on the report per Sales Order).

This is where I am hitting the wall.

Has anyone else ever tried something along these lines?

Thanks,
Brad   

  


More information about the AccessD mailing list