Batching Totals in Groups of 25 - How?

  • Thread starter Thread starter nrms
  • Start date Start date
N

nrms

I have created a report that lists names and amounts of money in two columns
from a query that pulls data from several tables in an Access 2003 database.

I would like to group the report so that the Amount field creates a batch
total every 25 records, like this:

Name1, Amount1
...
Name25, Amount25

Batch Total : SUM(Amount1 - Amount25)


Name26,Amount26
...
Name50,Amount50

Batch Total: SUM(Amount26 - Amount50)

and so on. I would think this is quite a simple task, but I'm having a
problem and can't see how to do it - Can anyone help?

Thanks
NigelS
 
You can create the groups in your report's record source query. The following
SQL create groups of 15 records from the Orders table in the Northwind sample
MDB.

SELECT (DCount("OrderID","Orders","OrderID<" & [OrderID])+15)\15 AS
GroupNum, Orders.*
FROM Orders
ORDER BY Orders.OrderID;
 
I dont have a field in the query which I can do math with as you are doing
with the OrderID number, only text field (Name) and currency (Amount).

NigelS

Duane Hookom said:
You can create the groups in your report's record source query. The following
SQL create groups of 15 records from the Orders table in the Northwind sample
MDB.

SELECT (DCount("OrderID","Orders","OrderID<" & [OrderID])+15)\15 AS
GroupNum, Orders.*
FROM Orders
ORDER BY Orders.OrderID;

--
Duane Hookom
Microsoft Access MVP


nrms said:
I have created a report that lists names and amounts of money in two columns
from a query that pulls data from several tables in an Access 2003 database.

I would like to group the report so that the Amount field creates a batch
total every 25 records, like this:

Name1, Amount1
..
Name25, Amount25

Batch Total : SUM(Amount1 - Amount25)


Name26,Amount26
..
Name50,Amount50

Batch Total: SUM(Amount26 - Amount50)

and so on. I would think this is quite a simple task, but I'm having a
problem and can't see how to do it - Can anyone help?

Thanks
NigelS
Click to expand...
Click to expand...
 
This is the SQL of the query underlying my report:

SELECT LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Sum(Daybook_Payments.Amount) AS BACSPaid, Daybook_Payments.Chq
FROM LandLords RIGHT JOIN (Daybook_Payments LEFT JOIN Rent_Book ON
Daybook_Payments.AccRef = Rent_Book.AccRef) ON LandLords.LRef = Rent_Book.LRef
GROUP BY LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Daybook_Payments.Chq
HAVING (((Daybook_Payments.Chq) Like "E*"))
ORDER BY LandLords.BACSRef;


Duane Hookom said:
You can create the groups in your report's record source query. The following
SQL create groups of 15 records from the Orders table in the Northwind sample
MDB.

SELECT (DCount("OrderID","Orders","OrderID<" & [OrderID])+15)\15 AS
GroupNum, Orders.*
FROM Orders
ORDER BY Orders.OrderID;

--
Duane Hookom
Microsoft Access MVP


nrms said:
I have created a report that lists names and amounts of money in two columns
from a query that pulls data from several tables in an Access 2003 database.

I would like to group the report so that the Amount field creates a batch
total every 25 records, like this:

Name1, Amount1
..
Name25, Amount25

Batch Total : SUM(Amount1 - Amount25)


Name26,Amount26
..
Name50,Amount50

Batch Total: SUM(Amount26 - Amount50)

and so on. I would think this is quite a simple task, but I'm having a
problem and can't see how to do it - Can anyone help?

Thanks
NigelS
Click to expand...
Click to expand...
 
I wasn't doing any math, just counting records. Try something like:

Save your SQL statement as a query [qgrpPayments] then create another query:

SELECT LRef, LName, BACSRef, BACSPaid, Chq,
(DCount("LRef","qgrpPayents","BACSRef<'" & [OrderID] & "'")+25)\25 AS GroupNum
FROM qgrpPayments
ORDER BY BACSRef;

