Deranged Calculation

I'm not sure this is going to make any sence
I need to take the Sum of these amounts in table A and B grouped by Account
# separetly and then compare them , grouped by account #, with some data from
Table C though whilst still keeping the sum of A and B separate. The problem
is that when i put the data together i get the sum from tables A and B
multiplied by the number of records in tables a and b. So if the sum of
account #12121 on table A is 10 and there are three records on table A for
that acccount I get 30. BUT, THis only happens when i put A and B together,
as long as I work with only table A and C or B and C the calculations work
fine.
Any ideas out there?
R,
K

This assumes that the account # is in both tables --
SELECT TableA.[account #], Sum(TableA.Score) AS SumOfScore1,
Sum(TableB.Score) AS SumOfScore
FROM TableA INNER JOIN TableB ON TableA.[account #] = TableB.[account #]
GROUP BY TableA.[account #];

This assumes that the account # may only be in TableA --
SELECT TableA.[account #], Sum(TableA.Score) AS SumOfScore1,
Sum(TableB.Score) AS SumOfScore
FROM TableA LEFT JOIN TableB ON TableA.[account #] = TableB.[account #]
GROUP BY TableA.[account #];

--
KARL DEWEY
Build a little - Test a little


"KWhamill" wrote:

I'm not sure this is going to make any sence
I need to take the Sum of these amounts in table A and B grouped by Account
# separetly and then compare them , grouped by account #, with some data from
Table C though whilst still keeping the sum of A and B separate. The problem
is that when i put the data together i get the sum from tables A and B
multiplied by the number of records in tables a and b. So if the sum of
account #12121 on table A is 10 and there are three records on table A for
that acccount I get 30. BUT, THis only happens when i put A and B together,
as long as I work with only table A and C or B and C the calculations work
fine.
Any ideas out there?
R,
K

KWhamill wrote:

I'm not sure this is going to make any sence
I need to take the Sum of these amounts in table A and B grouped by Account
# separetly and then compare them , grouped by account #, with some data from
Table C though whilst still keeping the sum of A and B separate. The problem
is that when i put the data together i get the sum from tables A and B
multiplied by the number of records in tables a and b. So if the sum of
account #12121 on table A is 10 and there are three records on table A for
that acccount I get 30. BUT, THis only happens when i put A and B together,
as long as I work with only table A and C or B and C the calculations work
fine.


Do the totals in separate queries, then create a third query
that joins the data from the first two queries.

--
Marsh
MVP [MS Access]

Karl Thank you for coming to the rescue,
what i have is this:
SELECT TableC[Account#], Sum([TableA].[Amount]) AS [SumOfAmount],
Sum([TableB].[Amount]) AS [SumOfAmount1]
FROM (TableC LEFT JOIN [TableA] ON TableC[account#] = [TableA].[Account])
LEFT JOIN [TAbleB] ON TAbleC .[Account#] = [TableB].[Account]
GROUP BY TableC.[Account#];
I get the Sum of the amounts I'm looking for but they are multiplied by the
number of records on the tables A and B. If i only use table A or B It works
correctly if I use both I get the multiplied numbers.
ANY ideas?
K,


"KARL DEWEY" wrote:

This assumes that the account # is in both tables --
SELECT TableA.[account #], Sum(TableA.Score) AS SumOfScore1,
Sum(TableB.Score) AS SumOfScore
FROM TableA INNER JOIN TableB ON TableA.[account #] = TableB.[account #]
GROUP BY TableA.[account #];

This assumes that the account # may only be in TableA --
SELECT TableA.[account #], Sum(TableA.Score) AS SumOfScore1,
Sum(TableB.Score) AS SumOfScore
FROM TableA LEFT JOIN TableB ON TableA.[account #] = TableB.[account #]
GROUP BY TableA.[account #];

--
KARL DEWEY
Build a little - Test a little


"KWhamill" wrote:

I'm not sure this is going to make any sence
I need to take the Sum of these amounts in table A and B grouped by Account
# separetly and then compare them , grouped by account #, with some data from
Table C though whilst still keeping the sum of A and B separate. The problem
is that when i put the data together i get the sum from tables A and B
multiplied by the number of records in tables a and b. So if the sum of
account #12121 on table A is 10 and there are three records on table A for
that acccount I get 30. BUT, THis only happens when i put A and B together,
as long as I work with only table A and C or B and C the calculations work
fine.
Any ideas out there?
R,
K

What purpose does your table C serve in this query?

Did you try what I posted?

--
KARL DEWEY
Build a little - Test a little


"KWhamill" wrote:

Karl Thank you for coming to the rescue,
what i have is this:
SELECT TableC[Account#], Sum([TableA].[Amount]) AS [SumOfAmount],
Sum([TableB].[Amount]) AS [SumOfAmount1]
FROM (TableC LEFT JOIN [TableA] ON TableC[account#] = [TableA].[Account])
LEFT JOIN [TAbleB] ON TAbleC .[Account#] = [TableB].[Account]
GROUP BY TableC.[Account#];
I get the Sum of the amounts I'm looking for but they are multiplied by the
number of records on the tables A and B. If i only use table A or B It works
correctly if I use both I get the multiplied numbers.
ANY ideas?
K,


"KARL DEWEY" wrote:

This assumes that the account # is in both tables --
SELECT TableA.[account #], Sum(TableA.Score) AS SumOfScore1,
Sum(TableB.Score) AS SumOfScore
FROM TableA INNER JOIN TableB ON TableA.[account #] = TableB.[account #]
GROUP BY TableA.[account #];

This assumes that the account # may only be in TableA --
SELECT TableA.[account #], Sum(TableA.Score) AS SumOfScore1,
Sum(TableB.Score) AS SumOfScore
FROM TableA LEFT JOIN TableB ON TableA.[account #] = TableB.[account #]
GROUP BY TableA.[account #];

--
KARL DEWEY
Build a little - Test a little


"KWhamill" wrote:

I'm not sure this is going to make any sence
I need to take the Sum of these amounts in table A and B grouped by Account
# separetly and then compare them , grouped by account #, with some data from
Table C though whilst still keeping the sum of A and B separate. The problem
is that when i put the data together i get the sum from tables A and B
multiplied by the number of records in tables a and b. So if the sum of
account #12121 on table A is 10 and there are three records on table A for
that acccount I get 30. BUT, THis only happens when i put A and B together,
as long as I work with only table A and C or B and C the calculations work
fine.
Any ideas out there?
R,
K

Karl,
Thank you, the answer to your second question is yes. and it proves that The
problem is not in the relationship between Tables A and B but in the way all
three tables relate together. Which brings me back around to your First
question. TableC is what I''m trying to match. I get that spreadsheet from
some one else and I have to prove that i match what's on there. Which sounds
wierd because i have the lists of transactions so whatever i come up with
should be right. But this websource is what we are given to reconcile to. So
Table C is the Basis for the report I produce I add the Totals by account
number from my transactions and save that as a spreadsheet. I also run a
query in the same Macro that tells me for which account numbers i have
discrepencies easier than scrolling through a spreadsheet. I hope that
explains it a little more clearly.
R,
K
I wanted to be a Lion Tamer but my parents wouldn't go for it
"KARL DEWEY" wrote:

What purpose does your table C serve in this query?

Did you try what I posted?

--
KARL DEWEY
Build a little - Test a little


"KWhamill" wrote:

Karl Thank you for coming to the rescue,
what i have is this:
SELECT TableC[Account#], Sum([TableA].[Amount]) AS [SumOfAmount],
Sum([TableB].[Amount]) AS [SumOfAmount1]
FROM (TableC LEFT JOIN [TableA] ON TableC[account#] = [TableA].[Account])
LEFT JOIN [TAbleB] ON TAbleC .[Account#] = [TableB].[Account]
GROUP BY TableC.[Account#];
I get the Sum of the amounts I'm looking for but they are multiplied by the
number of records on the tables A and B. If i only use table A or B It works
correctly if I use both I get the multiplied numbers.
ANY ideas?
K,


"KARL DEWEY" wrote:

This assumes that the account # is in both tables --
SELECT TableA.[account #], Sum(TableA.Score) AS SumOfScore1,
Sum(TableB.Score) AS SumOfScore
FROM TableA INNER JOIN TableB ON TableA.[account #] = TableB.[account #]
GROUP BY TableA.[account #];

This assumes that the account # may only be in TableA --
SELECT TableA.[account #], Sum(TableA.Score) AS SumOfScore1,
Sum(TableB.Score) AS SumOfScore
FROM TableA LEFT JOIN TableB ON TableA.[account #] = TableB.[account #]
GROUP BY TableA.[account #];

--
KARL DEWEY
Build a little - Test a little


"KWhamill" wrote:

I'm not sure this is going to make any sence
I need to take the Sum of these amounts in table A and B grouped by Account
# separetly and then compare them , grouped by account #, with some data from
Table C though whilst still keeping the sum of A and B separate. The problem
is that when i put the data together i get the sum from tables A and B
multiplied by the number of records in tables a and b. So if the sum of
account #12121 on table A is 10 and there are three records on table A for
that acccount I get 30. BUT, THis only happens when i put A and B together,
as long as I work with only table A and C or B and C the calculations work
fine.
Any ideas out there?
R,
K

I think your problem is that you have multiple instances of the account
numbers in TableC. So use a total query to rollup the account#'s.
QRY TableC_RollUp ---
SELECT TableC.[Account#]
FROM TableC
GROUP BY TableC.[Account#];

SELECT TableC_RollUp.[Account#], Sum(TableA.Amount) AS SumOfTableA,
Sum(TAbleB.Amount) AS SumOfTableB
FROM (TableC_RollUp LEFT JOIN TableA ON TableC_RollUp.[account#] =
TableA.Account) LEFT JOIN TAbleB ON TableC_RollUp.[Account#] = TAbleB.Account
GROUP BY TableC_RollUp.[Account#];
--
KARL DEWEY
Build a little - Test a little


"KWhamill" wrote:

Karl,
Thank you, the answer to your second question is yes. and it proves that The
problem is not in the relationship between Tables A and B but in the way all
three tables relate together. Which brings me back around to your First
question. TableC is what I''m trying to match. I get that spreadsheet from
some one else and I have to prove that i match what's on there. Which sounds
wierd because i have the lists of transactions so whatever i come up with
should be right. But this websource is what we are given to reconcile to. So
Table C is the Basis for the report I produce I add the Totals by account
number from my transactions and save that as a spreadsheet. I also run a
query in the same Macro that tells me for which account numbers i have
discrepencies easier than scrolling through a spreadsheet. I hope that
explains it a little more clearly.
R,
K
I wanted to be a Lion Tamer but my parents wouldn't go for it
"KARL DEWEY" wrote:

What purpose does your table C serve in this query?

Did you try what I posted?

--
KARL DEWEY
Build a little - Test a little


"KWhamill" wrote:

Karl Thank you for coming to the rescue,
what i have is this:
SELECT TableC[Account#], Sum([TableA].[Amount]) AS [SumOfAmount],
Sum([TableB].[Amount]) AS [SumOfAmount1]
FROM (TableC LEFT JOIN [TableA] ON TableC[account#] = [TableA].[Account])
LEFT JOIN [TAbleB] ON TAbleC .[Account#] = [TableB].[Account]
GROUP BY TableC.[Account#];
I get the Sum of the amounts I'm looking for but they are multiplied by the
number of records on the tables A and B. If i only use table A or B It works
correctly if I use both I get the multiplied numbers.
ANY ideas?
K,


"KARL DEWEY" wrote:

This assumes that the account # is in both tables --
SELECT TableA.[account #], Sum(TableA.Score) AS SumOfScore1,
Sum(TableB.Score) AS SumOfScore
FROM TableA INNER JOIN TableB ON TableA.[account #] = TableB.[account #]
GROUP BY TableA.[account #];

This assumes that the account # may only be in TableA --
SELECT TableA.[account #], Sum(TableA.Score) AS SumOfScore1,
Sum(TableB.Score) AS SumOfScore
FROM TableA LEFT JOIN TableB ON TableA.[account #] = TableB.[account #]
GROUP BY TableA.[account #];

--
KARL DEWEY
Build a little - Test a little


"KWhamill" wrote:

I'm not sure this is going to make any sence
I need to take the Sum of these amounts in table A and B grouped by Account
# separetly and then compare them , grouped by account #, with some data from
Table C though whilst still keeping the sum of A and B separate. The problem
is that when i put the data together i get the sum from tables A and B
multiplied by the number of records in tables a and b. So if the sum of
account #12121 on table A is 10 and there are three records on table A for
that acccount I get 30. BUT, THis only happens when i put A and B together,
as long as I work with only table A and C or B and C the calculations work
fine.
Any ideas out there?
R,
K