Copy And Paste From Access Into Columns

Mar 16, 2007

Is there a way to copy and paste data into columns when view the table columns?

I would like to be able to copy and paste data from an access database.

TIA

View 3 Replies


ADVERTISEMENT

Can Not Copy And Paste

Aug 23, 2006

Hi,

For some reason I can not cut and past objects in BIDS?

I receive the following error:

TITLE: Microsoft Visual Studio
------------------------------

An error occurred while objects were being copied. SSIS Designer could not serialize the SSIS runtime objects.

------------------------------
ADDITIONAL INFORMATION:

Could not copy object 'XSQL CICDB Synonym' to the clipboard.
(Microsoft.DataTransformationServices.Design)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=SerializeComponentsFailed&LinkId=20476

------------------------------

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)

------------------------------
BUTTONS:

OK
------------------------------


Any ideas why?

Thanks!

View 3 Replies View Related

HOW TO COPY DATABASE & PASTE IN NEW M/c

Mar 6, 2006

Hi,
HOW TO COPY DATABASE & PASTE IN NEW M/c

I have a database on remote SQL SERVER M/c
Now i want to copy the database from remote m/c to my local machine. I have SQL 2000 Desktop edition.
I backup the datatbase from server and when i restore a database in local m/c, error msg comes ::

TITILE : Microsoft SQL-DMO(ODBC SQL State:HY000)

Location : upgraddb.cpp:214
Expression : tableIndex <Array_len(upgradeMap)
SPID: 7
ProcessID:2400

Error 22021:[SQL-DMO]You must be logged in as 'sa', or a member of sysadmin, or a member of dbcreator to

perform this operation.

weather i should install SQL desktop again with any special option or is there any way to copy a database from one m/c

to other .

Knidly help me ..

View 3 Replies View Related

Preavent Copy Paste

Nov 2, 2006

hi to all

i want to stop paste in my webpage's textbox
<script type="text/javascript" >

var sSave = "";
function fnBeforeCut() {
event.returnValue = false;
}

function fnCut(id) {
event.returnValue = false;
sSave = id.innerText;
id.innerText = "";
}

function fnBeforePaste() {
event.returnValue = false;
}

function fnPaste(id) {
event.returnValue = false;
id.innerText = sSave;
}

</script>
use this script in html textbox

when asp:textbox
txtcpassword.Attributes.Add("onSelectStart", "return false;")
txtcpassword.Attributes.Add("onPaste", "return false;")use this code
it working fine in intenetexplorer(microsoft)
but not work in firefox
please give me a solution it's urgent

View 4 Replies View Related

Copy/paste Bugs

Jul 11, 2007

try copying a connection manager from one package to another



it does not work



e.g. excel connection manager



it loses the name of the connection manager and also the file you are pointing to

so it is basically no different to 'add new connection manager'





also



try copying the name of the connection manager to another connection



select connection, hit F2 (edit) ctrl C,



try pasting!



in order to paste you have to right click on the selected text and select copy from the context menu



this is not a bug

this sloppy, poorly tested software

View 1 Replies View Related

Copy And Paste Minor Irritation

Sep 24, 2007

Can anyone explain why when I copy a data element and attempt to paste it it always appears waaay off screen in my SSIS 2005 package? Why won't it paste where I am right-clicking and telling it to go? I hate having to hunt for the element in the flow.

View 1 Replies View Related

Moving Db Via Detach Then Copy And Paste 44 Gig Db File?

Aug 21, 2000

Hi,

I have a 44 gig db that I moved to another server via the NT copy and paste method. The db has gotten a little larger, and now NT gets almost to the end, but can not complete -- either starts over completely and just does the last few minutes over and over.

Is there an NT copy and paste limit? Any ideas what the problem is? Is the db mixed up?

Thanks for any help.

Judith

View 4 Replies View Related

Copy/paste Grid From QA - No Field Names!

Nov 23, 2004

for debug purposes i tried to copy paste some Query Analyser output into Excel... the data pastes fine, but i can't get the field-names to copy/paste. is there a trick? izy

View 3 Replies View Related

Copy Data From Excel Spreadsheet And Paste To Table

Aug 2, 2007

I have an excel spreadsheet and I want to transport its data into a table. I have tried copying and pasting but it doesn't work. Any ideas?

