Need help with SQL query in MS Access

[Descartes]

<< Well for a class, using Access to learn SQL queries isn't a bad idea. Access does its job well for what it was designed for. It's just portrayed as such a crappy database because so many companies use it for something it was never designed for. >>



I agree. I'm just bitter from having assimilated many projects that were implemented extremely poorly with Access.

 

[joohang]

<< I agree. I'm just bitter from having assimilated many projects that were implemented extremely poorly with Access. >>


Heh.

I've been building an app called "Visual Spammer" for a local user group. It's written in C# and takes a list of email addresses from any Access database to fire off an email to each member. Since it's designed for a single user app, it does its job well. I just gotta debug my crappy code since it's crashing so much. hehe

Funny how I know the guy who designed this Access database 3 or 4 years ago. He's a very good developer and now manages some big .NET projects. He got very creative with the database table names - using spaces and hyphens, etc. My app works fine with my own databases but crashes like crazy with his. Oh well.

 

[Descartes]

<< Descartes -- I changed the two to reflect what I want, which is to move down one year.
Would that be -1? I did that, and one of the tables it went from 2002 to 2000.
The other table went from 2002 to 1977??? >>



Are you sure? I've never witnessed the behavior you describe. Run a query listing the date before, and after like so:

select yourdatefield, dateadd("yyyy", -1, yourdatefield) as yourdatefield_after from yourtable

I get the correct behavior.

 

[LuckyTaxi]

it works with '-1' when i run a query to update one table. querying more than one table will make the weird changes i mentioned earlier.

 

[Descartes]

<< He got very creative with the database table names - using spaces and hyphens, etc. >>



Hehe

select [every time], [i have to], [do this], [to my], [SQL statements] from [i get angry]

The last Access database that was thrown on me for "conversion", I found myself immersed in a database that consisted of one table, hundreds of thousands of records, and hundreds of columns. I think the original "developer" took the idea of denormalization for performance to the extreme?

I actually use Access quite a bit, but only as a "lightweight" database I can distribute with my smaller applications that have frontends in some other language. I'm also endlessly frustrated by query builders (and the fact that Access destroys my hand-typed sql statements). I have seen some clean uses of Access, but extremely poor ones are more common.

 

[joohang]

hehe. Yeah. That square bracket is a pain.

Now I deal with another problem. I found a field name with a space! It crashes my app because I am using XML conversion in DataSet and something fishy is going on here. Grrr..

I hope I don't have to deal with regular expressions.. Oh god..

 

[joohang]

<< The last Access database that was thrown on me for "conversion", I found myself immersed in a database that consisted of one table, hundreds of thousands of records, and hundreds of columns. I think the original "developer" took the idea of denormalization for performance to the extreme? >>


LOL



<< I actually use Access quite a bit, but only as a "lightweight" database I can distribute with my smaller applications that have frontends in some other language. I'm also endlessly frustrated by query builders (and the fact that Access destroys my hand-typed sql statements). I have seen some clean uses of Access, but extremely poor ones are more common. >>


hahaha. Sounds like FrontPage and HTML, doesn't it? Maybe it's a common feature among all Microsoft Office apps.