none
Designing Advanced Reports with Access 2007

    Question

  • Hi All !

    I have two questions here and would like to know how to do this as I looked at several places on the web and found nothing significant:

    Q 1. How do I design an hierarchical report in Access 2007 and synchronize a Report with a Sub Report ?? 

    Q 2. I would like to design a form in Access 2007 which would supply parameters to a Report. This Report would be dynamically created as the user supplies the parameters in the specialized form.

    I am using tables in Access 2007 which have a One to Many relationship with each other and would like to know how to design these reports for the forms I am using.

    UPDATE:

    On second thoughts, what if I wanted to have a calculated field in my report which would calculate the percentage growth rate of churches last year to this year on a per district basis ?? I have 3 tables in my Access database one for Districts, one for Blocks (sub-districts) and one for villages among other tables.

    Hans, you know very well about the structure of my tables because you have been working on my database since the last few threads, how do you think this can be done ??

    Do I have to add a calculated field in one of the tables to make a report like this ??

    Please help.

    Thanks.

    Best Regards,

    ~~Maneesh



    Thursday, May 24, 2012 10:29 AM

Answers

  • You have added a Checkmark field to the Church table, but the Checkmark field in VillageQuery is still the Checkmark field from the Village1 subform. You should change this to the Checkmark field from the Church table. It should then be possible to tick the check box.

    Regards, Hans Vogelaar

    Monday, May 28, 2012 8:10 AM
  • All the queries and reports that I created look at the YrChrEst field in the Church table. You have left that field blank, however.

    The record source of the Village subform has the YrChrEst field from the Village1 table. If you want the queries such as CrosstabBlocks, CrossTabDistricts, CountByBlock and CountByDistrict to use that field, you must modify those queries.

    For example, as it is now:

    And after modification:

    The reports will then pick up YrChrEst from the Village1 table.


    Regards, Hans Vogelaar

    Friday, June 01, 2012 4:15 PM
  • The TNNonBFJ_Pre_Yr etc. fields are very different from the other fields because they already contain a count for previous year and current year, so you shouldn't look at YrChrEst when calculating the increase. In other words, you can't do that in the same queries we've been discussing above. You'll need separate queries for that.

    I have updated the database at https://skydrive.live.com/redir?resid=CAE9FF0A56DD9D4!233 with new queries. You can let Access create reports from these queries for you.


    Regards, Hans Vogelaar

    Sunday, June 03, 2012 10:25 AM

All replies

  • Q 2. I would like to design a form in Access 2007 which would supply parameters to a Report. This Report would be dynamically created as the user supplies the parameters in the specialized form.

    Hi Maneesh,

    You can open the report from your form, supplying the form's name in the OpenArgs parameter of the report.

    In the OnOpen event of the report you can set a variable prev_form of type Form using:

          Set prev_form = Forms(Me.Openargs).

    From then on you have access to all controls of the form.
    Hereafter you construct your RecordSource of the report using any value that is available on the form.

     

    Imb.

    Thursday, May 24, 2012 10:45 AM
  • If your tables are in a one (parent) to many (child) relationship, it is often easiest to join the tables in a query and use the query as the report's record source.  Then you can use the report's Sorting and Grouping feature to vreate header and footer sections for the parent data fields and put the child fields in the detail section.  This can be extended to up to 10 levels of grandparent - parent - child.  Alternatively, you can use a separate report for each table and place a child report as a subreport on its parent's report using the subreport control's LinkMaster/Child properties to link (synchronize)  them.

    A "parameter form" can be any form with unbound controls where the parameter values can be entered.  You should never create a form or controls on the fly.  Instead, just add a control for each field that can be used in a filter and let the users enter a criteria value in the unbound controls.  Use a command button's Click event to run code to open the report.

    As long as the form is open, a report's record source query can then use criteria like:

       =Forms![name of form].[name of control]

    A better way to filter a form or report when it is opened is to use VBA code to construct the OpenForm or OpenReport methods' WhereCondition argument.  The Click event procedure for the command button that opens the report might then look something like:

       If Not IsNull(Me.[name of control]) Then
          DoCmd.OpenReport "name of report", WhereCondition:= "name of field=" & Me.[name of control]
       Else    ' parameter not supplied, show all records
          DoCmd.OpenReport "name of report"
       End If

    Thursday, May 24, 2012 11:17 AM
  • Is a pastor equivalent to a church?

    If not, you'll need a table listing churches, with their location and the year they were established.


    Regards, Hans Vogelaar

    Thursday, May 24, 2012 4:31 PM
  • HI Hans,

    Nice to see you back.

    I don't understand what you mean by pastor being equivalent to a church ?? All I know is that pastors are working in villages which are in Blocks and the blocks are in the district. There a total of 72 Districts and each has several blocks. 

    Anyway, you mentioned a table listing churches with their location and year they were established. What will table look like and what fields need to be in this table ???

    Let's take this step by step. First, what will this table look like and what fields do I need in this table ? After that we can go on to the next step.

    Regards,

    ~~Maneesh

    Thursday, May 24, 2012 4:47 PM
  • If one village can have only one church, you don't need a separate Churches table; you can record information about the village church in the Village1 table.

    But if a village could have several churches, you need a separate Churches table.

    The exact structure of the table depends on your requirements, but it could look like this:

    ChurchID: AutoNumber, Primary Key
    ChurchName: String
    VID: Number (Long Integer) - links to VID in the Village1 table
    Established: either Date/Time if you want to record the date, or Number (Long Integer) if you only need the year

    You can add other fields, of course, that describe aspects of the church.

    If a church always has a single pastor, you can add a PastorID field that links to the Pastor table.


    Regards, Hans Vogelaar

    Thursday, May 24, 2012 5:18 PM
  • Hi Hans,

    Typically a village can have several churches, hence I think we need a separate table for the churches as you rightly said. I think if the tables in the database have the correct relations setup and everything looks nice, I would be able to do analysis on almost anything and answer almost any questions that my boss may ask. So it is very essential for the tables and relationships to be setup correctly.

    If I understand correctly, having a churches table with the fields you mentioned above have relations to both the Village1 and Pastor tables will enable me to carry out the reports I need. This Church table would be related to the Village1 table, which would be related to the Block table and the Block table is related to the District table. I am correct ?

    If this is the logical structure of the tables, then can I answer my boss's question like, what is the average percentage growth rate of a church on a per district basis from last year to this year ?? I have fields like Year Church Established, No. of Churches in Previous Year and No. of Churches in Current Year among others fields in the Village1 table. Hence I can have a calculated field either in the Report or in the Churches table itself that subtracts Previous year from Current Year and then divide that by 100 to get the %age growth. But here comes a problem. The problem is as you probably know by now by looking at the structure of my tables in the copy of the database you have, that a single Block can have hundreds of villages inside. And to keep a track of church growth in all those villages is impossible. So what needs to be done then is, we need to calculate an aggregate %age growth of churches of all the Blocks (there can be several Blocks within a District) of a particular District, some how calculate the %age growth of the aggregate of all those churches of all Blocks belonging to a District and then make a Report of each District to see what is the %age Growth Rate of churches within that District.

    SUMMARY: A summary of the above paragraph:

    • To get a complete picture of a particular District, calculate the growth rate of all Villages
    • Then calculate the Growth Rate of all Blocks within a District
    • Finally calculate the Growth Rate on a per District basis and generate the Report of each District.

    I hope all this makes sense and I am on the correct track. 

    I have already made the church table. A church can have only a single pastor so I included a PastorID too.

    Now I need to know the next steps in implementing the above for calculating the Growth Rate on a per District basis.

    Need your help.

    Thanks.

    Regards,

    ~~Maneesh


    Friday, May 25, 2012 4:32 AM
  • We'll have to look at the report later, and get the database structure right first.

    I think we'll have to drop the VillagePastors table. Instead, the pastor is linked to a village through the church table.

    Please take a look at the modified version PastorsWithChurches at https://skydrive.live.com/redir?resid=CAE9FF0A56DD9D4!230


    Regards, Hans Vogelaar

    Friday, May 25, 2012 11:04 PM
  • Hello Hans,

    Yes I think we'll have to put the Reporting to another day and first deal with the database structure.

    I went through the file that you have on SkyDrive but this is not the way I want it to work. In our country, the name of a church in a village area is the same as the name of the village. So for instance, a village by name Murdi which has a church in it would be referred to as Murdi church. This is customary among the villagers. 

    Please see this: https://skydrive.live.com/redir?resid=6D4E421A74CACA37!123&authkey=!AC0PLnaX_eWEUi8

    You may go ahead and delete the VillagePastors table and take the Church table instead. I have included fields that I want in it like, Year Church Established, No. of Churches in Previous Year, No. of Churches in Current Year etc. I would like to have the second tab called Ministry Details Tab to be exactly like it is in this database called 'Pastors - 4 Database Structure needs improvement.' On the Ministry Details tab, I would like to select a Block which then filters the villages in the Sub Form below with the names of the villages associated with the Block chosen and this would represent the "Church" in that village, the village name itself representing the Church name and not something like St. Mary's Church, St. Joseph's Church etc. Pastors -3 database was perfect,  it just needs another table (like Church table) to be attached with instead of the VillagePastors table. And this is all I need. The  Pastors - 3 database you designed is perfect and just what I need, it only needs  to be replaced with the Church table rather than the VillagePastors table. And I see that you also have a Church Query query which also needs a little bit of a change with the fields I have in Church table. 

    So this would work like a user entering details of a new Pastor from the Pastor Details tab and then selecting the Ministry Details tab to enter ministry details. He/she then selects a District from the Combo Box which filters the Blocks which then filters the villages below in the Sub Form. The user selects a village (representing the name of the Church) and checks the checkmark. The VillCODE field would be automatically populated with the village code and the user is free to enter other details like the Year the church was established in this chosen village, enter the no. of churches established in the previous year, enter no. of churches in this current year among other things and presses the save button or the navigation arrows at the bottom of the main form in order to save the record. 

    He/she then enters pastor details of another new pastor using the same process and selects the same village or villages for the pastor, because this new pastor works in the same village as the first one. The user should be able to select the same villages for this second pastor too. This is how I would like the application to work.

    How can I achieve this ?

    Regards,

    ~~Maneesh


    Saturday, May 26, 2012 4:19 AM
  • First, you wrote that a village can have several churches, now that the church in a village has the name of the village. That seems contradictory - if the church in Murdi is called Murdi Church, how can there be more than one?

    Regards, Hans Vogelaar

    Saturday, May 26, 2012 8:53 AM
  • Hi Hans,

    Yes, a village can have several churches (or pastors) working in it at the same time. Several Pastors belonging to different denominations and organizations can be working in a given village at the same time, which is also the case. No one can restrict a Pastor to work in the same village where another Pastor is working. So a village can have lots of churches belonging to different organizations and denominations. Generally an organization or church diocese sends only one of its pastors to establish a church there. But there can be several different denominations and organizations working in that particular village at the same time.

    Well anyway, I worked on the Church table and the database in the morning, trying to see carefully how you did it with the VillagePastors table and the Village Query query. And so I tried my best to replicate that same logic and I think I got it !!! I deleted the VillagePastors table and made a Church table instead and modified the Village Query to add the fields that I wanted to show up in the Church Query. Then I tried to run the database application and it seemed to work fine !!! I tested the application by entering records of 2 new Pastors working in the same village (belonging to different denominations; both of them calling their churches Murdi Church) and saved the records. Then I opened the Church table and also the Village1 table to see the results. I found that the Village1 table was not repeating the same village name that was selected but the Church table, since it has the VID on the "Many" side of the table, showed the SAME repeating VID for each of the 2 Pastors. So this means that I did get it right, the only thing that seems to be a little flaw was when the same village is selected for the second Pastor, the village is already checkmarked. If I can get a fresh checkmark when I pull up the same village for a different Pastor, then I'm done !

    I think the application is working fine now, only little thing is that I'm not getting a fresh check mark every time I select the same village for a different Pastor.

    Please let me know if I am misunderstanding something here and whether my application will have any problems later on with this configuration.

    Regards,

    ~~Maneesh

    Saturday, May 26, 2012 10:37 AM
  • There is only one record for each village in the Village1 table, and that's how it should be.

    So the Checkmark field in the Village1 table can NOT provide information about multiple pastors. Perhaps you should move the Checkmark field to the Church table?


    Regards, Hans Vogelaar

    Saturday, May 26, 2012 11:21 AM
  • Ok, I will do what you suggested. I think this is a very good suggestion. I will let you know how it goes.

    Thanks for all your help Hans, your're GREAT !!!

    Now can we go back and do the Reports ??? Please try to pick up the question I put over there.

    See you there.

    Regards, 

    ~~Maneesh

    Saturday, May 26, 2012 11:51 AM
  • Please finish the design of the table structure first. When it works exactly as you need, we can look at the reports; if we do that now, and if we had to change the tables and their relationships, the effort would be wasted.

    Regards, Hans Vogelaar

    Saturday, May 26, 2012 12:15 PM
  • Hi Hans,

    Ok Hans, your're right. I'll do this first and get back to you. I have to be away for a few hours now and then I'll get back.

    Thanks for the advice!

    Regards,

    ~~Maneesh

    Saturday, May 26, 2012 12:38 PM
  • Hi Hans,
    I tried to move the Checkmark from the Village1 table to the Church table and also incorporated the Checkmark into the ChurchQuery query and then run the application. I tried entering a fictitious record but it does not allow me to place a Checkmark in the Checkmark box. And the laptop beeps whenever I try to place a tick in the Checkmark box.

    Then I tried to move the Checkmark to where it was originally in the Village1 table and removed it from the Church table and then ran the application. Everything went back to normal and this time I could place a tick inside the Checkmark box. 

    Don't know what wrong I'm doing here ?? What should I do now ?

    Regards,
    ~~Maneesh
    Sunday, May 27, 2012 7:34 AM
  • Sorry, no idea.

    Regards, Hans Vogelaar

    Sunday, May 27, 2012 10:21 AM
  • So, do you think we should leave the application as it is because I have no idea either how to overcome this problem. Do you think we can now move on to the Reports section ?

    ~Maneesh

    Sunday, May 27, 2012 10:51 AM
  • Could you upload the latest version? Thanks!

    Regards, Hans Vogelaar

    Sunday, May 27, 2012 2:27 PM
  • Hi Hans,

    Here you go: 

    https://skydrive.live.com/redir?resid=6D4E421A74CACA37!125&authkey=!AFsqCXMuy-EhLlk

    The new one is called Pastors Restructured - 5

    Thanks.

    ~Maneesh

    Monday, May 28, 2012 2:03 AM
  • You have added a Checkmark field to the Church table, but the Checkmark field in VillageQuery is still the Checkmark field from the Village1 subform. You should change this to the Checkmark field from the Church table. It should then be possible to tick the check box.

    Regards, Hans Vogelaar

    Monday, May 28, 2012 8:10 AM
  • In the version on https://skydrive.live.com/redir?resid=CAE9FF0A56DD9D4!231, I have added some queries and reports - not yet the ones you asked for, but overviews of the number of churches by the year they were established.

    Regards, Hans Vogelaar

    Monday, May 28, 2012 8:35 AM
  • Hi Hans,

    Yeah, Hans, this is now working superbly !!! I went ahead and changed something else a little bit. I included more fields from the Church table into the VillageQuery query so now when I ran the application, all the data is now being written to the Church table (which is what I wanted) plus I'm getting the Checkmarks too ! Working GREAT !!!

    Full Marks to YOU !!!

    Best Wishes !

    ~Maneesh

    Monday, May 28, 2012 10:41 AM
  • Hi Hans,

    Thanks for setting everything right with the Form.

    Now, I would like to work on the Reports. First, I would like to thank you for giving me an overview of the Reports with the application. However, I did not understand the Crosstab Reports. Though I did pick up some cool ideas to work with from the Reports you sent. 

    First of all, I would like a Report based on 'Count By Block', but how can I make a report for no. of churches established from previous year to current year, for TNNonBFJ, TNBFJ and TNHC also and then calculate the %age growth for that Block. This would be in the following format:

    Count By Block

    Block Name     Particulars          in Previous Year     in Current Year        %age Growth Rate

                            No. of churches

                            TNNonBFJ

                            TNBFJ

                             TNHC

    Count By District

    Block Name     Particulars           in Previous Year    in Current Year         %age Growth Rate

                           No. of churches

                           TNNonBFJ

                           TNBFJ

                           TNHC

    The above Count By Block Report would mean calculating the No. of churches, TNNonBFJ, TNBFJ and TNHC of all the villages that Pastors have entered for that Block and then aggregating the TOTAL count for that Block and then calculating the %age growth rate.

    Whereas the Count By District would be a little bit easier, which would mean having to calculate the aggregate TOTAL of all Blocks of a particular District and then calculating the %age growth rate of that District.

    Is all this possible or achievable ??

    Best Regards,

    ~Maneesh



    Monday, May 28, 2012 11:39 AM
  • What exactly do you want to count? The number of churches established in a year, or the cumulative number of churches up to and including that year?

    Let's take a simple example: say you started in 2011.

    In 2011, 5 churches were established in a certain district (or block), and this year 2012), 3 churches.

    The number of churches established per year has decreased from 5 to 3, i.e. a decrease of 2/5 = 40%.

    But the total number of churches has gone up from 5 to 8, an increase of 3/5 = 60%.


    Regards, Hans Vogelaar

    Monday, May 28, 2012 2:08 PM
  • Hmm... that's interesting. I didn't think of that before.

    Well I suppose I can include 2 columns in the Report to show both the %age increase and %age decrease. 

    Regarding counting of churches, I think its a better idea to include both total number of churches established in a year as well as the cumulative count of churches including that year. That way, if anyone wanted to know the cumulative number of churches, they wouldn't have to manually add all the number of churches up every year to arrive at the cumulative number.

    Regards,

    ~Maneesh

    Monday, May 28, 2012 3:30 PM
  • I have updated the database at https://skydrive.live.com/redir?resid=CAE9FF0A56DD9D4!231 with a series of new queries and reports.

    Regards, Hans Vogelaar

    Monday, May 28, 2012 5:11 PM
  • Hi Hans,

    I must say, great reports ! Can you please walk me through and tell me how you actually did the queries and Reports. 

    Through out the afternoon I Googled for Crosstab queries and tried to understand them. But I couldn't find a site good enough to explain the details of a crosstab query. 

    Besides the queries that you did, I would like to ask you the following:

    1. Coming back to the query I am interested in, how can I calculate Count by Block/Count by Block Cumulative for churches ?? I think this would require me to calculate the total or aggregate of all the villages in that particular Block. When I've got the totals for all the villages in that Block, I can then calculate the %age increase or decrease within that particular Block. How can I accomplish this ??

    2. Count by District/Count by District Cumulative. This I think requires to calculate the totals for all the Blocks in a particular District. Having got all the Block aggregates, we can then calculate %age increase or decrease in that District. How can I accomplish this one ??

    Thanks. 

    Best Regards,

    ~Maneesh

    Tuesday, May 29, 2012 1:02 PM
  • See http://www.addictivetips.com/microsoft-office/ms-access-2010-create-simple-crosstab-query/ for an introduction, and http://allenbrowne.com/ser-67.html for more in-depth information about crosstab queries.

    Let's take blocks as an example.

    The CountByBlock query is a Totals query that counts the number of churches established per block and per year.

    The BlocksCumulative query takes CountByBlock and the Years table to count the cumulative number of churches established per block up to each year. For example, for Akola, the number for 2010 is the sum of the numbers of churches established in 2008, 2009 and 2010.

    CrosstabBlocksCumulative creates a cross tabulation of the data from BlocksCumulative: blocks as row headers, years as column headers, and cumulative number of churches as values. The Column Headings property of the query has been used to limit the years to last year and this year.

    BlocksCumulativeIncrease takes the crosstab query and calculates the increase from last year to this year.

    I then selected this query and clicked Report on the Create tab of the ribbon to create a report.

    It's quite similar for districts.

    As far as I can tell, it does what you asked.


    Regards, Hans Vogelaar

    Tuesday, May 29, 2012 4:15 PM
  • Hello Hans,

    Ok now I understand what Crosstab reports are and how they are made. Thanks for the links you gave above.

    I still fail to understand the complex underlying queries and reports that have been created by you and have a few questions:

    Ques 1. Why did you have to include a Years table into the database ? Could we not have achieved the same results taking into consideration only the Previous Year and Current Year of each field ??

    Ques. 2. How did you make formula for counting the Cumulative values in the BlocksCumulative Query ??

    Ques. 3. How did you make the formula for BlocksCumulativeIncrease query which uses an 'IIf' formula ???

    Ques. 4. How can I make similar formulas for calculating the TNNonBFJ, TNBFJ and TNHC fields ???

    Ques. 5. In the BlocksCumulativeIncrease Report Design, would I have to manually change the Prev and Curr year field headings every time, every year ?? Why can this not be designed in such a way so that the user just opens the Reports and gets the Prev. and Curr. year headings automatically ??

    Ques. 6. You got all the Reports based on something you added manually to the Queries in Datasheet View and not dynamically when the application was running. How can I get all the Reports generated dynamically when the application runs on the fly ??

    All the answers to these questions will help me to better understand what goes on underneath and help me to build further queries and generate Reports for other fields I need.

    Thanks a lot!

    Best Regards,

    ~Maneesh

    Wednesday, May 30, 2012 7:30 AM
  • 1) I added the Years table so that years in which there were no new churches in a block or district would be included in the results.

    2) In the formula

    Cumulative: Val(Nz(DSum("CountOfChurchID","CountByBlock","BlockID=" & [BlockID] & " AND YrChrEst<=" & [Yr]),0))

    Nz is used to replace missing values with 0 and Val to convert the result of Nz (which is usually a string) to a number. The central part is the DSum function:

    DSum("CountOfChurchID","CountByBlock","BlockID=" & [BlockID] & " AND YrChrEst<=" & [Yr])

    This sums the values of the field CountOfChurchID from the query CountByBlock for all records with the same BlockID as that in the current record and with a year less than or equal to the Yr of the current record. That is, we add the number of churches established this year and all previous years together.

    3) The increase from, say, 5 to 7 is calculated as 7/5 - 1 = 1.4 - 1 = 0.4 or 40%. We can't calculate the increase if there were no churches in 2011, so the IIf returns Null (that is, a blank) if the value for 2011 is blank.

    Otherwise, we take the value for 2012 (substituting 0 if it is blank), divide by the value for 2011, and subtract 1. The Format property of the columns is set to Percent, to display the increase as a percentage.

    Increase: IIf(IsNull([2011]),Null,Nz([2012],0)/[2011]-1)

    4) Since these fields already give the numbers for last year and this year, you can use, for example:

    IncreaseBFJ: IIf(IsNull([TNBFJ in Previous Year]), Null, Nz([TNNonBFJ in Current Year],0)/[TNBFJ in Previous Year]-1)

    and similar for the others.

    5) Yes, with the current setup you'll have to change the years manually.

    6) Making everything dynamic is possible, but I fear it's beyond the scope of the help I can provide.


    Regards, Hans Vogelaar

    Wednesday, May 30, 2012 10:29 AM
  • Hello Hans,

    Thank you for the explanations to my questions. I have a few more coming up for you. I tried my best to understand your queries and the underlying logic, but I fail to fully grasp what has been done. Please explain me in detail the following:

    1. In the CountByBlocks query, where did the CountByChurchID field come from ?? And from where have you entered values into this field ?? It seems that the values were not added through the Form. If this is the case, how then can I or a data entry person generate the Report for CountByBlock from the Form itself.

    2. Instead of CountOfChurchID, can we not have CountByVID, since VID is on the "Many" side of the Church table, as Pastors are entering multiple instances of a village (many pastors working in the same village), which is being updated in the Church table ???

    3. Can you please help me understand which query relates to which Report ??? And how the queries work ? It gets rather complicated at a certain stage for me and I get completely lost, especially with the Cumulative queries and Reports. 

    Having a proper understanding of everything going on around here will enable me to move forward and create more queries and Reports.

    Sorry for bombarding you with so many questions, but these are very essential for me to learn more and move on.

    Thanks once again. Appreciate your help.

    Regards,

    ~Maneesh

    Thursday, May 31, 2012 7:17 AM
  • 1. The CountByBlock query is a Totals query - if you open it in design view, you will see that the Totals button on the Design tab of the ribbon is highlighted.

    The query groups the records by BlockID, Blocks and YrChrEst. The Total option for the ChurchID field has been set to Count. This causes Access to count the number of ChurchIDs for each combination of BlockID, Blocks and YrChrEst. The column will automatically be labelled CountOfChurchID. If the column on which you count had been named MonteChristo, Access would have labelled it CountOfMonteChristo.

    2. In the CountByBlock query, using VID instead of ChurchID wouldn't make any difference - the number of records remains the same.

    3. I gave each report the same name as the query it is based on. For example, if you open the report BlocksIncrease in design view and activate the Data tab of the Property Sheet, you'll see that the Record Source is BlocksIncrease.

    Similarly, the DistrictsCumulativeIncrease report is based on the DistrictsCumulativeIncrease query. Here is a "family tree" of this query: each level is based on the level below.


    Regards, Hans Vogelaar

    Thursday, May 31, 2012 3:16 PM
  • HI Hans,

    That is the most excellent description I have come across ! Thanks Hans !

    I tried to enter in some years for the YrChrEst field of my application to see if the Reports and queries were working correctly. Only the CountByBlocks query and Report seems to be correctly working, while the rest of the Reports are not working as they should. For example, the BlocksIncrease Report does not show the years for 2011 or 2012 as it was entered from the form. I tried troubleshooting it but could'nt do anything to fix the problem. Please have a look at it. The link is given below. The file is called Pastors - Restructured 6 - NEEDS TROUBLESHOOTING !

    https://skydrive.live.com/redir?resid=6D4E421A74CACA37!127&authkey=!AGgfVK-6Pcy04rw

    Whence you find the problem, please let me know what was wrong with it and why it was not working.

    Thanks once again!

    Regards,

    ~Maneesh

    Friday, June 01, 2012 3:06 PM
  • All the queries and reports that I created look at the YrChrEst field in the Church table. You have left that field blank, however.

    The record source of the Village subform has the YrChrEst field from the Village1 table. If you want the queries such as CrosstabBlocks, CrossTabDistricts, CountByBlock and CountByDistrict to use that field, you must modify those queries.

    For example, as it is now:

    And after modification:

    The reports will then pick up YrChrEst from the Village1 table.


    Regards, Hans Vogelaar

    Friday, June 01, 2012 4:15 PM
  • Hello Hans,

    FANTASTIC !!! When I changed the YrChrEst to the Village1 table as shown above, Reports began to pick up data for the pre and curr years.

    All the Reports run very well, except for the DistrictCumulativeIncrease. Just wanted to know why this is so ??? Though I have entered some records for year 2011 and 2012, yet when the DistrictCumulativeIncrease Report runs, both fields for 2011 and 2012 remain blank and the Increase field shows #ERROR in the field. 

    Just wanted to know why this is so ??

    Regards,

    ~Maneesh

    Saturday, June 02, 2012 6:02 AM
  • Did you open the CrosstabDistricts query and change the Church field to the Village1 table? If you do that, the DistrictsCumulativeIncrease should display the correct data.


    Regards, Hans Vogelaar

    Saturday, June 02, 2012 8:38 AM
  • Hello,

    Yes I did open the CrosstabDistricts query and changed the Church field to Village1 table, yet this does not solve the problem. The DistrictCumulativeIncrease Report still gives an #ERROR in the Increase field. Have no idea how to fix this.

    Plus, you asked me to change the YrChrEst field from Church table to Village1 table in order for the fields to pick up data for the Previous and Current Years, but doing so has created another problem. Now when I want to select a village for a particular Pastor, the village checkmark is alreay selected. What do I do now ??? Seems like if one problem gets solved by fixing a particular field, the other one gets disturbed. I don't know what's going on ?? I can't think of any idea what to do in order to fix these 2 problems.

    Regards,

    ~Maneesh

    Saturday, June 02, 2012 12:34 PM
  • You only needed to change the queries CountByBlock, CountByDistrict, CrosstabBlocks and CrosstabDistricts. The screenshot in my reply from yesterday shows explicitly what to do.

    This has nothing to do whatsoever with the CheckMark field.

    On the 28th of May, we discussed the CheckMark field - in the VillageQuery, you should use the CheckMark field from the Church table instead of the CheckMark field from the Village1 table. You agreed that this was a good idea and reported that it worked well.

    Yet, in the Pastors (Restructured6) NEEDS TROUBLESHOOTING ! database, you have reverted to the earlier version, with the CheckMark field from the Village1 table. That's why it doesn't work correctly, not because we changed the YrChrEst field.

    See https://skydrive.live.com/redir?resid=CAE9FF0A56DD9D4!233


    Regards, Hans Vogelaar

    Saturday, June 02, 2012 1:02 PM
  • Ok, the Reports work well now with the change to Church table as shown above. 

    Within CountByBlocks query I managed to add the sum function which sums the TNBFJ and TBFJ fields for previous and current years but couldn't work on the BlocksIncrease, BlocksCumulativeIncrease, DistrictIncrease and DistrictCumulativeIncrease queries to show the increase in these fields. I have deleted the TNHC field as that wasn't needed, because we are already counting churches. I tried my best to closely follow your other queries to construct queries for these but miserably failed to do so. 

    Let's take them one by one here.

    BlocksIncrease. I would like to see the increase in the no. of TNBFJ for each Block. Your BlocksIncrease query uses the CrosstabBlocks query. The BlocksIncrease query has fields 2011 and 2012 from the CrosstabBlocks query and a YrChrEst field from Village1 table. It has BlocksID and Blocks as Row Headings and YrChrEst as Column Heading with ChurchID as value. Whereas I have TNBFJ_Pr_Yr and TNBFJ_Cur_Yr fields from Village1 table. If I want to show the BlockID and Blocks as Row Headings in the Report, this leaves only one Column Heading which can be selected, either TNBFJ_Pr_Yr or TNBFJ_Cur_Yr. Whereas I need both fields in the Report to show the values. Next, what should be selected for the Value field ??? The same is true for the TBFJ_Pr_Yr and TBFJ_Cur_Yr fields.

    I can't move on unless this query is constructed to show the BlocksIncrease.


    Regards, Maneesh


    Sunday, June 03, 2012 7:30 AM
  • The TNNonBFJ_Pre_Yr etc. fields are very different from the other fields because they already contain a count for previous year and current year, so you shouldn't look at YrChrEst when calculating the increase. In other words, you can't do that in the same queries we've been discussing above. You'll need separate queries for that.

    I have updated the database at https://skydrive.live.com/redir?resid=CAE9FF0A56DD9D4!233 with new queries. You can let Access create reports from these queries for you.


    Regards, Hans Vogelaar

    Sunday, June 03, 2012 10:25 AM
  • Hi Hans,

    The Reports are working very well. Thank you so much for helping me do this. I couldn't have done it without your help !!

    I have gone ahead and made the Reports too with those queries. 

    I would like to have a button on the first tab page, Pastor Details, which says, "Next >" to be able to navigate to the second tab page, Ministry Details, but I searched several websites and resources on the web and its rather strange that I couldn't find a way how to do this ??

    The Next > button is also visible on the second tab page, which I don't want. On the second tab page I want a button which says,             "< Back", and something like the "Next >" button being dimmed out. Other buttons I would like to have on the 2nd tab page are Save and Cancel. Pressing the Save button should pop up a dialog box, informing that the current record has been saved to the database. When the user presses the close button on the dialog box, it should either go back to the Pastor Details tab page or to the Main Switchboard page. I also want if that if the user presses the Cancel button on the 2nd tab page, all changes he/she was making to the database, either adding a new record or editing an existing record should be rolled back. While if the user adds or changes an existing record, but forgets to click the Save button, instead closes the form itself, I want the form closing event to ask if the changes need to be saved. If the user presses OK, the changes are saved to the database. If the user presses NO, the changes are not saved.

    All of this sounds quite simple in theory, but I've looked at a lot of resources on the web which do not give any idea of how to do this.

    Hope you can help me with this too.

    Thanks once again!


    Regards, Maneesh

    Monday, June 04, 2012 2:02 AM
  • I don't have time at the moment, sorry.

    Regards, Hans Vogelaar

    Monday, June 04, 2012 5:38 AM
  • Hi Hans,

    Well thanks for all your help and support.

    Can you guide me to some possible resources for the above ???

    Anyway, you've been a great help. 

    Thanks a lot.


    Regards, Maneesh

    Monday, June 04, 2012 5:51 AM
  • Hi Hans,

    Can you please do me a favor Hans ? Please either remove the links of the posts which point to the Skydrive folders in these posts or remove the folders from your SkyDrive inbox pointing to the Pastors folders for security reasons. Please do this as soon as possible.

    Thanks a lot.


    Regards, Maneesh

    Tuesday, June 05, 2012 7:30 AM
  • Hi Hans,

    This is my second request for you to please remove all Pastors folders from your SkyDrive account for obviuos security reasons. 

    I hope you're reading your mail and would comply with the above request at the earliest.

    Thank you.


    Regards, Maneesh

    Wednesday, June 06, 2012 5:54 AM
  • I was away from home and from computers for a few days, hence the delay in replying. I have removed all files from the Pastors folder on my SkyDrive.

    Regards, Hans Vogelaar

    Wednesday, June 06, 2012 6:21 PM
  • If you click on the first tab of the tab control and then add a control such as a command button to the tab page, that control will only be visible on the first tab page. The code for the Next > button could look like this:

    Private Sub cmdNext_Click()
       Me.Tabs.Value = 1
    End Sub 

    and that for the < Back button on the second tab:

    Private Sub cmdBack_Click()
        Me.Tabs.Value = 0
    End Sub

    where cmdNext and cmdBack are the names of the command buttons.

    Access automatically saves records, there is no need to have a command button to do so.


    Regards, Hans Vogelaar

    Wednesday, June 06, 2012 9:08 PM