View 4 Replies View Related

Copy And Paste Control Flow Item Error

Mar 26, 2008

Hi,

I'm trying to copy and paste an 'Execute SQL Tasks' within one of my packages.

I've managed to solve the copy part of the problem by registering the following dlls:



regsvr32.exe msxml3.dll
regsvr32.exe msxml6.dll
However, I can't paste the copied task onto my control flow. When I paste I get the following error:

The designer could not paste one or more executables.
Additional information:
At least one executable could not be pasted correctly.
the executable with the name 'Record Row Count' could not be pasted successfully.
Information about the state of the executable with the name
'Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask,
Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' could not be loaded from the clipboard.
Exception from HRESULT: 0xC0010028 (Microsoft.DataTransformationServices.Design)

Any help getting this fixed would be appreciated.

Tom

View 5 Replies View Related

SQL Tools :: Copy / Paste Fails In Management Studio

Mar 29, 2007

After opening Management Studio and doing some editing it is possible to copy/paste things in the query editor. After a while (working in another app) I come back to Management Studio and I can't copy/paste anything anymore. If I try to copy from the results window I get an error message that the clipboard operation did not succeed. If I try to copy from the query editor window it just doesn't copy. I can copy/paste in other apps without a problem.
 
It is not the clipboard getting full but I can't rule out much else.

View 10 Replies View Related

Reporting Services :: Report Viewer Data Copy / Paste

Sep 15, 2015

I am using Report Viewer 2013 to display Reports, using rdlc to bind the data in visual studio.

I cannot copy the report data from report viewer? is there any work around for copy ?

Does Copy functionality will be included in feature releases or copy functionality will not be there in report viewer?

View 6 Replies View Related

Copy(paste) 'serialize' Error Always Occurs With Task, Never With Components

Aug 15, 2007

Some more insight into the error I am getting.

It always occurs at the task (control flow) level and never at the component (data flow) level

It always occurs on the 'copy' action.

I have tried all the msxml/registry recommendations to no avail.



once again here are the error details

===================================

An error occurred while objects were being copied. SSIS Designer could not serialize the SSIS runtime objects. (Microsoft Visual Studio)

===================================

Could not copy object 'SQT Drop Create RAWMigDeal RAWBarrier table' to the clipboard.
(Microsoft.DataTransformationServices.Design)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=SerializeComponentsFailed&LinkId=20476

------------------------------
Program Location:

at Microsoft.DataTransformationServices.Design.DtsClipboardCommandHelper.SerializeRuntimeObjects(ICollection logicalObjects)
at Microsoft.DataTransformationServices.Design.ControlFlowClipboardCommandHelper.InternalMenuCopy(MenuCommand sender, CommandHandlingArgs args)

===================================

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)

------------------------------
Program Location:

at Microsoft.SqlServer.Dts.Runtime.PersistImpl.SaveToXML(XmlDocument& doc, XmlNode node, IDTSEvents events)
at Microsoft.SqlServer.Dts.Runtime.DtsContainer.SaveToXML(XmlDocument& doc, XmlNode node, IDTSEvents events)
at Microsoft.DataTransformationServices.Design.DtsClipboardCommandHelper.SerializeRuntimeObjects(ICollection logicalObjects)

View 7 Replies View Related

Reporting Services Image Problem Export Excel, Copy Paste ?

Mar 4, 2008



I have a server report which made by ssrs 2005, I am using vs 2005 to build an aspx page and call this report which already uses dynamic query. I use parameters and send these to reportviewer with setparameter method. I dont use DataBind and Refresh method. SetParameter is enough to generate Report. When report generates everything is fine. But when i export this report to excel the pictures in this report get smaller to fit in cell height. I used rendering extensions but its same.


try

{

ReportViewer1.ServerReport.SetParameters(paramList);

//ReportViewer1.ServerReport.Refresh();

//ReportViewer1.DataBind();



}

catch(Exception exc)

{

Response.Write(exc.ToString());

}



And i used copy contents of reportviewer and paste it to excel file. There is interesting case here. If this is the first time that i run the report and copy and paste. No image is seen in excel workseet but other report elements is fine. But if i click on refresh button on reportviewer toolbar and report regenerates, i try to copy and paste and it works fine, and if i dont close ie window copy and paste progress works good and i dont need to click on refresh button on each report to copy and paste?

This is so interesting subject, i tried lots of things for example i tried to use databind and ServerReport.Refresh methods maybe it acts like clicking on refresh button but this wasnt the same only difference is it took more time to generate the report.

Any help?

View 1 Replies View Related

Copy &&amp; Paste Of Custom Task Loses Internal Property Settings

Sep 21, 2006

We have a built a custom control flow task that we use frequently. It has a simple user interface form where we can set certain properties. Problem is, after I configure it once and then copy and paste it all of the internal property settings seem to be lost on the newly pasted task. When I open up the editor for the task all of the properties are displayed visually in the editor form, but the properties pane is empty.

How can this be corrected?

View 3 Replies View Related

COPY/PASTE From Project To Project

Jan 12, 2007

Howdy again...

Do I need to re-create a new package from scratch after doing what I did?
It seems I've made what is probably a typical newbie mistake: I used COPY/PASTE
to
consolidate containers from two packages into a single new project/package.

Now my new package generates multiple duplicate errors (23 of each...) whenever I open it:



Warning loading LoadBillingData.dtsx: Process configuration failed to set the
destination at the package path of "PackageData Flow Task to Load
Profiles, Versions, and
Transactions.EventHandlers[OnError].Variables[System::Propagate].Properties[Namespace]".
This occurs when attempting to set the destination property or variable fails.
Check the destination property or variable.



Error loading LoadBillingData.dtsx: Changing the name of a variable failed
because it is a system variable. System variables are read-only.



What happened:



new guy (goes without saying...) creates:

01. PROJECT A
02. add PACKAGE
A to PROJECT A
03. add some
containers, figger out what the heck is going on, get containers working
04. create
PROJECT B
05. create
PACKAGE B
06. add
containers, they work fine
07. whoops!
Want to consolidate the packages. Unix guy naively expects Bill has made MS
Visual Studio like all windows products (of which newbie is aware€¦):
08. create
PROJECT C
09. create
PACKAGE C
10. <ctrl
a> PACKAGE A
11. <crtl
c> PACKAGE A
12. <ctrl
v> PACKAGE C
13. <ctrl
a> PACKAGE B
14. <crtl
c> PACKAGE B
15. <ctrl
v> PACKAGE C
16. copy Flat
File Connection Managers
17. copy
Database Connection Manager
18. drag the
arrows around, fix the flat file sources, renew the column maps, etc
19. whoops! the
connections all look like a registry number! Fix the OLE DB Destinations,
Execute SQL Task containers by selecting the DB Connection Manager copied from
PROJECT A

20. successfully
execute PROJECT C/PACKAGE C
21. save all
22. exit
23. start MS
Visual Studio
24. Open
Solution
25. ERRORS!!!



Is this fixable?





Thanks,
Bill

View 1 Replies View Related

Detach-copy-attach: Can Not Copy Files - Getting Access Denied

Feb 28, 2008



Hi!
I did:
alter database mydb set single_user with rollback immediate;
exec sp_detach_db @dbname='mydb', @keepfulltextindexfile='true';

then I tried to copy files to new location on other drives, same server but got
>>Cannot copy <myfile>: Access is denied
Make sure the disk is not full or write-protected and that the file is not currently in use<<

I also tried rename of file without success.
I also tried with db service stoppet (not preferred) without success.

How to find out, which process locks the files?
Best regards

View 7 Replies View Related

DTS Copy Columns

Dec 20, 2005

I am using a DTS to copy columns from one database to another.
How do I do the same thing, but first check if the data alreday exists in the destination table and then only copy if it doesn't exist?

thanks in Advance
ActionAnt

View 1 Replies View Related

How To Copy Columns From A Table To Another?

Jan 5, 2002

At first I have to apologize that I write here. This is not right place for my problem but I could not find any better place to ask help.
I'm creating a new table and I have to copy some columns from the old one to the new. Desciption of the old table is something like that:
Table1: name char(32) not null primary key, variable1 char(32), variable2 char(32).
Now I would like to copy columns variable1 and variable2 to the new table (called Table2). I need to change the names to the id-numbers. (Don't ask me why, I just have to :) I have created Table2. Table2: id int not null primary key, newvariable1 char(32), newvariable2 char(32).
Now the problem is that how can I copy just columns variable1 and 2 without the name-column (witch is the primary key)? I just an error message:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2,
TABLEID=578, COLNO=0" is not allowed. SQLSTATE=23502
It just says that (if I understood the message) I can't leave the id-column blank. How can I generate the keys to Table2? It would be nice to get keys from 1,2,3...
Please help me. :)

