Sum Query not working properly

[Guest]

As I understand Access queries, when you ask it to give you a Sum of all
amounts under an index number (such as an item group number), you should get
a sum of all the records that fall under that index number. Well, what I'm
doing is taking the sums of every item group number (IGN) in one table and
comparing that to the sums of every IGN in another table. (I have to do some
minor formatting to get the IGN, but that's the easy part.) So I have
something like this:

Table A:

1234567-0 Group Total: $225

Table B:

1234567-0 Group Total: -$225

which is exactly what I want to get to, I want the sums that cancel
eachother out so I can clear them from my tables. (I'm doing an account
balancing database.) The problem I'm getting is that far too often Access is
not calculating the grouping correctly, so I'll have something like this:

Table A:

1234567-0-1: $200
1234567-0-2: $25

Table B:

1234567-0-1: -$200
1234567-0-2: -$25
1234567-0-3: -$35

The true total of that IGN for Table B should be -$260, not -$225... but for
some reason Access isn't calculating it that way, which is causing my
balancing to be off when I try to clear out the items for that IGN. I have a
clearing query that finds the sums for each IGN that cancel eachother out and
goes to the main data tables and updates their "Cleared" field to show that
they have been matched off, but when one of these bogus sums comes up, I end
up clearing off more records than actually should be cleared. It's putting me
about $10,000 over on my clearing. Can anybody give me some kind of an idea
why these queries would be doing this?

 

[Jeff Boyce]

Nicholas

You've described what you're doing, but have not posted the SQL statement of
your query. It's a bit tough to diagnose what isn't working in your query
without a look at your query.

I noticed that your two examples used two different numbering schemes. The
second example seemed to include sequence numbers (?transaction numbers)
that were missing from the first. Do you have both "versions" of ID numbers
in your tables? Could this difference be causing what you're seeing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I appreciate the prompt response, but between the time I posted the question
and the time you responded, I figured out what I was doing that was causing
the error. It seems that a field I had included in my query was causing
duplications in the lines because it was acting as a second index. (It was a
category field, and I didn't realize that some of the individual items in
each grouping were falling into different categories, which caused the query
to duplicate that IGN in the listing of group numbers.) Once I took this
field out, the query worked exactly as I expected. As far as the transaction
numbers go, the item numbering in this database is somewhat strange, the
first 9 digits (i.e., the 1234567-0) is the group number, or invoice number,
and everything after that is the line number/type identifier. To get the item
group number I was using Left([Invoice],9) to trim it off at 9 places. I know
if I went to the individual line level I would probably find more matches,
but the code I wrote to find those individual lines takes a good 4 hours to
run, as it has to sort through over 300,000 records in the main detail table.
(I still use that code, but because of the way the database works, I can't
include THIS table in that search, because I have to clear off whatever I
find in that search first before I do any other matching, otherwise I get
items clearing off against something that's already been cleared off.)

 

[Jeff Boyce]

Nicholas

I commented on the two different "IDs" because the second example seems to
embed more than one fact in the single field. Your response confirms my
hunch, since you have to extract a portion to get something you need.

I strong suggest you consider re-designing this aspect of your database to
use two fields to hold two facts. And if you have an index on each of
those fields, you might find the query to run considerably faster...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I appreciate the prompt response, but between the time I posted the
question
and the time you responded, I figured out what I was doing that was
causing
the error. It seems that a field I had included in my query was causing
duplications in the lines because it was acting as a second index. (It was
a
category field, and I didn't realize that some of the individual items in
each grouping were falling into different categories, which caused the
query
to duplicate that IGN in the listing of group numbers.) Once I took this
field out, the query worked exactly as I expected. As far as the
transaction
numbers go, the item numbering in this database is somewhat strange, the
first 9 digits (i.e., the 1234567-0) is the group number, or invoice
number,
and everything after that is the line number/type identifier. To get the
item
group number I was using Left([Invoice],9) to trim it off at 9 places. I
know
if I went to the individual line level I would probably find more matches,
but the code I wrote to find those individual lines takes a good 4 hours
to
run, as it has to sort through over 300,000 records in the main detail
table.
(I still use that code, but because of the way the database works, I can't
include THIS table in that search, because I have to clear off whatever I
find in that search first before I do any other matching, otherwise I get
items clearing off against something that's already been cleared off.)

Nicholas

You've described what you're doing, but have not posted the SQL statement
of
your query. It's a bit tough to diagnose what isn't working in your
query
without a look at your query.

I noticed that your two examples used two different numbering schemes.
The
second example seemed to include sequence numbers (?transaction numbers)
that were missing from the first. Do you have both "versions" of ID
numbers
in your tables? Could this difference be causing what you're seeing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Nicholas Scarpinato" <[email protected]>
wrote
in message news:[email protected]...

 

[Guest]

I actually do extract the first 9 into a new table and run my queries on that
new table, which I then use to match off the invoices in the master. I
suppose I could split it out in the master table, and once I finalize the
process of matching up the invoices I probably will, but I was somewhat
locked into doing things this way by the design of the database. It wasn't
originally designed to do what I'm doing now (it just happens to have the
same data that I needed for the report I was generating), and I wasn't given
enough time to make it work the way it needs to for this, my boss needed the
numbers immediately.

Nicholas

I commented on the two different "IDs" because the second example seems to
embed more than one fact in the single field. Your response confirms my
hunch, since you have to extract a portion to get something you need.

I strong suggest you consider re-designing this aspect of your database to
use two fields to hold two facts. And if you have an index on each of
those fields, you might find the query to run considerably faster...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I appreciate the prompt response, but between the time I posted the
question
and the time you responded, I figured out what I was doing that was
causing
the error. It seems that a field I had included in my query was causing
duplications in the lines because it was acting as a second index. (It was
a
category field, and I didn't realize that some of the individual items in
each grouping were falling into different categories, which caused the
query
to duplicate that IGN in the listing of group numbers.) Once I took this
field out, the query worked exactly as I expected. As far as the
transaction
numbers go, the item numbering in this database is somewhat strange, the
first 9 digits (i.e., the 1234567-0) is the group number, or invoice
number,
and everything after that is the line number/type identifier. To get the
item
group number I was using Left([Invoice],9) to trim it off at 9 places. I
know
if I went to the individual line level I would probably find more matches,
but the code I wrote to find those individual lines takes a good 4 hours
to
run, as it has to sort through over 300,000 records in the main detail
table.
(I still use that code, but because of the way the database works, I can't
include THIS table in that search, because I have to clear off whatever I
find in that search first before I do any other matching, otherwise I get
items clearing off against something that's already been cleared off.)

Nicholas

You've described what you're doing, but have not posted the SQL statement
of
your query. It's a bit tough to diagnose what isn't working in your
query
without a look at your query.

I noticed that your two examples used two different numbering schemes.
The
second example seemed to include sequence numbers (?transaction numbers)
that were missing from the first. Do you have both "versions" of ID
numbers
in your tables? Could this difference be causing what you're seeing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Nicholas Scarpinato" <[email protected]>
wrote
in message As I understand Access queries, when you ask it to give you a Sum of
all
amounts under an index number (such as an item group number), you
should
get
a sum of all the records that fall under that index number. Well, what
I'm
doing is taking the sums of every item group number (IGN) in one table
and
comparing that to the sums of every IGN in another table. (I have to do
some
minor formatting to get the IGN, but that's the easy part.) So I have
something like this:

Table A:

1234567-0 Group Total: $225

Table B:

1234567-0 Group Total: -$225

which is exactly what I want to get to, I want the sums that cancel
eachother out so I can clear them from my tables. (I'm doing an account
balancing database.) The problem I'm getting is that far too often
Access
is
not calculating the grouping correctly, so I'll have something like
this:

Table A:

1234567-0-1: $200
1234567-0-2: $25

Table B:

1234567-0-1: -$200
1234567-0-2: -$25
1234567-0-3: -$35

The true total of that IGN for Table B should be -$260, not -$225...
but
for
some reason Access isn't calculating it that way, which is causing my
balancing to be off when I try to clear out the items for that IGN. I
have
a
clearing query that finds the sums for each IGN that cancel eachother
out
and
goes to the main data tables and updates their "Cleared" field to show
that
they have been matched off, but when one of these bogus sums comes up,
I
end
up clearing off more records than actually should be cleared. It's
putting
me
about $10,000 over on my clearing. Can anybody give me some kind of an
idea
why these queries would be doing this?