Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    17

    Unanswered: Trouble with my SQL Count query

    I'm working on an SQL query in access 2000 which will count the number of images i have stored for each record in the database.I found that i could not link this table with the main one unless there was at least one field in the table for every program_Id in the main table. Theirfore i created placeholders which i will take out later through automation as new records are input. The problem is that with the Where condition i put in to filter out the placeholders, it's not returning 0 for programs which have no images, it's not including them. Here are the specs:

    One table:
    program_picture_info which contains all of the information i require.

    Program_ID: One key which contains a non unique program ID. All pictures for that program will have the same ID.

    Picture_ID: A unique generated autonumber which will contain a picture ID number.

    Description: A small text field which contains a note about that image.
    imagepath_filename: Contains the file path to the image.

    Here is my SQL code
    SELECT Program_Picture_Info.Program_ID, Count(Program_Picture_Info.Picture_ID) AS CountOfPicture_ID

    FROM Program_Picture_Info

    WHERE (((Program_Picture_Info.Description)<>"Placeholder ! Do not delete!"))

    GROUP BY Program_Picture_Info.Program_ID;
    Thanks for your help ahead of time

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Trouble with my SQL Count query

    you need to do two things:

    1) put your condition into a SELECT query (VIEW) Q, which should look like:

    SELECT Program_ID, count(*) as PictureCount
    FROM Program_Picture_Info
    WHERE Description<>"Placeholder! Do not delete!"
    GROUP BY Program_ID

    2) Use this view for an outer join with your program table in order to count all programs, also those without pictures:

    SELECT P.Program_ID, iif(isnull(PictureCount),0,PictureCount)
    FROM Program P LEFT OUTER JOIN Q ON P.Program_ID = Q.Program_ID
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Sep 2003
    Posts
    17

    Re: Trouble with my SQL Count query

    Originally posted by DoktorBlue
    you need to do two things:

    1) put your condition into a SELECT query (VIEW) Q, which should look like:

    SELECT Program_ID, count(*) as PictureCount
    FROM Program_Picture_Info
    WHERE Description<>"Placeholder! Do not delete!"
    GROUP BY Program_ID

    2) Use this view for an outer join with your program table in order to count all programs, also those without pictures:

    SELECT P.Program_ID, iif(isnull(PictureCount),0,PictureCount)
    FROM Program P LEFT OUTER JOIN Q ON P.Program_ID = Q.Program_ID
    I find it easy enough to implement the select query that you put down for 1, but i'm not sure which view you mean with respect the the 2nd.

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

    Cool

    With "the view" he means the view you get from the query from 1


    So make a new query with the results from query 1

  5. #5
    Join Date
    Sep 2003
    Posts
    17
    Originally posted by Marvels
    With "the view" he means the view you get from the query from 1


    So make a new query with the results from query 1

    How do i do an SQL query on the results from the previous one? I looked in my textbooks and on the internet but i couldn't find anything on it. Currently i have the code in this form:
    SELECT Program_ID, count(*) as PictureCount
    FROM Program_Picture_Info
    WHERE Description<>"Placeholder! Do not delete!"
    GROUP BY Program_ID

    SELECT P.Program_ID, iif(isnull(PictureCount),0,PictureCount)
    FROM Program P LEFT OUTER JOIN Program_Picture_Info ON Program_Info.Program_ID = Program_Picture_Info.Program_ID;

    I tagged the 2nd query after the first. I'm getting an error "Syntax Error Select P.Program_ID". Are P and Q paramaters that i need to define?

  6. #6
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by AdvantageC
    How do i do an SQL query on the results from the previous one? I looked in my textbooks and on the internet but i couldn't find anything on it. Currently i have the code in this form:
    SELECT Program_ID, count(*) as PictureCount
    FROM Program_Picture_Info
    WHERE Description<>"Placeholder! Do not delete!"
    GROUP BY Program_ID

    SELECT P.Program_ID, iif(isnull(PictureCount),0,PictureCount)
    FROM Program P LEFT OUTER JOIN Program_Picture_Info ON Program_Info.Program_ID = Program_Picture_Info.Program_ID;

    I tagged the 2nd query after the first. I'm getting an error "Syntax Error Select P.Program_ID". Are P and Q paramaters that i need to define?
    Gee, it's much much more easier than you think.

    1) Make a new Query, go into SQL mode and paste the statement from my first step. Save this query under the name Q. So, you've got a view (= SELECT query)

    2) Create another new query, paste the code from my point 2), and run!
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  7. #7
    Join Date
    Sep 2003
    Posts
    17
    Originally posted by DoktorBlue
    Gee, it's much much more easier than you think.

    1) Make a new Query, go into SQL mode and paste the statement from my first step. Save this query under the name Q. So, you've got a view (= SELECT query)

    2) Create another new query, paste the code from my point 2), and run!

    Ok, thanks for the help. It works very well. Now that i have an accurate image count i've put this query as the control source of a text box. When i close the linked form which adds pictures, the text box reverts to #ERROR. I used a command button with docmd.close. I'd been using Forms![program entry form]!Image_Num.Requery to update the image count after images are added but it isn't working anymore.

  8. #8
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Sorry, I can't follow you anymore.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

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