Count Based on Multiple Columns

[Robert]

I am new to Access so this could probably be simple. I have an excel import
that contains three columns. The first is a customer name (this name could
repeat several times throughout the column, the second is the date (the date
or may not be the same for the customer entry), and a money amount (which
could be debits or credits.) I want to know if I can generate the count of
customers with credits that appear only counting one per date. What I mean
is - if a customer had two credits on the same date - I only want to count
that one time. However, if a customer has two credits on one date and
another credit on a different date, that should count as two. Under no
circumstance do I want to count the debits that are occurring. To further
complicate it, I need to query this from two separate tables that contain the
same type of information and possibly the same customers, just two different
locations. Do I need to combine these into one table before I start? Thanks
for any help!

 

[Al Campagna]

Robert,
Pretty sure you'll have to combine the two tables. Two imports, and an append.
You should be able to get the reults you want through a Totals query with just 3
fields. (Name, Date, Amount)
First, you'll need to weed out the Debits (which should be negative numbers) using >0
on Amount.
Then the totals...
Name TransDate Amount
GroupBy GroupBy Sum

Note: using Name to group on is dangerous... if you had 2 different John Smiths those
totals would combine into 1 total. Better to use a unique identifier like CustomerID to
prevent that... if you have it.

 

[Robert]

Okay - I have tried this over and over again. It looks like it works at
first. The information is only in one table, so I did not have to add them
together or do anything where that part may have been an issue.

I had a customer ID that gave me unique identifiers and I went with that,
instead of the name. I also had to add a fourth column to determine which
of 6 different offices issued that credit. It gives me a count of the unique
accounts per day (which seem accurate so at least that part is good), but the
totals or sums of the credit amounts for those days are way out in left
field. Where I may have had 3 credits for one account on 08/11/06 for 35.00,
5.00 and 3.33 - it's showing the total sum of those credits to be 156.66.
Even when I look at all the credits for that account, regardless of the day,
it doesn't come close to that amount. As a matter of fact, the example
described above on 08/11/06 is a true example and were the only credits
issued to that account for that entire month. Heres what I did (each item
below is actually a column header):

Date Customer ID Amount City
Group BY Group By Sum Group By
like "8/*/2006" <0 like "*New
York*"

(my credits are in the negatives)

Is there a way to clear up that odd multiplying or adding of amounts that
seems to be happening?

Thanks again,

Robert

 

[Douglas J. Steele]

Assuming that Date is a Date field (as opposed to a Text field), you can't
use Like with it. You would have to add a computed field MonthYear:
Format([Date], "yyyymm"), set it to "WHERE" in the Total row, and put
"200608" as the criteria.

BTW, Date is a bad choice for a field name: that's a reserved word, and you
should never use reserved words for anything.

 

[Robert]

Thanks for the info on using Date. I actually did have a different column
title, and was just using it for my layout example, but I didn't know that.
It will help in the future. I didn't really understand what you meant by
adding computed field, sorry. However, I was able to get my information. I
had to run a query for just the dates I wanted first. Then I ran a query
against that query for my sums, and all of the information come out accurate.
Thanks to both of you for your help, though!

Assuming that Date is a Date field (as opposed to a Text field), you can't
use Like with it. You would have to add a computed field MonthYear:
Format([Date], "yyyymm"), set it to "WHERE" in the Total row, and put
"200608" as the criteria.

BTW, Date is a bad choice for a field name: that's a reserved word, and you
should never use reserved words for anything.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Okay - I have tried this over and over again. It looks like it works at
first. The information is only in one table, so I did not have to add them
together or do anything where that part may have been an issue.

I had a customer ID that gave me unique identifiers and I went with that,
instead of the name. I also had to add a fourth column to determine
which
of 6 different offices issued that credit. It gives me a count of the
unique
accounts per day (which seem accurate so at least that part is good), but
the
totals or sums of the credit amounts for those days are way out in left
field. Where I may have had 3 credits for one account on 08/11/06 for
35.00,
5.00 and 3.33 - it's showing the total sum of those credits to be 156.66.
Even when I look at all the credits for that account, regardless of the
day,
it doesn't come close to that amount. As a matter of fact, the example
described above on 08/11/06 is a true example and were the only credits
issued to that account for that entire month. Heres what I did (each item
below is actually a column header):

Date Customer ID Amount City
Group BY Group By Sum Group By
like "8/*/2006" <0 like "*New
York*"

(my credits are in the negatives)

Is there a way to clear up that odd multiplying or adding of amounts that
seems to be happening?

Thanks again,

Robert

 

[Douglas J. Steele]

I actually told you exactly how to add the computed field!

Assuming you're using the graphical query builder, you pick an empty column,
and type MonthYear: Format([Date], "yyyymm") in the field row of that
column. (use your real column name, rather than Date)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the info on using Date. I actually did have a different column
title, and was just using it for my layout example, but I didn't know
that.
It will help in the future. I didn't really understand what you meant by
adding computed field, sorry. However, I was able to get my information.
I
had to run a query for just the dates I wanted first. Then I ran a query
against that query for my sums, and all of the information come out
accurate.
Thanks to both of you for your help, though!

Assuming that Date is a Date field (as opposed to a Text field), you
can't
use Like with it. You would have to add a computed field MonthYear:
Format([Date], "yyyymm"), set it to "WHERE" in the Total row, and put
"200608" as the criteria.

BTW, Date is a bad choice for a field name: that's a reserved word, and
you
should never use reserved words for anything.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Okay - I have tried this over and over again. It looks like it works at
first. The information is only in one table, so I did not have to add
them
together or do anything where that part may have been an issue.

I had a customer ID that gave me unique identifiers and I went with
that,
instead of the name. I also had to add a fourth column to determine
which
of 6 different offices issued that credit. It gives me a count of the
unique
accounts per day (which seem accurate so at least that part is good),
but
the
totals or sums of the credit amounts for those days are way out in left
field. Where I may have had 3 credits for one account on 08/11/06 for
35.00,
5.00 and 3.33 - it's showing the total sum of those credits to be
156.66.
Even when I look at all the credits for that account, regardless of the
day,
it doesn't come close to that amount. As a matter of fact, the example
described above on 08/11/06 is a true example and were the only credits
issued to that account for that entire month. Heres what I did (each
item
below is actually a column header):

Date Customer ID Amount City
Group BY Group By Sum Group By
like "8/*/2006" <0 like
"*New
York*"

(my credits are in the negatives)

Is there a way to clear up that odd multiplying or adding of amounts
that
seems to be happening?

Thanks again,

Robert

:

Robert,
Pretty sure you'll have to combine the two tables. Two imports,
and
an append.
You should be able to get the reults you want through a Totals
query
with just 3
fields. (Name, Date, Amount)
First, you'll need to weed out the Debits (which should be negative
numbers) using >0
on Amount.
Then the totals...
Name TransDate Amount
GroupBy GroupBy Sum

Note: using Name to group on is dangerous... if you had 2 different
John Smiths those
totals would combine into 1 total. Better to use a unique identifier
like CustomerID to
prevent that... if you have it.
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I am new to Access so this could probably be simple. I have an excel
import
that contains three columns. The first is a customer name (this name
could
repeat several times throughout the column, the second is the date
(the
date
or may not be the same for the customer entry), and a money amount
(which
could be debits or credits.) I want to know if I can generate the
count of
customers with credits that appear only counting one per date. What
I
mean
is - if a customer had two credits on the same date - I only want to
count
that one time. However, if a customer has two credits on one date
and
another credit on a different date, that should count as two. Under
no
circumstance do I want to count the debits that are occurring. To
further
complicate it, I need to query this from two separate tables that
contain the
same type of information and possibly the same customers, just two
different
locations. Do I need to combine these into one table before I start?
Thanks
for any help!