simple report grouping problem

[Aivars]

Hello,
I am still a newbee so please bear with me. I still find reporting in
Access as hardest aspect to master.
I have created a query like this:

ITEM_NAME ROWNO AMOUNT
Name1 10 amount1
Name2 20 amount2
Name4 40 amount4
Name5 50 amount5
Name6 60 amount6

and so on

Now I need to simply sum amount1+amount2 so
that in a report this sum shows below line 20 having a number 30

Report should look:
Name1 10 amount1
Name2 20 amount2
Name3 30 amount3 (being sum
of amount1+amount2)
Name4 40 amount4
Name5 50 amount5

In Excel this line 30 would simply to sum two number above with SUM
formula. And I cannot figure out how to sum two rows above in Access
Should I have additional columns in this query somehow showing
grouping levels or what??

In addition I also need the sum(lines 40,60,70) substracted from this
line 30 forming new additional row 70.
Or am I asking too much?
(I am trying to create a financial Profit and Loss Account for a
company)

Using Access 2007

Thanks
Aivars

 

[Aivars]

Hello again,
I created another query containing only summary rows which are
calculated by DSUM based on the first query using ROWNO as criteria.
Then I did an UNION ALL query on these two queries and now I have what
i want! It runs a bit slow because I used VBA with DSUM, I think, but
at least it works.

But still I am confused that I was not able to do the summing directly
on report.
I feel my way of doing it is a bit ugly because I generaly have read
on these groups that it is better to do the summing and grouping on
the report

Thank you for reading this

Aivars

 

[BruceM]

There was really nothing in your original post (at least not that I could
see) to explain why you would sum RowNo 10 and 20 to produce the result in
RowNo 30. Similarly, there is no way to know why you would sum 40, 60, and
70 to create a new line 70. Your question suggests that you are thinking of
the query as a spreadsheet. Without knowing anything about the real-world
situation you are trying to address, there is no way to suggest a solution.
Having said that, if there is a reason 10 and 20 are grouped together you
can group the report by the appropriate field, then bind a text box on the
report to that field. Set the Running Sum property for that text box (I
iwll call it txtRunSum) to Over Group. This will show the cumulative result
in each record, so if you want to see just the final total, set the Visible
property of txtRunSum to No, and place a text box into the group footer with
its Control Source set to =[txtRunSum].

 

[hoachen]

I am trying to convert the ClientID to client’s name.

I have;
TABLE
tbl_Client
ClientID
ClientName
ClientAddr
ClientPhone
Tbl_Order
OrderID
ClientID
Account
OrderDate

The clientID is link to tbl_client

QUARIES
Qry_OrderSearch
OrderID
ClientID
Account
ClientName
OrderDate
Qry_Journal
OrderID
ClientID
Account
ClientName
OrderDate
etc

The clientID is link to the tbl_Order

REPORT
rpt_Order
OrderDate
ClientName
ClientID
Account
The clientID is link to the tbl_Order, because the result is query from the
order. How can I get the ClientName show instead of the client ID?
Since I use qry_Journal I have to put on “Control Source =[ClientID]
My question is how can use this clientID link to clientName.

 

[Aivars]

Hello, BruceM,
Thanks for your advice
Yes, I am coming from Excel world into Access and as I said i am very new to
Access. In Excel it is very easy to sum two rows above but Access is
different.
The reason I want to group and sum rows 10 and 20 is simple - this is/will
be a company financial profit and loss account
And row 10 is Net turnover. Row 20 is Cost of Sales. Net turnover is with
negative (-) sign, cost of sales positive (+) number. So summing these two
together I get Gross Profit (Net turnover - Cost of Sales = Gross Profit)

Probably i should rethink the way as how do I calculate the profit and loss
account (PL) in Access. Generaly speaking PL account consists of a certain
amount of rows (15) which are returned by query and then these rows shud be
grouped together on a report in specific way enabling to see the Gross
Profit, Profit before taxes, and Period Profit.

Will try now your suggestion with running sums


Aivars

 

[BruceM]

Row 10 is net turnover for whom? A single entity (company or whatever)? A
group of businesses? Same question for Row 20. Let's back up a little.
What is the source of the Row 10 data? How is the number derived?

When you say PL consists of a certain number of rows, the question again is
about the source of the data in those rows. What do they have in common
with each other?

In Access, a table is where data are stored. A table row is a record, and a
table column is a field. A query can be thought of in the same way as a
table, in general, in terms of records and fields. There are some
differences, such as with crosstab queries, in terms of how they are
organized, but that can wait.

A table contains information about a single real-world entity such as
person, bank account information, etc. The information is broken into
distinct units (fields) that each contain a single piece of information. In
a People table, those fields would include FirstName, LastName, Phone, etc.
There needs to be a way to uniquely identify the person (a primary key).
One simple way of doing that is to add an autonumber field, which assigns a
unique number to each new record you create. The fields would not include
bank account information, because a bank account is not an attribute of a
person. Still using the example of the People table, a record is
information about one person.

If a person has a bank account, that information would be in a separate,
related table. A person could have one bank account or a dozen; they would
be separate records in that table. Each record includes a field (the
foreign key) that corresponds to the primary key field in the People table.
Taking it a step further, each bank account contains a number of separate
transactions. There is another table, related to the Account table, for
these Account Details.

It is a fairly simple matter in Access to sum something like the DebitAmount
field from the AccountDetails table, either for a single Account or for a
group of Accounts. An important distinction from Excel is that you can
select any group of cells, and enter a calculation for those fields in
another cell. You can do that in Access, too, but the mechanism is very
different.

