RE: Looking for a database function

["Dan Goldstein" <DGoldstein -at- riverainmedical -dot- com>]

First of all, my sincere thanks to John, Tony, Geoff, Ned, David, Craig,
and Monique for taking the time to tackle this question. 

I myself had previously set up an Excel spreadsheet using the
IF(COUNTIF) function -- for example, <=IF(COUNTIF('Employee
columns'!C$2:C$35,Procedures!$A2)=0,"",D$1)>, with a second function for
sorting the results -- but this is beside the point. I'm not asking how
to create the grid; I'm asking, why do we even need to create it?

My original description was (IMNSHO) an excellent user interface for a
many-to-many relationship between two entities. There ought to be an app
that already offers this as a native function, and I think it's a shame
if there isn't.


> -----Original Message-----
> From: John Bruin
> Sent: Sunday, May 09, 2010 11:48 PM
> To: techwr-l -at- lists -dot- techwr-l -dot- com
> Subject: RE: Looking for a database function
> 
> If you have some familiarity with Excel then using a 
> combination of Excel "hlookup", "vlookup" and "if" functions 
> can do this quite easily. 
> 
> You would set up a table with value 1 entered as your X and 
> then beneath the table set up two columns of formulae : one 
> to list procedures  and one to list employees.
> 
> Setting up the formulae would be a little fiddly, but as it 
> is probably a one-off, not too onerous. Once set up then you 
> would "query" by typing "Joe Dobbs" in a particular cell and 
> it would show all his procedures. Likewise, typing the name 
> of a procedure in another cell would show a list of employees 
> trained in it.
> 
> the 2 sets of formula would look something like this:
> 
> =IF(VLOOKUP($B$19,A$1:H$14,2,FALSE)=1,B1,"")
> 
> =IF(HLOOKUP($E$19,A$1:H$14,2,FALSE)=1,A2,"")
> 
> where:
> 	- b19, e19 is the query value for procedure/ employee
> 	- a1:h14 is the table
> 	- 2 is the column/row offset  (need to manually change for 
> each element)
> 	- false = exact match
> 	- b1/a2 = row/ column headers (this is the bit that 
> returns the procedure/employee if the value is 1 and creates 
> the list)
> 
> 
> Just another option to consider, and relatively simple if you 
> don't mind playing with Excel (or have a friend who can help 
> you)
> 


This message contains confidential information intended only for the use of the addressee(s). If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing, copying, electronic storing or the taking of any action in reliance on the contents of this message is strictly prohibited.  If you have received this message by mistake, please notify us, by replying to the sender, and delete the original message immediately thereafter.  Thank you.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Use Doc-To-Help's XML-based editor, Microsoft Word, or HTML and 
produce desktop, Web, or print deliverables. Just write (or import) 
and Doc-To-Help does the rest. Free trial: http://www.doctohelp.com


 - Use this space to communicate with TECHWR-L readers - 
   - Contact admin -at- techwr-l -dot- com for more information - 


---
You are currently subscribed to TECHWR-L as archive -at- web -dot- techwr-l -dot- com -dot- 

To unsubscribe send a blank email to 
techwr-l-unsubscribe -at- lists -dot- techwr-l -dot- com
or visit http://lists.techwr-l.com/mailman/options/techwr-l/archive%40web.techwr-l.com


To subscribe, send a blank email to techwr-l-join -at- lists -dot- techwr-l -dot- com

Send administrative questions to admin -at- techwr-l -dot- com -dot-  Visit
http://www.techwr-l.com/ for more resources and info. 

Please move off-topic discussions to the Chat list, at:
http://lists.techwr-l.com/mailman/listinfo/techwr-l-chat