--
Duane Hookom
Microsoft Access MVP


nrms said:
This is the SQL of the query underlying my report:

SELECT LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Sum(Daybook_Payments.Amount) AS BACSPaid, Daybook_Payments.Chq
FROM LandLords RIGHT JOIN (Daybook_Payments LEFT JOIN Rent_Book ON
Daybook_Payments.AccRef = Rent_Book.AccRef) ON LandLords.LRef = Rent_Book.LRef
GROUP BY LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Daybook_Payments.Chq
HAVING (((Daybook_Payments.Chq) Like "E*"))
ORDER BY LandLords.BACSRef;


Duane Hookom said:
You can create the groups in your report's record source query. The following
SQL create groups of 15 records from the Orders table in the Northwind sample
MDB.

SELECT (DCount("OrderID","Orders","OrderID<" & [OrderID])+15)\15 AS
GroupNum, Orders.*
FROM Orders
ORDER BY Orders.OrderID;

--
Duane Hookom
Microsoft Access MVP


nrms said:
I have created a report that lists names and amounts of money in two columns
from a query that pulls data from several tables in an Access 2003 database.

I would like to group the report so that the Amount field creates a batch
total every 25 records, like this:

Name1, Amount1
..
Name25, Amount25

Batch Total : SUM(Amount1 - Amount25)


Name26,Amount26
..
Name50,Amount50

Batch Total: SUM(Amount26 - Amount50)

and so on. I would think this is quite a simple task, but I'm having a
problem and can't see how to do it - Can anyone help?

Thanks
NigelS
Click to expand...
Click to expand...
Click to expand...
 
I cant help thinking this should be easier!

Is there not a way to group using Sorting & Grouping?

I have also created a textbox on the report (ItemNumber) (not associated
with the query at all). It numbers each record in the record using a
"Running Sum" (ControlSource is =1), and is a technique described in MS
Access Help. I've been trying to group using the content of this control but
again without success.

NigelS
Duane Hookom said:
I wasn't doing any math, just counting records. Try something like:

Save your SQL statement as a query [qgrpPayments] then create another query:

SELECT LRef, LName, BACSRef, BACSPaid, Chq,
(DCount("LRef","qgrpPayents","BACSRef<'" & [OrderID] & "'")+25)\25 AS GroupNum
FROM qgrpPayments
ORDER BY BACSRef;

--
Duane Hookom
Microsoft Access MVP


nrms said:
This is the SQL of the query underlying my report:

SELECT LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Sum(Daybook_Payments.Amount) AS BACSPaid, Daybook_Payments.Chq
FROM LandLords RIGHT JOIN (Daybook_Payments LEFT JOIN Rent_Book ON
Daybook_Payments.AccRef = Rent_Book.AccRef) ON LandLords.LRef = Rent_Book.LRef
GROUP BY LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Daybook_Payments.Chq
HAVING (((Daybook_Payments.Chq) Like "E*"))
ORDER BY LandLords.BACSRef;


Duane Hookom said:
You can create the groups in your report's record source query. The following
SQL create groups of 15 records from the Orders table in the Northwind sample
MDB.

SELECT (DCount("OrderID","Orders","OrderID<" & [OrderID])+15)\15 AS
GroupNum, Orders.*
FROM Orders
ORDER BY Orders.OrderID;

--
Duane Hookom
Microsoft Access MVP


:

I have created a report that lists names and amounts of money in two columns
from a query that pulls data from several tables in an Access 2003 database.

I would like to group the report so that the Amount field creates a batch
total every 25 records, like this:

Name1, Amount1
..
Name25, Amount25

Batch Total : SUM(Amount1 - Amount25)


Name26,Amount26
..
Name50,Amount50

Batch Total: SUM(Amount26 - Amount50)

and so on. I would think this is quite a simple task, but I'm having a
problem and can't see how to do it - Can anyone help?

Thanks
NigelS
Click to expand...
Click to expand...
Click to expand...
 
Duane

I've done what you suggested and it seems to work (after replacing the
[OrderID] bit with [BACSRef], which I assume was just a typo).

