SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL/ACCESS query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Dante1987
Starting Member

Netherlands
6 Posts

Posted - 03/12/2013 :  11:42:03  Show Profile  Reply with Quote
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

263 Posts

Posted - 03/12/2013 :  12:45:38  Show Profile  Reply with Quote
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

Edited by - Grifter on 03/12/2013 12:48:31
Go to Top of Page

Dante1987
Starting Member

Netherlands
6 Posts

Posted - 03/13/2013 :  05:04:59  Show Profile  Reply with Quote
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

263 Posts

Posted - 03/13/2013 :  05:24:40  Show Profile  Reply with Quote
I think that should be what you are looking for

G
Go to Top of Page

Dante1987
Starting Member

Netherlands
6 Posts

Posted - 03/13/2013 :  06:08:43  Show Profile  Reply with Quote
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

263 Posts

Posted - 03/13/2013 :  09:10:17  Show Profile  Reply with Quote
Where is table1 and where is table2?

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

Dante1987
Starting Member

Netherlands
6 Posts

Posted - 03/13/2013 :  10:10:53  Show Profile  Reply with Quote
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

263 Posts

Posted - 03/14/2013 :  05:05:49  Show Profile  Reply with Quote
Did it work ok?
Go to Top of Page

Dante1987
Starting Member

Netherlands
6 Posts

Posted - 03/15/2013 :  03:22:58  Show Profile  Reply with Quote
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

Netherlands
6 Posts

Posted - 04/03/2013 :  03:26:56  Show Profile  Reply with Quote
nobody?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000