I don't fully undertand what you're doing. The first two queries, are those examples of the data inside those tables, or are they views/queries of some other table. It seems like all you need is a simple table join. Perhaps you could send me the database and cfml off list and I could look at it for you. You'll have to email me at [EMAIL PROTECTED]
Fregas ----- Original Message ----- From: "Jason Miller" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, January 10, 2003 7:03 PM Subject: SQL Query Help - Access Lookup tables and query output not accurate - not working > Hello List - > I can not get this query down for the life of me. After a wasted > afternoon - I am hoping ( I know you can) someone will assist me. > > I have an access table I am grabbing my information from - table - > ModuleFeatures > ModuleFeatures contains Column iModule -WHICH is a lookup to a > ApplicationModules table > SELECT [ApplicationModules].[iApplicationModuleID], > [ApplicationModules].[sModuleName] FROM ApplicationModules; > > and it has an iApplication colum - which again is a lookup to a > Application table - > SELECT [ApplicationTypes].[iApplicationID], > [ApplicationTypes].[sApplicationName] FROM ApplicationTypes; > > That is the code WITHIN access. Now on coldusion - I run a simple query > to grab these features - > <cfquery name="GetModuleFeatures" datasource="#dsndata#"> > SELECT iModule, iApplication, sModuleFeature FROM ModuleFeatures > ORDER BY iModule, iApplication > </cfquery> > > And the output is equally as simple - > <cfoutput query="GetModuleFeatures" group="iModule"> > #iModule# : within #iApplication#<br> > > <cfoutput> #sModuleFeature#<br></cfoutput> > </cfoutput> > This outputs perfectly. And the statement above - #iModule# : within > #iApplication# was for a test. And it outputs correctly. So what it ends > up with is > 8: within 2 - > 8 in Table ApplicationModules = Medical Records > 2 in Table ApplicationTypes = Financial Applications. > > So instead of > 8: within 2 - I need it to say > Medical Records: within Financial Applications > > When I modified my simple query and alter the query to grab from both > tables - it threw everything off. > > The ONLY working solution is that I can do this... modify the output > query (shown below )-which does the trick - but is a headache. Not to > mention - I have to do that with iModule - and there are upwards of 30 > modules. Setting those by hand defeats the purpose of pulling from a > database. > > I know I there is a way to modify my simple query - but going on 3 hours > of tinkering and can not get it to work right or if I get it working - > it's not accurate. > > Sorry this is long - but I figured the more info - the better someone > can help me! > Thanks!!!!!!!!!!!!!!!! > <!--- reassign numbers with names ---> > <cfparam name="ThisApp" default=""> > <cfif iApplication EQ 1> > <cfset ThisApp ="Financial Systems"> > <cfelseif iApplication EQ 2> > <cfset ThisApp ="Management Systems"> > <cfelseif iApplication EQ 3> > <cfset ThisApp ="Clinical Applications"> > </cfif> > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
