Results 1 to 2 of 2
  1. #1
    Lipi is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    1

    Multiple outer joins - error message

    Dear access forums,

    I'm in a very high level of emergency. I have to finish a report system for tomorrow, and yet, just after beginning it, I've ran into a problem.

    I have something a bit similar to:

    ------------------
    Table 1: Shops /
    ----------------


    - ID
    - Name
    - Type
    ------------------

    ------------------
    Table 2: Trades /
    -----------------
    - ID
    - Date
    - Shop_ID
    - Amount (currency)
    ------------------

    I want to make a query, that takes data from both tables, lists all Shops even ones without any trades in the given month.
    And well, that's what doesn't work: I want to only sum trades from a given month. I'm using an outer join for that:

    Code:
    SELECT Shops.Name
    FROM Shops LEFT JOIN Trades ON Shops.ID = Trades.Shop_ID AND
     ON Trades.Date=[When?]
    ORDER BY Shops.name;
    But that doesn't seem to work. It keeps telling me:
    Syntax error (missing operator) in query expression ".

    Thank you very much for your help in advance!

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Code:
    SELECT Shops.Name,sum(trades.amount) as sumtrade FROM Shops LEFT JOIN Trades ON Shops.ID = Trades.Shop_ID where Trades.Date=[When?]group by shops.name ORDER BY Shops.name;

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Ambiguous Outer Join Error
    By scruiks in forum Queries
    Replies: 3
    Last Post: 07-18-2010, 05:06 PM
  2. Error message when importing
    By ljenkins23 in forum Access
    Replies: 5
    Last Post: 06-10-2010, 09:30 AM
  3. error message
    By ngeng4 in forum Forms
    Replies: 85
    Last Post: 03-25-2010, 06:47 AM
  4. Error Message in Access2007
    By Storm91 in forum Access
    Replies: 3
    Last Post: 02-18-2010, 02:56 PM
  5. If / Then Error Message
    By Schwagr in forum Forms
    Replies: 4
    Last Post: 03-30-2006, 06:28 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums