Comparing two sums

  • Thread starter Thread starter KWhamill
  • Start date Start date
K

KWhamill

I'm having trouble with my program. I have two pieces of data that i need to
compare the first comes straight from a websource in text format and doesn't
need to be modified. the second is also a text file but, needs to be Summed
or totaled by account number. So I want the following:
Account Number(source1) Account total(source1) account total(source2) Compare.

So the first is a type of statement if you will, and the second is a list of
transactions. So when I Sum the second source by Account number i can compare
the total to the First source and then idenitfy the outages. The problem is
that I can't get the syntax for the SUM part right and so the whole thing
doesn't work. Need help thanks.
 
I'm having trouble with my program. I have two pieces of data that i needto
compare the first comes straight from a websource in text format and doesn't
need to be modified. the second is also a text file but, needs to be Summed
or totaled by account number. So I want the following:
Account Number(source1) Account total(source1) account total(source2) Compare.

So the first is a type of statement if you will, and the second is a listof
transactions. So when I Sum the second source by Account number i can compare
the total to the First source and then idenitfy the outages. The problem is
that I can't get the syntax for the SUM part right and so the whole thing
doesn't work. Need help thanks.
Click to expand...

so can you create a linked file and link to the text file and then
query it?
Sounds like you would do a totals query on one of them and then
compare against the second.

SELECT CustomerID, Txt1.AccountNumber, Sum(Txt2.AmountDue)
FROM Txt1 INNER JOIN Txt2 ON CustomerID
ORDER BY CustomerID, txt1.AccountNumber
GROUP BY CustomerID, txt1.AccountNumber;

or something along those lines.
 
That's what I thought but, i always get this error message that says I
haven't included the account number as part of an aggregate function.
Any ideas why?
 
OK For reasons I don't fully understand it wants me to GROUP BY on the ledger
balance. WHY!?
 
That's what I thought but, i always get this error message that says I
haven't included the account number as part of an aggregate function.
Any ideas why?
Click to expand...

post your SQL Statement. Open the Query in design view, click on the
button on the top left to select SQL. Then paste it here. Any column
that is NOT included in the GROUP BY clause *must* be used in an
aggregate function, so here, since I'm doing a Sum on Txt2.AmountDue,
I don't need it in the Group by clause. CustomerID and AccountNumber
need to be there, though.
 
That's what I thought but, i always get this error message that says I
haven't included the account number as part of an aggregate function.
Any ideas why?
Click to expand...

No, that's why I asked you to post the SQL statement.If you are not
Grouping By the account number, then of course the SQL statement won't
compile/function. Those are the rules. So I will ask again. Please
post your SQL statement.

Open the query in design view.
Click the grid button (or whatever the hell it is!) on the top left
under the file menu. Select SQL from the dropdown.
Copy the entire contents and paste them into this window.

Otherwise, I can't help you because I have no idea how to reproduce
your problem. (Work with me here, will ya?!!) Once you share that,
I'm sure LOTS of people can help you.

(sorry for being a little rough... but it's hard to help without
complete information... think of it this way - give everyone enough
information to understand and then reproduce the problem. Then it's
a whole lot easier to solve.

Pieter
 
Okay, do everyone who might be able to help you a favor... Post the
SQL statement that the query grid generates. We can't see your
database, so we need you to give us enough info to help you, and right
now, we don't have it.

My guess is that you have the ledger balance field in your query and
you are not grouping by it or applying some kind of summary function
to it (Average, Sum, Min, Max...) But that's merely conjecture
without seeing your SQL statement...
 
I would like to thank you for your help, I am truely appreciative of your
time. But,
I must refer you to the time stamps on the posts by the time you started
answering I had left for the day.
As for the query yes, I understand how to do SQL but like most people I'm
self taught, so i assume There is some more refined technique than my own
that can figure out how to do these things. Before I Left for home I figured
out that the query was satisfied if i grouped by everything that was not an
expression. Simply added one item to that section at a time until it produced
the desired information and it does so accurately. but this hardly seems
right. I don't understand why I need to group by the ledger balance's. the
SQL is Below I've simplified it to make it easier to understand, although
admitedly i got kind of lazy. it's actually rather a beast.
thank you
K,

SELECT link_table1.[ACCOUNT#], link_table1.[ACCOUNT NAME],
link_table1[LEDGER BALANCE B], link_table1.[LEDGER BALANCE G], [LEDGER
BALANCE B]+[LEDGER BALANCE G] AS [Total LEDGER BALANCE],table2.[LEDGER
BALANCE],
IIf(IsNull(Sum([qry_table3]![Amount])),0,Sum([qry_table3]![Amount])) AS
[Total Balance], IIf(IsNull([Total LEDGER BALANCE]-([LEDGER BALANCE]+[Total
Balance])),0,([Total LEDGER BALANCE]-([LEDGER BALANCE]+[Total Balance])),AS
[DIFFERENCE]
FROM (link_table1 LEFT JOIN table2 ON link_table1.[ACCOUNT#] = table2.[Focus
Account]) LEFT JOIN [qry_table3] ON link_table1.[account#]=
[qry_table3].[Focus Account]
GROUP BY link_table1account#], link_table1.[account
name],link_table1.[ledger balance b], link_table1ledger balacne g],
table2.[LEDGER BALANCE]
HAVING (((link_table1.[account #]) Is Not Null And (link_table1.[ACCOUNT
#])<>"ACCOUNT #") AND ((link_table1.[Account Name]) Is Not Null));
 
Back
Top