Query Totals to Report?

[Neo-fite]

This is probably a REALLY stupid question, but...

I have several queries that I would like to have the total number of records and the sum of the $ amounts of those records/queries appear on a report...being new to Access, how is that done?

 

[cookiegary01]

Dear Neo-fite,
A question is never stupid. Not asking is!

I'm presuming that you can create a report from the query, and you have the fields that need to be calculated, in the "details" area of your report.
In your report design view, add a "Textfield" (looks like "abI" in the workset) in the Report footer or header of the report.
In this field type Sum([fieldname]) and that's it. The fieldname is the field you want totalized.
To count the records, do the same, but now type Count(*)

 

[Neo-fite]

Dear Neo-fite,
A question is never stupid. Not asking is!

I'm presuming that you can create a report from the query, and you have the fields that need to be calculated, in the "details" area of your report.
In your report design view, add a "Textfield" (looks like "abI" in the workset) in the Report footer or header of the report.
In this field type Sum([fieldname] and that's it. The fieldname is the field you want totalized.
To count the records, do the same, but now type Count(*)

Actually, I don't have "Totals" fields in the queries...but I did use the Totals/Sigma in the datasheet view to display totals. Is there another way?

 

[cookiegary01]

Do you know how to create a report from your query?
If yes, then read my previous thread.

If not, then I suggest you learn to create a report , and in design view add the query.
Then add the $ field to the details section, then read my previous thread.

 

[Neo-fite]

I can't get this to work properly. I added the textbox and count get the total to appear, but I also would get all of the records from the first query...which I don't want.

I also tried to create a report and include all the necessary fields for each of the queries, but that seems to have an issue the queries using the start/end dates that are entered on another form and used in those query conditions.

Basically, each of the queries has a list of items in the various stages of a process. What I am looking to create for a report is to display just the Count of the items and the total of the dollar amounts of each item.

 

[cookiegary01]

Hi,
I understood that:
- You have 1 query that is working fine in the display view (even though you use the SIGMA to totalize in view mode).
- You also have a form with date1 and date2.
- The query reacts to the dates on the Form correctly in view mode.
If this is all correct, then I presume you have entered criteria in that query, refering to the 2 dates on the form.

You now need to make a report from this "Final query".
Make a report from that query, leaving the Form with dates open, so that the query in the report finds the dates on the open form. The new report will look at that query as a record source, which contains the 2 dates.

 

[cookiegary01]

Hi,

Perhaps it is a good idea for you to practice reports by using the "New-Object" Report wizard.
Try the "Wizard report" option, select the Final query, then when done, view the report in design view to analize it.
I'm sure you'll learn a lot from that.
You could also mail me screen shots of the form + query design view.
I understand that the query + report are working together on the dates?

I want to help, but we are beating around the bush here.

 

[Neo-fite]

Hi,
I understood that:
- You have 1 query that is working fine in the display view (even though you use the SIGMA to totalize in view mode).
- You also have a form with date1 and date2.
- The query reacts to the dates on the Form correctly in view mode.
If this is all correct, then I presume you have entered criteria in that query, refering to the 2 dates on the form.

You now need to make a report from this "Final query".
Make a report from that query, leaving the Form with dates open, so that the query in the report finds the dates on the open form. The new report will look at that query as a record source, which contains the 2 dates.

I've created summary queries for each of my queries that only display the Total Number of the items in the query and the Sum of the Dollar Amount. I then tried to create a report that would use those figures.

I started with a blank form and added a text box and use the property sheet to select the summary query and the totals field so the Control Source appears as:

=[Totals - 01]![CountOf Items]

I open the date_entry form and enter my start/end dates and then the new form view fills the text box with "#Name?" I also did see an error that said "You have chose fields from record sources when the wizard can't connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query."

 

[Neo-fite]

Hi,

Perhaps it is a good idea for you to practice reports by using the "New-Object" Report wizard.
Try the "Wizard report" option, select the Final query, then when done, view the report in design view to analize it.
I'm sure you'll learn a lot from that.
You could also mail me screen shots of the form + query design view.
I understand that the query + report are working together on the dates?

I want to help, but we are beating around the bush here.

I started to "toy" with making some other reports and had a report query appear that allowed me to add the summary queries to it...since I was just toying, I only added a couple. How can I add more? I can't find the report query.

 

[cookiegary01]

Hi,
You can find the fields in the report design view:
Menu... View... List with fields.
Now add the fields with "Drag and drop"

 

[Neo-fite]

Hi,
You can find the fields in the report design view:
Menu... View... List with fields.
Now add the fields with "Drag and drop"

Oy....I'm not seeing those options.

Are those in Access 2007?

 

[cookiegary01]

Yes, they are there. Check this site:
http://www.teacherclick.com/access2003/t_12_3.htm
Go to "The report design bar"
You need list of fields box

 

[Neo-fite]

Yes, they are there. Check this site:
http://www.teacherclick.com/access2003/t_12_3.htm
Go to "The report design bar"
You need list of fields box

I'll take a look. (At first glance, it is 2003)

 

[Neo-fite]

When I create/Blank Report I have my "Field List" to choose from...however, the only fields data I have to pick from are the fields from my table. I am not able to select the fields from the Queries.

 

[cookiegary01]

Because you based that report on a table.
You must base that report on your query.
Do you know how to base a report on a query?

 

[Neo-fite]

Yes, I can create a report based on a query...then I only have that query's fields to choose from.