Query for Report

[Luis Ferreira]

Hello All,



I need help to find a solution for my "small" problem...

Table Expenses
Name - Jan - Fev - Mar - ... - Dec
(Text - Value - Value - Value - ... - Value)

Form Month Select
StartMonth - EndMonth

Report
Name - Sum(of months selected in form Month Select)

Thanks in advance

[weekend00]

you need some VBA codes to do the calculation because you table design is not good for this calculation.

[Luis Ferreira]

you need some VBA codes to do the calculation because you table design is not good for this calculation.

Any idea where i can obtain that VBA code?

Thanks

[weekend00]

1 Create a query to transfer you table:
Query: UnionQry

SELECT Jan2Dec.Nname, 1 as mMonth, Jan2Dec.Jan as Expense FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 2 as mMonth, Jan2Dec.Feb FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 3 as mMonth, Jan2Dec.Mar FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 4 as mMonth, Jan2Dec.Apr FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 5 as mMonth, Jan2Dec.May FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 6 as mMonth, Jan2Dec.Jun FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 7 as mMonth, Jan2Dec.Jul FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 8 as mMonth, Jan2Dec.Aug FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 9 as mMonth, Jan2Dec.Sep FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 10 as mMonth, Jan2Dec.Oct FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 11 as mMonth, Jan2Dec.Nov FROM Jan2Dec UNION ALL 
SELECT Jan2Dec.Nname, 12 as mMonth, Jan2Dec.Dec FROM Jan2Dec;

2 set the vaules of your comboBox startMonth and EndMonth from 1 to 12,

3 use following as data source of your report:

select nName, sum(expense) as eExpense from unionqry where mMonth betwee forms!yourform.startMonth and forms!yourform.endMonth

[Luis Ferreira]

1 Create a query to transfer you table:
Query: UnionQry

SELECT Jan2Dec.Nname, 1 as mMonth, Jan2Dec.Jan as Expense FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 2 as mMonth, Jan2Dec.Feb FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 3 as mMonth, Jan2Dec.Mar FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 4 as mMonth, Jan2Dec.Apr FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 5 as mMonth, Jan2Dec.May FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 6 as mMonth, Jan2Dec.Jun FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 7 as mMonth, Jan2Dec.Jul FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 8 as mMonth, Jan2Dec.Aug FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 9 as mMonth, Jan2Dec.Sep FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 10 as mMonth, Jan2Dec.Oct FROM Jan2Dec union all
SELECT Jan2Dec.Nname, 11 as mMonth, Jan2Dec.Nov FROM Jan2Dec UNION ALL 
SELECT Jan2Dec.Nname, 12 as mMonth, Jan2Dec.Dec FROM Jan2Dec;

2 set the vaules of your comboBox startMonth and EndMonth from 1 to 12,

3 use following as data source of your report:

select nName, sum(expense) as eExpense from unionqry where mMonth betwee forms!yourform.startMonth and forms!yourform.endMonth

Hello,
First of all thank you for your interest in helping me.

This is the Unnion Query I do:
SELECT Jan2Dez.nConta, 0 as mMonth, Jan2Dez.Abe as Valor FROM [10273PlanoSoma] UNION ALL
SELECT Jan2Dez.nConta, 1 as mMonth, Jan2Dez.Jan as Valor FROM [10273PlanoSoma] UNION ALL
SELECT Jan2Dez.nConta, 2 as mMonth, Jan2Dez.Fev as Valor FROM [10273PlanoSoma] UNION ALL
SELECT Jan2Dez.nConta, 3 as mMonth, Jan2Dez.Mar as Valor FROM [10273PlanoSoma] UNION ALL
SELECT Jan2Dez.nConta, 4 as mMonth, Jan2Dez.Abr as Valor FROM [10273PlanoSoma] UNION ALL
SELECT Jan2Dez.nConta, 5 as mMonth, Jan2Dez.Mai as Valor FROM [10273PlanoSoma] UNION ALL
SELECT Jan2Dez.nConta, 6 as mMonth, Jan2Dez.Jun as Valor FROM [10273PlanoSoma] UNION ALL
SELECT Jan2Dez.nConta, 7 as mMonth, Jan2Dez.Jul as Valor FROM [10273PlanoSoma] UNION ALL
SELECT Jan2Dez.nConta, 8 as mMonth, Jan2Dez.Ago as Valor FROM [10273PlanoSoma] UNION ALL
SELECT Jan2Dez.nConta, 9 as mMonth, Jan2Dez.Set as Valor FROM [10273PlanoSoma] UNION ALL
SELECT Jan2Dez.nConta, 10 as mMonth, Jan2Dez.Out as Valor FROM [10273PlanoSoma] UNION ALL
SELECT Jan2Dez.nConta, 11 as mMonth, Jan2Dez.Nov as Valor FROM [10273PlanoSoma] UNION ALL
SELECT Jan2Dez.nConta, 12 as mMonth, Jan2Dez.Dez as Valor FROM [10273PlanoSoma] UNION ALL
SELECT Jan2Dez.nConta, 13 as mMonth, Jan2Dez.Reg as Valor FROM [10273PlanoSoma] UNION ALL
SELECT Jan2Dez.nConta, 14 as mMonth, Jan2Dez.Apu as Valor FROM [10273PlanoSoma];

And didin't work.... He do not sum de values

[weekend00]

the last query under step 3 sum the values:
select nName, sum(expense) as eExpense from unionqry where mMonth betwee forms!yourform.startMonth and forms!yourform.endMonth

[Luis Ferreira]

the last query under step 3 sum the values:
select nName, sum(expense) as eExpense from unionqry where mMonth betwee forms!yourform.startMonth and forms!yourform.endMonth

Hello,

Sorry for my delay. Your solution works very well.

Thank You Very Much.