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 said:
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 said:
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,
:
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
:
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
