incorrect query results when joining tables

  • Thread starter Thread starter robertm600635
  • Start date Start date
R

robertm600635

I have 2 tables:

[dbo].[tblFees](
[FeeID] [int] IDENTITY(1,1) NOT NULL,
[ClientID] [int] NULL,
[FeeDate] [datetime] NULL,
[DeptNo] [nvarchar](50) NULL,
[ServiceType] [nvarchar](50) NULL,
[Service] [nvarchar](50) NULL,
[Location] [nvarchar](50) NULL,
[FeeAmount] [money] NULL,
[FeeAdj] [money] NULL,
[TransNo] [nvarchar](50) NULL,
[PostedBy] [nvarchar](100) NULL,
[Comments] [nvarchar](max) NULL,

[dbo].[tblPayments](
[PayID] [int] IDENTITY(1,1) NOT NULL,
[FeeID] [int] NULL,
[PayDate] [datetime] NULL,
[PayType] [nvarchar](50) NULL,
[PayAmount] [money] NULL,
[ReceiptNo] [nvarchar](50) NULL,
[TransNo] [nvarchar](50) NULL,
[ReceivedBy] [nvarchar](100) NULL


I need to create a query that sums the following columns: Fees, Payments,
FeeAdj. Then I need to calculate the balance like: Fees - Payments + FeeAdj =
Balance. I also Group By the Service field because thats the way I need it
for the report.

I do fine up until I add the Payments table to the query then it produces
way too many rows and the calculations are way higher than they should be. I
know it has something to do with the join but I'm not good enough at this to
figure it out. Also there are records in the Fees table that do not yet have
a related record in the Payments table so that has to be accounted for
somehow. Any help would be grealy appreciated.
 
Probably that you need to learn how to make subqueries. However, it's not
clear from your post if you are working against a SQL-Server with an ADP
project (this newsgroup) or with an Access MDB or ACCDB database file (other
newsgroups such as microsoft.public.access.queries).

The syntax for subqueries between SQL-Server and Access is different; so you
should clarify this point.

Also, with Access, you can use functions such DCount and DSum to replace
subqueries in some places.

Another solution would be to use a temporary table to help you in
calculating the various results; as this kind of stuff can become quite
complicated sometimes.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)
 
I'm using an ADP file with SQL Server 2008 as the backend.

Sylvain Lafontaine said:
Probably that you need to learn how to make subqueries. However, it's not
clear from your post if you are working against a SQL-Server with an ADP
project (this newsgroup) or with an Access MDB or ACCDB database file (other
newsgroups such as microsoft.public.access.queries).

The syntax for subqueries between SQL-Server and Access is different; so you
should clarify this point.

Also, with Access, you can use functions such DCount and DSum to replace
subqueries in some places.

Another solution would be to use a temporary table to help you in
calculating the various results; as this kind of stuff can become quite
complicated sometimes.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


robertm600635 said:
I have 2 tables:

[dbo].[tblFees](
[FeeID] [int] IDENTITY(1,1) NOT NULL,
[ClientID] [int] NULL,
[FeeDate] [datetime] NULL,
[DeptNo] [nvarchar](50) NULL,
[ServiceType] [nvarchar](50) NULL,
[Service] [nvarchar](50) NULL,
[Location] [nvarchar](50) NULL,
[FeeAmount] [money] NULL,
[FeeAdj] [money] NULL,
[TransNo] [nvarchar](50) NULL,
[PostedBy] [nvarchar](100) NULL,
[Comments] [nvarchar](max) NULL,

[dbo].[tblPayments](
[PayID] [int] IDENTITY(1,1) NOT NULL,
[FeeID] [int] NULL,
[PayDate] [datetime] NULL,
[PayType] [nvarchar](50) NULL,
[PayAmount] [money] NULL,
[ReceiptNo] [nvarchar](50) NULL,
[TransNo] [nvarchar](50) NULL,
[ReceivedBy] [nvarchar](100) NULL


I need to create a query that sums the following columns: Fees, Payments,
FeeAdj. Then I need to calculate the balance like: Fees - Payments +
FeeAdj =
Balance. I also Group By the Service field because thats the way I need it
for the report.

I do fine up until I add the Payments table to the query then it produces
way too many rows and the calculations are way higher than they should be.
I
know it has something to do with the join but I'm not good enough at this
to
figure it out. Also there are records in the Fees table that do not yet
have
a related record in the Payments table so that has to be accounted for
somehow. Any help would be grealy appreciated.
Click to expand...


.
Click to expand...
 
Then I've just find something for you to do in the Xmas period: searching
the Internet for "sql-server subquery" or "sql-server correlated subquery"
and study the articles/examples.

You should end up with something like (untested) :

Select ( FeeAmount + FeeAdj - IsNull ( (Select Sum(PayAmount) from
tblPayments as P
where P.FeeId = F.FeeId), 0) ) as Balance
From tblFees as F

Notice that the above subquery is a correlated subquery because its result
depends on an external value, the F.FeeId in this case.

The IsNull() is there to take into account those fees without any associated
payment.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


robertm600635 said:
I'm using an ADP file with SQL Server 2008 as the backend.

Sylvain Lafontaine said:
Probably that you need to learn how to make subqueries. However, it's
not
clear from your post if you are working against a SQL-Server with an ADP
project (this newsgroup) or with an Access MDB or ACCDB database file
(other
newsgroups such as microsoft.public.access.queries).

The syntax for subqueries between SQL-Server and Access is different; so
you
should clarify this point.

Also, with Access, you can use functions such DCount and DSum to replace
subqueries in some places.

Another solution would be to use a temporary table to help you in
calculating the various results; as this kind of stuff can become quite
complicated sometimes.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


message
I have 2 tables:

[dbo].[tblFees](
[FeeID] [int] IDENTITY(1,1) NOT NULL,
[ClientID] [int] NULL,
[FeeDate] [datetime] NULL,
[DeptNo] [nvarchar](50) NULL,
[ServiceType] [nvarchar](50) NULL,
[Service] [nvarchar](50) NULL,
[Location] [nvarchar](50) NULL,
[FeeAmount] [money] NULL,
[FeeAdj] [money] NULL,
[TransNo] [nvarchar](50) NULL,
[PostedBy] [nvarchar](100) NULL,
[Comments] [nvarchar](max) NULL,

[dbo].[tblPayments](
[PayID] [int] IDENTITY(1,1) NOT NULL,
[FeeID] [int] NULL,
[PayDate] [datetime] NULL,
[PayType] [nvarchar](50) NULL,
[PayAmount] [money] NULL,
[ReceiptNo] [nvarchar](50) NULL,
[TransNo] [nvarchar](50) NULL,
[ReceivedBy] [nvarchar](100) NULL


I need to create a query that sums the following columns: Fees,
Payments,
FeeAdj. Then I need to calculate the balance like: Fees - Payments +
FeeAdj =
Balance. I also Group By the Service field because thats the way I need
it
for the report.

I do fine up until I add the Payments table to the query then it
produces
way too many rows and the calculations are way higher than they should
be.
I
know it has something to do with the join but I'm not good enough at
this
to
figure it out. Also there are records in the Fees table that do not yet
have
a related record in the Payments table so that has to be accounted for
somehow. Any help would be grealy appreciated.
Click to expand...


.
Click to expand...
Click to expand...
 
robertm600635 said:
I have 2 tables:

[dbo].[tblFees](
[FeeID] [int] IDENTITY(1,1) NOT NULL,
[ClientID] [int] NULL,
[FeeDate] [datetime] NULL,
[DeptNo] [nvarchar](50) NULL,
[ServiceType] [nvarchar](50) NULL,
[Service] [nvarchar](50) NULL,
[Location] [nvarchar](50) NULL,
[FeeAmount] [money] NULL,
[FeeAdj] [money] NULL,
[TransNo] [nvarchar](50) NULL,
[PostedBy] [nvarchar](100) NULL,
[Comments] [nvarchar](max) NULL,

[dbo].[tblPayments](
[PayID] [int] IDENTITY(1,1) NOT NULL,
[FeeID] [int] NULL,
[PayDate] [datetime] NULL,
[PayType] [nvarchar](50) NULL,
[PayAmount] [money] NULL,
[ReceiptNo] [nvarchar](50) NULL,
[TransNo] [nvarchar](50) NULL,
[ReceivedBy] [nvarchar](100) NULL


I need to create a query that sums the following columns: Fees, Payments,
FeeAdj. Then I need to calculate the balance like: Fees - Payments +
FeeAdj =
Balance. I also Group By the Service field because thats the way I need it
for the report.

I do fine up until I add the Payments table to the query then it produces
way too many rows and the calculations are way higher than they should be.
I
know it has something to do with the join but I'm not good enough at this
to
figure it out. Also there are records in the Fees table that do not yet
have
a related record in the Payments table so that has to be accounted for
somehow. Any help would be grealy appreciated.
Click to expand...
 
Back
Top