Thanks for this, but I still feel there ought to be a simpler way of
achieving the same.

NigelS

Duane Hookom said:
I wasn't doing any math, just counting records. Try something like:

Save your SQL statement as a query [qgrpPayments] then create another query:

SELECT LRef, LName, BACSRef, BACSPaid, Chq,
(DCount("LRef","qgrpPayents","BACSRef<'" & [OrderID] & "'")+25)\25 AS GroupNum
FROM qgrpPayments
ORDER BY BACSRef;

--
Duane Hookom
Microsoft Access MVP


nrms said:
This is the SQL of the query underlying my report:

SELECT LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Sum(Daybook_Payments.Amount) AS BACSPaid, Daybook_Payments.Chq
FROM LandLords RIGHT JOIN (Daybook_Payments LEFT JOIN Rent_Book ON
Daybook_Payments.AccRef = Rent_Book.AccRef) ON LandLords.LRef = Rent_Book.LRef
GROUP BY LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Daybook_Payments.Chq
HAVING (((Daybook_Payments.Chq) Like "E*"))
ORDER BY LandLords.BACSRef;


Duane Hookom said:
You can create the groups in your report's record source query. The following
SQL create groups of 15 records from the Orders table in the Northwind sample
MDB.

SELECT (DCount("OrderID","Orders","OrderID<" & [OrderID])+15)\15 AS
GroupNum, Orders.*
FROM Orders
ORDER BY Orders.OrderID;

--
Duane Hookom
Microsoft Access MVP


:

I have created a report that lists names and amounts of money in two columns
from a query that pulls data from several tables in an Access 2003 database.

I would like to group the report so that the Amount field creates a batch
total every 25 records, like this:

Name1, Amount1
..
Name25, Amount25

Batch Total : SUM(Amount1 - Amount25)


Name26,Amount26
..
Name50,Amount50

Batch Total: SUM(Amount26 - Amount50)

and so on. I would think this is quite a simple task, but I'm having a
problem and can't see how to do it - Can anyone help?

Thanks
NigelS
Click to expand...
Click to expand...
Click to expand...
 
Good luck finding a better method. I haven't ever found one that didn't
involve creating groups in the report's record source. If you didn't want to
create totals for the groups, it wouldn't be so difficult.

--
Duane Hookom
Microsoft Access MVP


nrms said:
Duane

I've done what you suggested and it seems to work (after replacing the
[OrderID] bit with [BACSRef], which I assume was just a typo).

Thanks for this, but I still feel there ought to be a simpler way of
achieving the same.

NigelS

Duane Hookom said:
I wasn't doing any math, just counting records. Try something like:

Save your SQL statement as a query [qgrpPayments] then create another query:

SELECT LRef, LName, BACSRef, BACSPaid, Chq,
(DCount("LRef","qgrpPayents","BACSRef<'" & [OrderID] & "'")+25)\25 AS GroupNum
FROM qgrpPayments
ORDER BY BACSRef;

--
Duane Hookom
Microsoft Access MVP


nrms said:
This is the SQL of the query underlying my report:

SELECT LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Sum(Daybook_Payments.Amount) AS BACSPaid, Daybook_Payments.Chq
FROM LandLords RIGHT JOIN (Daybook_Payments LEFT JOIN Rent_Book ON
Daybook_Payments.AccRef = Rent_Book.AccRef) ON LandLords.LRef = Rent_Book.LRef
GROUP BY LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Daybook_Payments.Chq
HAVING (((Daybook_Payments.Chq) Like "E*"))
ORDER BY LandLords.BACSRef;


:

You can create the groups in your report's record source query. The following
SQL create groups of 15 records from the Orders table in the Northwind sample
MDB.

SELECT (DCount("OrderID","Orders","OrderID<" & [OrderID])+15)\15 AS
GroupNum, Orders.*
FROM Orders
ORDER BY Orders.OrderID;

--
Duane Hookom
Microsoft Access MVP


:

