Sum Total Question in Query

You can't really have subtotals in a query. You would need a report for that. In a query, you can see either the full list, sorted any way you like, or the subtotals, but without the details. For example, this shows you the two subtotals:

  SELECT Sum(QFlow)
  FROM tblMain
  WHERE ID Between 9 And 15
  GROUP BY [Group];

In a report, you can show all your columns, add a grouping on tblMain.Group, and request subtotals on any fields in the group's footer.

(°v°)

A slight modification of the qryGroupThem query from the previous question will do it.

I've switched around the Select statements in the UNION ALL so that Access picks up the field type for ID from the non-Grouped select.
Then the first Select just returns QFlow from the individual rows and the second select returns Sum(QFlow) for the Grouped rows.

You won't be able to Order the list exactly as your target output showed since the grouped row has no ID.

SELECT ID,tblMain.CatID, tblSCType.SCType, tblSType.SType, QFlow
FROM tblSType INNER JOIN (tblSCType INNER JOIN tblMain ON tblSCType.SCTypeID = tblMain.SCTypeID) ON tblSType.STypeID = tblMain.STypeID
WHERE (((tblMain.Appdate)>=[forms]![frmDateRange]![BeginDate] And (tblMain.Appdate)<=[forms]![frmDateRange]![EndDate])) and not nz([Group], false)
 
         UNION ALL 
 
SELECT Null, tblMain.CatID, tblSCType.SCType, tblSType.SType, sum(QFlow) as QFlowA
FROM tblSType INNER JOIN (tblSCType INNER JOIN tblMain ON tblSCType.SCTypeID = tblMain.SCTypeID) ON tblSType.STypeID = tblMain.STypeID
WHERE (((tblMain.Appdate)>=[forms]![frmDateRange]![BeginDate] And (tblMain.Appdate)<=[forms]![frmDateRange]![EndDate]))
AND [Group] = true
GROUP BY tblMain.CatID, tblSCType.SCType, tblSType.SType
 
Order By ID

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

Mike:
From the template I made up (see QryTest) in the attached sample DB of my original question, I added one more field on the select statement INNER JOIN to process....

"tblCat.CatType"...

This means that my Select Statement is slightly different from yours...

FROM (tblSType INNER JOIN (tblSCType INNER JOIN tblMain ON tblSCType.SCTypeID = tblMain.SCTypeID) ON tblSType.STypeID = tblMain.STypeID) INNER JOIN tblCat ON tblMain.CatID = tblCat.CatID

Would you mind re-posting your suggested solution query using my posted (QryTest) for me to test on the posted sample db?

SELECT tblCat.CatType, tblSCType.SCType, tblSType.SType, tblMain.QFlow
FROM (tblSType INNER JOIN (tblSCType INNER JOIN tblMain ON tblSCType.SCTypeID = tblMain.SCTypeID) ON tblSType.STypeID = tblMain.STypeID) INNER JOIN tblCat ON tblMain.CatID = tblCat.CatID
WHERE (((tblMain.Appdate)>=[forms]![frmDateRange]![BeginDate] And (tblMain.Appdate)<=[forms]![frmDateRange]![EndDate]))
ORDER BY tblSCType.SCType;
                                              

1:
2:
3:
4:

I just noticed another error in my original question.

Record Id 16 has no "Yes" checked in the sample db for tblMain.Group. The correct record for record id 16 is shown below:

Record ID        CatID          SCTypeID         SType       Group       QFlow
    16                  GPS                 SD                   DY                            0.011       <- QFlow Sum for record id 16 listed separately

I did say "you can't really have subtotals in a query", and this looks a bit odd now (thanks to you Mike!).

bobby,

What I meant to say was more along the lines of "you don't want subtotals in queries anyway". You have difficulties understanding the queries and adjusting them only because of the requirement to show both details and summaries in query view.

Although you can UNION a table with a query for this purpose, during development you should concentrate on each individual query. Only when they work flawlessly in ever possible context will you UNION them, if you really need is.

Take Mike's suggestion, but test and debug the part above and below the UNION keyword separately. Only once both queries work should you worry about combining them.

Does that make sense?
(°v°)

Mike / Harfang:
Since Mike's last updated query, I have tried executing his suggestion in another sample db with an elaborated "query" ..........and I ran into problems.

To clarify it I have attached a new sample db here very much similar to very first one I uploaded earlier in my original question.