View 4 Replies View Related

How To Copy Rows To Columns

Aug 14, 2007

i have data in a table..
i want the values in the rows to place in columns and columns into rows..
for ex:-there is one table
name id dept
a 1 x
b 2 y
c 3 z
i want the resultant table should look like this
a b c
1 2 3
x y z

View 2 Replies View Related

Copy Columns From Source To Destination

Apr 10, 2007

Hi all,

I want to copy 2 columns from 1 database to another database.
I managed to do this, using a Ole DB source and a Ole DB destination dataset.

Now I want to merge 2 colums into 1:
Source Database: Column A: first name, Column B: Lastname.
Destination Database: Column 1: First and lastname customer.

Thanks for your help!

Kind regards,

Marcel Hijnen
eXDe Solutions B.V.

View 4 Replies View Related

How To Copy Columns Accross Tables

Aug 15, 2007

I have 1 table in my db that I have imported from a txt file using DTS.

In my database "DOJ" I have a table named "DOJGRAB" with ALL my data fresh from a import using DTS.

Also in "DOJ" are 6 other tables (NAME, PERSON, etc...)

How can I parse the columns out from my DOJGRAB table to fill existing columns in my other 6 tables? Basically a copy of specific columns from DOJGRAB to their proper places in the other tables.

Can somebody post an example of the syntax I would use to copy a column from one table to another? Thanks!

View 7 Replies View Related

Copy Text Columns To Nvarchar

Jun 15, 2006

Hi,I have a problem, I have a table with a text type column and anvarchar(2000) type column on my MS SQL 2000 Server.I know that the longest text in the text field is 1000 chars. I want tocopy the content the content of the text field into the nvarchar field.I tried convert and cast but after the update there are only 255 charsin the nvarchar field.Best regardsMarc

View 2 Replies View Related

Copy Table Leaving Nullable Columns

Nov 14, 2000

I would like to dynamically copy a table in Transact SQL, like this:

SELECT * into NEW_TABLE from MY_TABLE where 1 = 2

This creates an empty NEW_TABLE as desired. However, NEW_TABLE retains the column nullability of MY_TABLE; I would like NEW_TABLE to have all its columns nullable.

I tried to write Transact SQL logic to update the isnullable column in syscolumns for NEW_TABLE, but was told that the entire SQL Server would have to be reconfigured to allow this.

Does anyone know of another way to do this?

View 1 Replies View Related

DB Design :: Copy Specific Columns For Source To Destination

Nov 4, 2015

I am trying to Import data into SQL server 2008 using management studio.  The source data is an Access dbase.  I am trying to do this with queries as the tables do not match but I do need to copy specific columns for the source to the destination. Any brief example selecting a column from the source table, and just entering a dummy value for other columns(other column of data for destination table does not exist in source table). 

For the example

Source access dbase, just two columns but no primary key, T2dbase, Employee

New table.

First Name, Description
 Tom          , manager

Destination dbase SQLServer (T1dbase,Employee table), note 4 columns
Primary key NameID, FirstName nvarchar (20), LastName nvarchar (20), Description nvarchar(20)

View 5 Replies View Related

Need To Copy Data From Access To SQL

Dec 31, 2007

I am creating an ASP.Net project at work and have created a database in Microsoft SQL Server 2005.  I need to copy data from an old database (which is in Access) that the company has used for years.  The problem is that these databases are not very similar (the Access database is not normalized at all).  My question is simply this -- is there an easy way to do this.  I would like to avoid writing a lot of code if possible as this will only run one time.  Any help would be appreciated.  Thanks.

View 1 Replies View Related

Can't Paste Data

May 3, 2006