I have created a report that lists names and amounts of money in two columns
from a query that pulls data from several tables in an Access 2003 database.

I would like to group the report so that the Amount field creates a batch
total every 25 records, like this:

Name1, Amount1
..
Name25, Amount25

Batch Total : SUM(Amount1 - Amount25)


Name26,Amount26
..
Name50,Amount50

Batch Total: SUM(Amount26 - Amount50)

and so on. I would think this is quite a simple task, but I'm having a
problem and can't see how to do it - Can anyone help?

Thanks
NigelS
Click to expand...
Click to expand...
Click to expand...
 
One other little hiccup I'm finding is in the Sort order. In the final
report, each batch of 25 records is correctly sorted A-Z by the LRef Code.
Actually what I would like is for the report to sort A-Z on LRef over the
whole report, just broken up into batches of 25. In other words, what I
want is:

1-25 A-M
26-50 M-Z

what I'm getting is:

1-25 A-Z
26-50 A-Z

The pre-query (qgrpPayments) sorts the recordset correctly LRef Ascending,
but then when I run the query within the report the DCount function seems to
just ignore the sort order. Rather annoying, and I can't see why this is
happening.

NigelS


Duane Hookom said:
Good luck finding a better method. I haven't ever found one that didn't
involve creating groups in the report's record source. If you didn't want to
create totals for the groups, it wouldn't be so difficult.

--
Duane Hookom
Microsoft Access MVP


nrms said:
Duane

I've done what you suggested and it seems to work (after replacing the
[OrderID] bit with [BACSRef], which I assume was just a typo).

Thanks for this, but I still feel there ought to be a simpler way of
achieving the same.

NigelS

Duane Hookom said:
I wasn't doing any math, just counting records. Try something like:

Save your SQL statement as a query [qgrpPayments] then create another query:

SELECT LRef, LName, BACSRef, BACSPaid, Chq,
(DCount("LRef","qgrpPayents","BACSRef<'" & [OrderID] & "'")+25)\25 AS GroupNum
FROM qgrpPayments
ORDER BY BACSRef;

--
Duane Hookom
Microsoft Access MVP


:

This is the SQL of the query underlying my report:

SELECT LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Sum(Daybook_Payments.Amount) AS BACSPaid, Daybook_Payments.Chq
FROM LandLords RIGHT JOIN (Daybook_Payments LEFT JOIN Rent_Book ON
Daybook_Payments.AccRef = Rent_Book.AccRef) ON LandLords.LRef = Rent_Book.LRef
GROUP BY LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Daybook_Payments.Chq
HAVING (((Daybook_Payments.Chq) Like "E*"))
ORDER BY LandLords.BACSRef;


:

You can create the groups in your report's record source query. The following
SQL create groups of 15 records from the Orders table in the Northwind sample
MDB.

SELECT (DCount("OrderID","Orders","OrderID<" & [OrderID])+15)\15 AS
GroupNum, Orders.*
FROM Orders
ORDER BY Orders.OrderID;

--
Duane Hookom
Microsoft Access MVP


:

I have created a report that lists names and amounts of money in two columns
from a query that pulls data from several tables in an Access 2003 database.

I would like to group the report so that the Amount field creates a batch
total every 25 records, like this:

Name1, Amount1
..
Name25, Amount25

Batch Total : SUM(Amount1 - Amount25)


Name26,Amount26
..
Name50,Amount50

Batch Total: SUM(Amount26 - Amount50)

and so on. I would think this is quite a simple task, but I'm having a
problem and can't see how to do it - Can anyone help?

Thanks
NigelS
Click to expand...
Click to expand...
Click to expand...
 
Have you tried setting the sorting using the sorting and grouping dialog?

--
Duane Hookom
Microsoft Access MVP


nrms said:
One other little hiccup I'm finding is in the Sort order. In the final
report, each batch of 25 records is correctly sorted A-Z by the LRef Code.
Actually what I would like is for the report to sort A-Z on LRef over the
whole report, just broken up into batches of 25. In other words, what I
want is:

