CODEWITHSUNDEEP
sqlms-accesscalculated-columns
TSQL_Newbie
TSQL_Newbie

Reputation: 821

Access SQL Calculation

Good Day,

I'm having a brain freeze with an Access SQL calculation. I have a table with 4 columns with the following data:

enter image description here

What I am trying to achieve is a percentage calculation based on two page names, those page name 'App Process - Print / Save' and 'App Process - Shown'. So the calc would be:

'App Process - Print / Save' / 'App Process - Shown'

But this would also be for each 'Year' and 'WeekofYear'.

What would be the cleanest way to achieve this?

Additional Result Info: What I want to do is then feed this out into a table, most likely a pivot table to look something like this.. enter image description here

So essentially creating an additional column for the calculation/result. There will be other Pages with the same setup in this table but I have filtered out one just one group as an example for now.

Regards

Upvotes: 2

Views: 128

Answers (2)

Gustav
Gustav

Reputation: 55806

Use the wizard for creating a crosstab query - with this expression for the column names:

[Year] & "-" & [WeekNumber]

Upvotes: 1

Mr. Bhosale
Mr. Bhosale

Reputation: 3096

i tried with SQL. also u can try with any another.

Using pivot :

                      select YEAR ,page,[29],[30]
                     from #temp pivot
                    (
                      max([unique pageview])
                           FOR weekofyear IN ([29],[30])
                      )as p

                    order by  
                    case when page='App Process - shown' then 1
                     when page='App Process - step 1' then 2
                     when page='App Process - step 2' then 3
                     when page='App Process - Print/Save' then 4
                    end

For Total calculation use below query:

             select YEAR ,page,[29],[30] from
            #temp pivot
            (
            max([unique pageview])
                   FOR weekofyear IN ([29],[30])
            )as p

            union all

             select YEAR ,page,[29],[30]
             from (  select a.year, a.weekofyear,'Total Print / Save calculator Conversion' as 'page', 
                    CONVERT(DECIMAL(10, 3), a.[unique pageview])/
                    CONVERT(DECIMAL(10, 3), b.[unique pageview]) as 'Print / Save calculator Conversion'
                    from
                    (       
                     select year , weekofyear , page , [unique pageview]     
                     FROM #temp
                     where page in ('App Process - Print/Save')
                     )a inner join (        
                      select year , '' as 'Total Print / Save calculator Conversion',weekofyear , page , [unique pageview]     
                     FROM #temp
                     where page in ('App Process - shown')
                     )b on a.year=b.year and a.weekofyear=b.weekofyear 
                     )c 
            pivot
            (
            max([Print / Save calculator Conversion])
                   FOR weekofyear IN ([29],[30])
            )as p

let us know if u have any concerns on this.

Upvotes: 0

Related Questions