Creating query without using Wizard

[marc_donofrio]

Hi

I am trying writing a SQL query to pass into an Chart in access. My
problem is i need to combine 2 querys together to give me the two lines
in the chart. Using the wizards this is no problem but i want to use
sql so i can pass variables into the query to make it easily updatable
by the user.

query1 called "06"
SELECT Format([bkdate],"mmm") AS [Date],
Count((Format([bkdate],"yymm"))) AS NumBookings,
(Format([bkdate],"mm")) AS [Month]
FROM dbo_tblBook
GROUP BY Format([bkdate],"mmm"), (Format([bkdate],"mm")),
Format([bkdate],"mmm yy")
HAVING (((Format([bkdate],"mmm yy")) Like "*06"))
ORDER BY (Format([bkdate],"mm"));

query2 called "07"
SELECT Format([bkdate],"mmm") AS [Date],
Count((Format([bkdate],"yymm"))) AS NumBookings,
(Format([bkdate],"mm")) AS [Month]
FROM dbo_tblBook
GROUP BY Format([bkdate],"mmm"), (Format([bkdate],"mm")),
Format([bkdate],"mmm yy")
HAVING (((Format([bkdate],"mmm yy")) Like "*06"))
ORDER BY (Format([bkdate],"mm"));

joined query using wizard
SELECT [06].Date, [06].NumBookings, [07].NumBookings
FROM 06 INNER JOIN 07 ON [06].Month = [07].Month;

It is this last one that gets passed through to the graph and pulls
back the exactly what i need but would like to have it so that i don't
rely on query1 or query2

I hope you can help

Marc

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Both queries are the same. Are you trying to get data for the years
2006 and 2007? You should just have one query w/ a better WHERE clause
(the HAVING clause should be the WHERE clause). There should also be a
third column that has different data than what you have (2 columns
representing the same data, just in different format: The month number
and name). Here's a better query (but w/o the required 3rd significant
column):

PARAMETERS [Start Date] Date, [End Date] Date;
SELECT DISTINCT Format([bkdate],"mmm") AS BookMonth, Count(*) AS
NumBookings, Month([bkdate]) AS Month_Nbr
FROM dbo_tblBook
WHERE [bkdate] BETWEEN [Start Date] And [End Date]
ORDER BY Month(bkdate)

Since you're not using any aggregate functions you don't need to GROUP
you can use DISTINCT in the SELECT clause - it does the same thing as
GROUP BY w/o aggregate functions.

When you run this query you'll be prompted for the dates you want to see
data for.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUq784echKqOuFEgEQLqdACg7pWcqp1y42hM2Fc5jWM9S/jLSoUAnREA
YvYxqnEeMs6u/g7SE0vhGWdX
=f049
-----END PGP SIGNATURE-----

 

[marc_donofrio]

Thanks for for your help. I must have pasted the same query by mistake
- but you correctly guessed that i just want to return the months for
just 07. Two things though, for my chart to display correctly i need
the data in the following format

06 07
Jan 12 34
Feb 14 37
Mar 18 30
Apr 5 51
May 20 25
Jun....etc

All the data is in tblBook so executing your code would place the
counts in one column not two. Also i tried to use the select statement
you posted but it came up with the error
"you tred to execute a query that does not include the specified
expression 'Format([bkdate],"mmm")' as part of an aggregate function.

Thanks for you help in trying to get this sorted.

Marc

 

[Gary Walter]

Thanks for for your help. I must have pasted the same query by mistake
- but you correctly guessed that i just want to return the months for
just 07. Two things though, for my chart to display correctly i need
the data in the following format

06 07
Jan 12 34
Feb 14 37
Mar 18 30
Apr 5 51
May 20 25
Jun....etc

All the data is in tblBook so executing your code would place the
counts in one column not two. Also i tried to use the select statement
you posted but it came up with the error
"you tred to execute a query that does not include the specified
expression 'Format([bkdate],"mmm")' as part of an aggregate function.

PMFBI

It sure looks to me like you want a crosstab...

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
TRANSFORM Nz(Count(*),0) AS NumBookings
SELECT Format([bkdate],"mmm") AS BookMonth,
Month([bkdate]) AS MnthNum
FROM dbo_tblBook
WHERE (((tblBook.bkdate) Between [Start Date] And [End Date]))
GROUP BY Format([bkdate],"mmm"), Month([bkdate])
ORDER BY Month([bkdate])
PIVOT Format([bkdate],"yy");

 

[marc_donofrio]

Thanks Gary that is a great help but it only returns the data in one
column. What i need is the count for each month in year 06 in one
column then year 07 in the next column so i can compare the data

 

[Gary Walter]

Thanks Gary that is a great help but it only returns the data in one
column. What i need is the count for each month in year 06 in one
column then year 07 in the next column so i can compare the data

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
TRANSFORM Nz(Count(*),0) AS NumBookings
SELECT Format([bkdate],"mmm") AS BookMonth,
Month([bkdate]) AS MnthNum
FROM dbo_tblBook
WHERE (((tblBook.bkdate) Between [Start Date] And [End Date]))
GROUP BY Format([bkdate],"mmm"), Month([bkdate])
ORDER BY Month([bkdate])
PIVOT Format([bkdate],"yy");

say what?

the PIVOT clause will ensure there are as many
columns as there are different "yy"'s in the data
you filter for....

did you use following parameters?

StartDate = 1/1/2006
EndDate = 12/31/2007

 

[marc_donofrio]

Gary

Don't know if you have been told before - but you are a genius! Thanks
so much for you help it is very appreciated. I put the dates in in the
wrong format which was the problem!