Take a look at Allen Browne's Tips for Casual Users:
http://allenbrowne.com/tips.html. Such information is included in a number
of other sites, too. Allen is one among many who have made their expertise
available to the general public. Another site is
http://www.mvps.org/access/. Both sites contain links to many other
resources.

Hello, BruceM,
Thanks for your advice
Yes, I am coming from Excel world into Access and as I said i am very new
to Access. In Excel it is very easy to sum two rows above but Access is
different.
The reason I want to group and sum rows 10 and 20 is simple - this is/will
be a company financial profit and loss account
And row 10 is Net turnover. Row 20 is Cost of Sales. Net turnover is with
negative (-) sign, cost of sales positive (+) number. So summing these two
together I get Gross Profit (Net turnover - Cost of Sales = Gross Profit)

Probably i should rethink the way as how do I calculate the profit and
loss account (PL) in Access. Generaly speaking PL account consists of a
certain amount of rows (15) which are returned by query and then these
rows shud be grouped together on a report in specific way enabling to see
the Gross Profit, Profit before taxes, and Period Profit.

Will try now your suggestion with running sums


Aivars

There was really nothing in your original post (at least not that I could
see) to explain why you would sum RowNo 10 and 20 to produce the result
in RowNo 30. Similarly, there is no way to know why you would sum 40,
60, and 70 to create a new line 70. Your question suggests that you are
thinking of the query as a spreadsheet. Without knowing anything about
the real-world situation you are trying to address, there is no way to
suggest a solution.
Having said that, if there is a reason 10 and 20 are grouped together you
can group the report by the appropriate field, then bind a text box on
the report to that field. Set the Running Sum property for that text box
(I iwll call it txtRunSum) to Over Group. This will show the cumulative
result in each record, so if you want to see just the final total, set
the Visible property of txtRunSum to No, and place a text box into the
group footer with its Control Source set to =[txtRunSum].

 

[Aivars]

Thanks for your effort to help me.

I followed your suggestion about using running sum and it worked very
well! In a Profit and Loss report definition table I added another
three fields - one for grouping the lines I need to sum, the second
with the name of this sum as it should appear on the report and the
third one to show the numbering of the summed line. And that was it!

Aivars

Row 10 is net turnover for whom? A single entity (company or whatever)? A
group of businesses? Same question for Row 20. Let's back up a little.
What is the source of the Row 10 data? How is the number derived?

When you say PL consists of a certain number of rows, the question again is
about the source of the data in those rows. What do they have in common
with each other?

In Access, a table is where data are stored. A table row is a record, and a
table column is a field. A query can be thought of in the same way as a
table, in general, in terms of records and fields. There are some
differences, such as with crosstab queries, in terms of how they are
organized, but that can wait.

A table contains information about a single real-world entity such as
person, bank account information, etc. The information is broken into
distinct units (fields) that each contain a single piece of information. In
a People table, those fields would include FirstName, LastName, Phone, etc.
There needs to be a way to uniquely identify the person (a primary key).
One simple way of doing that is to add an autonumber field, which assigns a
unique number to each new record you create. The fields would not include
bank account information, because a bank account is not an attribute of a
person. Still using the example of the People table, a record is
information about one person.

If a person has a bank account, that information would be in a separate,
related table. A person could have one bank account or a dozen; they would
be separate records in that table. Each record includes a field (the
foreign key) that corresponds to the primary key field in the People table.
Taking it a step further, each bank account contains a number of separate
transactions. There is another table, related to the Account table, for
these Account Details.

It is a fairly simple matter in Access to sum something like the DebitAmount
field from the AccountDetails table, either for a single Account or for a
group of Accounts. An important distinction from Excel is that you can
select any group of cells, and enter a calculation for those fields in
another cell. You can do that in Access, too, but the mechanism is very
different.

Take a look at Allen Browne's Tips for Casual Users:
http://allenbrowne.com/tips.html. Such information is included in a number
of other sites, too. Allen is one among many who have made their expertise
available to the general public. Another site is
http://www.mvps.org/access/. Both sites contain links to many other
resources.

Hello, BruceM,
Thanks for your advice
Yes, I am coming from Excel world into Access and as I said i am very new
to Access. In Excel it is very easy to sum two rows above but Access is
different.
The reason I want to group and sum rows 10 and 20 is simple - this is/will
be a company financial profit and loss account
And row 10 is Net turnover. Row 20 is Cost of Sales. Net turnover is with
negative (-) sign, cost of sales positive (+) number. So summing these two
together I get Gross Profit (Net turnover - Cost of Sales = Gross Profit)

Probably i should rethink the way as how do I calculate the profit and
loss account (PL) in Access. Generaly speaking PL account consists of a
certain amount of rows (15) which are returned by query and then these
rows shud be grouped together on a report in specific way enabling to see
the Gross Profit, Profit before taxes, and Period Profit.

Will try now your suggestion with running sums


Aivars

There was really nothing in your original post (at least not that I could
see) to explain why you would sum RowNo 10 and 20 to produce the result
in RowNo 30. Similarly, there is no way to know why you would sum 40,
60, and 70 to create a new line 70. Your question suggests that you are
thinking of the query as a spreadsheet. Without knowing anything about
the real-world situation you are trying to address, there is no way to
suggest a solution.
Having said that, if there is a reason 10 and 20 are grouped together you
can group the report by the appropriate field, then bind a text box on
the report to that field. Set the Running Sum property for that text box
(I iwll call it txtRunSum) to Over Group. This will show the cumulative
result in each record, so if you want to see just the final total, set
the Visible property of txtRunSum to No, and place a text box into the
group footer with its Control Source set to =[txtRunSum].