You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Report (1 Viewer)
- Thread starter Cristina
- Start date
Cristina,
The revised one I uploaded here has a new report, rptExpediturePreviousCurrent, and after you enter the biginning and ending dates on Reports Menu, click the new button at the bottom.
What you should take a close look at is the union query, qryExpenditure_EU_PreviousCurrent, and then qryExpenditureSubTotal_EU_PreviousCurrent, which is the data source for the new report.
The key trick here is how to combine Previous Total and Current Total, and the answer is a union query.
Good luck.
Shoji
The revised one I uploaded here has a new report, rptExpediturePreviousCurrent, and after you enter the biginning and ending dates on Reports Menu, click the new button at the bottom.
What you should take a close look at is the union query, qryExpenditure_EU_PreviousCurrent, and then qryExpenditureSubTotal_EU_PreviousCurrent, which is the data source for the new report.
The key trick here is how to combine Previous Total and Current Total, and the answer is a union query.
Good luck.
Shoji
Attachments
Good night,
Thank you very much. Sorry, I don't know anything about union query so any advice you can give me would be appreciated. I need to add the budget so I can get the the balance for the report. When I run the union query the amounts for current expenditure show as symbols, resembling chinese, even when i used it to create a report.
So grateful, this is exactly what I need.
Thank you very much. Sorry, I don't know anything about union query so any advice you can give me would be appreciated. I need to add the budget so I can get the the balance for the report. When I run the union query the amounts for current expenditure show as symbols, resembling chinese, even when i used it to create a report.
So grateful, this is exactly what I need.
Hi Cristina,
The following pages may be helpful:
https://support.office.com/en-za/ar...on-query-3856f16c-0a22-43f2-8c23-29ec44acbc05
https://www.youtube.com/watch?v=-tUMfbsJVTA
I attached a revision that shows the balance. In it you will see a function, Nz. If you are not familiar with this Access function, this is a very useful tool and you should google about it.
Yes, I saw that. I don't know why or what causes it, but after I played around, the simple solution was to use "UNION ALL" instead of "UNION". You will see the result when you open the union query in design mode.
Shoji
Sorry, I don't know anything about union query so any advice you can give me would be appreciated.
The following pages may be helpful:
https://support.office.com/en-za/ar...on-query-3856f16c-0a22-43f2-8c23-29ec44acbc05
https://www.youtube.com/watch?v=-tUMfbsJVTA
I need to add the budget so I can get the the balance for the report.
I attached a revision that shows the balance. In it you will see a function, Nz. If you are not familiar with this Access function, this is a very useful tool and you should google about it.
When I run the union query the amounts for current expenditure show as symbols, resembling chinese, even when i used it to create a report.
Yes, I saw that. I don't know why or what causes it, but after I played around, the simple solution was to use "UNION ALL" instead of "UNION". You will see the result when you open the union query in design mode.
Shoji
Attachments
Good morning,
Thank you very much for taking the time to understand my problem, I am almost there. Two problems:
1. In my report, the current expenditure data that showed up as symbols show up as ERROR .(so the total also shows up as error) This is what I did.
The expenditure report should not report on GST nor disbursement, so I created a query QryDetail Budget Consumption1 where I put the Union query and for Item Code a criteria less than “7” the item code for GST. Disbursement is code 9. So that took care of that; and include a new column with item coes from the expenditure table so that I could have a column with all the item codes in one column. (In the union query the item codes show up in different columns for the current and previous columns)
This is needed because I need to match the expenditure with the budget by item code and show a difference and % spent as in the Summary report. See my report DETAIL BUDGET CONSUMPTION
The amount s for current expenditure show up fine as far as QryDetail Budget Consumption1, it shows up as error in QryDetail Budget Consumption
2. Cant figure out a calculation
On the summary for the Item, I averaged the budget and that works fine. Eg. 4.3.1 However I can’t fugure out how to get the correct figures for the other summaries. Any ideas?
You will note that the Total Previous and Total current match with my SUMMARY TABLE OF BUDGET CONSUMPTION BY BUDGET HEADING, so the report data is correct. Just need to figure put the summaries for the Budget amount and fix the “ERROR” showing up for the current records.
NB to get the EU figures we divide the exp by the exchange rate. I am checking to see why on the Consumption % shows up as 149.69 and on summary 149.70
Thanks again
Thank you very much for taking the time to understand my problem, I am almost there. Two problems:
1. In my report, the current expenditure data that showed up as symbols show up as ERROR .(so the total also shows up as error) This is what I did.
The expenditure report should not report on GST nor disbursement, so I created a query QryDetail Budget Consumption1 where I put the Union query and for Item Code a criteria less than “7” the item code for GST. Disbursement is code 9. So that took care of that; and include a new column with item coes from the expenditure table so that I could have a column with all the item codes in one column. (In the union query the item codes show up in different columns for the current and previous columns)
This is needed because I need to match the expenditure with the budget by item code and show a difference and % spent as in the Summary report. See my report DETAIL BUDGET CONSUMPTION
The amount s for current expenditure show up fine as far as QryDetail Budget Consumption1, it shows up as error in QryDetail Budget Consumption
2. Cant figure out a calculation
On the summary for the Item, I averaged the budget and that works fine. Eg. 4.3.1 However I can’t fugure out how to get the correct figures for the other summaries. Any ideas?
You will note that the Total Previous and Total current match with my SUMMARY TABLE OF BUDGET CONSUMPTION BY BUDGET HEADING, so the report data is correct. Just need to figure put the summaries for the Budget amount and fix the “ERROR” showing up for the current records.
NB to get the EU figures we divide the exp by the exchange rate. I am checking to see why on the Consumption % shows up as 149.69 and on summary 149.70
Thanks again
Hi Shoji,
Please bear with me. I went over and redid the last query.
Disregard portion of previous email regarding the queries and Report.(Detail 1) The Query QryDetailBudgetConsumption shows up correctly for the current records, however in the report it shows up 'error". However the summaries in the Report calculate correctly(except the Budget amt). Click the button Detail table of Expenditure by Budget Heading for period between 1/7/15 and today's date and match with the Summary table of budget consumption by Budget Heading. NB to convert to EU Bze expenditure / exchange rate = EU. Both reports match.
Other problem as detailed before:
2. Cant figure out a calculation
On the summary for the Item, I averaged the budget and that works fine. Eg. 4.3.1 However I can’t figure out how to get the correct figures for the other summaries. Any ideas?
You will note that the Total Previous and Total current match with my SUMMARY TABLE OF BUDGET CONSUMPTION BY BUDGET HEADING, so the report data is correct. Just need to figure put the summaries for the Budget amount and fix the “ERROR” showing up for the current records.
NB to get the EU figures we divide the exp by the exchange rate. I am checking to see why on the Consumption % shows up as 149.69 and on summary 149.70 for Item 4.3.1
Please bear with me. I went over and redid the last query.
Disregard portion of previous email regarding the queries and Report.(Detail 1) The Query QryDetailBudgetConsumption shows up correctly for the current records, however in the report it shows up 'error". However the summaries in the Report calculate correctly(except the Budget amt). Click the button Detail table of Expenditure by Budget Heading for period between 1/7/15 and today's date and match with the Summary table of budget consumption by Budget Heading. NB to convert to EU Bze expenditure / exchange rate = EU. Both reports match.
Other problem as detailed before:
2. Cant figure out a calculation
On the summary for the Item, I averaged the budget and that works fine. Eg. 4.3.1 However I can’t figure out how to get the correct figures for the other summaries. Any ideas?
You will note that the Total Previous and Total current match with my SUMMARY TABLE OF BUDGET CONSUMPTION BY BUDGET HEADING, so the report data is correct. Just need to figure put the summaries for the Budget amount and fix the “ERROR” showing up for the current records.
NB to get the EU figures we divide the exp by the exchange rate. I am checking to see why on the Consumption % shows up as 149.69 and on summary 149.70 for Item 4.3.1
Attachments
Hi Shoji,
I am still working on the last report data base. I studies the Union Query you suggested. I created a union query giving me the total budget for each item code, accounting code and Main heading. I was hoping to add that to my Query QryDetail Budget Consumption, but there was no data to link the tables.
I am still having the error show up in my report where the symbols were in the query. The data in the query now look fine, but when the report runs, it shows up as an error.
Any assistance would be appreciated.
I am still working on the last report data base. I studies the Union Query you suggested. I created a union query giving me the total budget for each item code, accounting code and Main heading. I was hoping to add that to my Query QryDetail Budget Consumption, but there was no data to link the tables.
I am still having the error show up in my report where the symbols were in the query. The data in the query now look fine, but when the report runs, it shows up as an error.
Any assistance would be appreciated.
Hi Shoji,
Thanks very much for your help. I figured out how to get the current records. I took the total TotalEUspent and subtracted the previous records. And finally figured how to deal with the totals etc.
So grateful for your help with the UNION query.
Kindest Regards
Bernadette
Thanks very much for your help. I figured out how to get the current records. I took the total TotalEUspent and subtracted the previous records. And finally figured how to deal with the totals etc.
So grateful for your help with the UNION query.
Kindest Regards
Bernadette
Similar threads
- Replies
- 1
- Views
- 406
- Replies
- 14
- Views
- 965
- Replies
- 9
- Views
- 1,022
Users who are viewing this thread
Total: 1 (members: 0, guests: 1)