Trouble with creating a simple query with calculated fields.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rex - 09 Mar 2007 02:42 GMT

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

Reply to this Message

Larry Linson - 09 Mar 2007 03:16 GMT

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

> Hi,
>
[quoted text clipped - 22 lines]
> I want to do this in a Query.. Thank you
> Rex

Reply to this Message

Rex - 09 Mar 2007 04:18 GMT

Yes I want to do sequential processing through tables

> 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
[quoted text clipped - 33 lines]
>
> - Show quoted text -

Reply to this Message

Larry Linson - 09 Mar 2007 09:58 GMT

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

On Mar 9, 1:16 pm, "Larry Linson" <boun...@localhost.not> wrote:

> 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
[quoted text clipped - 38 lines]
>
> - Show quoted text -

Reply to this Message

Rex - 13 Mar 2007 00:30 GMT

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
[quoted text clipped - 65 lines]
>
> - Show quoted text -

Reply to this Message

Rex - 13 Mar 2007 23:43 GMT

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..
>
[quoted text clipped - 170 lines]
>
> - Show quoted text -

Reply to this Message

Jamie Collins - 15 Mar 2007 15:09 GMT

> Here is complete explanation of what I want to do..
>
[quoted text clipped - 95 lines]
>
> Thanks

I'm not sure which engine, so here's a script that should work in both
Access/Jet (ANSI-92 Query Mode) and SQL Server:

CREATE TABLE Member (
memberID SMALLINT NOT NULL,
name CHAR(10),
CONSTRAINT PK_Member PRIMARY KEY (memberID)
)
;
INSERT INTO Member (memberID, name) VALUES (1, 'Smith')
;
INSERT INTO Member (memberID, name) VALUES (2, 'John')
;
INSERT INTO Member (memberID, name) VALUES (3, 'Allen')
;
INSERT INTO Member (memberID, name) VALUES (4, 'Kate')
;

CREATE TABLE Contribution (
contID SMALLINT NOT NULL,
memberID SMALLINT,
contribution DECIMAL(18, 0),
CONSTRAINT PK_Contribution PRIMARY KEY (contID),
CONSTRAINT FK_Contribution_Member
FOREIGN KEY (memberID)
REFERENCES Member (memberID)
ON DELETE CASCADE
ON UPDATE CASCADE
)
;
INSERT INTO Contribution (contID, memberID, contribution) VALUES (1,
1, 50)
;
INSERT INTO Contribution (contID, memberID, contribution) VALUES (2,
1, 5)
;
INSERT INTO Contribution (contID, memberID, contribution) VALUES (3,
2, 5)
;
INSERT INTO Contribution (contID, memberID, contribution) VALUES (4,
2, 5)
;
INSERT INTO Contribution (contID, memberID, contribution) VALUES (5,
3, 5)
;
INSERT INTO Contribution (contID, memberID, contribution) VALUES (6,
3, 5)
;
INSERT INTO Contribution (contID, memberID, contribution) VALUES (7,
3, 5)
;
INSERT INTO Contribution (contID, memberID, contribution) VALUES (8,
4, 10)
;
CREATE VIEW MemberContributionTotal
AS
SELECT M1.memberID,
SUM(C1.contribution) AS [Total Contribution]
FROM Member AS M1
INNER JOIN Contribution AS C1
ON M1.memberID = C1.memberID
GROUP BY M1.memberID
;
CREATE VIEW MemberContributionDifference
AS
SELECT MCT1.memberID,
SUM(MCT2.[Total Contribution]) - MCT1.[Total Contribution] AS
Difference
FROM MemberContributionTotal AS MCT1,
MemberContributionTotal AS MCT2
GROUP BY MCT1.memberID, MCT1.[Total Contribution]
;
CREATE VIEW MemberPayable
AS
SELECT MCD1.memberID, MCD1.Difference,
SUM(MCD2.Difference) / COUNT(*) AS Remainder,
(SUM(MCD2.Difference) / COUNT(*)) - MCD1.Difference AS Payables
FROM MemberContributionDifference AS MCD1,
MemberContributionDifference AS MCD2
GROUP BY MCD1.memberID, MCD1.Difference
;

There's a problem with implicit casting in the above for SQL Server
e.g. some explicit casting to four decimal places for the final query:

SELECT MCD1.memberID, MCD1.Difference,
CAST(SUM(MCD2.Difference) / COUNT(*) AS DECIMAL(20, 4)) AS Remainder,
CAST(SUM(MCD2.Difference) / COUNT(*) AS DECIMAL(20, 4)) -
CAST(MCD1.Difference AS DECIMAL(20, 4)) AS Payables
FROM MemberContributionDifference AS MCD1,
MemberContributionDifference AS MCD2
GROUP BY MCD1.memberID, MCD1.Difference
;

I've assumed that by "no. of Members" in your Remainder calculation
you mean *contributing* members i.e. join of Member and Contribution,
rather than the cardinality of the Member table

Jamie.

--

Reply to this Message