I am trying to migrate an Access database to SQL Express. I created the new database in SQL Express, and added the tables and columns. At this point, I have done so without defining any keys, indexes, or relationships. For most of the tables I was able to copy the data from Access and paste same into the SQL Express tables.
However, I am unable to create the last table, which is a link table to manage the many-to-many relationships.
The table has the following columns:
MediumCode (bigint, null)
ArtistCode (bigint, null)
SongCode (bigint, null)
TrackNumber (int, null)
Here is a sample of the data I am trying to paste:
1,180,204,1
2,2,45,1
3,3,80,1
4,4,30,1
5,5,22,1
6,6,108,1
When I perform the paste there is no error message. The data just doesn€™t get added to the table. I don€™t understand why the other tables worked and this one does not. I am trying to paste 85 rows. Any ideas?
Also I tried to do this as an INSERT query, but SQL Express does not like the following syntax:
INSERT INTO [Music].[dbo].[tblMediumDetails]
([MediumCode]
,[ArtistCode]
,[SongCode]
,[TrackNumber])
VALUES
(1, 180, 204, 1),
(2, 2, 45, 1),
(3, 3, 80, 1),
(4, 4, 30, 1),
(5, 5, 22, 1),
(6, 6, 108, 1)
Do I have to create an INSERT statement for each row? Is there a bulk load function available in SQL Express, or is that not available with the free version?
Thanks,
Robert

View 3 Replies View Related

Copy Data From Access To SQL Express In Code?

Jun 1, 2006

I have tried the upsize wizard from access 2000 and access 2003 and get records transfer in 90% of the tables I have data stored an 89 meg MS access database (2003) on an XP machine.  I can use, compact and copy etc in MS Access without problem. 

With the upsize, I get No errors, just does not reliably move ALL data.  I have run it multiple times and occasionally get some data in these tables, but never all data in a few tables and inconsistent.

========================================

So I have written code to copy the data from the access database and move it field by field into an Upsized database that was structure only, no data.

I have ONLY one Identity field (autonumber in MS access) that is the Key Relationship number to all other tables. 

1 questions and one "bug" report need solutions to both.

Question:  How can I copy the value from the MS access autonumber field to the identity field.  (Some of the autonumber rows have been deleted so not always sequentiall)  (I can do this moving data in code between to MS access databases that have autonumber fields)

=========================

I tied an elaborate write around to addNEW and then read the data in  the newly created row.  Reading the Identity field, I look up that number in the MS access database.  Then I copy the ACCESS data to the Newly created table row in SQL Exress and update. 

It works fine for about 30 record updates, then the Identify field stops giving me the correct sequence for the Identity field value.  Makes it impossible to make sure all of my Access rows get copied.    Tried transactions but not supported with the ADODB configuration, but doubt that would help.

Example data returned in the SQL Express recordset, when I read the Identify field back I get

1,2,3 etc  31, 33, 32 (out of sequence) then it (my code) can't figure out the out of sequence and end up with the next identity value at 41.

The only info I found was that it could be a cursorlocation problem, what do you recommend?

?Is there a way in code to change the identity field to string, then copy the data and change back to identity field again?

View 4 Replies View Related

How Can I Copy The Content Of MS Access Table In Database SQL Using Visual Studio Basic

Sep 26, 2007

Code Snippet

Hi there,

I'm struggeling for more than a week now with this problem, without a finding the solution.

I have two databases, MS Access and SQL Server 2005 Express Edition

Using a procedure in Visual Studio i would like to copy all the records from one table in MS Access into an existing table in SQL Server (the tables have the same name and the same layout)

I tried to prepare one Dataset to copy from Access into SQL Server but when i run the command 'DaSQL.Update(DsSQL, "Tabella") nothing happens (not even an exeption has been raised), looking during debug, the DataSet seems filled though...

Please could anyone explain what's wrong and / or is there a more quicker way to copy data from a table to another?


Note i woul have as a final goal to get data from an AS400 database by ODBC, manage it, and put it on SQL Server for a 'data mining' scope (eliminating the use of MS Access, not suited for FE-BE).

the procedure goes like this;


' Create a connection to the MS Access Database
Dim connectionToAccess As New OleDbConnection(DBConnectionAccString)
strsql = "SELECT * FROM [TABELLA]"
connectionToAccess.Open()
Dim DaAccess As New OleDbDataAdapter(strsql, connectionToAccess)

Dim DsAccess As New DataSet("ACCESS")
DaAccess.FillSchema(DsAccess, SchemaType.Source, "Tabella")
DaAccess.Fill(DsAccess, "Tabella")

