Adding fields from different reports

[adrianvasile]

I have a quick dilema and I was hoping maybe someone could provide insight. Here is my setup:
I have 5 reports - Monday through Friday that will summarize the number of accounts that a Salesman will visit during the day. Then, I have a command button on a form that will run a Macro that will print all reports at the same time - called Print Entire Week. I would like to have on the last report, Friday a count of all records from all reports together.
Each report gets a total by using the RecordCount feature; is it possible to create a formula to add all recordCounts from all reporst?
Thank you

 

[Remou]

You could use a subreport that summarizes the week on the final report.

http://lessthandot.com

 

[adrianvasile]

I guess I could but what formula will I use? How will I reference those fields from each report?

 

[Remou]

Do not reference the reports, reference the table or query on which those reports were built.

http://lessthandot.com

 

[adrianvasile]

I created the summary in each report by using recordCount feature. I don't have that in a querry so I can use it further in summary

 

[Remou]

You can build a query that uses count. Please post the SQL that the first report is based on (SQL is something like SELECT this, that FROM sometable), if you do not have this, what are the fields that you are using in the report?

http://lessthandot.com

 

[adrianvasile]

Ok - this is teh SQL

SELECT qryrbrmas.SMDAY5, qryrbrmas.CMACCT, qryCUSTOMERS.CMDBA, qryCUSTOMERS.CMSTR, qryCUSTOMERS.CMCITY, qryCUSTOMERS.CMPHONE, qryrbrmas.SEQDAY5
FROM qryrbrmas INNER JOIN qryCUSTOMERS ON qryrbrmas.CMACCT = qryCUSTOMERS.CMACCT
ORDER BY qryrbrmas.SMDAY5 DESC , qryrbrmas.SEQDAY5;

Based on this querry I run a report that will show the accounts to be visited for each SM. I wasn't able to create a formula that will generate a count.

Thank you for your help

 

[Remou]

Try this as a query, that is, cut and paste into SQL view of the query design window. I am not saying it is right, because I do not know what the fields mean, but it may give you an idea.

SELECT qryrbrmas.SMDAY5, qryrbrmas.SEQDAY5, Count(qryrbrmas.CMACCT)
FROM qryrbrmas INNER JOIN qryCUSTOMERS ON qryrbrmas.CMACCT = qryCUSTOMERS.CMACCT
GROUP BY qryrbrmas.SMDAY5 DESC , qryrbrmas.SEQDAY5;

http://lessthandot.com

 

[adrianvasile]

It won't work: syntax error. How about a new field using dcount maybe?

 

[Remou]

How about we correct the syntax error by removing DESC from Group By?

CODE
SELECT qryrbrmas.SMDAY5, qryrbrmas.SEQDAY5, Count(qryrbrmas.CMACCT)
FROM qryrbrmas INNER JOIN qryCUSTOMERS ON qryrbrmas.CMACCT = qryCUSTOMERS.CMACCT
GROUP BY qryrbrmas.SMDAY5, qryrbrmas.SEQDAY5;

http://lessthandot.com

 

[adrianvasile]

It did not work. I was able to get it working by using a dcount function but it will count all the record. When I invoke the report a filter will be applied and it may not work.
I will keep trying.

 

[Remou]

In what way did it not work? DCount is not suitable, as you already discovered. Please post a small section of data.

I am not doing so well with cut-and-paste:

SELECT qryrbrmas.SMDAY5, qryrbrmas.SEQDAY5, Count(qryrbrmas.CMACCT)
FROM qryrbrmas INNER JOIN qryCUSTOMERS ON qryrbrmas.CMACCT = qryCUSTOMERS.CMACCT
GROUP BY qryrbrmas.SMDAY5, qryrbrmas.SEQDAY5;

http://lessthandot.com

 

[adrianvasile]

Here is a screen of my data:
SMDAY5 CMACCT CMDBA CMSTR CMCITY CMPHONE SEQDAY5 Total
95 31263 GIANT EAGLE #0209 900 NORTHFIELD RD BEDFORD 4404391800 10 2778
95 41800 MARC'S SOLON #15 6231 SOM CENTER RD SOLON 4402488404 20 2778
95 06520 GIANT EAGLE #0213 20 SHOPPING PLAZA CHAGRIN FALLS 4402478777 30 2778
95 33200 HEINENS #05 30849 PINETREE RD PEPPER PIKE 2168318300 40 2778
95 00842 GIANT EAGLE #0201 34310 AURORA RD SOLON 4402484400 50 2778
95 41625 GILLOMBARDO G E #5844 1825 SNOW RD PARMA 2163982980 60 2778

I would like to have the Total field calculate the number of either accounts or SMDAY5.

 

[Remou]

One possibility:

[tt]SELECT q.CMACCT, Count(q.CMACCT) AS CountOfCMACCT    
FROM [Name of Your Query Here] As q
GROUP BY q.CMACCT[/tt]

http://lessthandot.com

 

[adrianvasile]

did not work.
It is OK - I'll work around it
Thank you for your help

 

[Remou]

It is usually best to say how something did not work.

http://lessthandot.com

 

[adrianvasile]

Well it lists only 2 columns and it doesn't add up the accounts. I will need to have all original columns and then probably an extra one that will be the count.
I wish I could show you some screen shots maybe it will be more clear.

 

[Remou]

What you say seems a little contradictory to me. If you want a summary, you cannot have all the columns, because they are not relevant.

For example, just looking at the data you posted above, the summary of SMDays would show:

[tt]SMDays Count
95 6[/tt]

Everything else is irrelevant.

For CMACCT analysis, you could show more, because presumably these are companies, so the address could be included, however, if you include too many fields, you no longer have a summary, you have a list.

Take the query I posted above, and switch to design view, once you have changed [Name of Your Query Here] to the name of your query. Now you should be able to drage fields down to the grid:

[tt]CMACCT CMCITY CMACCT
.....
Group BY Group By Count[/tt]

Does this get you any further?

http://lessthandot.com

 

[adrianvasile]

See, I use the last column of a querry (let's say called TOTAL) to generate totals on a report. So basically, I will create a querry with all the fields that I need to have on my report then I create calculated fields that I will use to summary data in a report.
Does that make sense?
I want to be able to do the same with this query: run a simple querry that will list all the accounts for a SM and the at the end add a calculated field that will count the total accounts for that particular SM( salesman).

Thanks for the help with this.

 

[Remou]

That sounds about right. Play around and if you get stuck, post the sql back with a note on the problem.

http://lessthandot.com