This is probably the dumbest question I've posted yet, but my mind is numb and I'm not getting anywhere, no matter how much I google terms.
I have three tables in a MS Access DB: Owners, Vehicles, Other. Owners is owner info, Vehicles is car info, Other is misc items info. Vehicles and Other have an OwnerID field that, funny enough, corresponds to the Owners ID field. What I'm trying to do is pull all the Vehicles and all the Other items that belong to a single owner and were added before a certain date. I'm obviously going about it backwards, because after two days of staring at this I now have a syntactically working query that will return 739 records when it should return 6. I think that's a new personal best. I guess the first question is whether I should run two seperate queries (one for Vehicles, one for Other) and merge them prettily with CF code, or pull everything at once with one big query. Neither idea, so far, has worked. The hoped for outcome is a report in the following format: Owner Name Vehicle info here, sorted by date added Vehicle info here, sorted by date added Other info here, sorted by date added Other info here, sorted by date added ----------------------------------------------------------- 4 records Owner Name Vehicle info here, sorted by date added etc etc ------------------------------------------------------------ # records, etc I wasn't successful looping through seperate queries, so here's the current Select statement. SELECT owners.ownerid, owners.fname, owners.lname, owners.bname, vehicles.id as vid, vehicles.ownerid as vownerid, vehicles.year as vyear, vehicles.mfr as vmfr, vehicles.model as vmodel, vehicles.price as vprice, vehicles.date_add as vdate_add, other.id as iid, other.ownerid as iownerid, other.year as iyear, other.mfr as imfr, other.model as imodel, other.item_name as iname, other.price as iprice, other.date_add as idate_add FROM owners, vehicles, other WHERE (vehicles.ownerID = owners.ownerID AND vehicles.date_add < #createODBCDate(variables.begin_date)#) OR (other.ownerID = owners.ownerID AND other.date_add < #createODBCDate(variables.begin_date)#) ORDER BY vehicles.ownerID, vehicles.Date_add, other.ownerID, other.date_add The resulting report is an interesting mix of duplicate cars, duplicate items, and duplicate names. Three owners are repeatedly intermixed throughout the report, along with a lot of their stuff. (out of 20+ owners, there should only be three picked, so it's doing that part right.) Anybody got a clue where I should restart? I know this shouldn't be this difficult. Thanks. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;203748912;27390454;j Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:308291 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
