Report based on a combination of 2 queries.

[George-Bowyer]

I have a database that reports activities by region.

Each week, my regional volunteers report statistics on a number of club activities. This is in the form of zero to theoretically infinite activity reports that they enter on a website. I download the .csv from the website, add the activity reports to the activity table and send them a totals summary every now and then.

The summary report shows figures for every club in the region, even if no activity reports have been entered for that club that week or ever.

This works fine, including forcing the query to return zeros when no reports have been submitted for that club.

What I want to do is have the report also show (in brackets next to each figure) the position as it was X number of days previously.

I can make the query and report to show the figures now.

I can make the query and report to show the figures X days ago.

What I cannot work out is how to combine the two queries into one report source so that I can get

Club 1 100(50) 75(0) 45(45)
Club 2 0(0) 0(0) 0(0)
Club 3 20(19) 0(0) 200(50)

etc

If I try and make a third query that gets the sums from qryNow and the sums from qryXdaysago for each record in qryClubsByRegion, I get two lines for each club.

:banghead:



Can someone point me in the right direction, please?

 

[CJ_London]

I think what you need to use what is called an aliased table (or 2)

Rather than providing the sql, since you haven't provided table or field names
Lets assume the table that contains the results is called tblActivities

Open a new query and in the query window, drag the table in twice (or three times)

you will see one instance is called tblActivities, the next is tblActivities_1 and the third tblActivities_2 - the latter two are aliased

Hopefully you will also have a table called (say) tblClubs which has records unique to each club - if so drag this in as well.

Now left join from tblClubs to each of the three tblActivities - this will ensure you bring through all results.

If you don't have a tblClubs left join from tblActivities to the other two tables - this will at least bring through results for the current week plus comparisons

now bring down into the grid the club name and the results column from the other three tables.

next bring down the date field that you report on - I'll assume they could be any day

for the criteria,
for tblActivities it will be >dateadd("d",-7,Date())
for tblActivities_1 it will be Between dateadd("d",-14,Date()) and dateadd("d",-8,Date())
for tblActivities_2 it will be Between dateadd("d",-21,Date()) and dateadd("d",-15,Date())

Now click the epsilon to group the query - group on club ID and sum the results and for the dates, change to where

This gives you the principle and I've assumed a lot regarding your table structure but hopefully it is close to what you require

 

[George-Bowyer]

Thanks for this. I seem to be nearly there.

My problem is that in cases where there are no records for Clubs in the "date-7" version, it is blocking showing the records from the "now" version, also.

To explain that a bit better: say 10 clubs had sent in records this week, but only 5 had last week, the query is yielding the two comparative figures as desired, but only for the 5 clubs that had records last week as well as this.

The records that have come in since date-7 have been suppressed, whereas I need the Club to show, with a zero value for last week and an accurate value for this week (even if this week it is zero also).

Thanks.

G

 

[CJ_London]

if you post the following, I should be able to advise further

a screenshot of your relationships
some sample data which includes records being brought through and some that are not, but should be
from this sample data an example of what the report should look like
the sql of the query you are using for the report

 

[George-Bowyer]

I have attached a sample db to show the problem.

The work reports submitted are in tblWorkDone.

You will see that there is a fldDate, which refers to the date it happened and a fldDateAdded, which refers to when the report was added to my db. It is fldDateAdded that is important.

rptGood shows the information as I want it, with a total for every Club (actually the clubs are further divided into sub areas (fldOrgID) which I subtotal in the report, but I don't think that's relevant here) - even the ones for which no reports have been submitted.

What I want to show is a figure in brackets in by each total in rptGood, which is the total for each column that had been submitted up until 7 days previously (by fldDateAdded), so that the recipient can see at a glance how much the figures have changed in the past week (or whatever).

qryReportSource shows that if I try and merge the current and historical figures into one query for a report, it only shows results for some clubs and also multiplies the figures so that the totals become thousands not hundreds.

I hope that this explains my problem.

Thanks.

George

 

[CJ_London]

For the brackets, you need to format the control of your report for example put

(0.0)

in the format property of the relevant controls

If you tried to put the field into brackets like this for example

Field1-7: "(" & Sum(Nz([tblWorkDone_1]![Field1],0)) & ")"

This changes the type from a number type to a text type, which importantly you cannot then sum - it will also be left justified and will sort differently (tho' not sure if this matters), so you will need to format your controls accordingly as above

it only shows results for some clubs ...

... also multiplies the figures so that the totals become thousands

I've reviewed your queries and they are more complex than they need to be so I have simplified them - the three queries have a CJ suffix. If you want zeros to appear in place of blanks then you will need to use them in the final query - I've done one as an example

 

[George-Bowyer]

Thanks for that.

I have decided to go with the date criteria "Between Date() And Date()-6" for fldDateAdded in qryWorkByClub-7CJ, so that I can show the total submitted in the last 7 days in brackets.

That seems to throw up the results I want in qryReportSourceCJ.

However...

Unfortunately my statement above: "(actually the clubs are further divided into sub areas (fldOrgID) which I subtotal in the report, but I don't think that's relevant here)" proved to be over-optimistic.

I attach a new copy of the sample db.

I have updated qryWorkByClubs so that rptGood shows the totals sub-divided into areas as I want them.

I have updated qryWorkByClubsCJ and qryWorkByClubs-7CJ (now with GB suffixes) to show this change.

However when I combine the two in qryReportSourceGB, the clubs with results in both queries get multiplied.

Eg: "Club 08" (fldClub = "119") shows 4 records in qryWorkByClubsGB, three of which are also in qryWorkByClubs-7GB. So qryReportSourceGB shows 12 records instead of 4, 3 of which should also have results from qryWorkByClubs-7GB

qryWorkByClubs-7GB is a sub-set of qryWorkByClubsGB, so therefore I somehow need to limit the clubs/areas shown in qryReportSourceGB to only those shown in qryWorkByClubsGB (with the results from qryWorkByClubs-7GB being shown in the same lines.)

I hope that makes sense.

Thanks again.

George

 

[CJ_London]

I have decided to go with the date criteria "Between Date() And Date()-6" for fldDateAdded in qryWorkByClub-7CJ

I think you are misunderstanding the way this works - go back to my original queries and see what I have as criteria. What you have changed the qryWorkByClub-7CJ to is only 1 day different to what is in qryWorkByClubCJ so will produce the same result.

It would also help if you used the relationships window to show how your tables work together - you have lots of missing links and it looks to me like your tables are not constructed correctly so not possible to know the 'flow of ownership'. I would guess that

1 VKregion has many clubregions
1 clubregion has many organisations

but the tables don't link like that