Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL/ACCESS query

Author  Topic 

Dante1987
Starting Member

6 Posts

Posted - 2013-03-12 : 11:42:03
Hi All,

I'm pretty new to SQL query writing...but i have spent 6 hours today to learn SQL commands from w3schools and im using razorsql to create queries. Too bad i don't have the query for the following...

info
TABLE1 has column "Platform", "Year", "User"
TABLE2 has column "OS", "REPORT", "Last Used"

Column "Platform" (almost) has the same data as "OS"
Column "Year" and "User" has also data. This data needs to be copied to TABLE2 the only problem is manual copy paste takes alot of time.

so now what needs to be done......
Column "REPORT" and "Last Used" needs to be filled in with the data from column "Year" and "User" but only if the data in "Platform" and "OS" match. Something like a build in check :-)

after a few try outs... i managed to copy the data from "Year" to "REPORT" but the rest of the columns were empty :-( i do have a backup so no worries.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-03-12 : 12:45:38
So you are saying if Table1.Platform and table2.OS data are equal then take "year" and "user" data from related row in table 1 and populate "report" and "Last Used" columns respectively in table 2

And from what you say above then the row in Table2 only contain OS value but no data for "report" and "Last Used" columns as this is where your "Year" and "User" data will be copied to?

Quite sure you could use a SELECT INTO and say where Table1.Platform = table2.OS

http://www.w3schools.com/sql/sql_select_into.asp

G
Go to Top of Page

Dante1987
Starting Member

6 Posts

Posted - 2013-03-13 : 05:04:59
So my code should be:

SELECT TABLE1.Year, TABLE1.User
INTO TABLE2.REPORT, TABLE2.Last Used
FROM TABLE1
WHERE TABLE1.Platform = TABLE2.OS;

Go to Top of Page

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-03-13 : 05:24:40
I think that should be what you are looking for

G
Go to Top of Page

Dante1987
Starting Member

6 Posts

Posted - 2013-03-13 : 06:08:43
When i run the query i get the message that it can not find the TABLE2.mdb. The TABLE2.mdb is a linked table. but when i excute the following query:

INSERT INTO TABLE2( Last Used )
SELECT TABLE1.Year
FROM TABLE1
WHERE TABLE1.Platform = TABLE2.OS;

It will prompt me for a parameter value, im not sure what to do if i insert the data that is in one of the fields from column Platform/OS Microsoft Access 2003 asks me if i would like to append 1 row(s), Yes or No...both have the same result there is no data in the Column "Last Used"

also i still don't understand why some examples are different, i mean to select columns you can use (brackets) or <tablename>.<column> can anyone explain to me when to use brackets () or just a dot . thanks in advance!
Go to Top of Page

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-03-13 : 09:10:17
Where is table1 and where is table2?

Are both in an Access DB?
Go to Top of Page

Dante1987
Starting Member

6 Posts

Posted - 2013-03-13 : 10:10:53
Correct but TABLE1 is linked to another .mdb that i did not know, just figured it out.
Go to Top of Page

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2013-03-14 : 05:05:49
Did it work ok?
Go to Top of Page

Dante1987
Starting Member

6 Posts

Posted - 2013-03-15 : 03:22:58
sorry had a day off, sadly no.....but how do i insert into a external .mdb is it the IN-syntax?
Go to Top of Page

Dante1987
Starting Member

6 Posts

Posted - 2013-04-03 : 03:26:56
nobody?
Go to Top of Page
   

- Advertisement -