Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2003
    Location
    Portugal
    Posts
    111

    Unanswered: A newbie Query/SQL question

    Another way to see a query is by SQL code.

    I saw this in view sql mode:

    SELECT AFazer.NDossier, Count(AFazer.NDossier) AS N, AFazer.Feito
    FROM AFazer
    GROUP BY AFazer.NDossier, AFazer.Feito
    HAVING (((AFazer.Feito)<>-1));

    What can I do with this, instead using the normal query? If I can use this code, where can I put it? Is it ready to go?

    I'm sorry for this biggg ignorance... I never used this and here we can have the best understanding...

    Thank you

    Jose Ruas

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Exclamation SQL

    SQL stands for standard query languages

    the code you see is SQL this is use by the server /computer to excecute your question/request

    the other page u see is an graphical view off SQL for some its easyer to understand if they dont know the basic SQL syntax

    u can use the code "SELECT a, b FROM x WHERE z GROUP BY a ORDER BY b"

    in the desing mode off "in this case Access" (aka : Visual Basic )

  3. #3
    Join Date
    Jul 2003
    Location
    Portugal
    Posts
    111

    Re: SQL

    Thank you for your answer, but I expected a more practical answer.

    Anyway, I appreciated your mess.

    Ruas

  4. #4
    Join Date
    Nov 2003
    Location
    Newcastle upon Tyne
    Posts
    17
    If you want to do group by in the Query Builder, you need to click the (sigma??) totals icon on the toolbar, then you'll be able to put group by in the columns you desire, and max/min/avg/whatever in the other columns.

    You should practice to see how grouping works - took me a while to get just the basics.

  5. #5
    Join Date
    Jul 2003
    Location
    Portugal
    Posts
    111
    I would like to know if using the SQL code generated by the Query I can use it instead the query.

  6. #6
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Jmruas: As quoted from Marvel's post, the SQL code is the query (Structured QUERY Language). You can't use the SQL code instead of itself.

    Are you asking how to use this in VBA code? I can't tell, your post basically asks if you can eat a banana instead of a banana.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  7. #7
    Join Date
    Nov 2002
    Posts
    49
    jmruas,

    I'm not sure if what you are asking is if you can code a SQL satement instead of using the query. If that is your question, the answer is yes. here is a snippet of cade from a project I'm working on right now:

    Dim rst As Recordset
    Dim strsql As String
    Dim Answer As String
    strsql = "Select * from tblSecurity where EmployeeID = "
    strsql = strsql & "'" & TxtEmployeeID & "'"
    If (IsNull(TxtEmployeeID) Or TxtEmployeeID = "") Then
    msgbox "You must enter in your Employee ID number"
    TxtEmployeeID.SetFocus
    ElseIf (IsNull(TxtPassword) Or TxtPassword = "") Then
    msgbox "you must enter your password"
    TxtPassword.SetFocus
    Else
    Set rst = CurrentDb.OpenRecordset("tblSecurity")
    rst.Index = "primarykey"
    rst.Seek "=", TxtEmployeeID
    If rst.NoMatch Then
    msgbox "Your ID is not found in the system. Please contact the Shift Supervisor for access to the system or use 'GST1111A' and 'Password' as your login."
    TxtEmployeeID = Null
    TxtPassword = Null
    TxtEmployeeID.SetFocus
    Exit Sub
    Else
    Set rst = CurrentDb.OpenRecordset(strsql, dbopensnapshot)
    If rst(3) <> TxtPassword Then
    msgbox "The password you entered is not correct. Please try again."
    TxtPassword.SetFocus
    Exit Sub
    Else
    SecLevel = rst(4)
    UserName = rst(0)
    DoCmd.Close
    DoCmd.OpenForm "switchboard"
    End If
    If rst(7) = True Then
    DoCmd.OpenForm "frmchangepassword", , , , , acDialog
    End If
    End If
    End If

    I prefer this method of querying vs the query function in Access since I can change the query depending on criteria and not have to design a new query. If this is what you are looking for, I would suggest you purchase a book on VBA for Access. The book will help you with writing and/or understanding code. Keep in mind that if you have no programming experience, you may get very frustrated, but the end results are usually well worth the hassle.

  8. #8
    Join Date
    Jul 2003
    Location
    Portugal
    Posts
    111
    Oh my God! You (all of you) are wonderfull to help and to try explain how things are. Thank you.

    The most important to have the right anwser is to make the right question. And here I didn't make a good job. I'm sorry.

    When creating a query by form or sellection (I think that's the name - we see tables and fields in a box) we can see in the View menu an option: SQL View. If choosen, we can see only something like this:
    SELECT Arquivo.ndossier, Count(Arquivo.ndossier) AS N
    FROM Arquivo
    GROUP BY Arquivo.ndossier;

    1. What for is this code generated?
    2. What can I do with it?
    3. Where put this code (it doesn't seem to work as is in the procedures)

    I thinked that using this code it was not necessary to use query by form.

    Well, this is my question. THANK YOU.

    Ruas

  9. #9
    Join Date
    Dec 2002
    Location
    Columbus, GA
    Posts
    81
    SQL is another type of language. It is just there as another type of wuery for those who understand that langauge. I use SQL on website sbut not in Access. It is not something you need to worry about. Some applications only understand SQL langaunge and in that case, you could go to the SQL view as you did and just copy whats there and past it into whatever application uses that code.

  10. #10
    Join Date
    Nov 2003
    Posts
    4
    When you create a query in design view, Access generates SQL code for you, which it then uses to generate your query results. Most of the time you don't need to do anything else, but there are many things that you can do with SQL which you can't do using Access query design view.

    SQL is a very powerful language used by a great many database applications. If you want to find out about SQL, there are many resources on the internet (search for "learn SQL" or something similar). You could also use MSAccess help system (put "select statement" in the search box)

  11. #11
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    I would definitely caution you about trying to learn SQL from the Access SQL generator. It's a good starting point to learn the structure of
    SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, but you'll find that it's inefficent in creating table aliases unless you specifically do that through the QBE. It also allows for Access functions such as IsNull instead of IS NOT NULL. There are also Subselects you can do with ANSI SQL that Access cannot handle.

    The most common usage I get out of the SQL window is to speed up the process for creating statements for recordsets and to do UNION queries.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  12. #12
    Join Date
    Nov 2003
    Posts
    1,487
    Heh...

    You can use a SQL statement in the ControlSource Property of a form, report or control. You can also use these statements withing the RowSource property of objects (ie: controls) that contain this property.

Posting Permissions

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