Trouble with creating a simple query with calculated fields.

[Rex]

Hi,

I want to creat a query that would look like the following. The only
trouble is I am not able to get the calculated fields to calculate the
values. It gives me #Error


Names | Contribution | Difference | remainder | Payables
----------------------------------------------------------------------------------------------
John 55 35 67.5 32.5
Smith 10 80 67.5 -12.5
Cooper 15 75 67.5 -7.5
Toby 10 80 67.5 -12.5
---------------------------------------------------------------------------------------------
90 270
---------------------------------------------------------------------------------------------

Rules:

TotalExp => Sum(Contribution)
Difference => TotalExp - Contribution
TotalDiff => Sum(Difference)
Remainder => TotalDiff * (1/4)
Payable => Remainder - Difference

I want to do this in a Query.. Thank you
Rex

 

[Larry Linson]

What's the underlying Table data? What's the SQL view of your Query? Your
description seems to be of a Totals Query, but it's not obvious to me from
the example that's what you are intending. I hope you were not expecting to
be able to use a Query to do sequential processing through your Table --
that's not what Queries in databases are intended for.

Larry Linson
Micosoft Access MVP

 

[Rex]

Yes I want to do sequential processing through tables

 

[Larry Linson]

Clarify for us the original data in the underlying Table. And, rather than
give individual "rules" based on the idea of sequential processing or how
you think it might be accomplished, describe what you are _trying to
accomplish_ in words. Something like the following

"For each member, total all dues payments, . . ."

You can't reliably do sequential processing in an Access query because the
reading/calculation may not take place when you think it should. Relational
database tables are, by definition, UNordered. They are ordered by sorting
(in SQL terms, using an ORDER BY clause) in the Query, but the ordering may
take place after the calculation.

You can sort the data in a Query and process sequentially in code, which is
one approach. But, you may be able to accomplish your purpose without
"sequential processing" in a Totals Query. If you can, it will be more
efficient as well as simpler to implement.

Larry Linson
Microsoft Access MVP

Yes I want to do sequential processing through tables

 

[Rex]

Here is complete explanation of what I want to do..


Hi I have two tables with values:


CREATE TABLE [Member] (
[memberID] [smallint] IDENTITY (1, 1) NOT NULL ,
[name] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED
(
[memberID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

values in the Member table:

memberID name
-------- ----------
1 Smith
2 John
3 Allen
4 Kate


CREATE TABLE [Contribution] (
[contID] [smallint] IDENTITY (1, 1) NOT NULL ,
[memberID] [smallint] NULL ,
[contribution] [decimal](18, 0) NULL ,
CONSTRAINT [PK_Contribution] PRIMARY KEY CLUSTERED
(
[contID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Contribution_Member] FOREIGN KEY
(
[memberID]
) REFERENCES [Member] (
[memberID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO

values in Contribution Table:

contID memberID contribution
------ -------- --------------------
1 1 50
2 1 5
3 2 5
4 2 5
5 3 5
6 3 5
7 3 5
8 4 10

A Query: PayablesQuery

SELECT dbo.Member.name, SUM(dbo.Contribution.contribution) AS
[Total Contribution]
FROM dbo.Contribution INNER JOIN
dbo.Member ON dbo.Contribution.memberID =
dbo.Member.memberID
GROUP BY dbo.Member.name

name Total Contribution
---------- ----------------------------------------
Allen 15
John 10
Kate 10
Smith 55


Now I want to add extra calculated fields to PayablesQuery which are
based on the Total Contribution field.
The fields that I want are Difference, Remainder and Payables that
would have the values.

The values for this field is derived according to following rules

1) Difference: sum(Total Contribtion) - Total Contribution
i.e.
90-15 = 75
90-10 = 80
90-10 = 80
90-55 = 35

2) Remanider: sum(Difference) * 1/(no. of Members, in this case its
4)
3) Payables: Remainder - Difference

So the resultant query would look like this:


name Total Contribution Differnce Remainder
Payables
---------- -------------------- --------------- --------------
-------------------
Allen 15 75 67.5 -7.5
John 10 80 67.5 -12.5
Kate 10 80 67.5 -12.5
Smith 55 35 67.5 32.5

Thanks

 

[Rex]

My question is I want to create a Query payables which will have the
fields as given in the eg. of Payables query..

Here is complete explanation of what I want to do..

Hi I have two tables with values:

CREATE TABLE [Member] (
[memberID] [smallint] IDENTITY (1, 1) NOT NULL ,
[name] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED
(
[memberID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

values in the Member table:

memberID name
-------- ----------
1 Smith
2 John
3 Allen
4 Kate

CREATE TABLE [Contribution] (
[contID] [smallint] IDENTITY (1, 1) NOT NULL ,
[memberID] [smallint] NULL ,
[contribution] [decimal](18, 0) NULL ,
CONSTRAINT [PK_Contribution] PRIMARY KEY CLUSTERED
(
[contID]
) ON [PRIMARY] ,
CONSTRAINT [FK_Contribution_Member] FOREIGN KEY
(
[memberID]
) REFERENCES [Member] (
[memberID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO

values in Contribution Table:

contID memberID contribution
------ -------- --------------------
1 1 50
2 1 5
3 2 5
4 2 5
5 3 5
6 3 5
7 3 5
8 4 10

A Query: PayablesQuery

SELECT dbo.Member.name, SUM(dbo.Contribution.contribution) AS
[Total Contribution]
FROM dbo.Contribution INNER JOIN
dbo.Member ON dbo.Contribution.memberID =
dbo.Member.memberID
GROUP BY dbo.Member.name

name Total Contribution
---------- ----------------------------------------
Allen 15
John 10
Kate 10
Smith 55

Now I want to add extra calculated fields to PayablesQuery which are
based on the Total Contribution field.
The fields that I want are Difference, Remainder and Payables that
would have the values.

The values for this field is derived according to following rules

1) Difference: sum(Total Contribtion) - Total Contribution
i.e.
90-15 = 75
90-10 = 80
90-10 = 80
90-55 = 35

2) Remanider: sum(Difference) * 1/(no. of Members, in this case its
4)
3) Payables: Remainder - Difference

So the resultant query would look like this:

name Total Contribution Differnce Remainder
Payables
---------- -------------------- --------------- --------------
-------------------
Allen 15 75 67.5 -7.5
John 10 80 67.5 -12.5
Kate 10 80 67.5 -12.5
Smith 55 35 67.5 32.5

Thanks

Clarify for us the original data in the underlying Table. And, rather than
give individual "rules" based on the idea of sequential processing or how
you think it might be accomplished, describe what you are _trying to
accomplish_ in words. Something like the following

"For each member, total all dues payments, . . ."

You can't reliably do sequential processing in an Access query because the
reading/calculation may not take place when you think it should. Relational
database tables are, by definition, UNordered. They are ordered by sorting
(in SQL terms, using an ORDER BY clause) in the Query, but the orderingmay
take place after the calculation.

You can sort the data in a Query and process sequentially in code, which is
one approach. But, you may be able to accomplish your purpose without
"sequential processing" in a Totals Query. If you can, it will be more
efficient as well as simpler to implement.

Larry Linson
Microsoft Access MVP

"Rex" <[email protected]> wrote in message

Yes I want to do sequential processing through tables

- Show quoted text -- Hide quoted text -

- Show quoted text -