Migrating from Access to SQL

Hi guys, its been awhile. I inherited a database thats an MDB and I need to get it on to sql server but I am having problem with this query, can anyone help please. Its suppose to give you the month and year when you click on it, you just put that in and up come the results on a query or report, now I have something similar to this but the code is very different.

SELECT Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Action Type], Sum([Main Table].[Action Type]) AS [Main Table_Action Type]
FROM [Action Type] INNER JOIN [Main Table] ON [Action Type].ID = [Main Table].[Action Type]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Action Type]
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the month and the Year]));Which bit are you struggling with? How far have you got?|||The format$ is the part thats giving me fits sQL server doesnt recognize that function|||try this --SELECT DATEPART(year,[Main Table].[Date]) AS [Date By Year]
, DATEPART(month,[Main Table].[Date]) AS [Date By Month]
, [Main Table].[Action Type]
, Sum([Main Table].[Action Type]) AS [Main Table_Action Type]
FROM [Action Type]
INNER
JOIN [Main Table]
ON [Action Type].ID = [Main Table].[Action Type]
WHERE [Main Table].[Date] >= '2007-03-01' /* first day of this month */
AND [Main Table].[Date] < '2007-03-01' /* first day of next month */
GROUP
BY DATEPART(year,[Main Table].[Date])
, DATEPART(month,[Main Table].[Date])
, [Main Table].[Action Type]note you could concatenate those two datepart expressions if you need to

also note the index-friendly method of extracting rows for a certain month (anything else will involve table scans)

and of course access doesn't know the difference between HAVING and WHERE, and in this case, you need WHERE, not HAVING|||and of course access doesn't know the difference between HAVING and WHERE, and in this case, you need WHERE, not HAVINGUh? How can it not know the difference? Are you confusing access and access developers?|||no, access itself generates that crap|||Only if you don't add a second column into your query designer and make that a "Where".

But yes - Access does not encourage thoughtful & efficient query design.|||Thanks guys its just that the query he created gives you a dialog box "like the edit parameters" box and you can put the month and year in yourself and it generates a report. Now I have one for searching my subforms last name field ALTER FUNCTION dbo.SearchLName
(@.Enter_SubjLastName nvarchar(100))
RETURNS TABLE
AS
RETURN ( SELECT dbo.Report.[Incident Report No], dbo.Subjects.SubjFirstName, dbo.Subjects.SubjLastName, dbo.Report.TypeOfIncident
FROM dbo.Report INNER JOIN
dbo.Subjects ON dbo.Report.[Incident Report No] = dbo.Subjects.[Incident number]
WHERE (dbo.Subjects.SubjLastName = @.Enter_SubjLastName) )

This is a function and you enter in the last name and it generate a report for you, now from what I see hes doing the same thing but with Month and Year, does that make sense??|||Here is what I have so far but I need to do this by month too they get a pretty detailed report

SELECT DATEPART(year, dbo.[Main Table].Date) AS [Date By Year], DATEPART(month, dbo.[Main Table].Date) AS [Date By Month],
dbo.[Main Table].[Action Type], SUM(dbo.[Main Table].[Action Type]) AS [Main Table_Action Type]
FROM dbo.[Action Type] INNER JOIN
dbo.[Main Table] ON dbo.[Action Type].ID = dbo.[Main Table].[Action Type]
GROUP BY DATEPART(year, dbo.[Main Table].Date), DATEPART(month, dbo.[Main Table].Date), dbo.[Main Table].[Action Type]
HAVING (DATEPART(year, dbo.[Main Table].Date) = @.Enter_Date)|||Create an MS-Access form with two date controls, an Ok and a Cancel button on it When the user clicks Ok, validate the data in the controls, then use them to call the stored procedure.

-PatP|||The results would have to show in a report, I'm so frustrated, this is what I got so far but its spitting out an error message "Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'Main Table'."

SELECT Year(3/01/2001)([Main Table].[Date],'yyyy') AS [Date By Year], [Main Table].[Violation Type], Sum([Main Table].[Loss])
AS [Sum Of Loss], Count([Main Table].[Violation Type])
AS [CountOfViolation Type]
FROM [Main Table]
GROUP BY Year(3/01/2001)([Main Table].Date,'yyyy'), [Main Table].[Violation Type], Year([Main Table].[Date])
HAVING ((Year()([Main Table].Date)=[Please Enter the Year]))
GO|||You'll have to excuse me, but can we go back a few posts to that first query. Why are we doing an inner join??? I can see no reason for it because we're not bringing back any results in our resultset from the join table...|||SELECT
DATEPART(year, dbo.[Main Table].Date) AS [Date By Year],
DATEPART(month, dbo.[Main Table].Date) AS [Date By Month],
SUM(dbo.[Main Table].[Action Type]) AS [Main Table_Action Type]
FROM dbo.[Action Type]
INNER JOIN
dbo.[Main Table] ON dbo.[Action Type].ID = dbo.[Main Table].[Action Type]
WHERE
(DATEPART(year, dbo.[Main Table].Date) = @.Enter_Date)
GROUP BY
DATEPART(year, dbo.[Main Table].Date), DATEPART(month, dbo.[Main Table].Date), dbo.[Main Table].[Action Type]

Incidently i'm not seeing a need for the inner join above...|||Quick guess :

SELECT
([Main Table].[Date],'yyyy') AS [Date By Year],
[Main Table].[Violation Type], Sum([Main Table].[Loss]) AS [Sum Of Loss],
Count([Main Table].[Violation Type]) AS [CountOfViolation Type]
FROM [Main Table]
WHERE DATEPART(year,[Main Table].Date)=@.YEAR
GROUP BY DATEPART(year,[Main Table].Date), [Main Table].[Violation Type]|||I inherited this database it was an MDB and I'm trying to convert it to an ADP I ran that select statement in the query analyzer and this is the error message i'm gettting

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.|||Hold on, i need some clarity here for piece of mind, to make sure that i'm working with the right thing :

Are you running these queries in MSSQL or MSAccess ?|||Found my error :

SELECT
DATEPART(year,[Main Table].[Date]) AS [Date By Year],
[Main Table].[Violation Type], Sum([Main Table].[Loss]) AS [Sum Of Loss],
Count([Main Table].[Violation Type]) AS [CountOfViolation Type]
FROM [Main Table]
WHERE DATEPART(year,[Main Table].Date)=@.YEAR
GROUP BY DATEPART(year,[Main Table].Date), [Main Table].[Violation Type]|||I am running them in MSSQL, I imported the tables over from Access 2003 onto SQL server. The user set up his reports a certain way and since Jet SQL and SQL Server dont exactly speak the same language I have to redo allot of the queries. Does that make sense??|||Why are we doing an inner join??? I can see no reason for it because we're not bringing back any results in our resultset from the join table...the necessity for doing an inner join is not always predicated on returning something from the joined table

sometimes it is just those rows which have a match that you are interested in

example: say you are the HR department and you have an employees table

suppose you needed to store the email address of their parole officer (let's say to email them confirmation of the employee's attendance)

not every employee has a parole officer, check? so you don't keep the parole officer's email in the employees table (it would need to be NULL for most employees), you keep it in a separate table (incidentally, this is another example of proper normalization, it's 4nf i believe)

now you want to produce a list of employee names and departments for all employees who have a parole officer, but you don't actually need the parole officer's email address

what does your query look like?

that's right, an inner join, selecting only from the left table

:)|||See this the query he set up in Access, if you go into the [Main table] down where the field properties are, he has made one of the fields a combo box and the rowsource is that table [Action Type], and Violation Type is another table that also linked to it. Does that make sense??
SELECT DISTINCTROW Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Violation Type], Sum([Main Table].Loss) AS [Sum Of Loss], Count([Main Table].[Violation Type]) AS [CountOfViolation Type], [Action Type].[Action Type]
FROM [Action Type] INNER JOIN [Main Table] ON [Action Type].ID = [Main Table].[Action Type]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Violation Type], [Action Type].[Action Type], Year([Main Table].Date)*12+DatePart('m',[Main Table].Date)-1
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the month and year]));

Migrating AS Repository Advantages

Is there any performance gain on migrating Analysis Services Repository from
.mdb to SQL Server 2000 database ?IMHO the repository will be more manageable, more robust and more secure.
I personally create a seperate DB for the repository i.e. AS_REP
The default is to migrate to MSDB. The problem with that being your jobs
and DTS packages amongst other things are also in there.
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Sws" <anonymous@.discussions.microsoft.com> wrote in message
news:50311460-D284-46A9-BE92-8A1B172C5F7A@.microsoft.com...

quote:

---

> Is there any performance gain on migrating Analysis Services Repository

---

from .mdb to SQL Server 2000 database ?|||synchonizing backups of the repository and the data folder are much easier if you use a
dedicated database. For more information, see the Analysis Services Operations Guide a
nd the Analysis Services Performance Guide at /url]
/bianalysis.asp
Posted using Wimdows.net NntpNews Component - Posted from SQL Servers Largest Community
Website: [url]http://www.sqlJunkies.com/newsgroups/" target="_blank">www.microsoft.com/sql/eveluation/bi...com/newsgroups/

Migrate Access 2002 to SQL Express 2005

Hello
I'm trying to migrate my access mdb (XP version) to SqlExpress 2005. I use Updating wizzard from Access and i get an error saying that my sql server is not compatible and that i have to use SQL 6,5 SP 5 or later. What i'm doing wrong?The best way to migrate your data would be to use the new integration services in sql server 2005. However, I'm not sure what Express edition offers in this way.|||I believe that you are right but this feature is not supported in SQL Express
Thanks|||Well you can download demo version of the full sql server 2005. Perhaps you should look at purchasing the developer version or an MSDN subscription to get the tools you need.|||This is possible but not in the default configuration. See the blog post at http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx for more info on how to configure to do this. Thanks< MJ

Microsoft SQL Server Migration Assistant for Access Fails to Migrate Data

I attempted to use SSMA to migrate a 2000 file format .mdb into SQL Server Express (and, as I've just purchased VS2005 Pro, into SQL Server Developer Edition).

The file is actually a backend, so nothing to migrate other than tables.

After several runs during which I received (and fixed up) some errors, the process runs smoothly with no errors or warnings, until it comes to the last step; migrating the data itself.

Data migration into 'parent' tables works fine. However, wherever I have data in a table with a foreign key relationship to any of the aforementioned 'parent' tables, it refuses to migrate it. The text of the error message will be very smiliar to the following (from the log):

[Datamigrator: Error] [464/7] [2007-09-23 14:18:54]: Exception: The INSERT statement conflicted with the FOREIGN KEY constraint "Branch$CompanyBranch". The conflict occurred in database "DPMTest", table "dbo.Company", column 'CompanyID'.

There is nothing wrong, so far as I can determine, with the relationships involved.

I can insert data into the tables using any of the following methods:

1. Directly in Access, in the source backend.

2. Using the original Access frontend application, attached to the source backend.

3. Using VB.Net forms I am developing, in the 'upsized' database.

4. Directly in the Management Studio, in the 'upsized' database.

None of these four methods complains in the least about the relationships which SSMA balks at.

I would be grateful for any ideas....thanks.

The error is self explanatory...

You are inserting companyID that are not there in the master table. Just run a select some thing like this

Select *from ThisTableName where CompanyID not In (select companyID from ParentComapnyTableName)

You will get few rows. this needs to be deleted otherwise you have to correct the data.

Madhu

|||

As I said, I can insert data via all other methods. I am not inserting CompanyID where it does not exist in the master table. There is only one CompanyID at this point, whose ID is '1'. If I add a Branch with CompanyID of '1' in the Access source database which, again, doesn't complain about the relationship - because there is no problem with the relationship, or the data I'm inserting - the Migration Assistant throws the abovementioned error.

I appreciate your response, but I feel you didn't comprehend my original post. Thanks, anyway.

|||I have "successfully" migrated MDB files only to find out that SQL and Visual Studio balks at them. Problems with constraints, views, and other stuff.

Microsoft SQL Server Migration Assistant for Access Fails to Migrate Data

I attempted to use SSMA to migrate a 2000 file format .mdb into SQL Server Express (and, as I've just purchased VS2005 Pro, into SQL Server Developer Edition).

The file is actually a backend, so nothing to migrate other than tables.

After several runs during which I received (and fixed up) some errors, the process runs smoothly with no errors or warnings, until it comes to the last step; migrating the data itself.

Data migration into 'parent' tables works fine. However, wherever I have data in a table with a foreign key relationship to any of the aforementioned 'parent' tables, it refuses to migrate it. The text of the error message will be very smiliar to the following (from the log):

[Datamigrator: Error] [464/7] [2007-09-23 14:18:54]: Exception: The INSERT statement conflicted with the FOREIGN KEY constraint "Branch$CompanyBranch". The conflict occurred in database "DPMTest", table "dbo.Company", column 'CompanyID'.

There is nothing wrong, so far as I can determine, with the relationships involved.

I can insert data into the tables using any of the following methods:

1. Directly in Access, in the source backend.

2. Using the original Access frontend application, attached to the source backend.

3. Using VB.Net forms I am developing, in the 'upsized' database.

4. Directly in the Management Studio, in the 'upsized' database.

None of these four methods complains in the least about the relationships which SSMA balks at.

I would be grateful for any ideas....thanks.

The error is self explanatory...

You are inserting companyID that are not there in the master table. Just run a select some thing like this

Select *from ThisTableName where CompanyID not In (select companyID from ParentComapnyTableName)

You will get few rows. this needs to be deleted otherwise you have to correct the data.

Madhu

|||

As I said, I can insert data via all other methods. I am not inserting CompanyID where it does not exist in the master table. There is only one CompanyID at this point, whose ID is '1'. If I add a Branch with CompanyID of '1' in the Access source database which, again, doesn't complain about the relationship - because there is no problem with the relationship, or the data I'm inserting - the Migration Assistant throws the abovementioned error.

I appreciate your response, but I feel you didn't comprehend my original post. Thanks, anyway.

|||I have "successfully" migrated MDB files only to find out that SQL and Visual Studio balks at them. Problems with constraints, views, and other stuff.