Converting Data from Access 2000 to SQL Server 2000

Hi,
I worked on a project in ASP.NET using SQL server 2000 as the back end. Its a conversion application that I rewrote in ASP.NET using C#. I need to import the old data in Access db into SQL server 2000 and I have very little knowledge about doing it. The data in not a direct one -one transformation. There are considerable changes to the Database design and data types. Any help and suggestions wud be really helpful. Also, any article links wud be great.

Thanks.
HOW TO: Convert an Access Database to SQL Server

Converting an Access database

Converting an Access database

I uninstalled sqlserver2005 express and reinstalled sqlserver2005 ctp. Now, everything is in order.

I've just searched books online for convert(conversion) and import(imports, importing).

I see no instructions on how to convert my access 2003 database into an sqlserver database.

It was easy enough in sqlserver 2000. However, I've uninstalled that. Is there a way of coverting in sqlserver 2005? I'm in big trouble if I can't.

A related question. I'm very familiar with Access 2003. So I'll continue to develop the database structure in Access. Can I import the incremental changes? Or do I have to delete the current conversion and re-import(assuming I can) the whole new database each time?

dennist685

You can use the access upsizing wizard (tools-> database utilities ->upsizing wizard) from within access to push the data. Alternatively, use SSIS from SQL 2005 to import the data from access.

Cathal|||Cathal,

Thank you very much. I had no idea one could do it from Access.

dennist685|||Search as I could, I couldn't find SSIS in sqlserver 2005

Dennist685

Converting an Access database

Converting an Access database

I uninstalled sqlserver2005 express and reinstalled sqlserver2005 ctp. Now, everything is in order.

I've just searched books online for convert(conversion) and import(imports, importing).

I see no instructions on how to convert my access 2003 database into an sqlserver database.

It was easy enough in sqlserver 2000. However, I've uninstalled that. Is there a way of coverting in sqlserver 2005? I'm in big trouble if I can't.

A related question. I'm very familiar with Access 2003. So I'll continue to develop the database structure in Access. Can I import the incremental changes? Or do I have to delete the current conversion and re-import(assuming I can) the whole new database each time?

dennist685

You can use the access upsizing wizard (tools-> database utilities ->upsizing wizard) from within access to push the data. Alternatively, use SSIS from SQL 2005 to import the data from access.

Cathal|||Cathal,

Thank you very much. I had no idea one could do it from Access.

dennist685|||Search as I could, I couldn't find SSIS in sqlserver 2005

Dennist685

converting access to sqlserver 7

I have a query which fixes area codes. it runs:
UPDATE (nocall INNER JOIN pildta ON (nocall.SUFF = pildta.suff) AND (nocall.PREF = pildta.preff) AND (nocall.AREA = pildta.area)) INNER JOIN ZipCodeUS ON pildta.Zip = ZipCodeUS.ZIP SET nocall.AREA = [a/c]
WHERE (((IIf([nocall]![area]<>[a/c],1,0))=1));

I managed to replace the IIF with a CASE for displaying the unmatched tables like this:
SELECT dbo.nocall.AREA, dbo.pildta.Zip, dbo.ZipCodeUS.[A/C], case when [a/c]=[nocall].[area] then 1 else 0 end as match
FROM dbo.pildta INNER JOIN
dbo.ZipCodeUS ON dbo.pildta.Zip = dbo.ZipCodeUS.ZIP INNER JOIN
dbo.nocall ON dbo.pildta.area = dbo.nocall.AREA AND dbo.pildta.preff = dbo.nocall.PREF AND dbo.pildta.suff = dbo.nocall.SUFF

How can I make this an update query based on [match] having a value of 1?How about moving the [a/c]=[nocall].[area] from a CASE clause to a WHERE clause (which should limit the results to records that would return a 1 in your CASE clause), and then making that query a subquery to your UPDATE command? E.g., UPDATE table SET column = value WHERE tableprimarykey IN (subquery).|||ok. What am I doing wrong. I'm now trying this:
UPDATE nocall
SET AREA = [a/c] IN (SELECT ZipCodeUS.[A/C] FROM ZipCodeUS INNER JOIN pildta ON ZipCodeUS.ZIP = pildta.Zip INNER JOIN nocall ON pildta.area = nocall.AREA AND pildta.preff = nocall.PREF AND pildta.suff = nocall.SUFF)
WHERE [ctr]=[ctr] IN (SELECT nocall.AREA, ZipCodeUS.[A/C], nocall.ctr FROM ZipCodeUS INNER JOIN pildta ON ZipCodeUS.ZIP = pildta.Zip INNER JOIN nocall ON pildta.area = nocall.AREA AND pildta.preff = nocall.PREF AND pildta.suff = nocall.SUFF AND ZipCodeUS.[A/C] <> nocall.AREA)

When i try to save, I get incorrect syntax near the keyword IN, incorrect syntax near the word WHERE|||Well, the first error in the query in your last post is "SET AREA = [a/c] IN (..." This would have to be like "SET AREA = [a/c] WHERE AREA IN (..."

I'm not sure I understand what the query is to accomplish well enough to give you complete rewrite, so tell me if this is correct:

Your goal is to replace values in NoCall.Area with values from ZipCodeUS.[A/C] when and only when:

1. NoCall.Area does not already equal ZipCodeUS.[A/C]
AND
2. ZipCodeUS has a matching record in Pildta based on .Zip
AND
3. NoCall has a record that matches Pildta's Area, Preff, and Suff

converting access to sql

is there any quick n easy way to convert an old access database to sql
yes i know it sounds easy and it isnt!
the access table was poorly designed - eg 1 table 50-60 fields
i need to transfer the data to sql - which has been redesigned with multi
tables - any easy way to do this ?
thanks
mark
Hi,
USE DTS tools in sql server to do this.
In DTS you can create multiple transformations to transfer the data in one table in MS access to multiple normalized table in SQL Server.
Otherwise move the data into SQL server single table and then write DMLS to transfer to multiple tables.
Thanks
Hari
MCDBA
-- mark wrote: --
is there any quick n easy way to convert an old access database to sql
yes i know it sounds easy and it isnt!
the access table was poorly designed - eg 1 table 50-60 fields
i need to transfer the data to sql - which has been redesigned with multi
tables - any easy way to do this ?
thanks
mark
|||"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:BCF4D728-A338-45FB-A1D7-A9F196018198@.microsoft.com...
> Hi,
> USE DTS tools in sql server to do this.
> In DTS you can create multiple transformations to transfer the data in one
table in MS access to multiple normalized table in SQL Server.
> Otherwise move the data into SQL server single table and then write DMLS
to transfer to multiple tables.
> Thanks
> Hari
> MCDBA
> -- mark wrote: --
>
what if the column names are slightly different ? - can i import into a
table that already exists ?
thanks
mark
|||Hi,
In the transformation you can map the source coulumn with the destination
column.
So if you have emp_number in MS Access and emp_no in SQL server, inside
transformation you can map each other.
Thanks
Hari
MCDBA
"mark" <mark@.remove.com> wrote in message
news:fmImc.74$TO3.69@.newsfe3-win.server.ntli.net...[vbcol=seagreen]
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:BCF4D728-A338-45FB-A1D7-A9F196018198@.microsoft.com...
one
> table in MS access to multiple normalized table in SQL Server.
> to transfer to multiple tables.
> what if the column names are slightly different ? - can i import into a
> table that already exists ?
> thanks
> mark
>
|||"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OsH$OiBNEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> In the transformation you can map the source coulumn with the destination
> column.
> So if you have emp_number in MS Access and emp_no in SQL server, inside
> transformation you can map each other.
> Thanks
> Hari
> MCDBA
>
thanks ive got it sussed now!
mark

converting access to sql

is there any quick n easy way to convert an old access database to sql
yes i know it sounds easy and it isnt!
the access table was poorly designed - eg 1 table 50-60 fields
i need to transfer the data to sql - which has been redesigned with multi
tables - any easy way to do this ?
thanks
markHi
USE DTS tools in sql server to do this
In DTS you can create multiple transformations to transfer the data in one table in MS access to multiple normalized table in SQL Server.
Otherwise move the data into SQL server single table and then write DMLS to transfer to multiple tables
Thank
Har
MCDB
-- mark wrote: --
is there any quick n easy way to convert an old access database to sq
yes i know it sounds easy and it isnt
the access table was poorly designed - eg 1 table 50-60 field
i need to transfer the data to sql - which has been redesigned with mult
tables - any easy way to do this
thank
mar|||"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:BCF4D728-A338-45FB-A1D7-A9F196018198@.microsoft.com...
> Hi,
> USE DTS tools in sql server to do this.
> In DTS you can create multiple transformations to transfer the data in one
table in MS access to multiple normalized table in SQL Server.
> Otherwise move the data into SQL server single table and then write DMLS
to transfer to multiple tables.
> Thanks
> Hari
> MCDBA
> -- mark wrote: --
>
what if the column names are slightly different ? - can i import into a
table that already exists ?
thanks
mark|||Hi,
In the transformation you can map the source coulumn with the destination
column.
So if you have emp_number in MS Access and emp_no in SQL server, inside
transformation you can map each other.
Thanks
Hari
MCDBA
"mark" <mark@.remove.com> wrote in message
news:fmImc.74$TO3.69@.newsfe3-win.server.ntli.net...
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:BCF4D728-A338-45FB-A1D7-A9F196018198@.microsoft.com...
> >
> > Hi,
> >
> > USE DTS tools in sql server to do this.
> > In DTS you can create multiple transformations to transfer the data in
one
> table in MS access to multiple normalized table in SQL Server.
> >
> > Otherwise move the data into SQL server single table and then write DMLS
> to transfer to multiple tables.
> >
> > Thanks
> > Hari
> > MCDBA
> > -- mark wrote: --
> >
> what if the column names are slightly different ? - can i import into a
> table that already exists ?
> thanks
> mark
>|||"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:OsH$OiBNEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> In the transformation you can map the source coulumn with the destination
> column.
> So if you have emp_number in MS Access and emp_no in SQL server, inside
> transformation you can map each other.
> Thanks
> Hari
> MCDBA
>
thanks ive got it sussed now!
mark

Converting Access SQL to T-Sql

Any help converting the following sql to T-Sql would be helpful. I created it in Access ant works great but cant get the case to work. Need to put it into a accounting program that uses T-Sql. The purpose it to come up with a new field called STATUS based on key words in the "decoded" column.

Thanks!

Status: IIf([TableName]![ColumnName] Like "*PA'D*","PA'D",IIf([TableName]![ ColumnName] Like "*SOLD*","SOLD",IIf([TableName]![ ColumnName] Like "*DNU*","DNU","ACTIVE")))case when TableName.ColumnName Like '%PA''D%'
then 'PA''D'
when TableName.ColumnName Like '%SOLD%'
then 'SOLD'
when TableName.ColumnName Like '%DNU%'
then 'DNU'
else 'ACTIVE' end as Status|||There is a saying that data should be stored in your tables, not in your code. Is there a specific reason you do not have a table for these status codes that you can then reference in your queries? What if one of those status codes changes or a new code needs to be added?sqlsql

Converting Access Reports to SRS 2005

Hello Everyone,
Is there a wizard of Visual Studio add-in that I can use to convert MS Access
Reports to SRS 2005?
Regards,
Pete Zerger, MCSE(Messaging) | MCTS(SQL 2005) | MVP - MOM
Founder, SystemCenterForum.org
URL:http://www.systemcenterforum.org
BLOG: http://www.it-jedi.net/
mailto:pete.zerger AT gmail.coOn Apr 21, 5:51 pm, Pete Zerger <pete.zer...@.gmail.com> wrote:
> Hello Everyone,
> Is there a wizard of Visual Studio add-in that I can use to convert MS Access
> Reports to SRS 2005?
> Regards,
> Pete Zerger, MCSE(Messaging) | MCTS(SQL 2005) | MVP - MOM
> Founder, SystemCenterForum.org
> URL:http://www.systemcenterforum.org
> BLOG:http://www.it-jedi.net/
> mailto:pete.zerger AT gmail.com
I use Visual Studio 2005 Professional, and the SSRS Report Designer
has a import utility for Access Report definitions built right into
it. If you start a Report Project, and then right-click on the
Reports folder within the Solution Explorer window, you should see an
option to "Import Reports, Microsoft Access..." appear in a context
menu.
I don't know if BIDS or VB.NET has the same options - I would guess
they do, however.

Converting Access report to Reporting Services

Hi,

I am trying to convert an Access report where I use expressions to calculate the number of males/females for example.

I have used the following expression, but it always returns an incorrect figure:

=Sum(IIf(Fields!gender.Value ="Female", 1, 0))

When I check the query however it has 46 rows = to 'Female', but when I previes the report it always comes out as 24.

What's causing this?

Thanks

Hi,

From your description, it seems the expression doesn't return the right number of those 'Female' field, right?

What I can see is to use a TRIM function to remove all the leading and trailing while-spaces characters from the value in each filed. Sometimes theses leading and trailing white-spaces characters may cause the expression "IIf(Fields!gender.Value = "Female", 1, 0)" returns 0. You may use TRIM to have a try, see the following code:

=Sum(IIf(TRIM(Fields!gender.Value) = "Female", 1, 0))

Thanks.

|||

Thanks, that seems to have worked.
I wasn't aware of the leading and trailing white-spaces.

Converting Access Query to SQL Server Query

Would anyone know how to convert this Access Query to SQL Server Query?

Format((([ResponsivenessQ1]+[ResponsivenessQ2]+[ResponsivenessQ3]+[ResponsivenessQ4])/(IIf([ResponsivenessQ1]>0,1,0)+IIf([ResponsivenessQ2]>0,1,0)+IIf([ResponsivenessQ3]>0,1,0)+IIf([ResponsivenessQ4]>0,1,0))),"Standard")

What this query is doing in Access is Averaging numbers that are not equal to 0. If the number is equal to 0, it should not be averaged in. The average is set to 2 decimal places.try this

----optional just for testing --------
declare @.resq1 int,@.resq2 int,@.resq3 int,@.resq4 int
set @.resq1 = -1
set @.resq2 = 2
set @.resq3 = 0
set @.resq4 = 5
------end optional-----------

select convert(decimal(10,2),(@.resq1+@.resq2+@.resq3+@.resq4 )/((case when @.resq1 > 0 then 1 else 0 end)+
(case when @.resq2 > 0 then 1 else 0 end)+
(case when @.resq3 > 0 then 1 else 0 end)+
(case when @.resq4 > 0 then 1 else 0 end)))

Converting Access qrys with logical expressions

I'm upsizing an Access database. Got the data converted, working on
the front end, converting queries to views, but having trouble
converting queries that use logical expressions like the following:
SELECT OrderId,
Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems
FROM OrderDetails
INNER JOIN Items
ON (OrderDetails.ClientId = Items.ClientId)
AND (OrderDetails.ItemId = Items.ItemId)
WHERE (NOT (SitesCustomerTypeId = 2
AND ExpressBackorder =True
AND OrderUrgency = 1 ))
GROUP BY OrderId;

Can someone suggest a strategy to achieve the same result, ie
OrderId,ReadBackOrderItems that I can use in further joins?

Thanks in anticipation
Terry Bell"Terry Bell" <dreadnought8@.hotmail.com> wrote in message
news:923537d6.0409142346.301c9c3@.posting.google.co m...
> I'm upsizing an Access database. Got the data converted, working on
> the front end, converting queries to views, but having trouble
> converting queries that use logical expressions like the following:
> SELECT OrderId,
> Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems
> FROM OrderDetails
> INNER JOIN Items
> ON (OrderDetails.ClientId = Items.ClientId)
> AND (OrderDetails.ItemId = Items.ItemId)
> WHERE (NOT (SitesCustomerTypeId = 2
> AND ExpressBackorder =True
> AND OrderUrgency = 1 ))
> GROUP BY OrderId;
> Can someone suggest a strategy to achieve the same result, ie
> OrderId,ReadBackOrderItems that I can use in further joins?
> Thanks in anticipation
> Terry Bell

Are you asking how to rewrite the SUM expression? I don't know exactly what
the syntax above means, so this is a guess:

sum(case when BackOrderQtyAvailable > 0 then BackOrderQtyAvailable else 0
end * -1)

If this is wrong, then I suggest you post CREATE TABLE and INSERT statements
to create your tables and populate some sample data, along with the result
you expect to see from your query.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<41480077$1_2@.news.bluewin.ch>...
> "Terry Bell" <dreadnought8@.hotmail.com> wrote in message
> news:923537d6.0409142346.301c9c3@.posting.google.co m...
> > I'm upsizing an Access database. Got the data converted, working on
> > the front end, converting queries to views, but having trouble
> > converting queries that use logical expressions like the following:
> > SELECT OrderId,
> > Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems
> > FROM OrderDetails
> > INNER JOIN Items
> > ON (OrderDetails.ClientId = Items.ClientId)
> > AND (OrderDetails.ItemId = Items.ItemId)
> > WHERE (NOT (SitesCustomerTypeId = 2
> > AND ExpressBackorder =True
> > AND OrderUrgency = 1 ))
> > GROUP BY OrderId;
> > Can someone suggest a strategy to achieve the same result, ie
> > OrderId,ReadBackOrderItems that I can use in further joins?
> > Thanks in anticipation
> > Terry Bell
> Are you asking how to rewrite the SUM expression? I don't know exactly what
> the syntax above means, so this is a guess:
> sum(case when BackOrderQtyAvailable > 0 then BackOrderQtyAvailable else 0
> end * -1)
> If this is wrong, then I suggest you post CREATE TABLE and INSERT statements
> to create your tables and populate some sample data, along with the result
> you expect to see from your query.
> Simon

Thanks very much Simon you have given me the direction I needed.
For the record, here's my full converted code - with some side errors
fixed

SELECT Q845UndeliveredOrderDetails.OrderId, SUM(CASE WHEN
BackOrderQtyAvailable > 0 THEN 1 ELSE 0 END) AS ReadyBackOrderItems
FROM Q845UndeliveredOrderDetails INNER JOIN
Items ON (Q845UndeliveredOrderDetails.ClientId =
Items.ClientId) AND (Q845UndeliveredOrderDetails.ItemId =
Items.ItemId)
WHERE (NOT (SitesCustomerTypeId = 2 AND ExpressBackorder = 1 AND
OrderUrgency = 1))
GROUP BY Q845UndeliveredOrderDetails.OrderId;

So:
Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems ... in Access
SQL
becomes
SUM(CASE WHEN BackOrderQtyAvailable > 0 THEN 1 ELSE 0 END) AS
ReadyBackOrderItems ... in SQL

I also note that in Access you can say something like

WHERE IsBackOrder

and it evaluates IsBackOrder as a logical expression
whereas in sql server we need to say

WHERE IsBackorder = 1

Is that right?

Then I guess I need to think about NULL too ...

Also I notice in the query analyser it comes up with a message saying
it can't understand the CASE statement, but I can ignore that, can I,
as it seems to go ahead and execute the query anyway?

Once again thanks a million this has saved me lots of time

Terry Bell|||<snip
> I also note that in Access you can say something like
> WHERE IsBackOrder
> and it evaluates IsBackOrder as a logical expression
> whereas in sql server we need to say
> WHERE IsBackorder = 1
> Is that right?

Not quite - there is no Boolean data type in MSSQL, so how to evaluate
'true' or 'false' depends on the data type you've chosen. One common
solution is to use the bit data type, with 1 for true and 0 for false, in
which case your code above is correct (assuming true = 1).

> Then I guess I need to think about NULL too ...

Yes - this is one reason why you often see requests for DDL (CREATE TABLE
etc.), as this makes it clear which columns allow NULL and which don't.
Something that seems to work fine may fail when NULLs are involved, so you
need to code for them if the data model allows them.

> Also I notice in the query analyser it comes up with a message saying
> it can't understand the CASE statement, but I can ignore that, can I,
> as it seems to go ahead and execute the query anyway?

I have no idea without seeing the full error, but perhaps this is error 8153
"Warning: Null value is eliminated by an aggregate or other SET operation."?
If so, it's just a warning that the column you SUMmed on contains NULL data.

> Once again thanks a million this has saved me lots of time
> Terry Bell

You're welcome.

Simon|||Generally just copy and paste from Access to Query Analyser. Check the
query runs correctly and then add CREATE PROCEDURE blah blah to the top
and run. This turns the script in to a stored procedure and loads it in
to the current database.

You might want to move all the restrictions to the WHERE clause other
wise you can get some interesting results if you are not very careful.

Sum((BackOrderQtyAvailable)* -1)

(BackOrderQtyAvailable > 0)

Adrian

Terry Bell wrote:
> I'm upsizing an Access database. Got the data converted, working on
> the front end, converting queries to views, but having trouble
> converting queries that use logical expressions like the following:
> SELECT OrderId,
> Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems
> FROM OrderDetails
> INNER JOIN Items
> ON (OrderDetails.ClientId = Items.ClientId)
> AND (OrderDetails.ItemId = Items.ItemId)
> WHERE (NOT (SitesCustomerTypeId = 2
> AND ExpressBackorder =True
> AND OrderUrgency = 1 ))
> GROUP BY OrderId;
> Can someone suggest a strategy to achieve the same result, ie
> OrderId,ReadBackOrderItems that I can use in further joins?
> Thanks in anticipation
> Terry Bell|||On Thu, 16 Sep 2004 17:27:35 +0200, Simon Hayes wrote:
>"Terry Bell" <dreadnought8@.hotmail.com> wrote:
>>
>> Also I notice in the query analyser it comes up with a message saying
>> it can't understand the CASE statement, but I can ignore that, can I,
>> as it seems to go ahead and execute the query anyway?
> I have no idea without seeing the full error, but perhaps this is error 8153
> "Warning: Null value is eliminated by an aggregate or other SET operation."?
> If so, it's just a warning that the column you SUMmed on contains NULL data.

I don't think it's a null error -- I think he was editing his query in MS
Access's query editor, in an ADP file, rather than using SQL Server's Query
Analyzer. I've gotten that error from MS Access myself.

As Terry said, Access goes ahead and executes it anyway. It just can't
parse it properly to represent it in the graphical query editor.

converting access db to mysql

Hey people,

I have to convert MS Access 2000 database into mysql database, the whole
thing being part of this project I'm doing for one of my faculty
classes. My professor somehow presumed I knew db's and gave me long list
of things to do with that particular database, first thing being that
particular conversion. Truth is that I don't know a first thing about
db's, let alone using mysql... I downloaded mysql form www.mysql.com and
still searching for MS Access 2000 (it doesn't work with 2003 I have,
or I don't know how to make it work).

Any kind of help will be welcomed and highly appreciated!!!

Thanks,

MarioCenturies ago, Nostradamus foresaw when chicha <mario@.blindsight.org> would write:
> Hey people,
> I have to convert MS Access 2000 database into mysql database, the
> whole thing being part of this project I'm doing for one of my
> faculty classes. My professor somehow presumed I knew db's and gave
> me long list of things to do with that particular database, first
> thing being that particular conversion. Truth is that I don't know a
> first thing about db's, let alone using mysql... I downloaded mysql
> form www.mysql.com and still searching for MS Access 2000 (it
> doesn't work with 2003 I have, or I don't know how to make it work).
> Any kind of help will be welcomed and highly appreciated!!!

Hmm.

It sounds as though you're interested in sorts of assistance that
would be considered to be academic offenses.

Thanks for the offer to "aid and abet," but no thanks.
--
let name="cbbrowne" and tld="ntlug.org" in name ^ "@." ^ tld;;
http://www.ntlug.org/~cbbrowne/multiplexor.html
Rules of the Evil Overlord #40. "I will be neither chivalrous nor
sporting. If I have an unstoppable superweapon, I will use it as early
and as often as possible instead of keeping it in reserve."
<http://www.eviloverlord.com/>|||"Christopher Browne" <cbbrowne@.acm.org> wrote in message
news:2pu2isFonehqU1@.uni-berlin.de...

> It sounds as though you're interested in sorts of assistance that
> would be considered to be academic offenses.

This one doesn't sound like "do my homework for me" to me.

It's more like "moving from one db to the other is a preliminary step before
the project can really begin".

Of course, in any project like this, it's always the first step that's the
giant killer.|||Hi, Mario.

> My professor somehow presumed I knew db's and gave me long list
> of things to do with that particular database, first thing being that
> particular conversion. Truth is that I don't know a first thing about
> db's, let alone using mysql...

You have set yourself up for failure. The first thing you should do is go
back to your professor and explain that you got carried away by the
excitement of the project assignment, but that you now realize that this
database conversion is way over your head. How can one tell this is way
over your head? Reread the following statement that you made:

> still searching for MS Access 2000 (it doesn't work with 2003 I have,
> or I don't know how to make it work).

Access 2000 is the default database format for Access 2000, 2002, and 2003.
This is going to sound harsh, but if you can't figure out "how to make it
work," then either you aren't using any of these versions of Access or don't
know how to open an Access database file with Access. Might you be using
Access 97 or another application in the Office 2003 suite in your attempts
to open the Access 2000 database?

Converting a database from Access to MySQL isn't just selecting a menu item
to launch a Wizard and assigning a path and file name to the new MySQL
database through the Wizard GUI. You can probably export each of the tables
from the Access database to *.CSV files that can be imported into MySQL, but
you've got to do the relational database design and likely the table
creation first in MySQL. None of the queries, forms, reports, macros,
modules or data access pages can be exported from Access into a format that
MySQL can read and convert into a MySQL format for the new database.

The scope of this project is so vast that the type of help you need is for
someone to do the entire project for you. However, since this is a class
project, I doubt you'll get much assistance from the News Groups.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

"chicha" <mario@.blindsight.org> wrote in message
news:chcfuv$n00$1@.bagan.srce.hr...
> Hey people,
> I have to convert MS Access 2000 database into mysql database, the whole
> thing being part of this project I'm doing for one of my faculty
> classes. My professor somehow presumed I knew db's and gave me long list
> of things to do with that particular database, first thing being that
> particular conversion. Truth is that I don't know a first thing about
> db's, let alone using mysql... I downloaded mysql form www.mysql.com and
> still searching for MS Access 2000 (it doesn't work with 2003 I have,
> or I don't know how to make it work).
> Any kind of help will be welcomed and highly appreciated!!!
> Thanks,
> Mario|||In message <chcfuv$n00$1@.bagan.srce.hr>, chicha <mario@.blindsight.org>
writes
>Hey people,
>I have to convert MS Access 2000 database into mysql database, the whole
>thing being part of this project I'm doing for one of my faculty
>classes. My professor somehow presumed I knew db's and gave me long list
>of things to do with that particular database, first thing being that
>particular conversion.

You can't just convert an Access database to MySQL because there are
lots of things that Access can do that MySQL can't. You may be able to
copy the data from an Access database to a MySQL Server but that's a
trivially small part of a complete conversion.

> Truth is that I don't know a first thing about
>db's, let alone using mysql... I downloaded mysql form www.mysql.com and
> still searching for MS Access 2000 (it doesn't work with 2003 I have,
>or I don't know how to make it work).

Access 2003 should be able to open an Access 2000 database file. Check
the file extension for the database file you have, do you have a file
with a .MDB extension or is it something else?

>Any kind of help will be welcomed and highly appreciated!!!

Run away screaming. It's the most helpful thing I can suggest.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.|||"chicha" <mario@.blindsight.org> wrote in message
news:chcfuv$n00$1@.bagan.srce.hr...
> Hey people,
> I have to convert MS Access 2000 database into mysql database, the whole
> thing being part of this project I'm doing for one of my faculty
> classes. My professor somehow presumed I knew db's and gave me long list
> of things to do with that particular database, first thing being that
> particular conversion. Truth is that I don't know a first thing about
> db's, let alone using mysql... I downloaded mysql form www.mysql.com and
> still searching for MS Access 2000 (it doesn't work with 2003 I have,
> or I don't know how to make it work).
> Any kind of help will be welcomed and highly appreciated!!!

If your a quick study, Google can be your best friend.

If you have approx. $100 USD and a 2 week window, go to rentacoder.com.
Someone from Pakistan will do your job for you for that price with glee.

If neither of those works for you, you're probably F%#*ked.|||You might want to consider exporting the data from Access to a flat
text file. then import the flat text file into mysql. This way, you
can avoid having to find a way to convert directly from one to the
other.

I know mysql can import flat files, as a co-worked did it to load
legacy data from our operational system into a mysql db for a web
page.
hth
Rob

chicha <mario@.blindsight.org> wrote in message news:<chcfuv$n00$1@.bagan.srce.hr>...
> Hey people,
> I have to convert MS Access 2000 database into mysql database, the whole
> thing being part of this project I'm doing for one of my faculty
> classes. My professor somehow presumed I knew db's and gave me long list
> of things to do with that particular database, first thing being that
> particular conversion. Truth is that I don't know a first thing about
> db's, let alone using mysql... I downloaded mysql form www.mysql.com and
> still searching for MS Access 2000 (it doesn't work with 2003 I have,
> or I don't know how to make it work).
> Any kind of help will be welcomed and highly appreciated!!!
> Thanks,
> Mario|||Thanks guys for your replies!

I see now I phrased my post in a completely wrong way.

First of all, what I'm trying to do here is to get some guidance and
friendly advice to get me started. I HAVE to do this project, I was told
to. For any of you who read somewhere between my lines "please could
somebody do this for free instead of me??", I just must stress that that
was NOT my idea for this post. There are easier and more reliable ways
to cheat at faculty then using newsgroups...

I'm new with db's but not with PC's. What I should have written is that
I got the database made in Access 2000 (.MDB) which doesn't work with
Access 2003. I know this is wrong, and that it should work, but it
doesn't. I spoke with the author/professor assistant who obviously did
it using "Databases for dummies" book and he said he's sure it works but
has bugs(!!!). He doesn't know what bugs though :). That's why he gave
me the whole thing to put it in mysql. When I tried it in 2003, sure as
hell, it didn't work, bug or no bug. It opened and loaded, but didn't
read relations nor data. It works in A2000 because the whole "office of
construction in mech. eng." at my faculty uses it. I'm not gonna tell
that guy he did it all wrong, because he knows it. He knows I know it
too. Saying it loud would mean my academical suicide. I'll rather use
Access 2000 and fix it there.

I mentioned it in the post because I thought someone could offer an
advice. My apologies that I haven't explained the situation more
thoroughly, I thought it was uneccessary. It would save me from you guys
thinking I'm complete idiot or lousy cheat.

Thanks to all of you guys who provided insightfull info and links, I
appreciate it. I'll check it out, and see what can be done.

Cheers,

Mario

rkc wrote:

> "chicha" <mario@.blindsight.org> wrote in message
> news:chcfuv$n00$1@.bagan.srce.hr...
>>Hey people,
>>
>>I have to convert MS Access 2000 database into mysql database, the whole
>>thing being part of this project I'm doing for one of my faculty
>>classes. My professor somehow presumed I knew db's and gave me long list
>>of things to do with that particular database, first thing being that
>>particular conversion. Truth is that I don't know a first thing about
>>db's, let alone using mysql... I downloaded mysql form www.mysql.com and
>> still searching for MS Access 2000 (it doesn't work with 2003 I have,
>>or I don't know how to make it work).
>>
>>Any kind of help will be welcomed and highly appreciated!!!
>
> If your a quick study, Google can be your best friend.
> If you have approx. $100 USD and a 2 week window, go to rentacoder.com.
> Someone from Pakistan will do your job for you for that price with glee.
> If neither of those works for you, you're probably F%#*ked.
>
>|||In message <chd16b$jso$1@.bagan.srce.hr>, chicha <mario@.blindsight.org>
writes
>Thanks guys for your replies!
>I see now I phrased my post in a completely wrong way.
>First of all, what I'm trying to do here is to get some guidance and
>friendly advice to get me started. I HAVE to do this project, I was
>told to. For any of you who read somewhere between my lines "please
>could somebody do this for free instead of me??", I just must stress
>that that was NOT my idea for this post. There are easier and more
>reliable ways to cheat at faculty then using newsgroups...

OK. Here's a few things to think about.

First, Access is not just a database system. It includes a forms
designer and a report designer, neither of which exist in MySQL. In
addition to moving the data from Access to MySQL you will also have to
design data-entry forms and reports. You will need to choose additional
software to do that.

It is possible to use Access as a front-end with the data stored in a
MySQL database. If you do it right you won't need to redesign any of
your forms or reports to do that. This is probably what you should aim
at as the first step in migration.

MySQL is designed as a client-server system and is usually used with the
data stored on a central server and client programs running on users'
workstations. You can run both client and server software on the same
machine, but you don't have to. If your site has any other MySQL
databases in use then you may find that there are already MySQL experts
available.

A copy of Access 2003 should open an Access 2000 database file without
any problems. If you are having problems with the database file then
switching to Access 2000 probably won't fix the problem. Find a machine
where the system works. Open up the database file and look at the tables
in it. Look for attached tables that use data that is actually stored
elsewhere. That might be in another Access database file or perhaps even
in a MySQL database on a server elsewhere. If your database uses
attached tables then you have to bring a copy of the data source to your
new machine in addition to the database file itself. Check this and let
us know whether there are any attached tables.

[...]

>I mentioned it in the post because I thought someone could offer an
>advice. My apologies that I haven't explained the situation more
>thoroughly, I thought it was uneccessary. It would save me from you
>guys thinking I'm complete idiot or lousy cheat.

It's pretty difficult to gather all of the information on a system if
expert users are standing right in front of you. Trying to do it through
a newsgroup is even more difficult.

Speaking of newsgroups, I'm not sure that comp.databases.theory is the
right newsgroup for this. You might want to drop that from the
crossposting list (unless anyone in that newsgroup wants it to
continue.)

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.|||You can certainly move the data to mysql, and keep the ms-access front end.

A large portion, in fact most of the forms and reports will work. However,
do you need to be aware of some differences etc.

And, most code as is will also work.

The main areas to watch are:

You need to have both a primary key, and a timestamp in EACH table on
the mysql side.

You can continue to use the ms-access query builder. For reports,
queries that have multi-table joins should be changed to pass-through, and
that performances VERY well with mysql.

So, this project is do-able. However, you should have a good familiarly with
ms-access, and your initial problems with ms-access need to be solved first.

good luck!

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msn|||"chicha" wrote

> When I tried it in 2003, sure as hell, it didn't
> work, bug or no bug. It opened and loaded,
> but didn't read relations nor data. It works in
> A2000 because the whole "office of con-
> struction in mech. eng." at my faculty uses it.

That seems to indicate that the TableDefs in the database you are trying to
work with are simply links to tables in another "back-end" Access database.
You are assuming a cause when you haven't even identified what the problem
is.

If the "whole office of..." are all working with the same tables, that is
almost certainly the way it would have been set up. Almost certainly, it has
not a thing to do with whether you are using Access 2000, 2002, or 2003 to
work with it.

If the computer where you are trying Access 2003 is not connected on the
same LAN as the "whole office of..." then you are unlikely to be able to fix
that, unless you find and copy the shared tables to the machine/environment
where you are working. If you do that, or if your Access 2003 computer is on
the same LAN, then you may be able to fix the problem as simply as finding
the shared tables and using the Linked Table Manager (on the menu, Tools |
Database Utilities | Linked Table Manager) to relink the TableDefs to the
proper backend database.

Did I sufficiently emphasize the key here is "FIND THE SHARED TABLES AND
LINK THEM"?

I strongly urge you do that before you waste any more of your time and
effort on worrying about Access 2000. If, however, you try to do it in
Access 2000, on a machine that is in the same LAN as the "whole office of
..." and it _does_ work, you cannot assume that it worked just because you
used Access 2000 (see above).

As many have told you, you can convert the data -- BUT you first have to
HAVE the data, which you do not seem to have. Other than just hipshooting a
possible (probably not a workable) solution, I don't see any good reason to
convert even the data to MySQL.

Should I emphasize again, the key is "FIND THE SHARED TABLES"?

But certainly, you can convert nothing BUT the data to MySQL, because it is
a server database and has no support for any of the other features (to wit,
the user interface) of Access.

Larry Linson
Microsoft Access MVP|||chicha <mario@.blindsight.org> wrote:
> Hey people,

[...]

> Any kind of help will be welcomed and highly appreciated!!!

Convert tables -> .*csv -> mysql

I don't know MS Access 2000...

> Thanks,
> Mario

Servus,
Dietrich|||chicha schrieb:
> Hey people,
> I have to convert MS Access 2000 database into mysql database, the whole
> thing being part of this project I'm doing for one of my faculty
> classes. My professor somehow presumed I knew db's and gave me long list
> of things to do with that particular database, first thing being that
> particular conversion. Truth is that I don't know a first thing about
> db's, let alone using mysql... I downloaded mysql form www.mysql.com and
> still searching for MS Access 2000 (it doesn't work with 2003 I have,
> or I don't know how to make it work).
> Any kind of help will be welcomed and highly appreciated!!!
> Thanks,
> Mario

Hello,
http://www.dbtools.com.br/EN/dbmanager.php

Gru Matthias

--
www.source-code.de
- The Developers Source -|||"Matthias Gutmann" wrote

> http://www.dbtools.com.br/EN/dbmanager.php

Matthias, I'll bet your tool won't work unless he finds and uses the actual
data tables. Linked TableDefs just are not sufficient to retrieve the data
unless the Links are valid, and there is a strong probability that is
chicha's problem. Once he has them, that appears to be a very useful set of
features.

Larry Linson
Microsoft Access MVP|||"chicha" <mario@.blindsight.org> wrote in message
news:chcfuv$n00$1@.bagan.srce.hr...
> Hey people,
> I have to convert MS Access 2000 database into mysql database, the whole
> thing being part of this project I'm doing for one of my faculty
> classes. My professor somehow presumed I knew db's and gave me long list
> of things to do with that particular database, first thing being that
> particular conversion. Truth is that I don't know a first thing about
> db's, let alone using mysql... I downloaded mysql form www.mysql.com and
> still searching for MS Access 2000 (it doesn't work with 2003 I have,
> or I don't know how to make it work).
> Any kind of help will be welcomed and highly appreciated!!!
> Thanks,
> Mario

OK, now that we have a clearer picture of what you're up against maybe we
can break it down into steps:

1. Open the DB up with MS Access.
2. Unload the data into portable format files.
3. Extract the data definitions (at least tables and indexes) into some
form for migration.
4. Create an empty target DB.
5. Define tables, indexes, and whatever necessary infrastructure to contain
the same data.
6. Load the data from the protable format files.

These tasks are not equally easy. You can probably do some of them by the
seat of your pants, just using the general PC skills and savvy you've picked
up elsewhere. For some of them, you probably need help.

As far as step 1 goes. I'd really advise you to get someone with the right
version of MS Access to open the DB. The last thing you need to be doing at
this stage is messing around with version skew or incompatible software.
What about the professor? Can he open the DB with his version of Access?

We'll help you with the remaining steps once you can open the DB.|||Check this link

http://www.convert-in.com/acc2sql.htm

Nerver used it but it looks interesting.

Cheers

"chicha" <mario@.blindsight.org> wrote in message
news:chd16b$jso$1@.bagan.srce.hr...
> Thanks guys for your replies!
> I see now I phrased my post in a completely wrong way.
> First of all, what I'm trying to do here is to get some guidance and
> friendly advice to get me started. I HAVE to do this project, I was told
> to. For any of you who read somewhere between my lines "please could
> somebody do this for free instead of me??", I just must stress that that
> was NOT my idea for this post. There are easier and more reliable ways to
> cheat at faculty then using newsgroups...
> I'm new with db's but not with PC's. What I should have written is that I
> got the database made in Access 2000 (.MDB) which doesn't work with Access
> 2003. I know this is wrong, and that it should work, but it doesn't. I
> spoke with the author/professor assistant who obviously did it using
> "Databases for dummies" book and he said he's sure it works but has
> bugs(!!!). He doesn't know what bugs though :). That's why he gave me the
> whole thing to put it in mysql. When I tried it in 2003, sure as hell, it
> didn't work, bug or no bug. It opened and loaded, but didn't read
> relations nor data. It works in A2000 because the whole "office of
> construction in mech. eng." at my faculty uses it. I'm not gonna tell that
> guy he did it all wrong, because he knows it. He knows I know it too.
> Saying it loud would mean my academical suicide. I'll rather use Access
> 2000 and fix it there.
> I mentioned it in the post because I thought someone could offer an
> advice. My apologies that I haven't explained the situation more
> thoroughly, I thought it was uneccessary. It would save me from you guys
> thinking I'm complete idiot or lousy cheat.
> Thanks to all of you guys who provided insightfull info and links, I
> appreciate it. I'll check it out, and see what can be done.
> Cheers,
> Mario
>
> rkc wrote:
>> "chicha" <mario@.blindsight.org> wrote in message
>> news:chcfuv$n00$1@.bagan.srce.hr...
>>
>>>Hey people,
>>>
>>>I have to convert MS Access 2000 database into mysql database, the whole
>>>thing being part of this project I'm doing for one of my faculty
>>>classes. My professor somehow presumed I knew db's and gave me long list
>>>of things to do with that particular database, first thing being that
>>>particular conversion. Truth is that I don't know a first thing about
>>>db's, let alone using mysql... I downloaded mysql form www.mysql.com and
>>> still searching for MS Access 2000 (it doesn't work with 2003 I have,
>>>or I don't know how to make it work).
>>>
>>>Any kind of help will be welcomed and highly appreciated!!!
>>
>>
>> If your a quick study, Google can be your best friend.
>>
>> If you have approx. $100 USD and a 2 week window, go to rentacoder.com.
>> Someone from Pakistan will do your job for you for that price with glee.
>>
>> If neither of those works for you, you're probably F%#*ked.
>>
>>
>|||Thanks, thanks, thanks!!!

I spent this few days doing my research and thanks to your kind answers
and ideas I finally have idea what's going on.

Database I got to work on, now finally loaded up in Access 2003 but
cannot be worked with since it has password protection. I got the
username and password together with the db form that guy who made it,
but program just doesn't reckognize neither of those... it doesn't even
prompt me to use them, it just says I don't have permission to open/run
any of components. Although he made username for me, now it's nowhere
tzo be found, only Admin.

Obviously there's something wrong with db itself. Now I applied to get
faculty's copy of Access 2000 with student license (that's gonna take
some time...), then I'll see what's going on. When I make it work in
both 2000 & 2003 Access I'll start thinking about conversion.

I'll send posts about my progress.

Thanks again for your replies!!

mario

Bolt Upright wrote:
> Check this link
> http://www.convert-in.com/acc2sql.htm
> Nerver used it but it looks interesting.
> Cheers
>
> "chicha" <mario@.blindsight.org> wrote in message
> news:chd16b$jso$1@.bagan.srce.hr...
>>Thanks guys for your replies!
>>
>>I see now I phrased my post in a completely wrong way.
>>
>>First of all, what I'm trying to do here is to get some guidance and
>>friendly advice to get me started. I HAVE to do this project, I was told
>>to. For any of you who read somewhere between my lines "please could
>>somebody do this for free instead of me??", I just must stress that that
>>was NOT my idea for this post. There are easier and more reliable ways to
>>cheat at faculty then using newsgroups...
>>
>>I'm new with db's but not with PC's. What I should have written is that I
>>got the database made in Access 2000 (.MDB) which doesn't work with Access
>>2003. I know this is wrong, and that it should work, but it doesn't. I
>>spoke with the author/professor assistant who obviously did it using
>>"Databases for dummies" book and he said he's sure it works but has
>>bugs(!!!). He doesn't know what bugs though :). That's why he gave me the
>>whole thing to put it in mysql. When I tried it in 2003, sure as hell, it
>>didn't work, bug or no bug. It opened and loaded, but didn't read
>>relations nor data. It works in A2000 because the whole "office of
>>construction in mech. eng." at my faculty uses it. I'm not gonna tell that
>>guy he did it all wrong, because he knows it. He knows I know it too.
>>Saying it loud would mean my academical suicide. I'll rather use Access
>>2000 and fix it there.
>>
>>I mentioned it in the post because I thought someone could offer an
>>advice. My apologies that I haven't explained the situation more
>>thoroughly, I thought it was uneccessary. It would save me from you guys
>>thinking I'm complete idiot or lousy cheat.
>>
>>Thanks to all of you guys who provided insightfull info and links, I
>>appreciate it. I'll check it out, and see what can be done.
>>
>>Cheers,
>>
>>Mario
>>
>>
>>
>>rkc wrote:
>>
>>
>>>"chicha" <mario@.blindsight.org> wrote in message
>>>news:chcfuv$n00$1@.bagan.srce.hr...
>>>
>>>
>>>>Hey people,
>>>>
>>>>I have to convert MS Access 2000 database into mysql database, the whole
>>>>thing being part of this project I'm doing for one of my faculty
>>>>classes. My professor somehow presumed I knew db's and gave me long list
>>>>of things to do with that particular database, first thing being that
>>>>particular conversion. Truth is that I don't know a first thing about
>>>>db's, let alone using mysql... I downloaded mysql form www.mysql.com and
>>>> still searching for MS Access 2000 (it doesn't work with 2003 I have,
>>>>or I don't know how to make it work).
>>>>
>>>>Any kind of help will be welcomed and highly appreciated!!!
>>>
>>>
>>>If your a quick study, Google can be your best friend.
>>>
>>>If you have approx. $100 USD and a 2 week window, go to rentacoder.com.
>>>Someone from Pakistan will do your job for you for that price with glee.
>>>
>>>If neither of those works for you, you're probably F%#*ked.
>>>
>>>
>>>
>|||Chicha,

You have to have the MDW file with which it was secured before the userid
and password will do you any good. I'll say again, using Access 2000 isn't
going to change that. Along with the userid and password, find and join the
MDW, and you'll be able to open it.

You are _fixated_ on Access 2000 as the solution, but you still haven't
understood the problem.

Larry Linson
Microsoft Access MVP

"chicha" <mario@.blindsight.org> wrote in message
news:chg6ns$hrj$1@.bagan.srce.hr...
> Thanks, thanks, thanks!!!
> I spent this few days doing my research and thanks to your kind answers
> and ideas I finally have idea what's going on.
> Database I got to work on, now finally loaded up in Access 2003 but
> cannot be worked with since it has password protection. I got the
> username and password together with the db form that guy who made it,
> but program just doesn't reckognize neither of those... it doesn't even
> prompt me to use them, it just says I don't have permission to open/run
> any of components. Although he made username for me, now it's nowhere
> tzo be found, only Admin.
> Obviously there's something wrong with db itself. Now I applied to get
> faculty's copy of Access 2000 with student license (that's gonna take
> some time...), then I'll see what's going on. When I make it work in
> both 2000 & 2003 Access I'll start thinking about conversion.
> I'll send posts about my progress.
> Thanks again for your replies!!
> mario
>
> Bolt Upright wrote:
> > Check this link
> > http://www.convert-in.com/acc2sql.htm
> > Nerver used it but it looks interesting.
> > Cheers
> > "chicha" <mario@.blindsight.org> wrote in message
> > news:chd16b$jso$1@.bagan.srce.hr...
> >>Thanks guys for your replies!
> >>
> >>I see now I phrased my post in a completely wrong way.
> >>
> >>First of all, what I'm trying to do here is to get some guidance and
> >>friendly advice to get me started. I HAVE to do this project, I was told
> >>to. For any of you who read somewhere between my lines "please could
> >>somebody do this for free instead of me??", I just must stress that that
> >>was NOT my idea for this post. There are easier and more reliable ways
to
> >>cheat at faculty then using newsgroups...
> >>
> >>I'm new with db's but not with PC's. What I should have written is that
I
> >>got the database made in Access 2000 (.MDB) which doesn't work with
Access
> >>2003. I know this is wrong, and that it should work, but it doesn't. I
> >>spoke with the author/professor assistant who obviously did it using
> >>"Databases for dummies" book and he said he's sure it works but has
> >>bugs(!!!). He doesn't know what bugs though :). That's why he gave me
the
> >>whole thing to put it in mysql. When I tried it in 2003, sure as hell,
it
> >>didn't work, bug or no bug. It opened and loaded, but didn't read
> >>relations nor data. It works in A2000 because the whole "office of
> >>construction in mech. eng." at my faculty uses it. I'm not gonna tell
that
> >>guy he did it all wrong, because he knows it. He knows I know it too.
> >>Saying it loud would mean my academical suicide. I'll rather use Access
> >>2000 and fix it there.
> >>
> >>I mentioned it in the post because I thought someone could offer an
> >>advice. My apologies that I haven't explained the situation more
> >>thoroughly, I thought it was uneccessary. It would save me from you guys
> >>thinking I'm complete idiot or lousy cheat.
> >>
> >>Thanks to all of you guys who provided insightfull info and links, I
> >>appreciate it. I'll check it out, and see what can be done.
> >>
> >>Cheers,
> >>
> >>Mario
> >>
> >>
> >>
> >>rkc wrote:
> >>
> >>
> >>>"chicha" <mario@.blindsight.org> wrote in message
> >>>news:chcfuv$n00$1@.bagan.srce.hr...
> >>>
> >>>
> >>>>Hey people,
> >>>>
> >>>>I have to convert MS Access 2000 database into mysql database, the
whole
> >>>>thing being part of this project I'm doing for one of my faculty
> >>>>classes. My professor somehow presumed I knew db's and gave me long
list
> >>>>of things to do with that particular database, first thing being that
> >>>>particular conversion. Truth is that I don't know a first thing about
> >>>>db's, let alone using mysql... I downloaded mysql form www.mysql.com
and
> >>>> still searching for MS Access 2000 (it doesn't work with 2003 I have,
> >>>>or I don't know how to make it work).
> >>>>
> >>>>Any kind of help will be welcomed and highly appreciated!!!
> >>>
> >>>
> >>>If your a quick study, Google can be your best friend.
> >>>
> >>>If you have approx. $100 USD and a 2 week window, go to rentacoder.com.
> >>>Someone from Pakistan will do your job for you for that price with
glee.
> >>>
> >>>If neither of those works for you, you're probably F%#*ked.
> >>>
> >>>
> >>sqlsql

converting Access database to sql server2000

Hi all
Im new to SQL server and have now spent a couple of weeks trying to convert our companies Access 2000 database to SQL Server 2000. which has about 15 users however more users will be required to use it up to 50. I have been trying to turn my MDB database into ADP however have done tables, forms, reports and views completed and working fine, however i need to update, delete and add etc information in several tables at once, i.e. customer, orders, employee, ect. I used a query in access, i know views work but only with one table at a time. can someone please enlighten me as to what the norm is for using the equivelent of a query to update etc information from several table linked to a form. (Is it a stored procedure or a transact SQL statement?)
Thanks
JimAs far as I know SQL generally, you need to execute one UPDATE per table. You have no forms in SQL Server itself, so you'll need to code the updates in a stored proc.
Maybe someone else knows a better way?|||Cheers Coolberg
I had thought that some long winded code was required to perform this operation, creating stored procedures does seem to be the only option i have. Never mind better get my head in a book and get coding.
It is a bit of a letdown when you diversify from access 2000 MDB to an Access ADP file and you get so far no information is available for creating something similiar to access queries, where you can combine information from several tables onto one form and update, delete etc.
Thanks for your help... if anyone else has any ideas web links etc than id be more than greatful
Jim|||Hello James,

I am not 100% sure I am following everything you are talking about but you can easily move data from Access to SQL Server using a DTS package and more specifically the DTS Designer. This is a lot easier than writing the transact sql unless you are just doing SELECT INTO statements. Here is a link to get you started:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_tools_des_4fg2.asp

I also believe that you are referring to preserving the parent/child relationships that are displayed by using a form. However, if you are just replicating the data then the relationships will remain if you include all the keys.

Finally, if you are asking how to mimic the forms in Access in SQL Server then this is only done through another language, be it VB / C++ / C# / ASP / Perl / Java, ect...

I hope that helps point you in the right direction.|||Hi Krusty
Thanks for the info on DTS package have just used it. It did transfer tables etc. However much of this was accomplished when I used the upsize wizard to convert form Access to SQL. Hasnt really helped with the complicated queries involving several joins (which were original part of my Access database linked to my main input form). Think Im going to have to compare Access and SQL server Delimiters, Constants and Operators within my Access queries and change them accordingly then try it. Will be a lot of work but hopefully worth it.

I also believe that you are referring to preserving the parent/child relationships that are displayed by using a form. However, if you are just replicating the data then the relationships will remain if you include all the keys. This is correct the tables and relationships within my SQL server database linked to ADP are fine, what the problem is if I can hopefully explain it briefly, I want SQL server 2000 to somehow work with several tables at once linked to a main form that users can update, delete, create new records etc which is how Access works using a query (with several tables within that query linked to a form) I know that queries dont exist within SQL server and want to use something similar.

I did consider linking my Access.mdb with SQL using ODBC however this would not improve Performance and scalability of my database application, therefore this is the easiest option but not a viable one for me.

Finally, if you are asking how to mimic the forms in Access in SQL Server then this is only done through another language, be it VB / C++ / C# / ASP / Perl / Java, ect... I do intend to reproduce the forms reports etc in Visual Basic 6 i.e. the parts on the clients machine, however my intention is to get the application working with Access ADP first as this is much simpler, this will be done at a later stage.
Thanks for your help Krusty
Jim|||James,

Ok I think I now understand what it is you are trying to do. This can be accomplished in a single query using transaction flow, let me explain.

Lets say you have a customer table and an address table. The address table uses the customer ID as a foreign key to connect the two tables. Here is a query that you could use to insert those records.

BEGIN TRAN
INSERT INTO CUSTOMER (NAME) VALUES (@.NAME)
IF @.@.IDENTITY IS NOT NULL
INSERT INTO ADDRESS (ADDRESS, CUSTOMERID) VALUES (@.ADDRESS, @.@.IDENTITY)
IF @.@.IDENTITY IS NULL
ROLLBACK TRAN
ELSE
COMMIT TRAN
GO

One the query assumes that you are using an identity field as the primary key. The @.@.IDENTITY function will return NULL if the insert fails but will return the value of the identity field if the insert were successful. This is not a statement that I would use in production but more or less give you an idea of how to accomplish inserting related data. Updates work very similar. Check out the Global functions to help you along the way they all start with @.@..

HTH!

PS: One thing you will have to change in your current queries from Access to SQL Server is changing " to ' for string values. Access uses a double quote where SQL Server uses a single quote.|||Thanks Krusty,wasnt quite sure what you meant by this code, maybe i need to get a bit more advanced on the subject.
However found what i needed! after surfing the web for hours.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/access2sql.asp

Basically for anyone else stuck on this problem it says:
Migrating Microsoft Access Queries
You must move your existing Access queries into SQL Server in one of these formats:

Transact-SQL scripts
Transact-SQL statements are usually called from database programs, but you can use SQL Server Query Analyzer, included in SQL Server 7.0, to run them against the database directly. SQL Server Query Analyzer helps developers to test Transact-SQL statements against development databases, or to run Transact-SQL statements that perform queries, data manipulation (INSERT, UPDATE, DELETE), or data definition (CREATE TABLE).

Stored procedures
Developers can move most Transact-SQL statements that originate from Access queries (SELECT, INSERT, UPDATE, and DELETE) into stored procedures. Stored procedures written in Transact-SQL can be used to encapsulate and standardize your data access, and are actually stored within the database. Stored procedures can run with or without parameters and are called from database programs or manually from SQL Server Query Analyzer.

Views
Views are used as virtual tables that expose specific rows and columns from one or more tables. They allow users to create queries without directly implementing the complex joins that underlie the query. Views do not support the use of parameters. Views that join more than one table cannot be modified using INSERT, UPDATE, or DELETE statements. Views are called from Transact-SQL statements, and can also be used in *.scripts that are run in SQL Server Query Analyzer. SQL Server views and the SQL-92 standard do not support ORDER BY clauses in views.

Thanks all|||I'm in a similar position migrating complex Access queries to SQLServer2000.

I'm intending to code all the subordinate queries/views and to encapsulate these within a stored procedure that feeds user parameters to the relevant subordinate views.

However, using "View Designer" seems fine until you get to trying to use user-entered parameters - is there any way around this? ie. How can you introduce parameters into views?

Hoping someone can help me here.
Regards, Alex|||This only applies to the data itself:

I just did this. I used Access to export to SQL. Very easy to do and
if you want, I can send you my procedures. Oh heck, let me go and
find it and post it below..........

While using Access, export to SQL by:

1) Save as type: ODBC Database()
2) Export table to: same name CTR-C to copy name to buffer (youll need this later).
3) DSN: CTR-V, New
4) Data Source: SQL Server
5) Data Source Name: CTR-V, Next, Finish
6) Description: CTR-V
7) Server: Local (from drop down)
8) Authentication: NT
9) Default Database: yourdatabase (and not the default master)

That's it. Be sure to give permissions within SQL to tables etc.|||Updating of multiple tables in view can be done by instead of update trigger on view.
This trigger is run instead of operation and that is why it can be used to extract updates for single tables
from virtual inserted table.
See my post http://dbforums.com/showthread.php?threadid=640545
and BOL for more information about triggers (topic "CREATE TRIGGER").|||Bobby: Thanks for your response, I had problems exporting, but managed to solve my problems by using UDFs instead of Views which cannot accept any parameters. Essentially what I now have is a series of linked dependent UDFs (which generate their respective temp.tables), and then a stored procedure at the top level, which in turn communicates with my Coldfusion CFC (& then to Flash MX via Flash Remoting).
THis now seems to work fine!

Thanks again (also much indebted to Isapleny!).
Alex|||I'm glad to hear it's working. Have a great weekend (which is already
started for you).

BobbyJ

Converting Access database to SQL Server w/ flat files

I recently received a large Access database that I needed to do a lot
of analysis on. Since I prefer working with SQL Server (to put things
mildly) and I dislike writing VB/VBA code, I thought I'd share my
solution. I know someone will find this useful when searching the
archives.
There are probably much more efficient ways of doing this, but this
solution works well enough for me. There are three functions here that
simplify converting an Access schema to SQL Server and loading the data
into that schema. The first function generates all of the CREATE TABLE
statements for SQL Server. There may be some data types off the list,
since I only used the ones I needed for this conversion. Also, there
are no data contraints or indexes defined. The second dumps all the
data in all the tables to pipe-delimitted flat files and the third
generates all the BULK INSERT statements to load the data into SQL
Server. While I could've left the table names as they were and used
brackets or quotes, I hate writing them in my SQL, so I decided to
replace bad characters with underscores. I hope I've saved someone some
time here.
-Alan
'By Alan Samet 3/8/2006
'Use this code at your own risk. I claim no liability. If it works for
you and you share it
'please give me credit. If it messes things up, take the credit
yourself.
'
'There are three functions you need to use:
' GenerateSqlServerSchema -- creates the schema for SQL Server
' ExportFlatFiles -- creates tab-delimitted flat files to bulk insert
' GenerateBulkInsertStatements -- generates the BULK INSERT
statements
'
'All paths need to be fully-qualified. Where a directory is an
argument,
'don't append the ending backslash (\)
Public Function GetSqlServerDataType(accessDataType As Integer, size As
Integer) As String
Select Case accessDataType
Case dbInteger: '3
GetSqlServerDataType = "INT"
Case dbLong: '4
GetSqlServerDataType = "BIGINT"
Case dbSingle, dbDouble: '6, 7
GetSqlServerDataType = "FLOAT"
Case dbCurrency: '5
GetSqlServerDataType = "MONEY"
Case dbDate: '8
GetSqlServerDataType = "DATETIME"
Case dbText: '10
GetSqlServerDataType = "VARCHAR(" & size & ")"
Case Else
Err.Raise 0, , "Unrecognized Data Type: " & accessDataType
End Select
End Function
Function CleanName(name As String)
'Dim c As New Collection
'c.Add "_", "-"
'c.Add "_", " "
'c.Add "", "(R)"
'c.Add "_", "/"
'Dim s As Variant
CleanName = name
'For Each s In c
' CleanName = Replace(CleanName, s, c(s))
'Next
CleanName = Replace(CleanName, "-", "_")
CleanName = Replace(CleanName, " ", "_")
CleanName = Replace(CleanName, "(R)", "")
CleanName = Replace(CleanName, "/", "_")
CleanName = Replace(CleanName, "_&", "_And")
CleanName = Replace(CleanName, "1st", "First")
CleanName = Replace(CleanName, "2nd", "Second")
CleanName = Replace(CleanName, "3rd", "Third")
CleanName = Replace(CleanName, "4th", "Fourth")
End Function
Public Function GenerateSqlServerSchema(databasePath As String) As
String
Dim db As Database
Set db = OpenDatabase(databasePath)
'Dim types(50) As Boolean
Dim t As TableDef
Dim c As Field
Dim createTableSql As String
Dim fullScript As String
For Each t In db.TableDefs
If Left(t.name, 4) <> "MSys" Then
'Debug.Print t.Name
createTableSql = "CREATE TABLE " & CleanName(t.name) &
vbCrLf & "(" & vbCrLf
Dim bIsFirst As Boolean
bIsFirst = True
For Each c In t.Fields
If Not bIsFirst Then createTableSql = createTableSql &
","
bIsFirst = False
createTableSql = createTableSql & vbTab &
CleanName(c.name) & vbTab & GetSqlServerDataType(c.Type, c.size) &
vbCrLf
'types(c.Type) = True
Next
createTableSql = createTableSql & ")"
fullScript = fullScript & vbCrLf & vbCrLf & createTableSql
& vbCrLf & vbCrLf & "GO"
End If
Next
GenerateSqlServerSchema = fullScript
' Dim i As Integer
' For i = 0 To UBound(types)
' If types(i) Then Debug.Print i
' Next
End Function
Public Sub ExportFlatFiles(databasePath As String, rootExportPath As
String)
Dim db As Database
Set db = OpenDatabase(databasePath)
Dim t As TableDef
Dim i As Integer
For Each t In db.TableDefs
If Left(t.name, 4) <> "MSys" Then
Open rootExportPath & "/" & CleanName(t.name) & ".pipe" For
Output As #1
Dim values() As String
ReDim values(t.Fields.Count - 1) As String
With t.OpenRecordset(dbOpenForwardOnly)
While Not .EOF
For i = 0 To .Fields.Count - 1
values(i) = Nz(.Fields(i).Value, "")
Next
Print #1, Join(values, "|")
.MoveNext
Wend
End With
Close #1
End If
Next
db.Close
End Sub
Public Function GenerateBulkInsertStatements(databasePat
h As String,
rootExportPath As String) As String
Dim db As Database
Set db = OpenDatabase(databasePath)
Dim t As TableDef
For Each t In db.TableDefs
If Left(t.name, 4) <> "MSys" Then
GenerateBulkInsertStatements = GenerateBulkInsertStatements
& _
"RAISERROR('Loading " & CleanName(t.name) & "', 10,
10)" & vbCrLf & _
"BULK INSERT " & CleanName(t.name) & " FROM '" & _
rootExportPath & "\" & CleanName(t.name) & _
".pipe' WITH (ROWTERMINATOR='\n',FIELDTERMINATOR='|')
"
& vbCrLf
End If
Next
db.Close
End FunctionIs there a reason you didn't just use Data Transformation Services?
You could just create the package and if necessary even run
the package from your vbscript.
Why go through the trouble of doing all this manually?
Robbe Morris - 2004-2006 Microsoft MVP C#
Earn money answering .NET questions
http://www.eggheadcafe.com/forums/merit.asp
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1141871152.130312.288310@.i39g2000cwa.googlegroups.com...
>I recently received a large Access database that I needed to do a lot
> of analysis on. Since I prefer working with SQL Server (to put things
> mildly) and I dislike writing VB/VBA code, I thought I'd share my
> solution. I know someone will find this useful when searching the
> archives.
> There are probably much more efficient ways of doing this, but this
> solution works well enough for me. There are three functions here that
> simplify converting an Access schema to SQL Server and loading the data
> into that schema. The first function generates all of the CREATE TABLE
> statements for SQL Server. There may be some data types off the list,
> since I only used the ones I needed for this conversion. Also, there
> are no data contraints or indexes defined. The second dumps all the
> data in all the tables to pipe-delimitted flat files and the third
> generates all the BULK INSERT statements to load the data into SQL
> Server. While I could've left the table names as they were and used
> brackets or quotes, I hate writing them in my SQL, so I decided to
> replace bad characters with underscores. I hope I've saved someone some
> time here.
> -Alan
> 'By Alan Samet 3/8/2006
> 'Use this code at your own risk. I claim no liability. If it works for
> you and you share it
> 'please give me credit. If it messes things up, take the credit
> yourself.
> '
> 'There are three functions you need to use:
> ' GenerateSqlServerSchema -- creates the schema for SQL Server
> ' ExportFlatFiles -- creates tab-delimitted flat files to bulk insert
> ' GenerateBulkInsertStatements -- generates the BULK INSERT
> statements
> '
> 'All paths need to be fully-qualified. Where a directory is an
> argument,
> 'don't append the ending backslash (\)
> Public Function GetSqlServerDataType(accessDataType As Integer, size As
> Integer) As String
> Select Case accessDataType
> Case dbInteger: '3
> GetSqlServerDataType = "INT"
> Case dbLong: '4
> GetSqlServerDataType = "BIGINT"
> Case dbSingle, dbDouble: '6, 7
> GetSqlServerDataType = "FLOAT"
> Case dbCurrency: '5
> GetSqlServerDataType = "MONEY"
> Case dbDate: '8
> GetSqlServerDataType = "DATETIME"
> Case dbText: '10
> GetSqlServerDataType = "VARCHAR(" & size & ")"
> Case Else
> Err.Raise 0, , "Unrecognized Data Type: " & accessDataType
> End Select
> End Function
> Function CleanName(name As String)
> 'Dim c As New Collection
> 'c.Add "_", "-"
> 'c.Add "_", " "
> 'c.Add "", "(R)"
> 'c.Add "_", "/"
> 'Dim s As Variant
> CleanName = name
> 'For Each s In c
> ' CleanName = Replace(CleanName, s, c(s))
> 'Next
> CleanName = Replace(CleanName, "-", "_")
> CleanName = Replace(CleanName, " ", "_")
> CleanName = Replace(CleanName, "(R)", "")
> CleanName = Replace(CleanName, "/", "_")
> CleanName = Replace(CleanName, "_&", "_And")
> CleanName = Replace(CleanName, "1st", "First")
> CleanName = Replace(CleanName, "2nd", "Second")
> CleanName = Replace(CleanName, "3rd", "Third")
> CleanName = Replace(CleanName, "4th", "Fourth")
> End Function
> Public Function GenerateSqlServerSchema(databasePath As String) As
> String
> Dim db As Database
> Set db = OpenDatabase(databasePath)
> 'Dim types(50) As Boolean
> Dim t As TableDef
> Dim c As Field
> Dim createTableSql As String
> Dim fullScript As String
> For Each t In db.TableDefs
> If Left(t.name, 4) <> "MSys" Then
> 'Debug.Print t.Name
> createTableSql = "CREATE TABLE " & CleanName(t.name) &
> vbCrLf & "(" & vbCrLf
> Dim bIsFirst As Boolean
> bIsFirst = True
> For Each c In t.Fields
> If Not bIsFirst Then createTableSql = createTableSql &
> ","
> bIsFirst = False
> createTableSql = createTableSql & vbTab &
> CleanName(c.name) & vbTab & GetSqlServerDataType(c.Type, c.size) &
> vbCrLf
> 'types(c.Type) = True
> Next
> createTableSql = createTableSql & ")"
> fullScript = fullScript & vbCrLf & vbCrLf & createTableSql
> & vbCrLf & vbCrLf & "GO"
> End If
> Next
> GenerateSqlServerSchema = fullScript
> ' Dim i As Integer
> ' For i = 0 To UBound(types)
> ' If types(i) Then Debug.Print i
> ' Next
> End Function
> Public Sub ExportFlatFiles(databasePath As String, rootExportPath As
> String)
> Dim db As Database
> Set db = OpenDatabase(databasePath)
> Dim t As TableDef
> Dim i As Integer
> For Each t In db.TableDefs
> If Left(t.name, 4) <> "MSys" Then
> Open rootExportPath & "/" & CleanName(t.name) & ".pipe" For
> Output As #1
> Dim values() As String
> ReDim values(t.Fields.Count - 1) As String
> With t.OpenRecordset(dbOpenForwardOnly)
> While Not .EOF
> For i = 0 To .Fields.Count - 1
> values(i) = Nz(.Fields(i).Value, "")
> Next
> Print #1, Join(values, "|")
> .MoveNext
> Wend
> End With
> Close #1
> End If
> Next
> db.Close
> End Sub
> Public Function GenerateBulkInsertStatements(databasePat
h As String,
> rootExportPath As String) As String
> Dim db As Database
> Set db = OpenDatabase(databasePath)
> Dim t As TableDef
> For Each t In db.TableDefs
> If Left(t.name, 4) <> "MSys" Then
> GenerateBulkInsertStatements = GenerateBulkInsertStatements
> & _
> "RAISERROR('Loading " & CleanName(t.name) & "', 10,
> 10)" & vbCrLf & _
> "BULK INSERT " & CleanName(t.name) & " FROM '" & _
> rootExportPath & "\" & CleanName(t.name) & _
> ".pipe' WITH (ROWTERMINATOR='\n',FIELDTERMINATOR='|')
"
> & vbCrLf
> End If
> Next
> db.Close
> End Function
>|||Well, for one, I've never had any use for DTS. From my understanding,
you have to use that cumbersome GUI to make DTS packages. With the
exception of well-done tools like Interface Builder on OSX, I don't
like using a GUI to develop my applications -- especially when I can't
work with code. I've been able to do anything that DTS can do using SQL
Server Agent and script (preferably Python). The code that I just
posted only took a couple hours to write, so it really wasn't that much
work, aside from the unpleasantness of working with Access and VBA.
Also, in this situation, I don't want my SQL Server touching this
Access database. Since there are a lot of objects that I haven't looked
at in this database, I was running it on a virtual machine, pulling the
data out and then loading the data onto my development machine. This
method totally isolates the two.
-Alan|||No offense, but I have to agree with Robbe - DTS may have been a better
choice. The GUI tools reduce the time it takes to build stuff and
encapsulates all sorts of error checking etc. While it took a couple
of hours for you to write this manually, someone familiar with DTS
would probably accomplish the same result as the code posted in about 5
to 10 minutes. (And with fewer debugging steps - unless like most of
us you write perfect code all the time. LOL!)|||The Access Upsizing Wizard is also a real easy to use tool. The last
time I used it was probably 6 or 8 yrs ago... surely it is even better
now though. Some documentation here:
http://support.microsoft.com/defaul...B;EN-US;Q325017|||I do write perfect code all the time. It's why I dislike being forced
to use Microsoft's development tools so much.
-Alan

Converting Access cross-tab query to SQL Server

I have an Access cross-tab query that conditionally breaks up a field into multiple groups and averages the results in each group and I am having trouble doing this in SQL Server. The SQL Server query I have below works but does not find the averages by node A, B, RA, or RB.

Is there a better way to do this than what I have below?

How would I do this in SQL Server?

In the Access query I use:

right( [Serial No], iif( len([Serial No])-3>=0, len([Serial No])-3, len([Serial No]))

Table:

[Work Order No] [Serial No] [Parameter Name] [Type] [Min] [Max] [Value] [Pass/Fail]

M1000 001A "Test Name" "System1" 0 100 50 P

M1000 001B "Test Name" "System1" 0 100 40 P

M1000 002A "Test Name" "System1" 0 100 45 P

M1000 002B "Test Name" "System1" 0 100 70 P

M1000 002RA "Test Name" "System1" 0 100 30 P

M1000 002RB "Test Name" "System1" 0 100 20 P

M1001 001A "Test Name" "System1" 0 100 50 P

M1001 001B "Test Name" "System1" 0 100 30 P

The Query Output should be:

[Work Order No] [Node] [Min] [Max] [Avg Value]

M1000 A 0 100 47.5

M1000 B 0 100 55

M1000 RA 0 100 30

M1000 RB 0 100 20

M1001 A 0 100 50

M1001 B 0 100 30

Access Query:

TRANSFORM Avg([Value]) AS AvgOfValue

SELECT [Work Order No], [Min], [Max], RIGHT( [Serial No], iif(len([Serial No])-3>=0,

len([Serial No])-3, len([Serial No]))) AS [Node], max([DateTime]) as [MaxOfDateTime]

FROM [Inspection Header] INNER JOIN [Inspection Data]

ON [Inspection Header].[Work Order No] = [Inspection Data].[Work Order No]

AND [Inspection Header].[Serial No] = [Inspection Data].[Serial No]

WHERE [Parameter Name] = "Test Name" AND [Pass/Fail] = "P"

GROUP BY [Work Order No], [Min], [Max], RIGHT([Serial No],iif(len([Serial No])-3>=0,

len([Serial No])-3, len([Serial No]))), [Pass/Fail]

PIVOT [Parameter Name]

Incomplete SQL Server Query:

set ANSI_NULLS OFF

set QUOTED_IDENTIFIER OFF

GO

ALTER procedure [dbo].[dt_AvgTest]

as

begin

select b.[Work Order No], b.[TN], c.[PF], d.[Min], d.[Max]

from (((select a.[Work Order No]

, avg(case a.[Parameter Name] when "Test Name" then [Value] end) as [TN]

from dbo.[Inspection Data] as a group by a.[Work Order No]) b

inner join (select [Work Order No], min([Pass/Fail]) as PF

from dbo.[Inspection Data] group by [Work Order No] ) c on c.[Work Order No] = b.[Work Order No] )

inner join (select [Work Order No], [Min], [Max]

from dbo.[Inspection Data] where [Parameter Name] = "Test Name" group by [Work Order No], [Min], [Max] ) d

on d.[Work Order No] = c.[Work Order No] )

where c.[PF] = 'P'

end

Sam:

Is your server running SQL Server 2000 or SQL Server 2005?

Dave

|||SQL Server 2005|||

-- -
-- 1. I am guessing based on the proposed output data that
-- the "Serial No" field must be broken apart to get the
-- [Node data]. This is a poor choice for data design.
-- This means that this field is not "atomic" and if
-- possible should be divided into two separate columns.
-- 2. I am assuming here that the "Parameter Name" column
-- contains quote characters as part of the target data.
-- 3. I am assuming here that the "Type" column contains
-- quote characters as part of the target data.
-- 4. I have chosen to display the "average value" field as
-- a numeric (9,1) column. It is unclear what the
-- precision of this field needs to be based on the
-- proposed output; it is likely that this display
-- column needs adjustment.
-- 5. The use of the 10-Level REPLACE function to remove
-- the numeric portion of the "Serial No" field to
-- obtain the "Node" portion of this field is a guess
-- that the "numeric" portion of the field is NOT a part
-- of the "Node". If this guess is not correct then
-- an alternate method of obtaining this field is
-- necessary.
-- 6. The "Min" and "Max" field are aggregated as a
-- precaution in case these fields should ever be
-- different for different line items. If these are
-- not aggregated an additional line will be generated
-- whenever these items might otherwise differ.
--
-- Questions:
-- 1. Can the "Serial No" field be broken down into
-- two separate fields?
-- 2. What are the rules for obtaining the "Node" data?
-- 3. What is the output specification for the "Value"
-- field.
-- -

select [Work Order No],
left (replace(replace(replace(replace(replace(replace(replace
(replace(replace(replace
([Serial No],'0',''),'1',''),'2',''),'3',''),'4',''),
'5',''),'6',''),'7',''),'8',''),'9',''), 10) as [Node],
min ([Min]) as [Min],
max ([Max]) as [Max],
convert (numeric (9,1), avg (convert (numeric (9,1), [value])))
as [Value]
from [Inspection Data]
where [Pass/Fail] = 'P'
and [Parameter Name] = '"Test Name"'
group by [Work Order No],
left (replace(replace(replace(replace(replace(replace(replace
(replace(replace(replace
([Serial No],'0',''),'1',''),'2',''),'3',''),'4',''),
'5',''),'6',''),'7',''),'8',''),'9',''), 10)
order by [Work Order No],
left (replace(replace(replace(replace(replace(replace(replace
(replace(replace(replace
([Serial No],'0',''),'1',''),'2',''),'3',''),'4',''),
'5',''),'6',''),'7',''),'8',''),'9',''), 10)

-- -- Sample Output

-- Work Order No Node Min Max Value
-- - -- -
-- M1000 A 0 100 47.5
-- M1000 B 0 100 55.0
-- M1000 RA 0 100 30.0
-- M1000 RB 0 100 20.0
-- M1001 A 0 100 50.0
-- M1001 B 0 100 30.0

|||

This worked great!

You are right about breaking the Serial Number down into two separate fields.

Thank you very much.

Sam

Converting Access Code - FORMAT

Hello,
A colleague passed me this make-table query which works in Access, and we'd
like to automate in SQL. I did some basic debugging but now on parse, SQL
returns that Trim and Format are not recognized function nameS. I think you
can see the what formatting is doing to provide a standard string length.
Could someone advise what I need to do to get this to work in SQL? Any help
would be much appreciated. Thanks, Pancho
SELECT GVMOI2.TranDateSold, GVMOI2.CustomerID, GVMOI2.TaxIDNum,
GVMOI2.TaxIDType,
GVMOI2.ApplicationCode, GVMOI2.AccountNo,
Left("00000000000000000000",20-Len(GVMOI2.TraceNbr)) &
Trim(GVMOI2.TraceNbr) AS TraceNbrEdt, IIf(IsNumeric(GVMOI2.CreditAmtCash),
Left(Format([CreditAmtCash],"0000000000.00"),10) &
Right(Format([CreditAmtCash],"0000000000.00"),2),
"000000000000") AS CashCRFmt, IIf(IsNumeric(GVMOI2!DebitAmtCash),
Left(Format([DebitAmtCash],"0000000000.00"),10) &
Right(Format([DebitAmtCash],"0000000000.00"),2),
"000000000000") AS DebitAmtCashFmt,
IIf(IsNumeric(GVMOI2. CreditAmtChecks),Left(Format([CreditAmtC
hecks],"0000000000.00"),10) &
Right(Format([CreditAmtChecks],"0000000000.00"),2),"000000000000") AS
CreditAmtChecksFmt,
IIf(IsNumeric(GVMOI2. DebitAmtChecks),Left(Format([DebitAmtChe
cks],"0000000000.00"),10) &
Right(Format([DebitAmtChecks],"0000000000.00"),2),"000000000000") AS
DebitAmtChecksFmt,
GVMOI2.TranCode, GVMOI2.TranName, GVMOI2.TellerID, GVMOI2.BranchNo,
GVMOI2.CheckReferenceNbr,
GVMOI2.CheckNbr, GVMOI2.BankNumber, GVMOI2.Remitter1, GVMOI2.Payee1,
GVMOI2.ThirdParty,
"0000000000000000000000000" AS DenominationFmt, GVMOI2.IDType,
GVMOI2.IDNumber,
GVMOI2.IDIssueBy, GVMOI2.IDOthers
INTO MOI_Prep
FROM GVMOI2;Follow the below guidelines to convert the query.
1. & ==> +
2. iif(<condition>,<true>,<false> ) ==> case when <condition> then <true>
else <false> end
3. <table>!<column> ==> <table>.<column>
4. trim(<value> ) ==> rtrim(ltrim(<value> ))
-oj
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:92287E29-C913-43A2-8FA5-0C0B7441CBB6@.microsoft.com...
> Hello,
> A colleague passed me this make-table query which works in Access, and
> we'd
> like to automate in SQL. I did some basic debugging but now on parse, SQL
> returns that Trim and Format are not recognized function nameS. I think
> you
> can see the what formatting is doing to provide a standard string length.
> Could someone advise what I need to do to get this to work in SQL? Any
> help
> would be much appreciated. Thanks, Pancho
> SELECT GVMOI2.TranDateSold, GVMOI2.CustomerID, GVMOI2.TaxIDNum,
> GVMOI2.TaxIDType,
> GVMOI2.ApplicationCode, GVMOI2.AccountNo,
> Left("00000000000000000000",20-Len(GVMOI2.TraceNbr)) &
> Trim(GVMOI2.TraceNbr) AS TraceNbrEdt, IIf(IsNumeric(GVMOI2.CreditAmtCash),
> Left(Format([CreditAmtCash],"0000000000.00"),10) &
> Right(Format([CreditAmtCash],"0000000000.00"),2),
> "000000000000") AS CashCRFmt, IIf(IsNumeric(GVMOI2!DebitAmtCash),
> Left(Format([DebitAmtCash],"0000000000.00"),10) &
> Right(Format([DebitAmtCash],"0000000000.00"),2),
> "000000000000") AS DebitAmtCashFmt,
> IIf(IsNumeric(GVMOI2. CreditAmtChecks),Left(Format([CreditAmtC
hecks],"0000000000.00"),10)
> &
> Right(Format([CreditAmtChecks],"0000000000.00"),2),"000000000000") AS
> CreditAmtChecksFmt,
> IIf(IsNumeric(GVMOI2. DebitAmtChecks),Left(Format([DebitAmtChe
cks],"0000000000.00"),10)
> &
> Right(Format([DebitAmtChecks],"0000000000.00"),2),"000000000000") AS
> DebitAmtChecksFmt,
> GVMOI2.TranCode, GVMOI2.TranName, GVMOI2.TellerID, GVMOI2.BranchNo,
> GVMOI2.CheckReferenceNbr,
> GVMOI2.CheckNbr, GVMOI2.BankNumber, GVMOI2.Remitter1, GVMOI2.Payee1,
> GVMOI2.ThirdParty,
> "0000000000000000000000000" AS DenominationFmt, GVMOI2.IDType,
> GVMOI2.IDNumber,
> GVMOI2.IDIssueBy, GVMOI2.IDOthers
> INTO MOI_Prep
> FROM GVMOI2;
>
>|||OJ,
Thank you for these clear instructions. I will try these tomorrow. Looks
good!
Best Wishes,
P
"oj" wrote:

> Follow the below guidelines to convert the query.
> 1. & ==> +
> 2. iif(<condition>,<true>,<false> ) ==> case when <condition> then <true>
> else <false> end
> 3. <table>!<column> ==> <table>.<column>
> 4. trim(<value> ) ==> rtrim(ltrim(<value> ))
> --
> -oj
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:92287E29-C913-43A2-8FA5-0C0B7441CBB6@.microsoft.com...
>
>|||Hi oj,
I replaced & with +, ! with . on table.column names, and
rtrim(ltrim(<value> ) parses OK now.
Pls take a look at the revised script. I don't quite follow how to change
the iif to case when. I am getting an incorrect syntax error near THEN.
Please let me know what I'm doing wrong on the first CASE line and I'll fix
the rest the same way. Thanks! P
SELECT GVMOI2.TranDateSold, GVMOI2.CustomerID, GVMOI2.TaxIDNum,
GVMOI2.TaxIDType,
GVMOI2.ApplicationCode, GVMOI2.AccountNo,
Left("00000000000000000000",20-Len(GVMOI2.TraceNbr)) +
RTrim(LTrim(GVMOI2.TraceNbr)) AS TraceNbrEdt,
CASE WHEN (IsNumeric(GVMOI2.CreditAmtCash)) THEN
Left(Format([CreditAmtCash],"0000000000.00"),10) +
Right(Format([CreditAmtCash],"0000000000.00"),2), ELSE FALSE
"000000000000") AS CashCRFmt, IIf(IsNumeric(GVMOI2.DebitAmtCash),
Left(Format([DebitAmtCash],"0000000000.00"),10) +
Right(Format([DebitAmtCash],"0000000000.00"),2),
"000000000000") AS DebitAmtCashFmt,
IIf(IsNumeric(GVMOI2. CreditAmtChecks),Left(Format([CreditAmtC
hecks],"0000000000.00"),10) +
Right(Format([CreditAmtChecks],"0000000000.00"),2),"000000000000") AS
CreditAmtChecksFmt,
IIf(IsNumeric(GVMOI2. DebitAmtChecks),Left(Format([DebitAmtChe
cks],"0000000000.00"),10) +
Right(Format([DebitAmtChecks],"0000000000.00"),2),"000000000000") AS
DebitAmtChecksFmt,
GVMOI2.TranCode, GVMOI2.TranName, GVMOI2.TellerID, GVMOI2.BranchNo,
GVMOI2.CheckReferenceNbr,
GVMOI2.CheckNbr, GVMOI2.BankNumber, GVMOI2.Remitter1, GVMOI2.Payee1,
GVMOI2.ThirdParty,
"0000000000000000000000000" AS DenominationFmt, GVMOI2.IDType,
GVMOI2.IDNumber,
GVMOI2.IDIssueBy, GVMOI2.IDOthers
INTO MOI_Prep
FROM GVMOI2;
"oj" wrote:

> Follow the below guidelines to convert the query.
> 1. & ==> +
> 2. iif(<condition>,<true>,<false> ) ==> case when <condition> then <true>
> else <false> end
> 3. <table>!<column> ==> <table>.<column>
> 4. trim(<value> ) ==> rtrim(ltrim(<value> ))
> --
> -oj
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:92287E29-C913-43A2-8FA5-0C0B7441CBB6@.microsoft.com...
>
>|||There is no format() in sqlserver. You'd want to use convert() instead.
IIf(IsNumeric(GVMOI2.DebitAmtCash),
Left(Format([DebitAmtCash],"0000000000.00"),10) +
Right(Format([DebitAmtCash],"0000000000.00"),2),
"000000000000") AS DebitAmtCashFmt,
==>
case when IsNumeric(GVMOI2.DebitAmtCash)=1 then
right(convert(varchar,convert(money,1000
0000000+GVMOI2.DebitAmtCash)),13)
else replicate('0',10) AS DebitAmtCashFmt,
-oj
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:E2C8FF99-A473-4D32-9BB2-59AEFF996FC9@.microsoft.com...
> Hi oj,
> I replaced & with +, ! with . on table.column names, and
> rtrim(ltrim(<value> ) parses OK now.
> Pls take a look at the revised script. I don't quite follow how to change
> the iif to case when. I am getting an incorrect syntax error near THEN.
> Please let me know what I'm doing wrong on the first CASE line and I'll
> fix
> the rest the same way. Thanks! P
> SELECT GVMOI2.TranDateSold, GVMOI2.CustomerID, GVMOI2.TaxIDNum,
> GVMOI2.TaxIDType,
> GVMOI2.ApplicationCode, GVMOI2.AccountNo,
> Left("00000000000000000000",20-Len(GVMOI2.TraceNbr)) +
> RTrim(LTrim(GVMOI2.TraceNbr)) AS TraceNbrEdt,
> CASE WHEN (IsNumeric(GVMOI2.CreditAmtCash)) THEN
> Left(Format([CreditAmtCash],"0000000000.00"),10) +
> Right(Format([CreditAmtCash],"0000000000.00"),2), ELSE FALSE
> "000000000000") AS CashCRFmt, IIf(IsNumeric(GVMOI2.DebitAmtCash),
> Left(Format([DebitAmtCash],"0000000000.00"),10) +
> Right(Format([DebitAmtCash],"0000000000.00"),2),
> "000000000000") AS DebitAmtCashFmt,
> IIf(IsNumeric(GVMOI2. CreditAmtChecks),Left(Format([CreditAmtC
hecks],"0000000000.00"),10)
> +
> Right(Format([CreditAmtChecks],"0000000000.00"),2),"000000000000") AS
> CreditAmtChecksFmt,
> IIf(IsNumeric(GVMOI2. DebitAmtChecks),Left(Format([DebitAmtChe
cks],"0000000000.00"),10)
> +
> Right(Format([DebitAmtChecks],"0000000000.00"),2),"000000000000") AS
> DebitAmtChecksFmt,
> GVMOI2.TranCode, GVMOI2.TranName, GVMOI2.TellerID, GVMOI2.BranchNo,
> GVMOI2.CheckReferenceNbr,
> GVMOI2.CheckNbr, GVMOI2.BankNumber, GVMOI2.Remitter1, GVMOI2.Payee1,
> GVMOI2.ThirdParty,
> "0000000000000000000000000" AS DenominationFmt, GVMOI2.IDType,
> GVMOI2.IDNumber,
> GVMOI2.IDIssueBy, GVMOI2.IDOthers
> INTO MOI_Prep
> FROM GVMOI2;
>
> "oj" wrote:
>|||Thanks again oj. I have marked both of your posts as helpful and will try
this code.
Best Regards,
p
"oj" wrote:

> There is no format() in sqlserver. You'd want to use convert() instead.
> IIf(IsNumeric(GVMOI2.DebitAmtCash),
> Left(Format([DebitAmtCash],"0000000000.00"),10) +
> Right(Format([DebitAmtCash],"0000000000.00"),2),
> "000000000000") AS DebitAmtCashFmt,
> ==>
> case when IsNumeric(GVMOI2.DebitAmtCash)=1 then
> right(convert(varchar,convert(money,1000
0000000+GVMOI2.DebitAmtCash)),13)
> else replicate('0',10) AS DebitAmtCashFmt,
>
> --
> -oj
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:E2C8FF99-A473-4D32-9BB2-59AEFF996FC9@.microsoft.com...
>
>|||oj,
This is getting closer but now I get an incorrect syntax near the word AS on
the last line of the section below:
SELECT GVMOI2.TranDateSold, GVMOI2.CustomerID, GVMOI2.TaxIDNum,
GVMOI2.TaxIDType,
GVMOI2.ApplicationCode, GVMOI2.AccountNo,
Left("00000000000000000000",20-Len(GVMOI2.TraceNbr)) +
RTrim(LTrim(GVMOI2.TraceNbr)) AS TraceNbrEdt,
CASE WHEN IsNumeric(GVMOI2.CreditAmtCash)=1 THEN
Right(Convert(varchar,convert(money,1000
000000000+GVMOI2.CreditAmtCash)),13)
ELSE
Replicate('0',13) AS CashCRFmt,
It looks like they want to name the column CreditAmtCash when there is a
numeric value and name it CashCRFmt when the value is non-numeric. Let me
know if I am using Replicate correctly.
Thanks,
p
"oj" wrote:

> There is no format() in sqlserver. You'd want to use convert() instead.
> IIf(IsNumeric(GVMOI2.DebitAmtCash),
> Left(Format([DebitAmtCash],"0000000000.00"),10) +
> Right(Format([DebitAmtCash],"0000000000.00"),2),
> "000000000000") AS DebitAmtCashFmt,
> ==>
> case when IsNumeric(GVMOI2.DebitAmtCash)=1 then
> right(convert(varchar,convert(money,1000
0000000+GVMOI2.DebitAmtCash)),13)
> else replicate('0',10) AS DebitAmtCashFmt,
>
> --
> -oj
>
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:E2C8FF99-A473-4D32-9BB2-59AEFF996FC9@.microsoft.com...
>
>