comparison

  • Thread starter Thread starter SHETTY
  • Start date Start date
S

SHETTY

Dear Friends ,

I have 2 querries viz qrybilllog & QryBudget

qrybilllog is summury of state wise billing recorded for a period -with
fields billed_date, bill_Amount, Branch_Name

QryBudget is summry of state wise budget -with fields Budget_Date,
Budget_Amount, Branch_State

I want to compare state wise billing recorded agaist branchwise budget like
%ge of budget achieved in a given period

Please guide me how to solve above problem
I am looking for a consolidated/comparitive report like this

Branch Name/ Month / BdgetAllotted/ BillingAchieved/ %ge of budget

Please advise

Thanks

Ramesh Shetty
 
Which fields are the join fields.?

Is the summary result just one row? (I don't think so because of the date
field)
 
Branch name is same in the both the fields
Date value is stored in the Month field need to be grouped monthwise in both
the querries

In the summry result report there will be record for each branch listed in
the querry
report will look like this
Branch_Name/ Month / Bdget_Allotted/ Billing_Achieved/ %ge of budget
Branch_1...... /April-08/$.1000........... / $.800 .............. /80%
Branch_2...... /April-08/$.2000.........../ $.1000 .............. /50%
Branch_3...... /April-08/$.5000.........../ $.4000 .............../80%
Branch_4 /April-08/$.1000.........../ $.800 ................/80%
Branch_5 /April-08/$.1000.........../ $.800 .............../80%


Thank you
 
Not sure if this will answer your need!

Enter this expression in both your queries
Mths: Format([invoice-date],"mmm") & "-" & Format([invoice-date],"yyyy")

When you join the queries join the branch and Mths

You should be able to do the calculations.
 
both the querries are ready,
How I can make comparision between data of 2 querries

Wether I have to union both the above querries and then start comparison or
any other way to compare

please guide


AY said:
Not sure if this will answer your need!

Enter this expression in both your queries
Mths: Format([invoice-date],"mmm") & "-" & Format([invoice-date],"yyyy")

When you join the queries join the branch and Mths

You should be able to do the calculations.


SHETTY said:
Branch name is same in the both the fields
Date value is stored in the Month field need to be grouped monthwise in
both
the querries

In the summry result report there will be record for each branch listed
in
the querry
report will look like this
Branch_Name/ Month / Bdget_Allotted/ Billing_Achieved/ %ge of budget
Branch_1...... /April-08/$.1000........... / $.800 .............. /80%
Branch_2...... /April-08/$.2000.........../ $.1000 .............. /50%
Branch_3...... /April-08/$.5000.........../ $.4000 .............../80%
Branch_4 /April-08/$.1000.........../ $.800 ................/80%
Branch_5 /April-08/$.1000.........../ $.800 .............../80%


Thank you
Click to expand...
Click to expand...
 
Create a 3rd query using query 1 & 2.

Join the Branch and Mths.
Drag the required fields

Branch/Mths/Bdget_Allotted/ Billing_Achieved

Create an expression field
%ge of budget :Billing_Achieved/Bdget_Allotted
on format select percent and decimal 0

Now create a new report

Attach the 3rd query to the Record source
drag all fields to the detail section of the report.
Open the report. I hope it shows what you want.

Note: I do not think you need a Union query.

SHETTY said:
both the querries are ready,
How I can make comparision between data of 2 querries

Wether I have to union both the above querries and then start comparison or
any other way to compare

please guide


AY said:
Not sure if this will answer your need!

Enter this expression in both your queries
Mths: Format([invoice-date],"mmm") & "-" & Format([invoice-date],"yyyy")

When you join the queries join the branch and Mths

You should be able to do the calculations.


SHETTY said:
Branch name is same in the both the fields
Date value is stored in the Month field need to be grouped monthwise in
both
the querries

In the summry result report there will be record for each branch listed
in
the querry
report will look like this
Branch_Name/ Month / Bdget_Allotted/ Billing_Achieved/ %ge of budget
Branch_1...... /April-08/$.1000........... / $.800 .............. /80%
Branch_2...... /April-08/$.2000.........../ $.1000 .............. /50%
Branch_3...... /April-08/$.5000.........../ $.4000 .............../80%
Branch_4 /April-08/$.1000.........../ $.800 ................/80%
Branch_5 /April-08/$.1000.........../ $.800 .............../80%


Thank you

Which fields are the join fields.?

Is the summary result just one row? (I don't think so because of the
date
field)

:

Dear Friends ,

I have 2 querries viz qrybilllog & QryBudget

qrybilllog is summury of state wise billing recorded for a
eriod -with
fields billed_date, bill_Amount, Branch_Name

QryBudget is summry of state wise budget -with fields Budget_Date,
Budget_Amount, Branch_State

I want to compare state wise billing recorded agaist branchwise budget
like
%ge of budget achieved in a given period

Please guide me how to solve above problem
I am looking for a consolidated/comparitive report like this

Branch Name/ Month / BdgetAllotted/ BillingAchieved/ %ge of
budget

Please advise

Thanks

Ramesh Shetty
Click to expand...
Click to expand...
Click to expand...
 
Back
Top