1-25 A-M
26-50 M-Z

what I'm getting is:

1-25 A-Z
26-50 A-Z

The pre-query (qgrpPayments) sorts the recordset correctly LRef Ascending,
but then when I run the query within the report the DCount function seems to
just ignore the sort order. Rather annoying, and I can't see why this is
happening.

NigelS


Duane Hookom said:
Good luck finding a better method. I haven't ever found one that didn't
involve creating groups in the report's record source. If you didn't want to
create totals for the groups, it wouldn't be so difficult.

--
Duane Hookom
Microsoft Access MVP


nrms said:
Duane

I've done what you suggested and it seems to work (after replacing the
[OrderID] bit with [BACSRef], which I assume was just a typo).

Thanks for this, but I still feel there ought to be a simpler way of
achieving the same.

NigelS

:

I wasn't doing any math, just counting records. Try something like:

Save your SQL statement as a query [qgrpPayments] then create another query:

SELECT LRef, LName, BACSRef, BACSPaid, Chq,
(DCount("LRef","qgrpPayents","BACSRef<'" & [OrderID] & "'")+25)\25 AS GroupNum
FROM qgrpPayments
ORDER BY BACSRef;

--
Duane Hookom
Microsoft Access MVP


:

This is the SQL of the query underlying my report:

SELECT LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Sum(Daybook_Payments.Amount) AS BACSPaid, Daybook_Payments.Chq
FROM LandLords RIGHT JOIN (Daybook_Payments LEFT JOIN Rent_Book ON
Daybook_Payments.AccRef = Rent_Book.AccRef) ON LandLords.LRef = Rent_Book.LRef
GROUP BY LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Daybook_Payments.Chq
HAVING (((Daybook_Payments.Chq) Like "E*"))
ORDER BY LandLords.BACSRef;


:

You can create the groups in your report's record source query. The following
SQL create groups of 15 records from the Orders table in the Northwind sample
MDB.

SELECT (DCount("OrderID","Orders","OrderID<" & [OrderID])+15)\15 AS
GroupNum, Orders.*
FROM Orders
ORDER BY Orders.OrderID;

--
Duane Hookom
Microsoft Access MVP


:

I have created a report that lists names and amounts of money in two columns
from a query that pulls data from several tables in an Access 2003 database.

I would like to group the report so that the Amount field creates a batch
total every 25 records, like this:

Name1, Amount1
..
Name25, Amount25

Batch Total : SUM(Amount1 - Amount25)


Name26,Amount26
..
Name50,Amount50

Batch Total: SUM(Amount26 - Amount50)

and so on. I would think this is quite a simple task, but I'm having a
problem and can't see how to do it - Can anyone help?

Thanks
NigelS
Click to expand...
Click to expand...
Click to expand...
 
Try this and in report Sorting and Grouping sort on Page_No and LName.

SELECT LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Sum(Daybook_Payments.Amount) AS BACSPaid, Daybook_Payments.Chq, ((SELECT
Count(*) FROM LandLords AS [Dupe] WHERE LandLords.LRef & LandLords.LName &
LandLords.BACSRef <= Dupe.LRef & Dupe.LName & Dupe.BACSRef ORDER BY
LandLords.LRef, LandLords.LName, LandLords.BACSRef) \25)+1 AS Page_No
FROM LandLords RIGHT JOIN (Daybook_Payments LEFT JOIN Rent_Book ON
Daybook_Payments.AccRef = Rent_Book.AccRef) ON LandLords.LRef =Rent_Book.LRef
GROUP BY LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Daybook_Payments.Chq
HAVING (((Daybook_Payments.Chq) Like "E*"))
ORDER BY LandLords.LName;

--
Build a little, test a little.


nrms said:
One other little hiccup I'm finding is in the Sort order. In the final
report, each batch of 25 records is correctly sorted A-Z by the LRef Code.
Actually what I would like is for the report to sort A-Z on LRef over the
whole report, just broken up into batches of 25. In other words, what I
want is:

