help with SQL statement

K

koturtle

I have a union query in Access that takes a long time to run. I would
like to put this query into SQL and see if i get any performance
increases. My union query in access looks like this:

select Janvalue as TotalValue, cvdate("1/31/" & [Year]) as MonthEnd,
costelementtypevalue,valuetype, projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
where Iif(Left([Forms]![General_Form]![Project_ID],2)="V/",Left
([Forms]![General_Form]![Project_ID],1) & Right([Forms]![General_Form]!
[Project_ID],4),[Forms]![General_Form]![Project_ID])=projprojectid
UNION ALL select Febvalue, cvdate("2/28/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Marvalue, cvdate("3/31/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Aprvalue, cvdate("4/30/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Mayvalue, cvdate("5/31/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Junvalue, cvdate("6/30/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Julvalue, cvdate("7/31/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Augvalue, cvdate("8/31/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Sepvalue, cvdate("9/30/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Octvalue, cvdate("10/31/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Novvalue, cvdate("11/30/" & [Year]),
costelementtypevalue,valuetype, projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Decvalue, cvdate("12/31/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts];


Apparenlty the cvdate doesn't work and it doesn't like "=" or "!"
either.
[dbo_TotalProjectCosts] is a query in SQL already.
Any help would be great!

TIA,
KO
 
J

Jerry Whittle

The biggest problem is the table design. I noticed that you have Janvalue,
Febvalue, Marvalue, etc. fields in your table. This is what is causing the
performance problem as it requires the union queries.

Instead of having fields across with the different monthly data, you should
have at least one more table holding the values. If each record included a
date field, you could even extract the MonthEnd easier.

Speaking of MonthEnd, your SQL statement will have a problem in February
about every 4 years.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


koturtle said:
I have a union query in Access that takes a long time to run. I would
like to put this query into SQL and see if i get any performance
increases. My union query in access looks like this:

select Janvalue as TotalValue, cvdate("1/31/" & [Year]) as MonthEnd,
costelementtypevalue,valuetype, projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
where Iif(Left([Forms]![General_Form]![Project_ID],2)="V/",Left
([Forms]![General_Form]![Project_ID],1) & Right([Forms]![General_Form]!
[Project_ID],4),[Forms]![General_Form]![Project_ID])=projprojectid
UNION ALL select Febvalue, cvdate("2/28/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Marvalue, cvdate("3/31/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Aprvalue, cvdate("4/30/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Mayvalue, cvdate("5/31/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Junvalue, cvdate("6/30/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Julvalue, cvdate("7/31/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Augvalue, cvdate("8/31/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Sepvalue, cvdate("9/30/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Octvalue, cvdate("10/31/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Novvalue, cvdate("11/30/" & [Year]),
costelementtypevalue,valuetype, projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Decvalue, cvdate("12/31/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts];


Apparenlty the cvdate doesn't work and it doesn't like "=" or "!"
either.
[dbo_TotalProjectCosts] is a query in SQL already.
Any help would be great!

TIA,
KO
Click to expand...
 
D

Duane Hookom

You have more issues than just cvdate(). SQL Server will not be able to
resolve your references to controls on forms from Access.

I would not take a number ([Year]) concatenate it to a string "1/31/" and
then convert it to a date. This seems like too much work. I would try:
DateSerial([Year],1,31) As MonthEnd,

It seems a bit unusual that you apply a where clause to the first SELECT for
Jan but not for additional months.

I would consider creating the union query on the SQL Server returning a
month number and the year. Once you get the values to Access you can use
DateSerial() in a form or report to create a date like:
=DateSerial([Year], [Mth]+1,0)


--
Duane Hookom
Microsoft Access MVP


koturtle said:
I have a union query in Access that takes a long time to run. I would
like to put this query into SQL and see if i get any performance
increases. My union query in access looks like this:

select Janvalue as TotalValue, cvdate("1/31/" & [Year]) as MonthEnd,
costelementtypevalue,valuetype, projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
where Iif(Left([Forms]![General_Form]![Project_ID],2)="V/",Left
([Forms]![General_Form]![Project_ID],1) & Right([Forms]![General_Form]!
[Project_ID],4),[Forms]![General_Form]![Project_ID])=projprojectid
UNION ALL select Febvalue, cvdate("2/28/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Marvalue, cvdate("3/31/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Aprvalue, cvdate("4/30/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Mayvalue, cvdate("5/31/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Junvalue, cvdate("6/30/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Julvalue, cvdate("7/31/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Augvalue, cvdate("8/31/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Sepvalue, cvdate("9/30/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Octvalue, cvdate("10/31/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Novvalue, cvdate("11/30/" & [Year]),
costelementtypevalue,valuetype, projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts]
UNION ALL select Decvalue, cvdate("12/31/" & [Year]),
costelementtypevalue, valuetype,projprojectid, resourceCode,
resourcedescription, WBSID, WBSDescription from
[dbo_TotalProjectCosts];


Apparenlty the cvdate doesn't work and it doesn't like "=" or "!"
either.
[dbo_TotalProjectCosts] is a query in SQL already.
Any help would be great!

TIA,
KO
Click to expand...
 
K

koturtle

Unfortunately the table design is not mine and not one i can change.
It comes from a data wharehouse of one of our products.
They save project data into buckets Jan, Feb, Mar etc.

I'm not sure where to go from here.
thanks,
KO
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help combining queries 1
SQL Statment Help Needed 3
Stumped with an upcoming birthdays query 16
Access Access SQL - union joins with parameters 2
Please help me with an SQL query 4
Spelling or syntax problem with SQL 4
Amend SQL Query with VBA 7
Help with a SQL ORDER BY statement 3

Top