In it, you find a new query object "qrySumQFlow" which I now have a hard time coming up with sum(QFlow)/4 as QFlowA ....per Mike's UNION AL  Query.

I will appreciate a good solution based on the question request.

TESTING:
To test the new sample, please do the following:

(a). Doubleclick the Query object "qrySumQFlow"
(b). Type in:
      BeginDate = 07/01/09
      End Date  =  09/30/09

(c). Query opens with all the data in it.

If you could assist further by processing (sum(QFlow)/4 as QFlowA WHERE tblMain.Group = True) in the query it would great.

In other words..
.......list each record of QFlowNew for all records in the Date range; and process (sum(QFlow)/4 as QFlowA WHERE tblMain.Group = True)
...per my original question.

My regards

Bobby

I'm sure I don't follow. Anyway, this is the query:

SELECT Sum(Qflow)/4 AS QFlowNew
FROM tblMain
WHERE [Group]
 AND CatID In ('Gen','pks','CED')
 AND AppDate Between #7/1/2009# And #9/30/2009#
 AND STypeID In ('NT','DY')
 AND CIDate Is Not Null
 AND PNo Is Not Null

Since it's a single number, I don't know how you expect to incorporate it into your detail query. Note that when you don't use fields from a table, it's usually safe to remove them from the source.

Basically, you are trying to write a SELECT query and a GROUP BY query at the same time. It's really one or the others.

Working from the above, you can try to re-introduce some fields. Your query will be GROUPed BY those fields, and your total becomes a subtotal. For each field, you can also display the highest, the lowest. For numbers, you could display the total, the average, etc.

At the very least, is the figure the one you expected?

(°v°)

Mike / Harfang,
Please disregard the sample I uploaded under ID:25771692. I have refined the sample further to include Mike's Query "UNION_QUERY_Mike"

I tested the attached Mike's suggested query and "QFlowA" did not give the desired result. I presume that it was due to the fact that I could not figure out
how the UNION ALL Query calculated the "QFlowA" moreso that "similar records now depends solely on BNo on one hand followed by
CatID, SCTypeID, SType WHERE tblMain.Group = True on the other......

In other words, in tblMain, similar record consist of the following in that order:

BNo   "<------ "BNo" is the major deciding factor here due to the fact that the BNo must be a Unique number throughout the group
                        ..... as you could see here from record Id 87,88,89,90,91 and 81

followed by the following fields on the other hand......
CatID
SCTypeID
SType
Group MUST BE set to "Yes"

NOTE: "LNo" is not part of the similar record hence, it was not used when I utilized Mike's suggested query
.....but I will still like to list the "LNo" against the "BNo" as shown below

(See the attached sample db):

Record ID        BNo      LNo      CatID          SCTypeID         SType           Group         QFlow            QFlowNew
                                                                                                                                                                     =(QFlow) /4          
    87                 5888     1552     GPS                SD                   DY               Yes              0.091              0.02275
   88                 5888         53      GPS                SD                   DY               Yes              0.091              0.02275
   89                 5888     1549     GPS                SD                   DY               Yes              0.091              0.02275
   90                 5888     1550     GPS                SD                   DY               Yes              0.091              0.02275
   91                 5888     1548     GPS                SD                   DY               Yes              0.091              0.02275
   81                 5888     1547     GPS                SD                   DY               Yes              0.091              0.02275
   ********************************************************************************************************************************
                                                                                                                            Sum Total (QFlowA)      = 0.1365   '<-------- = Sum(QFlowNew)    

   Note:
Upon processing Mike's Query, this formular =Sum(QFlowNew) produce a calculated value of 0.11375  '<===== This is wrong
........compare with the above manually calculated result.

TESTING MIKES' method:
To simulate Mike's test result, please doubleclick on "UNION_QUERY_Mike", then type in 07/01/09 & 09/30/09

Question Objective:
What I will like to achieve with this question is to be able to configure almost any query then......
........list each record of QFlowNew for all records in the Date range; and process (sum(QFlow)/4 as QFlowA WHERE tblMain.Group = True)
...per my original question.

In this case, my new query "qrySumQFlow" - (attached with this sample) is what I will now appreciate assistance with and this I believe will allow
me to be able to manipulate the query or any other query as needed.

So, any assistance in using "qrySumQFlow" to get QFlowA that listed all records QFlow as well a sum up similar records where tblMain.Group = True
will be appreciated.

