Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    Join Date
    Nov 2003
    Posts
    23

    Question Unanswered: Mulitple Select Statements in One Query

    I'm trying to make a query in Access that will search for events in a single month where the events all have different names. I have SELECT Count(*) AS EVENT1 in September FROM blah WHERE Date LIKE Sep and EVENT LIKE 1 and then I have another SELECT COUNT statement for event2 in sept with event2 being the heading for the column and it aparently doesn't like that. I'm sorry, but I'm really new to this so I don't know if I'm explaining my problem correctly.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT Event, COUNT(*)
    FROM myTable99
    WHERE EventDat >= '9/1/2003' AND EventDate < '10/1/2003'
    GROUP BY Event


    ???
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you show some sample rows of the table, and then some sample result rows that you expect to get

    rudy

  4. #4
    Join Date
    Nov 2003
    Posts
    23
    number date type
    224433 sep fha
    224432 sep fha
    224424 sep fnma
    234443 oct fha

    I want it to run the query on September and if type = fha count those and if type = fnma count those so I would want it to return

    FHA in Sep FNMA in Sep
    2 1

    And then I'll run a separate query on Oct with the same types in the headings

  5. #5
    Join Date
    Nov 2003
    Posts
    23
    sorry it didn't post correctly that should be a 2 under FHA in sep and a 1 under the FNMA in Sep

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please tell me you did not name your column "date"
    Code:
    select sum(case when type='fha'
                    then 1 else 0 end) as "FHA in Sep" 
         , sum(case when type='fnma'
                    then 1 else 0 end) as "FNMA in Sep"    
      from yourtable
     where [date] = 'sep'
    rudy
    http://r937.com/

  7. #7
    Join Date
    Nov 2003
    Posts
    23
    Thanks for the advice and yes I labeled my column Date, it was lock_effective_date, but I didn't feel like typing that a bunch of times. So what is the problem with that? Too general?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    reserved word, will cause syntax errors if you aren't careful

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What happens when you get more than those [types]?

    And if you don't like type (good for you), better get used to [brackets]
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2003
    Posts
    23
    I have about 20 different types. I've got them labeled as ProgramName right now. And I'll change "date" to something else.

    What do the brackets do? Set it apart as being a field name and not some special modifier or something?

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    exactamundo
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2003
    Posts
    23
    I hate to ask y'all to spoon feed me, but I tried that code and it didn't work and this really isn't my forte.

    SELECT SUM(case when ProductName LIKE 'FHA30*'
    then 1 else 0 end) as "FHA in Sep"
    , sum(case when ProductName LIKE 'FNMA30*'
    then 1 else 0 end) as "FNMA in Sep"
    FROM Calculations2
    WHERE LockDate LIKE '*Sep*';

    It threw a syntax error.

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    As close as I can get:

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99 ([number] int, [date] char(3), [type] varchar(5))
    GO
    
    INSERT INTO myTable99 ([number], [date], [type])
    SELECT 224433, 'sep', 'fha' UNION ALL
    SELECT 224432, 'sep', 'fha' UNION ALL
    SELECT 224424, 'sep', 'fnma' UNION ALL
    SELECT 234443, 'oct', 'fha'
    GO
    
    SELECT [type] + ': ' +CONVERT(varchar(10), COUNT(*)) AS Denorm  INTO #bk_Temp 
    FROM myTable99
    WHERE [date] = 'sep'
    GROUP BY type
    
    DECLARE @Result varchar(8000)
    
    SELECT @Result = ''
    SELECT @Result = @Result + Denorm + ' ' FROM #bk_Temp
    
    SELECT RTRIM(@Result)
    GO
    
    
    DROP TABLE myTable99
    GO
    DROP TABLE #bk_Temp
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Nov 2003
    Posts
    23
    I'm sorry, but that's totally past my understanding. I've already got the table in there I was just trying to make heads and tails of the code that r937 supplied for the if-then statement in the SUM section:

    select sum(case when type='fha'
    then 1 else 0 end) as "FHA in Sep"
    , sum(case when type='fnma'
    then 1 else 0 end) as "FNMA in Sep"

    The syntax error it throws is a missing operator in the statement:
    SUM(case when ProductName LIKE 'FHA30*'
    then 1 else 0 end)

    I think the rest of it is okay.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by poontz13
    I'm sorry, but that's totally past my understanding.
    My sample is cut and paste-able, and should run in QA with no problem..

    check it out...

    You're problem though, is everytime you get a new type, you'll be hosed..
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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