Re: Create 1 CFSELECT from two sources
Not with Access. With Access you are going to need to write two seperate queries, then use query of query to merge them into one. -Adam On Apr 11, 2005 12:14 PM, Claremont, Timothy <[EMAIL PROTECTED]> wrote: > Using 6.1 and Access DB... > > Is there a way to combine two data sources to generate a CFSELECT list? > > I have a list of storage locations, and a list of participants. My > select list will result in allowing the end user to tell me that the > item is at a storage location, or with a participant. > > The list of participants is in one database, and the list of locations > is in another. How do I make them appear in one list? > ** > This email and any files transmitted with it are confidential and > intended solely for the use of the individual or entity to whom they > are addressed. If you have received this email in error please delete it from > your system. > > This footnote also confirms that this email message has been swept for > the presence of computer viruses. > > Thank You, > Viahealth > ** > > ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202184 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Create 1 CFSELECT from two sources
Query of queries. The only way without letting your logic in the CFSELECT get too weird. -- Damien McKenna - Web Developer - [EMAIL PROTECTED] The Limu Company - http://www.thelimucompany.com/ - 407-804-1014 #include > -Original Message- > From: Claremont, Timothy [mailto:[EMAIL PROTECTED] > Sent: Monday, April 11, 2005 12:15 PM > To: CF-Talk > Subject: Create 1 CFSELECT from two sources > > Using 6.1 and Access DB... > Is there a way to combine two data sources to generate a > CFSELECT list? ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202185 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Create 1 CFSELECT from two sources
How would it be done if Access wasn't used? -- Damien McKenna - Web Developer - [EMAIL PROTECTED] The Limu Company - http://www.thelimucompany.com/ - 407-804-1014 #include > -Original Message- > From: Adrocknaphobia [mailto:[EMAIL PROTECTED] > Sent: Monday, April 11, 2005 12:17 PM > To: CF-Talk > Subject: Re: Create 1 CFSELECT from two sources > > Not with Access. With Access you are going to need to write two > seperate queries, then use query of query to merge them into one. ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202186 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Create 1 CFSELECT from two sources
In Oracle you could setup a dblink. On Apr 11, 2005 11:18 AM, Damien McKenna <[EMAIL PROTECTED]> wrote: > > How would it be done if Access wasn't used? > > -- > Damien McKenna - Web Developer - [EMAIL PROTECTED] > The Limu Company - http://www.thelimucompany.com/ - 407-804-1014 > #include > > > -Original Message- > > From: Adrocknaphobia [mailto:[EMAIL PROTECTED] > > Sent: Monday, April 11, 2005 12:17 PM > > To: CF-Talk > > Subject: Re: Create 1 CFSELECT from two sources > > > > Not with Access. With Access you are going to need to write two > > seperate queries, then use query of query to merge them into one. > > ~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202188 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Create 1 CFSELECT from two sources
> Using 6.1 and Access DB... > > Is there a way to combine two data sources to generate a > CFSELECT list? > > I have a list of storage locations, and a list of > participants. My select list will result in allowing the end > user to tell me that the item is at a storage location, or > with a participant. > > The list of participants is in one database, and the list of > locations is in another. How do I make them appear in one list? You can use query-of-query functionality to build one query from the union of two existing queries, as long as the datatypes of the data you want are similar in both queries. You may need to alias fields in the original queries to get the names to match. SELECT storageid AS id, storagename AS name, 'storage' AS type FROM storage SELECT participantid AS id, participantname AS name, 'participant' AS type FROM participant SELECT id, name, type FROM q1 UNION SELECT id, name, type FROM q2 Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202189 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Create 1 CFSELECT from two sources
One way would be to combine the two queries into one. You have the two datasources already in cfqueries just do this. Select a.*, b.* from queryA a, queryB b Of course you may have to make some small changes like making the column names match, ordering by a certain field... but this will give you one query with all of your data -Ferg -Original Message- From: Claremont, Timothy [mailto:[EMAIL PROTECTED] Sent: Monday, April 11, 2005 11:15 AM To: CF-Talk Subject: Create 1 CFSELECT from two sources Using 6.1 and Access DB... Is there a way to combine two data sources to generate a CFSELECT list? I have a list of storage locations, and a list of participants. My select list will result in allowing the end user to tell me that the item is at a storage location, or with a participant. The list of participants is in one database, and the list of locations is in another. How do I make them appear in one list? ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please delete it from your system. This footnote also confirms that this email message has been swept for the presence of computer viruses. Thank You, Viahealth ** ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202190 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Create 1 CFSELECT from two sources
Yeah, kinda sorta. Keep in mind, I'm not advocating this as a solution. I've used it, and finally decided to merge the databases into one, eliminating the Access database. But anyway, for awhile I was in a situation where employee information was kept in Oracle and employee Intranet accounts were stored in Access. I ended up creating a link table in Access to the Oracle DB, using an ODBC reference, and building queries out of the Access tables and the link table. We ended up merging the account information into Oracle, though. Hope this helps. Matt Osbun Web Developer Health Systems, International -Original Message- From: Claremont, Timothy [mailto:[EMAIL PROTECTED] Sent: Monday, April 11, 2005 11:15 AM To: CF-Talk Subject: Create 1 CFSELECT from two sources Using 6.1 and Access DB... Is there a way to combine two data sources to generate a CFSELECT list? I have a list of storage locations, and a list of participants. My select list will result in allowing the end user to tell me that the item is at a storage location, or with a participant. The list of participants is in one database, and the list of locations is in another. How do I make them appear in one list? ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please delete it from your system. This footnote also confirms that this email message has been swept for the presence of computer viruses. Thank You, Viahealth ** ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202191 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Create 1 CFSELECT from two sources
Use remote queries. http://www.vb123.com/toolshed/02_accvb/remotequeries.htm It allows you to join multiple dbm files in one query. I have also seen syntax like: Select * from c:\path\accessdb.mdb\tablename And this works, I've done it but it's been a long time. - Matt Small -Original Message- From: Claremont, Timothy [mailto:[EMAIL PROTECTED] Sent: Monday, April 11, 2005 12:15 PM To: CF-Talk Subject: Create 1 CFSELECT from two sources Using 6.1 and Access DB... Is there a way to combine two data sources to generate a CFSELECT list? I have a list of storage locations, and a list of participants. My select list will result in allowing the end user to tell me that the item is at a storage location, or with a participant. The list of participants is in one database, and the list of locations is in another. How do I make them appear in one list? ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please delete it from your system. This footnote also confirms that this email message has been swept for the presence of computer viruses. Thank You, Viahealth ** ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:202218 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