Please see the attached new sample db here.

TESTING My included new Query:
If a new Query is formulated using my query "qrySumQFlow",
.....then simply test it by doubleclicking on "qrySumQFlow"
.....and type 07/01/09 (BeginDate) and 09/30/09 (End Date)

.......the result for record id 87 - 91 and 81 altogether should produce a QFlowA of 0.1365 using the formular =Sum(QFlowNew) or
simply (sum(QFlow)/4 as QFlowA WHERE tblMain.Group = True)

Please let me know if you need additional information

Bobby

Harfang:
I just tested your suggested Query using my most recent sample db under ID:25772824.

Result:
I received a single result of 0.1382.
I am unable to make meanings out of this result since there are several groups of similar records in tblMain...as such, I expect to receive a list of all
QFlow in tblMain PLUS QFlowA for all groups of similar records in tblMain.

Please open Mike's suggested Query and test, this will give you an idea of the result I expect to get from a successful Query.

Hi Bobby.

Let's see if I can help.


> I could not figure out...

As I was trying to explain in {http:#25771372}, do not even try to modify the UNION query. Instead, use either the query before the word UNION, or the one below. You will have better chances of success.

Basically, the top query is just a selection. Every record in the date range, for which the Group is unchecked. These records will be shown in detail.

The bottom query, on the other hand, performs a grouping. Every column displayed is part of the grouping. If you don't want that level of detail, you can't display that column: it must be left blank, just like the column ID.


> "similar records now depends solely on BNo [...]"

Precisely. BNo is included, so records are grouped by that field. Should this field be blank for the subtotals? That would leave a grouping on CatType and SCType (constant over your current group selection), and SType (with two possible values: Day and Evening). In other words, removing BNo will collapse your subtotals into two records.


> I will still like to list the "LNo" against the "BNo"

If you add more grouping keys, you create more groups. It's the opposite effect. In the end, it won't be a subtotal any more. For example, BNo=5888 is one group. If you add the column LNo, since all values of LNo are different, you are no longer grouping on BNo. Instead, you will see one record for each different value of LNo.

Note: these records are marked for grouping.


> [Total for BNo=5888] = 0.1365 [compared to:] 0.11375  '<===== This is wrong

I must disagree. You calculate 0.02275 × 6, because you include record ID=81. As per your instructions:

> type in 07/01/09 & 09/30/09

Please verify in your table that record ID=81 has no value in AppDate, and therefore does not fall between those dates. Furthermore, your own query, qrySumQFlow, contains the explicit (although redundant) clause:

    WHERE ... And (tblMain.AppDate) Is Not Null ...

You may also verify that 0.02275 × 5 = 0.11375


I hope this sheds some light onto your adventures.

Regards
(°v°)

Harfang / Mike:
Thanks for the analysis:

My qrySumQFlow was designed specifically to exclude all records missing data in that range...
"Between [Forms]![frmDateRange]![BeginDate] And [Forms]![frmDateRange]![EndDate] And (tblMain.AppDate) Is Not Null)"

I have added the missing data for record id 81 as shown below and my query now picks up record id 81:

ID      CatID      BNo      LNo      SCTypeID      STypeID      QFlow      Group      LocalID      AppDate           PNo        CIDate          SerialNo   HConNo
81      GEN      5888      1547      SD      DY      0.091      Yes      CTVE      9/11/2009    550431  9/3/2009    0029-08     1  '<------Note

Mike's query has also added record id 81 and I now get a calculated value for all BNo with 5888 as 0.1365.

That being said, I will appreciate a solution using my query "QrySumQFlow", even if it's Mike's UNION All type so long I am able to manipulate the fields
to contain all fields I want to utilize in my report....as the result is meant for the report ultimately.

Bobby.

Mike:
Please disregard my comment under ID:25772824, .....your query works as originally designed, However, I still need help to figure out ways to include fields like LNo
(etc..) to be listed against the BNo ...specifically for printing report,

I will prefer that my query (QrySumQFlow) be utilized in evolving a new solution...this will assist me in being able to manipulate the query in order to get the desired result
in my reports.

The sample db I uploaded under ID:25772824 is a better sample which you may want to utilize in evolving a solution on the subject matter.

Bobby.

Bobby,
As harfang explained in his last post, my solution for your request consists of two seperate SELECT statements to get the two distinct result set types that you want, then UNIONs them together into a single result set.

The first SELECT gets the rows that will appear as individual rows in the final reult set, for this you include all the columns that you want for your report

The second SELECT groups together rows tagged with GROUP=True in the original data. To be able to concatenate these two result sets using UNION there must be the same number of columns in this second select as in the first one and they must be of compatible data type.

For this second SELECT to successfully GROUP the data as you want it to, any column that would break the grouping must be effectively ignored. This is achieved by supplying Null as the value for the column - this is demonstrated by the ID column in the answers I've already provided.

To add any further columns to your final result set all you need to do is include the column name in the column list in the first Select and add the selection of a NULL value in the corresponding position in the column list of the second Select.
(NB, although I supplied an Alias name, QFlowA, for the calculated result of QFlow in the second select, there wasn't any need for it, the column names in a UNION come from the first Select statement)

E.g. If the values in Locality would break the GROUPing, add it like this:

SELECT ID, Locality, CatType, BNo, tblSCType.SCType, tblSType.SType, QFlow
...
UNION ALL

SELECT Null, Null, CatType, BNo, tblSCType.SCType, tblSType.SType, sum(QFlow)/4 as QFlowA

I believe that you will profit more by trying this for yourself rather than

Sorry, hit the wrong button,
...
I believe that you will profit more by trying this for yourself and asking for clarification if you don't succeed, rather than have us supply cut and dried solutions to an ever-moving target.

Hope this helps,
Mike

Mike:
Believe me sincerely, I have tried all the options myself...I am not one of those tht rely on "Cut and Dry" solutions....

The sample db I provided under ID:25772824 proves it..anyway...I'll give it another shot.

Bobby

Bobby,
I appreciate that.
As harvang suggested, you can test the first and second select statements separately - put them in separate queries if you like - then only UNION them together when you're satisfied that both pieces work.
Mike

oops: harfang, apologies for the misspelling.

Bobby,

If this is ultimately destined for a report, why not try to create both lists in separate sub-reports? You can show a total row for each, and print them one below the other from a main report.

This avoid the UNION query, and will probably be more readable.

Mike,

No problem. In German, the 'v' is pronounced 'f'...

(°v°)

Mike:
I am not an SQL: guy and not that familiar with UNION ALL. I know my limitations.
The SQL in the code snippet was how far I could go. I grouped everything and provide data for the QFlowNew by dividing QFlow / 4.

Any further assistance from here will be appreciated.

SELECT DISTINCTROW tblMain.ID, tblLocality.Locality, tblMain.SerialNo, tblMain.BNo, tblMain.LNo, tblMain.PNo, tblMain.AppDate, tblMain.HConNo, tblSType.SType, ([Qflow])/4 AS QFlowNew, tblSCType.SCType
FROM (tblSType INNER JOIN (tblMain INNER JOIN tblLocality ON tblMain.LocalID = tblLocality.LocalID) ON tblSType.STypeID = tblMain.STypeID) INNER JOIN tblSCType ON tblMain.SCTypeID = tblSCType.SCTypeID
WHERE (((tblMain.CatID) In ('Gen','pks','CED')) AND ((tblMain.AppDate) Between [Forms]![frmDateRange]![BeginDate] And [Forms]![frmDateRange]![EndDate] And (tblMain.AppDate) Is Not Null) AND ((tblMain.STypeID) In ('NT','DY')) AND ((tblMain.CIDate) Is Not Null) AND ((tblMain.PNo) Is Not Null))
GROUP BY tblMain.ID, tblLocality.Locality, tblMain.SerialNo, tblMain.BNo, tblMain.LNo, tblMain.PNo, tblMain.AppDate, tblMain.HConNo, tblSType.SType, ([Qflow])/4, tblSCType.SCType
ORDER BY tblMain.BNo;
                                              

1:
2:
3:
4:
5:

Harfang:
You said...
.....why not try to create both lists in separate sub-reports?

.....I am willing to try anything that would work.

Thanks

Bobby,
All that a UNION ALL does is to concatenate the rows returned from two or more select statements into one result set.
In your case you want to do something different with rows marked Group=True, so I suggested to separated them from the reset into their own Select statement so they could be grouped, the rows returned from that Select get tagged onto the end of the rows returned from the non-grouped Select statement.

In order to successfully advise you we need to know more about the content of your data, specifically which fields can be included in the GROUP BY without breaking the the logic of your intended grouping. For example, in your sample data at least, the field Location can be included in the GROUP BY because it always has a single value for the rows you want to group together, however some fields will break the grouping - an obvious one being ID which if includeded as a GROUP BY column would cause every row to be returned as its own group. That's why I have Null as the selected Value for ID in the second select statement.

However, we can't know whether the sample data is covering all cases. Could it be that with a full set of data, there might be a set of rows you want to group together that have more than one location - only you know the anwer to that.

So, is it possible that you could list the fields from the final query that are safe to use in GROUP BY without breaking the logic behing the use of Group=True? Then it will be possible to provide a definitive solution to your problem.

Mike

@harfang
Yes, my wife's Dutch so my use of V rather than F probably slipped in from there.
But harfang is the French for a snowy owl, isn't it? - which confuses even more.
And for tonight's piece of trivia, the Owl is the mascot of the soccer team I support, Sheffield Wednesday.
Cheers,
Mike

Mike:
The sample data covered all cases (that is all fields I needed for my reports are there....

and the last query I uploaded also has all the data  for my report EXCEPT the column that will output result of  "Group=True"

List of fields that are safe to use are:
Locality, SerialNo, BNo, LNo, PNo, AppDate, HConNo, SCType, SType, QFlow

Bobby,

Mike is trying very hard to answer your actual question, which is about performing subtotals "in a query". As you have seen, it's difficult to mix details and subtotals in a query window.

About the reporting idea:

To avoid confusion with your own data structure, imagine I'm inviting people to an event. I have a table 'People', with names, etc. Some are VIP, others are parts of various groups. I want a list with every VIP spelled out, but just count people that are in a group ("Knitting Club: 8; Carl's Party: 2; ...").

Since the list is simple (two-three columns), you can perform the UNION query exercise as you are trying to do here.

The other solution would be one main report, used as a frame, showing two sub-reports.

------------------------------------------
 INVITATION LIST
   VIP List
   ------------------------------
   Dr. K. Faust
   Ms Julie Bonjour
   ...
   ------------------------------
   Groups
   ------------------------------
   Knitting Club             8
   Carl's Party               2
   ...
   ------------------------------
------------------------------------------

Going back to your own project, this means creating two distinct queries, used in two sub-reports. They don't even need to have the same number of columns. And it's easy to get various totals per list. It's also possible to add both totals from the sub-reports.

I hope this explains it better.

(°v°)


@Mike: Yes 'chouette harfang' is the snowy owl in French. I think it comes from a Scandinavian language; it also used to be the scientific name. I do like owls, as you might have guessed! -- (^v°)

Bobby,
Fell at the first fence - SerialNo breaks the grouping.
Attached example shows a single BNo split into three rows by differing SerialNo.
Are there any of the others like that?

Mike / Harfang:
Based on your suggestions, I came up with these two attached queries. Upon doubleclicking on Qry_First to test I received the following error:

"The number of columns in the two selected tables or queries of a UNION queries do not match"

If you could assist with these two queries it would be great.

(1). Qry_First
SELECT tblMain.ID, tblLocality.Locality, tblMain.SerialNo, tblMain.BNo, tblMain.LNo, tblMain.PNo, tblMain.AppDate, tblMain.HConNo, tblSType.SType, tblSCType.SCType, Sum(tblMain.QFlow)/4 AS QFlowA
FROM qryGroupThem AS tblMain
GROUP BY tblMain.ID, tblLocality.Locality, tblMain.SerialNo, tblMain.BNo, tblMain.LNo, tblMain.PNo, tblMain.AppDate, tblMain.HConNo, tblSType.SType, ([Qflow])/4, tblSCType.SCType
ORDER BY tblMain.ID;
 
(2). QryGroupThem
SELECT tblMain.ID, tblLocality.Locality, tblMain.SerialNo, tblMain.BNo, tblMain.LNo, tblMain.PNo, tblMain.AppDate, tblMain.HConNo, tblSType.SType, tblSCType.SCType
FROM (tblSType INNER JOIN (tblMain INNER JOIN tblLocality ON tblMain.LocalID = tblLocality.LocalID) ON tblSType.STypeID = tblMain.STypeID) INNER JOIN tblSCType ON tblMain.SCTypeID = tblSCType.SCTypeID
WHERE (((tblMain.CatID) In ('Gen','pks','CED')) AND ((tblMain.AppDate) Between [Forms]![frmDateRange]![BeginDate] And [Forms]![frmDateRange]![EndDate] And (tblMain.AppDate) Is Not Null) AND ((tblMain.STypeID) In ('NT','DY')) AND ((tblMain.CIDate) Is Not Null) AND ((tblMain.PNo) Is Not Null)) 
AND [Group] = True
GROUP BY  tblMain.ID, tblLocality.Locality, tblMain.SerialNo, tblMain.BNo, tblMain.LNo, tblMain.PNo, tblMain.AppDate, tblMain.HConNo, tblSType.SType, tblSCType.SCType
         UNION ALL SELECT Null,  tblMain.ID, tblLocality.Locality, tblMain.SerialNo, tblMain.BNo, tblMain.LNo, tblMain.PNo, tblMain.AppDate, tblMain.HConNo, tblSType.SType, tblSCType.SCType
FROM (tblSType INNER JOIN (tblMain INNER JOIN tblLocality ON tblMain.LocalID = tblLocality.LocalID) ON tblSType.STypeID = tblMain.STypeID) INNER JOIN tblSCType ON tblMain.SCTypeID = tblSCType.SCTypeID
WHERE (((tblMain.CatID) In ('Gen','pks','CED')) AND ((tblMain.AppDate) Between [Forms]![frmDateRange]![BeginDate] And [Forms]![frmDateRange]![EndDate] And (tblMain.AppDate) Is Not Null) AND ((tblMain.STypeID) In ('NT','DY')) AND ((tblMain.CIDate) Is Not Null) AND ((tblMain.PNo) Is Not Null));
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

Bobby,
>>> (2) qryGroupThem

This does not follow in any way the solution description and examples I have provided.
From a previous post:

>>>>The first SELECT gets the rows that will appear as individual rows in the final reult set, for this you include all the columns that you want for your report

>>>>The second SELECT groups together rows tagged with GROUP=True in the original data. To be able to concatenate these two result sets using UNION there >>>>must be the same number of columns in this second select as in the first one and they must be of compatible data type.

>>>>For this second SELECT to successfully GROUP the data as you want it to, any column that would break the grouping must be effectively ignored. This is achieved >>>>by supplying Null as the value for the column - this is demonstrated by the ID column in the answers I've already provided.

What you have posted has the Grouping query as the first one, the reverse of my suggestion, and includes all the fields that will break your grouping logic. The error message is only repeating what was in my post:  "there must be the same number of columns in this second select as in the first one " You've kept the Null I put in for ID, albeit in the wrong select statement, then added the ID column after it, thus adding an extra column to the second query, hence the mismatch in column numbers.

qry_first doesn't do anything at all useful. Harfang's advice, which I supported, was to take the two select statements from the Union query and put each into its own query that could be tested separately. These could then have be pulled together in a UNION as so:

Select * From qryDetailRows
UNION ALL
Select * From qryGroupedRows

I'll clean it all up and post a solution

Bobby,
Here it is. Three queries that give you the overall structure. I don't know the intent of dividing QFlow by 4, so I've just left it as-is in the Group=True query.
Hopefully this is clear enough for you now to be able to make any final adjustments.  

qryFilterMain: Joins all the tables together and applies your selection criteria

qrySumQFlow: Handles the Group=True records, using qryFilterMain as data source

qryReport: Selects the non-grouped rows from qryFilterMain and concatenates the rows from qrySumQFlow using UNION ALL

qry FilterMain
 
SELECT tblMain.ID, tblLocality.Locality, tblMain.SerialNo, tblMain.BNo, tblMain.LNo, tblMain.PNo, tblMain.AppDate, tblMain.HConNo, tblSType.SType, tblSCType.SCType, tblMain.QFlow, tblMain.Group
FROM (tblSType INNER JOIN (tblMain INNER JOIN tblLocality ON tblMain.LocalID = tblLocality.LocalID) ON tblSType.STypeID = tblMain.STypeID) INNER JOIN tblSCType ON tblMain.SCTypeID = tblSCType.SCTypeID
WHERE (((tblMain.PNo) Is Not Null) AND ((tblMain.AppDate) Between [BeginDate] And [EndDate]) AND ((tblMain.CatID) In ('Gen','pks','CED')) AND ((tblMain.STypeID) In ('NT','DY')) AND ((tblMain.CIDate) Is Not Null));
 
qrySumQFlow
 
SELECT DISTINCTROW Null AS ID, qryFilterMain.Locality, Null AS SerialNo, qryFilterMain.BNo, Null AS LNo, Null AS PNo, Null AS AppDate, qryFilterMain.HConNo, qryFilterMain.SType, qryFilterMain.SCType, Sum([Qflow]/4) AS QFlowNew
FROM qryFilterMain
WHERE (((qryFilterMain.Group)=True))
GROUP BY qryFilterMain.Locality, qryFilterMain.BNo, qryFilterMain.HConNo, qryFilterMain.SType, qryFilterMain.SCType;
 
qryReport
 
SELECT ID, Locality, SerialNo, BNo, LNo, PNo, AppDate, HConNo, SType, SCType, QFlow 
FROM qryFilterMain Where not nz(Group,True)
UNION ALL Select ID, Locality, SerialNo, BNo, LNo, PNo, AppDate, HConNo, SType, SCType, QFlowNew 
FROM qrySumQFlow
ORDER BY ID;
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

Mike:
Thank you for the attachment and the suggested queries. I tried them all and the closest to what I am hoping for are: qryFinal and qryRawdata in that it listed all the fields I want to populate as well as the data for QFlowA field.
I exported the output generated by the Date Range 07/01/09 & 09/30/09 and pasted the output into Microsoft Excel and named it "QryRawData_Amended.xls". I deleted what are not necessary and left only fields that must be populated and the way each field is expected to be populated.

In the attachment ("QryRawData_Amended.xls"),  you will find the following:

(a). LNos matching each BNo are populated right next to the BNo field (The LNos needs to be populated for the report.

(b). There is a UNIQUE (DISTINCT) record of Serial No for each listed BNo - Serial No field is equally important for the report.

(c). QFLOWA field populated data for QFlow for each BNo in which [Group] = False
      as well as populated aggregate "QFLOWA" value for each BNo which [Group] = True

If you can assist in getting Access Queries to populate data in the EXACT format of the attached Excel file
("QryRawData_Amended.xls"),  -  it would be greatly appreciated.

Cheers !!!

Bobby  

Mike:
Sorry...
I renamed your qryFilterMain to "qryFinal"; I also renamed your qryReport to qryRawdata for logistic reasons in my own sample db.....
...so please no reason to wonder why?

Mike:
Diregard my previous sample Excel file...After I pasted data from the Query result in Excel and amended everything, I sorted all records by BNo field....It escaped my mind that Excel is not like the Database sorting.

I have eleiminated all fields that could break the grouping. All that is needed now is a way to list all LNos
against each matching BNo WHERE tblMain.Group = "Yes"

In other words....I have included a new excel file that showed QFlow and QFlowA, including unique Serial no for each record.
I have also deleted fields like CIEDate, PNo..etc..which I think could break the Groupings.

(1). Qflow is a value for each record in tblMain.
(2). QFlowA ia a value for "similar records "sum together all the [QFlow] and divided by 4 - this is the formular.
(3) (a). Similar records are records with same "BNo" but different LNos matching the BNo.
      (b). Once all the records of the same BNos are collated, and the following fields:
             SConnType, SType are also similar        
               ....AND tblMain.Group = "Yes" Then
(c). Calculate QFlowA using the formular =Sum([tblMain.Group])/4
(d). List the group of similar BNo with matching LNos as one record with the QFlowA value.

Note:
The new qryRawdata_Amended_2.xls would clarify the above statement.

If there is a way to list the LNos against each matching BNo for my report - it should resolve my concern.

Cheers !!!
Bobby

Bobby,
It seems that each time I give an answer to a question you pose in this thread the question immediately mutates into something different.
I suggest that you close-out this question and ask a follow up, since your target now differers so much from any iteration of the question so far.

Mike:
I did not....I was only trying to figure out the best to include "LNo" or any other important field with a query even if to separate the field(s) in a separate query since UNION ALL Query has proved to be problematic. I hope you'll understand.

For example, if you check out "qryReport" that you posted,, When you run the query, it did not list out all the LNos, it listed it to a point and stopped. It is this field I was only trying to sort out how to get it in a query format which I can later use for a report - since the ultimate goal is to use the query in a report and getting LNo field to that report is very important..

Mike / Harfang:
I awarded the points based on the fact that Mike's original post answered the question and I have since gotten assistance from an outside source for the LNo / other field listings in the final SQL result using VBA.

Thanks for your assistance.

Regards

Bobby.