' Create a connection to the SQL Database
Dim connectionToSQL As New SqlConnection(DBConnectionSQLString)
connectionToSQL.Open()
Dim DaSQL As New SqlDataAdapter(strsql, connectionToSQL)

Dim DsSQL As New DataSet("SQL")
DaSQL.FillSchema(DsSQL, SchemaType.Source, "Tabella")
DaSQL.Fill(DsAccess, "Tabella")

DaSQL.Update(DsSQL, "Tabella")

Note I tried also the following, withou a result;


DsSQL = DsAccess.Copy
DaSQL.Update(DsSQL, "Tabella")

Please is there someone who could respond !!???

View 6 Replies View Related

WARNING. A Simple Cut And Paste Of 8 Records Can Distroy A SQL Server Table

Jul 20, 2005

Today I need to copy 8 records in a table. I have to use Access 200 becauseof the limitation of Enterprise Manager's inability to cope with field withmore than 900 characters. Selected records, cut, paste. I got an erroormessage about not being able to have a null Key_ID (I copied the reords andtried to paste the Key_ID as part of the records - normally I hide theKey_ID).Now I can't access either the new records or the originals that I was tryingto copy (because, it would seem, they have identical primary keys). I alsocannot export the table via DTS 'unspecified error' and 'integrityviolation'.Or delete the offending records with a Query Anaylyser delete query.Basically the entire SQL Server database has been destroyed with a couple ofkeystrokes.Now, I've being developing database applications for over 20years and theone thing, maybe the only thing I expect from a database server is toprotect the integrity of my data. SQL Server does not, it would seem. Theserecords aren't just any random unimportant records either. They contain the'create views' that my entire application require to function and each oneapproaches the 8000 record limit and have take years to perfect and justchecking that the table is valid could take me days.

View 5 Replies View Related

In A Real Pinch With FreeTextBox / Paste From Word / Ntext Field In SQL2k!

Apr 10, 2005

I am in a real squeeze here.  I am working on my first ASP.NET project here and I am having some big problems with a content management aspect of this so far.  I have a freetextbox control that my users want to be able to paste out of Word and save the content to a MSSQL Database.  Well this was working great until I found out that I have a 4000 character limitation, and most of the pasted data is well over 14000 characters at least.
I've been working on these for about two weeks and just have come to a dead end.  Here's what I had before:  Form with Freetextbox that saved the information in the form to a database by calling a function in a class.vb file, the class file then called a stored procedure using parameters.  Similiarly the display page loaded with a record id, and fired off a function stored in a class file, using stored procedures and using output parameters to I could assign the returned values to label fields etc.  This has completely gone down the toilet since i had to change to text field to accomodate the larger text sizes.  I have found some resources about chunking out to the file system, but I really don't want to do that, I just want it to display the information out on a web page, and I have been scouring google for weeks!  Can someone tell me if this the wrong approach?  How do I go about storing / retrieving HTML in an ntext database field just like these forums?  I am completely stumped!

View 2 Replies View Related

I A View In Access Project I See Only All Columns

Oct 11, 2006

I try to create a view in Access Project but in design view I only see "all columns" in a table. The individual columns are not listed. This happens only with a certain SQL Server database. Any suggestions ?

View 1 Replies View Related

Convert Access With Multiple Columns?

Nov 14, 2014

I am converting some access to SQL at work and I am having a problem with a query that uses the access FIRST function

My query looks like this in access

SELECT
TBL1.ID
, FIRST(TBL1.Code) AS FirstCode
FROM
TBL1
WHERE
TBL1.Marker = 'X'
AND Left(ID,1) IN('A','B','C','D','E')
GROUP BY
ID

I have tried to convert it using a slightly different method. So instead of picking the first value of the Code field that appears in the select, (which is what access would do and SQL can’t do as far as I’m aware). I first select the ID and code with the highest value associated with them. The below script is what I did. I would’ve thought they would produce the same number of records but they don’t.

SELECT
ID
, Code FirstCode
FROM
(
SELECT
ID
, Code
, MAX(Value) MaxValue
FROM
TBL1
WHERE
TBL1.Marker = 'X'
AND LEFT(ID,1) IN('A','B','C','D','E')
GROUP BY
ID
, Code
) T1

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved