Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unhappy Unanswered: 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.

    Code:
    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]));
    Last edited by desireemm; 03-21-07 at 16:54.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Which bit are you struggling with? How far have you got?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Hi Pootle thanks for replying

    The format$ is the part thats giving me fits sQL server doesnt recognize that function

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    and of course access doesn't know the difference between HAVING and WHERE, and in this case, you need WHERE, not HAVING
    Uh? How can it not know the difference? Are you confusing access and access developers?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, access itself generates that crap
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Talking Action Query

    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
    Code:
    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??
    Last edited by desireemm; 04-15-09 at 17:23.

  9. #9
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    So Far this is what I go

    Here is what I have so far but I need to do this by month too they get a pretty detailed report


    Code:
    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)

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  11. #11
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Hi Pat

    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'."


    Code:
    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

  12. #12
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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....

  13. #13
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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...

  14. #14
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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]

  15. #15
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Hi Aschk

    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

    Code:
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near ','.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •