Hi Will, I built the query using MS SQL, but as far as i can see the code is pretty standard.
One comment i would make is re. the naming of your id fields. When I create relational databases I always use the same name for the ID on which you will link the tables. Scenario - customers, invoices Customer table - CustomerID, Address, customer details ... Invoice Table - InvoiceID, CustomerID, invoice details ... This would make it much easier for you to see where links are happening. Also, make sure you set the same data types and paramaters on the link field. On MS SQL I use Int data type (whenever possible), been a while since I used access. Last of all, you should avoid running complex queries in CF. On your own example, build and run the query in access and only use CF for the where clauses. Once you have the query built in Access/MS SQL when you access it from CF you are effectively handling one table of data. So doing searches on it is very easy, and it will run much faster and leaner this way. Here's a link I found to Access and relational databases http://www.utexas.edu/its/windows/database/access/start.html#rel Good luck! Jenny ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:224768 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