1-25 A-M
26-50 M-Z

what I'm getting is:

1-25 A-Z
26-50 A-Z

The pre-query (qgrpPayments) sorts the recordset correctly LRef Ascending,
but then when I run the query within the report the DCount function seems to
just ignore the sort order. Rather annoying, and I can't see why this is
happening.

NigelS


Duane Hookom said:
Good luck finding a better method. I haven't ever found one that didn't
involve creating groups in the report's record source. If you didn't want to
create totals for the groups, it wouldn't be so difficult.

--
Duane Hookom
Microsoft Access MVP


nrms said:
Duane

I've done what you suggested and it seems to work (after replacing the
[OrderID] bit with [BACSRef], which I assume was just a typo).

Thanks for this, but I still feel there ought to be a simpler way of
achieving the same.

NigelS

:

I wasn't doing any math, just counting records. Try something like:

Save your SQL statement as a query [qgrpPayments] then create another query:

SELECT LRef, LName, BACSRef, BACSPaid, Chq,
(DCount("LRef","qgrpPayents","BACSRef<'" & [OrderID] & "'")+25)\25 AS GroupNum
FROM qgrpPayments
ORDER BY BACSRef;

--
Duane Hookom
Microsoft Access MVP


:

This is the SQL of the query underlying my report:

SELECT LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Sum(Daybook_Payments.Amount) AS BACSPaid, Daybook_Payments.Chq
FROM LandLords RIGHT JOIN (Daybook_Payments LEFT JOIN Rent_Book ON
Daybook_Payments.AccRef = Rent_Book.AccRef) ON LandLords.LRef = Rent_Book.LRef
GROUP BY LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Daybook_Payments.Chq
HAVING (((Daybook_Payments.Chq) Like "E*"))
ORDER BY LandLords.BACSRef;


:

You can create the groups in your report's record source query. The following
SQL create groups of 15 records from the Orders table in the Northwind sample
MDB.

SELECT (DCount("OrderID","Orders","OrderID<" & [OrderID])+15)\15 AS
GroupNum, Orders.*
FROM Orders
ORDER BY Orders.OrderID;

--
Duane Hookom
Microsoft Access MVP


:

I have created a report that lists names and amounts of money in two columns
from a query that pulls data from several tables in an Access 2003 database.

I would like to group the report so that the Amount field creates a batch
total every 25 records, like this:

Name1, Amount1
..
Name25, Amount25

Batch Total : SUM(Amount1 - Amount25)


Name26,Amount26
..
Name50,Amount50

Batch Total: SUM(Amount26 - Amount50)

and so on. I would think this is quite a simple task, but I'm having a
problem and can't see how to do it - Can anyone help?

Thanks
NigelS
Click to expand...
Click to expand...
Click to expand...
 
Karl,

Thanks for this. When I copy & paste your SQL code into my report as the
data source, it saves OK, but when I try & run it, it gives an error msg:
"Query Too Complex". Has I misunderstood something?


KARL DEWEY said:
Try this and in report Sorting and Grouping sort on Page_No and LName.

SELECT LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Sum(Daybook_Payments.Amount) AS BACSPaid, Daybook_Payments.Chq, ((SELECT
Count(*) FROM LandLords AS [Dupe] WHERE LandLords.LRef & LandLords.LName &
LandLords.BACSRef <= Dupe.LRef & Dupe.LName & Dupe.BACSRef ORDER BY
LandLords.LRef, LandLords.LName, LandLords.BACSRef) \25)+1 AS Page_No
FROM LandLords RIGHT JOIN (Daybook_Payments LEFT JOIN Rent_Book ON
Daybook_Payments.AccRef = Rent_Book.AccRef) ON LandLords.LRef =Rent_Book.LRef
GROUP BY LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Daybook_Payments.Chq
HAVING (((Daybook_Payments.Chq) Like "E*"))
ORDER BY LandLords.LName;

--
Build a little, test a little.


