Recent content by bholm

This makes a lot of sense to me. Thanks for the lengthy and much appreciated explanation! I forgot to mention last time that I think you are right about the GROUP BY claus as well so I removed them. I'm using this query in Cold Fusion within a <CFOUTPUT GROUP=&quot;&quot;> type of tag where...

With the addition of &quot;Events.&quot; to a couple column names in the Join portion of the query....IT WORKS!!! THANKS!!! I should have thought of the UNION operator but didn't. I am curious why UNION ALL is done instead of just UNION since UNION ALL includes duplicates?

This doesn't give an error but it doesn't give the results set I need. It disqualifies locations that don't have any events and disqualifies those that have events that only have events older than today. I need all locations (with or without events) but only join with events that are future...

The error message isn't complaining about single or double quotes around British Columbia, it's complaining about the IN subquery...it states there's an error in 'Locations.ID IN ( SELECT EventLocID FROM Events WHERE EventDate > #11/11/02# ) WHERE Locations.StateProv = &quot;British...

I need to know how to do a SQL query in Access that does an outer join with another table...but here's the problem: The joined table needs to be qualified by date...i.e. I want the results set to show ALL records qualified in the main select statement (all locations by City found in 'British...

I removed the GROUP BY but this did not change the results. I've had this type of syntax difficulty before working with date syntax, I'm actually not sure what flavor of SQL to use. I try Oracle SQL and it almost seems to work but the error clearly says it's ODBC Microsoft Access Driver. But...

I am using the following query in a Cold Fusion page. The server says this is not a supported statement: &quot;Error Diagnostic Information ODBC Error Code = S1000 (General error) [Microsoft][ODBC Microsoft Access Driver] Join expression not supported.&quot; Here is the query: SELECT...

I want to filter or reduce my query results further based on only those records that have a count > some number. How can I use a subquery to do this? I have a ID column in table A and a table B that has Cat column and ID column that references table A ID column value. There are many records in...

How do you format a date in a query against a Access db? I need to know the function to use. In Oracle you have the TO_DATE(date,mask) function. What is the equivalent in Access land? Here is my query attempt that failed: SELECT * FROM classes WHERE TO_DATE(ClassDate,'mm/dd/yy') = TO_DATE(...

Thanks so much, Karluk. I tried it and it eventually came up with the right results but it took forever. Someone on another forum gave me this script which works pretty fast and also gets the right results. It's very similar. But THANKS anyway!!! delete from my_table where subid || userid in...

What tecnique do you use to remove duplicates? Let's say I have data set made up of the following columns: id subid userid 1 27 3872 2 32 3872 3 51 3872 4 16 4218 5 19 5211 The subid is an id for the magazine subscription for that user. But now...

Thanks so much, Terry! That helps. What makes it particularly tricky for me working with Cold Fusion is that CF specifies variables with # symbols surrounding them. So to have a date variable in an MS SQL query that uses # symbols makes it hard at times. Bruce

Does anyone know of a good online source for a summary on how to use dates within MS SQL and Access 97. I'm trying to understand and learn the usage of dates while programming a Cold Fusion template using MS SQL queries to talk to a Access 97 db. MS does things the usual confusing way... Some...

When designing a db table it's a good idea to always have a column that contains unique data for this purpose. I would suggest you create a column for an ID value and sequencially increment it with each login record. Then you can update any login with the logout info by using &quot;WHERE...

I just now figured it out... you have to put # symbols around the date....so its WHERE date1 > #04/01/2001# Then in Cold Fusion I have to escape those with additional # symbols so it becomes WHERE date1 > ##04/01/2001## I'd still like an online reference to the Date type functions that MS...