Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2011
    Posts
    6

    Unanswered: several queries in access 2007

    I am new to working with access 2007 but I do have experience working with sql server 2008 r2. I am having trouble trying to understand the chain of queries that the report calls. Is the various queries like a left outer join? Are all the queries executed?

    Can you explain the queries listed below to me?

    1. The report calls the following query.

    SELECT [A3].PO_NAME, [A3].PO_ID, [A3].LAST_NAME, [A3].FIRST_NAME, [A3].PHONE, [A3].PHONE_EXT, [A3].[TR Code], [A3].SumOfCount, [A3].[Batch ID], [A3].Submitter, [A3].[Create Date], [A3].[TRC Category], [Reject Categories].Explanation, [A3].[Batch Size], [A3].[TR Code Description]
    FROM ([A3] LEFT JOIN Sub ON [A3].Submitter = Sub.racfid) LEFT JOIN [Reject Categories] ON [A3].[TRC Category] = [Reject Categories].Field1
    WHERE ((([A3].SumOfCount)>9));

    2. The A3 query listed above is the following and calls another query called A2
    SELECT [A2].PO_NAME, [A2]._ID, [A2].LAST_NAME, [A2].FIRST_NAME, [A2].PHONE, [A2].PHONE_EXT, [A2].[Batch ID], [A2].[TR Code], Sum([A2].Count) AS SumOfCount, [A2].[Create Date], [A2].Submitter, [A2].[TRC Category], [A2].[Batch Size], [A2].[TR Code Description], [A2].Category
    FROM [A2]
    GROUP BY [A2].PO_NAME, [A2].PO_ID, [A2].LAST_NAME, [A2].FIRST_NAME, [A2].PHONE, [A2].PHONE_EXT, [A2].[Batch ID], [A2].[TR Code], [A2].[Create Date], [A2].Submitter, [A2].[TRC Category], [A2].[Batch Size], [A2].[TR Code Description], [A2].Category
    HAVING (((Sum([A2].Count))>9));

    3. The following is the A2 query
    SELECT [A1].*, [All Reject TRCs].Category AS [TRC Category]
    FROM [A1] INNER JOIN [All Reject TRCs] ON [A1].[TR Code] = [All Reject TRCs].[TRC Code]
    WHERE ((([All Reject TRCs].Category)<>"valid"));

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    stored queries in msaccess are equivalent to views in sqlserver
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2011
    Posts
    6

    stored procedure in access

    What can you do in access 2007 to see the stored procedures?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    msaccess has stored procedures?? whoa!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2011
    Posts
    6

    access 2007

    In access I meant to say stored queries? Are the stored queries the 'queries' I see in the navigation pane? If not, can you tell me what the stored queries are and how to access them?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a stored query is a query that has been stored and (usually visible) in the queries tab
    you can also manipuate the queries object (querydef) and add, modify or delete queries via vba code

    Access can use stored procedures assuming your data is stored in a db which uses stored procedures. Access when talking to its default database storage mechanism (JET) knows nothing of stored procedures
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2011
    Posts
    6

    thanks!

    thanks alot1

Posting Permissions

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