nrms said:
One other little hiccup I'm finding is in the Sort order. In the final
report, each batch of 25 records is correctly sorted A-Z by the LRef Code.
Actually what I would like is for the report to sort A-Z on LRef over the
whole report, just broken up into batches of 25. In other words, what I
want is:

1-25 A-M
26-50 M-Z

what I'm getting is:

1-25 A-Z
26-50 A-Z

The pre-query (qgrpPayments) sorts the recordset correctly LRef Ascending,
but then when I run the query within the report the DCount function seems to
just ignore the sort order. Rather annoying, and I can't see why this is
happening.

NigelS


Duane Hookom said:
Good luck finding a better method. I haven't ever found one that didn't
involve creating groups in the report's record source. If you didn't want to
create totals for the groups, it wouldn't be so difficult.

--
Duane Hookom
Microsoft Access MVP


:

Duane

I've done what you suggested and it seems to work (after replacing the
[OrderID] bit with [BACSRef], which I assume was just a typo).

Thanks for this, but I still feel there ought to be a simpler way of
achieving the same.

NigelS

:

I wasn't doing any math, just counting records. Try something like:

Save your SQL statement as a query [qgrpPayments] then create another query:

SELECT LRef, LName, BACSRef, BACSPaid, Chq,
(DCount("LRef","qgrpPayents","BACSRef<'" & [OrderID] & "'")+25)\25 AS GroupNum
FROM qgrpPayments
ORDER BY BACSRef;

--
Duane Hookom
Microsoft Access MVP


:

This is the SQL of the query underlying my report:

SELECT LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Sum(Daybook_Payments.Amount) AS BACSPaid, Daybook_Payments.Chq
FROM LandLords RIGHT JOIN (Daybook_Payments LEFT JOIN Rent_Book ON
Daybook_Payments.AccRef = Rent_Book.AccRef) ON LandLords.LRef = Rent_Book.LRef
GROUP BY LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Daybook_Payments.Chq
HAVING (((Daybook_Payments.Chq) Like "E*"))
ORDER BY LandLords.BACSRef;


:

You can create the groups in your report's record source query. The following
SQL create groups of 15 records from the Orders table in the Northwind sample
MDB.

SELECT (DCount("OrderID","Orders","OrderID<" & [OrderID])+15)\15 AS
GroupNum, Orders.*
FROM Orders
ORDER BY Orders.OrderID;

--
Duane Hookom
Microsoft Access MVP


:

I have created a report that lists names and amounts of money in two columns
from a query that pulls data from several tables in an Access 2003 database.

I would like to group the report so that the Amount field creates a batch
total every 25 records, like this:

Name1, Amount1
..
Name25, Amount25

Batch Total : SUM(Amount1 - Amount25)


Name26,Amount26
..
Name50,Amount50

Batch Total: SUM(Amount26 - Amount50)

and so on. I would think this is quite a simple task, but I'm having a
problem and can't see how to do it - Can anyone help?

Thanks
NigelS
Click to expand...
Click to expand...
Click to expand...
 
