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.SELECT Event, COUNT(*)
FROM myTable99
WHERE EventDat >= '9/1/2003' AND EventDate < '10/1/2003'
GROUP BY Event

?|||could you show some sample rows of the table, and then some sample result rows that you expect to get

rudy|||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|||sorry it didn't post correctly that should be a 2 under FHA in sep and a 1 under the FNMA in Sep|||please tell me you did not name your column "date" :eek:
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/|||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?|||reserved word, will cause syntax errors if you aren't careful|||What happens when you get more than those [types]?

And if you don't like type (good for you), better get used to [brackets]|||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?|||exactamundo|||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.|||As close as I can get:

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|||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.|||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..|||your use of asterisks as wildcards is consistent with microsoft access, not sql server

if it's actually access, then shame on you for posting an access question in the sql server forum without mentioning it

this would explain why the CASE structure throws an error

in access you have to use IIF

rudy|||I'm sure you're getting frustrated with me Brett, but again, I'm really not too good at coding; to my understanding, your code looks like it is creating a table and then picking the number, date and type and putting them in there. I have a table with about 450 entries where the number is the primary key and I just want it to search through the table and count the number of items with a specific productName that occurs in a given month. I tried the following code, but it returned the number 72 which is the total number of items in September:

SELECT COUNT(ProductName LIKE 'FHA30*') AS ["FHA in Sep"],
COUNT(ProductName LIKE 'FNMA30*') AS ["FNMA in Sep"]
FROM Calculations2
WHERE LockDate LIKE '*Sep*';

So obviously my count isn't specifying the productName correctly and it is just couting all items.

Any other advice? I do appreciate it.|||AW, CRAP

my bad

sincere apologies

yes, you did mention access

hang on a sec and i'll rewrite the query for ya...

rudy|||I'm sorry! Yes it's access, I'm new to the forum and I guess I didn't see the Access forum...so, I need to use:

SELECT SUM(IIF ProductName LIKE 'FHA30*'
then 1 else 0 end) as "FHA in Sep"
, sum(IIF ProductName LIKE 'FNMA30*'
then 1 else 0 end) as "FNMA in Sep"
FROM Calculations2
WHERE LockDate LIKE '*Sep*';|||SELECT SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS "FHA in Sep"
, SUM(IIF(ProductName LIKE 'FNMA30*',1,0)) AS "FNMA in Sep"
FROM Calculations2
WHERE LockDate LIKE '*Sep*'|||That worked! (Not that I doubted it would once I phrased it right to y'all) I truly appreciate it, this will help me out a lot.|||great

once again, i apologize for chiding you, when in fact you did mention access right in your first sentence

rudy|||No problem...you're just trying to help me.

I do have one other question.

Instead of hard coding different queries for each month...can I prompt the user when they click on the query to type in the month they want to find?|||yes

is LockDate an actual date/time field?

SELECT [enter month 1 - 12] as "month"
, SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS "FHA"
, SUM(IIF(ProductName LIKE 'FNMA30*',1,0)) AS "FNMA"
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12]|||Originally posted by r937
yes

is LockDate an actual date/time field?

SELECT [enter month 1 - 12] as "month"
, SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS "FHA"
, SUM(IIF(ProductName LIKE 'FNMA30*',1,0)) AS "FNMA"
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12]

Okay, I understand your code, right now it's set up as 01-Sep-03. I left it as text when I imported it from excel to access just for uniformity sake. So if I prompt the user to enter 1-12, I guess I'll have to define somewhere that 1 = Jan, 2 = Feb, ... or whatever my date column has as the abreviation for the month? If so where do I put the declaration.|||Access...ok now I understand the confusion...

I was going to give you this

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

DECLARE @.SQL varchar(2000), @.Select varchar(1000)
SELECT @.SQL = ' SELECT ', @.Select = ''
SELECT @.Select = @.Select + 'SUM(CASE WHEN [type] = ' + '''' + [Type] + '''' + ' THEN 1 ELSE 0 END) AS ' + [Type]+','
FROM (SELECT DISTINCT [type] FROM myTable99 WHERE [date] = 'sep') AS A
SELECT @.Select = SUBSTRING(@.Select,1,LEN(@.Select)-1)
SELECT @.SQL = @.SQL + @.Select
SELECT @.SQL = @.SQL + ' FROM myTable99 WHERE [date] = ' + '''' + 'sep' + ''''
SELECT @.SQL
EXEC(@.SQL)

DROP TABLE myTable99
GO

But since you don't have QA, it's useless...

What it does though is what you want..it's dynamic sql and build a select based on data in your table...

I've done stuff in Access.(sortof) using the make query function...

But it's been a loooooong time...|||I got ya...thanks for trying to help though, so now I guess you can see why I was extremely confused. I sort of understood what your code was saying, but got lost in there somewhere. Thanks again.|||you will get lots more mileage, and simpler queries, if you convert your column from text to datetime

meanwhile, try this --

... where LockDate like '*' + [enter month xxx] + '*'

and don't forget to change it in the SELECT as well

rudy|||Originally posted by r937
you will get lots more mileage, and simpler queries, if you convert your column from text to datetime

meanwhile, try this --

... where LockDate like '*' + [enter month xxx] + '*'

and don't forget to change it in the SELECT as well

rudy

I'll change it to date/time, makes sense, so when the user inputs the month number, what tells access that 4 equates to the 4th month and not the 4th day or 4th year? Also, if I use what you stated above, the user would then have to know that September is Sep right? Like if they typed in Sept then it wouldn't work. Am I right? So again your method for date/time would be better.|||>> what tells access that 4 equates to the 4th month

because the query would be using the MONTH() function on the datetime field|||But again...ALL of this is a presentation layer issue that can probably be more easily handled with code...

You're using Access 2000 and Forms?

AND, if you are, don't you expect that you need n number of controls to accept the results?

Your row length is variable

And if the User type in a wrong date, it'll yell at them...|||Oh I see...in the WHERE statement month() is a function, I thought it was being treated like an object as declared in the SELECT like in JAVA. I'm going to try this...I appreciate it.|||The error now is "The SELECT statement includes a reserved word or an argument name that is mispelled or missing, or the punctuation is incorrect."

Here's my code:

SELECT [enter month 1 - 12] as "Month",
SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS ["FHA 30 in Sep"],
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12];|||SELECT [enter month 1 - 12] as myMonth,
SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS [FHA 30 in Sep],
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12];

Spaces in column names is not a good thing...

EDIT Look at that, the same exact time...

double quote in Access mean data, [] mean columns|||take the square brackets off the alias

... AS "FHA 30 in Sep"|||HECK YEAH!!! It works...thanks soo much. I sure you haven't heard the last from me. Thanks. Rudy...do you have a problem with me emailing you if I have a specific question? If it's general I'll obviously post it to the forum.|||no, no problem, although i have a hard time seeing why it wouldn't be general enough to let others take a crack at it

i mean, look at brett, how eager he was, there are bound to be others, and i may not always be available...

rudy