Rewrite an sql sentence

  • Thread starter Thread starter Xavier
  • Start date Start date
X

Xavier

Hi all!

I've upgraded my Access database to an MSDE-driven one. I use an Access 2002
project as front-end and MSDE as back-end.
Now I find that all the crosstab queries nolonger work. I've searched the
Internet and , as far as I understand, everywhere I look, it says that MSDE
does not suport crosstab queries, or at least, not in the way Access writes
them and some webpages offer apps that convert the SQL sentence.

If someone knows how to re-write a query such as the following one, I beg
him/her to tell me. I have several queries to re-write, so if you can
explain the general way to convert them, I would be very grateful.

Thanks!!
Xavier


One of the queries to be re-written is:

TRANSFORM Sum(DesgloseFact.Gasto) AS [El Valor]
SELECT DesgloseFact.Vehicle, DesgloseFact.Acreedor, Sum(DesgloseFact.Gasto)
AS [Total de Gasto]
FROM DesgloseFact
WHERE (((DesgloseFact.Vehicle)=[Forms]![EstAcreedor]![Matrícula]) AND
((Year([Data]))=[Formularios]![EstAcreedor]![any]))
GROUP BY DesgloseFact.Vehicle, DesgloseFact.Acreedor, DesgloseFact.Data
PIVOT Format([Data],"mmm") In
("ene","feb","mar","abr","may","jun","jul","ago","sep","oct","nov","dic");
 
You must use a combination of Sum and Case; 12 column/combinations (one for
each month). For the first column, the Case will return the value that you
want to add if month = 'ene' or 0 otherwise:

Select Sum (Case When MONTH = 'ene' then DesgloseFact.Gasto Else 0 End) as
SumValorEne, Sum (Case ....) ...

For the extraction of the month from the date, you muste replace the Format
function with the Convert () or the Cast functions.

Finally, in you summation, you must take precaution of using the function
isNull () is there is possibility of having a Null value in the field
DesgloseFact.Gasto.

S. L.
 
Sylvain,

Thanks for your answer.
I'm affraid I don't understand what should I do...... I think I'll have to
study T-SQL better.....

At least, I have a point to start from.
Anyway, thank you.

Xavier.
 
Take a quick look at the Case statement on the BOL and you will see what to
do. This statement is one of the most usefull instruction in T-SQL and I
don't see what you will be able to do with MSDE if you don't have a clear
understanding of this statement of your own and of the Cast/Convert
functions as well.

If after that you are still unable to understant what to do, then don't be
afraid to ask again. The explanation that I have given might have been a
little skimpy but only because it was my understanding that someone able to
create a transformation query on Access - and willing to go with MSDE -
should be able to see what to do with only a few words of explanation.

S. L.
 
Back
Top