Need help with SQL query in MS Access

[LuckyTaxi]

I need to update a date field in three separate tables. The dates vary within each tables,
and there are like 40 rows I need to change the year from 2002 to 2001. How can I go about doing
this with a query? The date format is 'm/d/yyyy.'

I know how to do it is the dates were all the same, but they're all different.

 

[StageLeft]

Sounds like maybe that database was made badly...?

 

[gopunk]

i really don't know that much about SQL, but can't you use a wildcard character?

 

[LuckyTaxi]

<< i really don't know that much about SQL, but can't you use a wildcard character? >>



tried, but because the month and dates vary, I can't get it to change without changing the month
and dates themselves. also, the format really screws me up. If it were a string field, it would be easier
and i could use the wildcard

 

[gopunk]

oh i see, so i take it the month and year are in the same field?

 

[StageLeft]

Why can't you update each table at a time?

 

[gopunk]

<< Why can't you update each table at a time? >>



yea that's true, 40 isn't that bad. you'll probably spend more time figuring out how to write the statement than if you just did it.

 

[LuckyTaxi]

actually this is for a school assigment, and i need to write a SQL query to do this.

 

[StageLeft]

<< actually this is for a school assigment, and i need to write a SQL query to do this. >>

Well that's a sick question. You're really not likely to come up against something like that in the professional world because without knowing anymore it seems to me that the guy who wrote the DB didn't know what he was doing if this a problem you'd really have

 

[IJump]

What is the item that makes those 40 records different from everything else? Are they the only fields with a 2001 year in them? They either have to be the only records in the tables or there has to be something unique that they share in order to run a SQL query to update them...

 

[IJump]

<<

<< actually this is for a school assigment, and i need to write a SQL query to do this. >>

Well that's a sick question. You're really not likely to come up against something like that in the professional world because without knowing anymore it seems to me that the guy who wrote the DB didn't know what he was doing if this a problem you'd really have >>




Maybe the teacher of the class screwed up some database he/she wrote for a customer on the side and now his/her class has to fix it for him/her......

 

[LuckyTaxi]

<< What is the item that makes those 40 records different from everything else? Are they the only fields with a 2001 year in them? They either have to be the only records in the tables or there has to be something unique that they share in order to run a SQL query to update them... >>



Think of it as an employee database with their hire dates. They were all hired in 2002, but at different dates throughout the year.
Now, change 2002 to 2001, but leave the month and date intact. I'll be happy if I can figure out how to update just one table

 

[bunker]

<<

<< actually this is for a school assigment, and i need to write a SQL query to do this. >>

Well that's a sick question. You're really not likely to come up against something like that in the professional world because without knowing anymore it seems to me that the guy who wrote the DB didn't know what he was doing if this a problem you'd really have >>


There's absolutely nothing wrong with the database.

Real-world situation. You have a database of patients with the date they were treated. The records clerk screws up and enters 2001 instead of 2002 for the date.

The boss needs a report based on those records in 20 minutes, not enough time to manually fix the records, so you come up with a SQL statement to quickly remedy them.

lilcam, I know this doesn't help you , but I wanted to show Skoorb that this is an entirely feasible situation. All of my SQL books are at work so I haven't got a clue. You may want to check out www.tek-tips.com. They've helped me with a lot of SQL/Access questions.

 

[StageLeft]

True - I misunderstood initially, but you're not going to keep the date hired for employees in three separate tables.

 

[Descartes]

Note: I don't condone the use of MS Access in any scenario, but I'll give you the answer anyway

update yourtable set yourdatefield = dateadd("yyyy", 2, yourdatefield)

 

[Descartes]

In the above, you'll obviously need to change the 2 to however many years you want to add.

 

[LuckyTaxi]

<< True - I misunderstood initially, but you're not going to keep the date hired for employees in three separate tables. >>



Yes I know that sounds dumb, but I was giving the employee scenario as an example. The database is some Sales Tracking thing with the three tables being 1) No of Visit 2) No of testdrives 3) number of purchases

they all link together somehow, and they have a date field in them.

Descartes -- thx a lot man, it worked like a charm! BTW, i dont condone Access either, but our database class uses access. so stupid!!!
bring on oracle or sql 2000

 

[LuckyTaxi]

<< In the above, you'll obviously need to change the 2 to however many years you want to add. >>



how would i update more than 2 tables in the same query?

 

[joohang]

<< Descartes -- thx a lot man, it worked like a charm! BTW, i dont condone Access either, but our database class uses access. so stupid!!!
bring on oracle or sql 2000 >>


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.

 

[bunker]

Should be able to update as many tables as you need. Separate the names of the tables with a comma.

 

[bunker]

<<

<< Descartes -- thx a lot man, it worked like a charm! BTW, i dont condone Access either, but our database class uses access. so stupid!!!
bring on oracle or sql 2000 >>


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. >>


Hell, we developed an app for another country using Access, just for the front end though, back end was MS SQL 2k.

 

[tikwanleap]

yeah Descartes nailed it with the DateAdd function. Here's a link to the MSDN info about this function.

For updating tables, you can only update one table in one query..... BTW do you have dates in all three table? I would think the dates you need to change are in one table and the 3 tables are just linked together through their identity fields.

edit: fixed link

 

[LuckyTaxi]

Figured out how to update more than 2 tables, but it's not updating correctly.

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???

 

[LuckyTaxi]

<< yeah Descartes nailed it with the DateAdd function. Here's a http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_3vtw.asp to the MSDN info about this function.

For updating tables, you can only update one table in one query..... BTW do you have dates in all three table? I would think the dates you need to change are in one table and the 3 tables are just linked together through their identity fields. >>



No, they are different dates. One table has the date for the Visit Number. The next is the date for when the person buys the car. It could be the following day after the initial visit. Dont ask, I didnt create the database

 

[joohang]

<< yeah Descartes nailed it with the DateAdd function. Here's a link to the MSDN info about this function.

For updating tables, i think you can only update one table in one query..... >>


Nice! I wasn't aware that there was a T-SQL reference in MSDN.

BTW, your linkie is messed up. Here's the Fixed Linkie.