Try this SQL
SELECT
LandLords.LRef,
LandLords.LName,
LandLords.BACSRef,
Sum(Daybook_Payments.Amount) AS BACSPaid,
Daybook_Payments.Chq, (DCount("*","LandLords","LRef & LName & BACSRef <=
""" & LRef & LName & BACSRef & """") \25)+1 AS Page_No
FROM LandLords RIGHT JOIN (
Daybook_Payments LEFT JOIN
Rent_Book ON Daybook_Payments.AccRef = Rent_Book.AccRef) ON
LandLords.LRef = Rent_Book.LRef
WHERE Chq Like "E*"
GROUP BY LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Daybook_Payments.Chq
ORDER BY LandLords.LName;
--
Duane Hookom
Microsoft Access MVP


nrms said:
Karl,

Thanks for this. When I copy & paste your SQL code into my report as the
data source, it saves OK, but when I try & run it, it gives an error msg:
"Query Too Complex". Has I misunderstood something?


KARL DEWEY said:
Try this and in report Sorting and Grouping sort on Page_No and LName.

SELECT LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Sum(Daybook_Payments.Amount) AS BACSPaid, Daybook_Payments.Chq, ((SELECT
Count(*) FROM LandLords AS [Dupe] WHERE LandLords.LRef & LandLords.LName &
LandLords.BACSRef <= Dupe.LRef & Dupe.LName & Dupe.BACSRef ORDER BY
LandLords.LRef, LandLords.LName, LandLords.BACSRef) \25)+1 AS Page_No
FROM LandLords RIGHT JOIN (Daybook_Payments LEFT JOIN Rent_Book ON
Daybook_Payments.AccRef = Rent_Book.AccRef) ON LandLords.LRef =Rent_Book.LRef
GROUP BY LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Daybook_Payments.Chq
HAVING (((Daybook_Payments.Chq) Like "E*"))
ORDER BY LandLords.LName;

--
Build a little, test a little.


nrms said:
One other little hiccup I'm finding is in the Sort order. In the final
report, each batch of 25 records is correctly sorted A-Z by the LRef Code.
Actually what I would like is for the report to sort A-Z on LRef over the
whole report, just broken up into batches of 25. In other words, what I
want is:

1-25 A-M
26-50 M-Z

what I'm getting is:

1-25 A-Z
26-50 A-Z

The pre-query (qgrpPayments) sorts the recordset correctly LRef Ascending,
but then when I run the query within the report the DCount function seems to
just ignore the sort order. Rather annoying, and I can't see why this is
happening.

NigelS


:

Good luck finding a better method. I haven't ever found one that didn't
involve creating groups in the report's record source. If you didn't want to
create totals for the groups, it wouldn't be so difficult.

--
Duane Hookom
Microsoft Access MVP


:

Duane

I've done what you suggested and it seems to work (after replacing the
[OrderID] bit with [BACSRef], which I assume was just a typo).

Thanks for this, but I still feel there ought to be a simpler way of
achieving the same.

NigelS

:

I wasn't doing any math, just counting records. Try something like:

Save your SQL statement as a query [qgrpPayments] then create another query:

SELECT LRef, LName, BACSRef, BACSPaid, Chq,
(DCount("LRef","qgrpPayents","BACSRef<'" & [OrderID] & "'")+25)\25 AS GroupNum
FROM qgrpPayments
ORDER BY BACSRef;

--
Duane Hookom
Microsoft Access MVP


:

This is the SQL of the query underlying my report:

SELECT LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Sum(Daybook_Payments.Amount) AS BACSPaid, Daybook_Payments.Chq
FROM LandLords RIGHT JOIN (Daybook_Payments LEFT JOIN Rent_Book ON
Daybook_Payments.AccRef = Rent_Book.AccRef) ON LandLords.LRef = Rent_Book.LRef
GROUP BY LandLords.LRef, LandLords.LName, LandLords.BACSRef,
Daybook_Payments.Chq
HAVING (((Daybook_Payments.Chq) Like "E*"))
ORDER BY LandLords.BACSRef;


:

You can create the groups in your report's record source query. The following
SQL create groups of 15 records from the Orders table in the Northwind sample
MDB.

SELECT (DCount("OrderID","Orders","OrderID<" & [OrderID])+15)\15 AS
GroupNum, Orders.*
FROM Orders
ORDER BY Orders.OrderID;

--
Duane Hookom
Microsoft Access MVP


:

I have created a report that lists names and amounts of money in two columns
from a query that pulls data from several tables in an Access 2003 database.

I would like to group the report so that the Amount field creates a batch
total every 25 records, like this:

Name1, Amount1
..
Name25, Amount25

Batch Total : SUM(Amount1 - Amount25)


Name26,Amount26
..
Name50,Amount50

Batch Total: SUM(Amount26 - Amount50)

and so on. I would think this is quite a simple task, but I'm having a
problem and can't see how to do it - Can anyone help?

Thanks
NigelS
Click to expand...
Click to expand...
Click to expand...
 
Back
Top