NZ and Sum by Group

Here is my SQL. i am trying to Sum the Stock and Used data by DateEnter and if its nothing I want it to show up as a 0.

But I get the following error
You tried to execute a query that does not include the specified expression 'Item' as part of an aggregate function

INSERT INTO AllDataTogether ( DateEnter )
SELECT RawInfoUNION.*, [enter date] AS DateEnter
FROM (SELECT Information.Item, Location, Supplier, Used, Sum(Nz(Raw Data]![Stock],0)) AS Stock FROM Information LEFT JOIN [Raw Data] ON Information.Item = [Raw Data].Item
UNION SELECT [Information 2].Item, Location, Supplier, Sum(Nz(Raw Data 2]![Used],0)) AS Used, Sum(Nz(Raw Data 2]![Stock],0)) AS Stock FROM [Information 2] LEFT JOIN [Raw Data 2] ON [Information 2].Item = [Raw Data 2].Item) AS RawInfoUNION
WHERE (((RawInfoUNION.Location)="NJ")) OR (((RawInfoUNION.Location)="CA"));

Post your answer or comment

comments powered by Disqus
Hello there,
i am Trying to sum the total quality scores and then divide it by the sum of the total checks. At times their are 0 values in both the fields for a particular day. To fix that I trying to use the nz function however without any sucess. the Query runs but the output on a datasheet gives #Error.
My query is
SELECT FinalledAccounts.[Manager Name],Sum(Nz([Quality Score 1])) +Sum(Nz([Quality Score 2])) AS [Quality Score] ,Sum(Nz([Number Of Checks])) AS [Total Checks],Nz([Quality Score]/[Total Checks]) AS [Total Quality Score]
FROM FinalledAccounts
GROUP BY FinalledAccounts.[Manager Name], FinalledAccounts.WeekDate;

Kindly throw some light guys. Thanks

I should know how to do this by now, but reporting is one of my weak spots

My report groups:

Basic info:
This is a mailing tracking database. We mail letters out and track results. The main form contains a field called "MailListSize" (number of letters sent), there is a subform that contains the results which can rusult in multimple entries (one-to-many with the main form), such as "appointment set", "contact management entered"...

What I need:
On the report I want a total for the campaign, by district of, the number of letters sent. Then I want a grand total of the letters sent.

The problem:
It seems the number of letters sent is being counted multiple times. The query results display a duplicate main form result for every subform entry. So if one main form entry contains two subform entries, the letters sent is counted twice when trying to sum the results in the report. This all seems to be a result of the one-to-many relationship of the two forms that are entered into.

I thought grouping would solve the problem, but I can see to find the right grouping combination to get the results I need.

Any help would be appreciated!

Hi, I'm hoping someone can help me; not very good with MS Access yet and definitely not versed in VB. Using Access 2002-2003. What I'm trying to do is Group / calculate on 2 types of incidents Sorted (and 'summed up' by Day of the week. So if we had Incident1 3 times Sunday this week, 4 times on Sun last week, we'd see 7 Incident1's on Sunday. Add to that, ie: 4 Incident 2's on Sunday this week and 1 Incident2 last week, we'd see 5 Incident2's also under Sunday. With those added together for Sunday - seeing 12 total incidents for Sundays. Does that make sense? I've searched and I'm able to get the query to use 1-7 as days of the week, but don't know how I can transfer that to the report (I already have the query as the source for the report, but only see the table's fields as options for the report).

is it possible to sum up different type of group?
For example..

a sales table contain date,brand,code,colour,size and qty
in this table there will be records that have same brand,code,colour,size but different qty.

is it possible to count the total qty by grouping the brand,code,colour,size ?

I did a search for Gouping and sums, but nothing really helped me.

What I am trying to figure out: I have a table where I am trying to use 4 fields in a query.

Vendor, items, Cost, Date1

- I am trying to Group by Vendor, Sub-Group by Items (Easy, I did this)
- Now I am trying to Filter this by date1 (Easy, I did this)

But I would like to sum the Cost for the Groups, but not list every repeated item. For Example:

Vendor A     Brush     100     1/1/2005
Vendor C     Book      50       1/2/2005
Vendor A     Brush     100     1/3/2005
Vendor B     Pencil     10       1/4/2005
Vendor A     Hat        50       1/5/2005
Vendor B     Pencil     10       1/6/2005
Vendor B     Pen       10       1/7/2005
Vendor B     Eraser    10       1/8/2005
Vendor A     Shoe       40      1/9/2005
Vendor D     House     1000    1/10/2005

Doing a search from 1/1/2005 - 1/9/2005 we get:


Vendor A     Brush      200       2
             Hat       50        1
             Shoe      40        1
Vendor B     Pencil     20        2
             Pen          10      1
             Eraser     10        1
Vendor C     Book        50       1

Hi everyone,

I have one table that I want to query. It's a basic table that contains:

1234430, 100
1234435, 200
1234435, 100

1264435, 300
1264430, 200

1154435, 100
1154435, 219

1294430, 983
2010, 845
........ so on and so forth

Now, I simply want to GROUP MyKEY ENDING with 4430 and 4435 into one line and SUM the AMOUNT, thus showing like this:

1234430and1234435, 100+200+100 --> (123 being the first common group)

1264430and1264435, 300+200

1154435and1154435, 100+219

etc etc.

i tried using simple sum and group by but does NOT work:

WHERE MYKEY LIKE ('*4430') OR ('*4435')

It does NOT ROLL-UP the 4430 and 4435 together.

QS: do i need to use VBA for this?

QS: do i need to have subqueries?

QS: please help?


Greetings Access Forum!

I have another question thats bugging me.
I have a table like this
Code: Sim_Run Price, Number, Type 1 50 1 A 1 100 3 B 1 150 5 C 2 200 2 A 2 250 3 B 3 300 1 A (How to use this "TABLE" thing right)

Now i have a query1:
SELECT Sim_Run, Sum(Price*Number) AS Volume, Type
FROM table
GROUP BY Sim_Run, Type;

And a query2:
SELECT Sim_Run, Sum(Price*Number) AS Volume2
FROM table

But since table is very big opening it twice costs much time.

Im sure there is another way of doing this task. What i basicly want is just to add "sum(Volume) Group By Sim_run" into query1 than all would be finished.
But all the ways i tried i get an error.

All i want is tu sum up the "Volumes" for each Sim_Run despite the type.
But i also need the type and Volume data.

Any Idea?

Thanks in advance

Hi All,

I am pretty new to the Report Function of Access. I would like to know how I could how the Total of a particular column grouped by a few categories. I tried searching it in Help and not be able to find it.

Thanks a lot for your help!

What I have is payment data for bond repayments. Each year there are two payments. I'm able to get all the data in one query with each type of bond payment summed by date. However, I also want to list the total payments for each fiscal year in the report in the row for the second payment each fiscal year. I calculated the total fiscal year payment in another query by grouping by fiscal year. However, I don't know how to create the report using mulitple record sources and I don't know how I could put all the data in one query because I don't want the overall data grouped by fiscal year. From what I've read on subqueries that wont work because I am not trying to reduce the overall number of records. I just want another column of data that lists the total payments for each fiscal year. The total number of records I start with is around 5000. The number of records for the report is 964.

hey folks,

I am trying to run a query on sql server using the query analyzer ... the query works just fine, but I am attempting to IGNORE any instance where a unique identifier has a sum of units less than zero, and the query doesn't seem to do that:

select provider, fiscal_year, count(distinct ID), sum(finalnetamt), sum(new_quantity) from claimsunion where (stat = 'paid') group by provider, fiscal_year having order by provider, fiscal_year

The above correctly renders to me the proper counts and sum of units and dollars by provider and fiscal year.

Some of the IDs have a 0 sum of (new_quantity) and those I am attempting to ignore from the count, because they wash out/net 0, and should not be counted ... so in an attempt to do that, I am trying a HAVING clause ...

HAVING sum(new_quantity) >0

which renders the same answer and does not remove the IDs that have 0 from the count. I think it is because I don't know how to code that piece that tells sql that it is a having sum(new_quantity) >0 by ID within the group, if that makes sense?

Any help would be greatly appreciated. I know that if I add ID to the group and then use the HAVING sum(new_quantity) >0 it works but it gives me every single ID number and I just want the count not every single ID number.

Thank you very much!!!

Hi all,

How can I change the following table..I've tried group by and sum but no good

Here are the fields and table

Starting with...something like this

FisherID, FishedState, FishedOtherState, DaysFished
1................Y................................ ............2
1.......................................Y......... ............3
2................Y................................ ............2
3.......................................Y......... ............5

Wanting to get....this

FisherID, FishedState, FishedOtherState, DaysFished
1.................Y....................Y.......... ...........5
2.................Y............................... ............2
3.......................................Y......... ............5

You can see that the record for Fisher 1 has been merged and DaysFished summed. I wouldn't have thought this was too tricky but its got me stumped today!

Any help is appreiciated


if I have the following amount other names in a table and I am trying to group them and sum them up. Curently i have figured out how to strip out the 3 names from the table but what i am trying to really do is group them as a BofA field with the sums of the 3.. how could i accomplish this?Thanks
Name 2010 2011 2012
BofA 1 22 44
Country Wide 55 66 88
Merrill Lynch 23 12 42

I have a report that has a grouping on 6 different items from a table. And I have sums over different items within the 6 groups. I have the report so it gives a total sum of all 6 groups. But if I would like it to give me the sums of say groups 1-3, and then a different sum for groups 5 and 6 how would this be done?
Thanks in advance for the help!

I have a report that sums by group. Each new group begins on a new page & I have page numbers in the page footer that reset to 1 with each new group. I have the grand total print on a new page (the last page) of the report, and I want to hide the page footer on this last page because it doesn't make sense (it becomes "Page 1" because of reset function).

I tried the code:

If [page]=[pages] then pagefooter.visible = false

but it doesn't work because the last page's [page] is not equal to the report's [pages].

Does anyone have a suggestion?

Thx, Neal


I don't know if this is possible, but what I want to do is sum a value over 2 different grouping levels but not over the report as a whole.

I have a field which contains a number of hours, calculated for each month. I would like to sum these hours per job, so i have set up another control showing number of hours summed over group. This works fine.

Now I have to show the number of hours since last invoice as well as total hours for the job.

What I would like to do is set up a group for the invoice amount so the hours are totaled since the last invoice month. I can do this but not keep my running total for all hours as well.

Is it possible to have a running sum for group for 2 different levels of grouping, and if so how do I do it.

The answer is probably to get the numbers I want before producing the report, but I'm not going into details of the entire data structure here, as to produce this report I already have several layers of total, crosstab and union queries, so i don't want to give anyone else the headache!

Thanks for any feedback


I have a report that have a subreport. The report is like an invoice and in the subreport are the rows of that invoice.

The subreport has a group (number_doc) and i want to show, in the main report in page header, the number of page and total pages by group (like 1/5), i.e., i want to reset the total page number for each "number_doc" of sub-report.

Anyone knows if this is possible and how can i do it?

How can I set my page numbering by group within a report, so that it restarts at page 1 for each group? I tried to follow MS Access help, creating a macro, setting the OnFormat and ForceNewPage properties, etc. but that did not seem to work

Hi All,

How do I calculate a record by group?
I have 3 groups in my report:
- ActivityDate
- FunctionsID
- EmployeeID

I want to calculate how many records are in the employeeID.

Also, is there a way to number the employees? (let's say i have 7 employees, and i want each employee to have a number 1 - 7)

I don't have any entries under Detail section.

Thank you,

I have a data base that is used to track our 4-hers. there are three fields that i have to total. here is and example of what i am tying to do in a report.

for back number 123

total all the points found in [fair points] and [fair questions] and [may point show] and [june point show]

this then repeats for each of the back numbers

the report is grouped on back numbers I have a text box that but i can not figure out what the control should say. my text box placement works if i do a single field and total over group, but when i add an addition field it does not work... any suggestions

Hi, I have a report in which I want to create individual reports by group in "pdf" format, save it in a folder with the name of the group and name the report in a standard way.

For example, let's say that the group is "Plan ID" and has the values: 101, 102, 204, 405 and in the report there is a variable called: service month (example: 201101). I want to be able to create a report for each Plan ID, save 101 in folder name 101 and so on and in the name it has the month and plan id like: "this_is_thereport_101_201101.pdf"

Thank you in advance for your help. I need to produce a report listing items sorted by budget codes. The code itself is reported in both the page header and footer. The report contains a budget code header and footer also. Where I run into trouble is that while a single code may span multiple pages, I must number and sum the items on each page. In other words, I need to produce a simplified report that looks like this:

Budget Code: 1234

Item Description Cost
1 gloves 4.00
2 frogs 0.50
3 stamps 6.00

Page Total: 10.50

And, code 1234 continues over serval more pages.

Any ideas? Thanks!

Firstly, i am a sql newbie so apologies if this is an obvious answer

I need to joing a table to another but unfortunatly theres no one to one relationship. What I would like to do on the table with mutiple records is is group by the key and sum by amount . I then want the query to select the max amount by record within the group and show me the details of the max amount. Is there a tecnique I can use that’s not to complicated. Any help is appreciated


Key Amount text
1 10 Wrong text 1
1 5 Wrong text 2
1 1000.26 RightText

The query should return

groupedKey SumAmount MaxAmount Maxtext
1 1015.26 1000.26 Right Text T

I have a network of ~80 users from 12 departments and 14 printers, some of which are colour.
Each month I generate a report in CSV format from my print charger application.
The report has column headings.
The report includes all print jobs to all printers on the network and it is sorted alphabetically by user name where the user name format is lastname.firstname
I wish to create a summary of the report that indicates which users printed to the colour printers.
I presently 'massage' the CSV file to a point where I can use the subtotals feature of excel to yield the report I need.
By 'massage' I mean,
-there are several column fields that I don't need so I delete them
-I sort by printer and delete all jobs to all BW printers
-there is an 'attributes' field and I delete all 'failed' and all 'gray' print jobs NB: The 'attributes' field may indicate null (blank) or 'colour' for a successful colour print job
-I sum the 'pages' each user printed then multiply by $0.50 to yield a cost per user.
The report does not include the user's department so I use a vlookup table to cross-reference the department then I sort by department, then by user with user and department totals.
This report takes a long time (many hours) and I believe I can use a script or an Access query on the CSV file to achieve the same outcome in the blink of an eye.
15 years ago I wrote code using 'Clipper'. Presently I tinker with the odd VB script. I understand the 'logic' of what I want to achieve, but I do not know the beginnings of extracting the information from my CSV file.
The CSV file is consistent in that if a field is blank, there is simply another comma delineating the next field. (I have seen cases where this is not the case in so-called CSV files, eg. online phone-calls-made statements)
Essentially, what I think I need to learn is
1.How to 'read' the header row and how to relate the header to the rows below it.
2.How to extract the user data that pertains to successful colour print jobs and sum the number of pages printed
3.How to corelate this parsed data with the user-department table and print it in a report.
I might have to produce the February report the old way but I am sure I will achieve my goal of automating the process by my sheer bloodyminded perseverence. If you can help me, I'd be very appreciative.

What is Filter by group used for and where can I find a tutorial about it?

Not finding an answer? Try a Google search.