Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    12

    Unanswered: help in temp tables

    how do i put the result of this query in a temp table ? and how do i create the temp table etc.. all help needed on the temp table

    SELECT DISTINCT Client.id
    FROM Client
    WHERE Client.Active='YES' AND Client.id NOT IN (SELECT Client_ID_Monthpayed FROM MonthPayed Where Month_Payed=Insert_Month);

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No such thing as temp tables in Access. You can create a table easily enough, though there are all sorts of difficulties you could run in to:
    Code:
    SELECT DISTINCT Client.id INTO myTable
    FROM Client
    WHERE Client.Active='YES' AND Client.id NOT IN (SELECT Client_ID_Monthpayed FROM MonthPayed Where Month_Payed=Insert_Month);
    It might be worth telling us your problem, as there is very possibly a better solution.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2009
    Posts
    12
    the goal is getting the sum(account.price) for all the clients but i get duplicates values, so the sum wont be correct, thats why i use DISTINCT, whats the solution for this common problem btw DISTINCT and sum()

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - in that case you don't need a temporary table. What is your query as it stands to perform the SUM()? We can sort out the SQL to do everything in one go.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2009
    Posts
    12
    SELECT sum(account.price)
    FROM Client, Account
    WHERE Client.Accountspeed = Account.speed AND Client.Active='YES' AND Client.id NOT IN (SELECT Client_ID_Monthpayed FROM MonthPayed Where Month_Payed=Insert_Month);

    it is working now, i dont know how lol, i must have changed the order of something in the sql im getting a correct sum without duplicates

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT sum(account.price)
    FROM (SELECT DISTINCT Client.id
    FROM Client
    WHERE Client.Active='YES' AND NOT EXISTS (SELECT * FROM MonthPayed Where Month_Payed=Insert_Month AND Client.id = Client_ID_Monthpayed)) AS clients 
    INNER JOIN 
    Account 
    ON Clients.Accountspeed = Account.speed
    Do you really have duplicate client ids in you client table?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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