Query and count

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Bellow is my query. It gives me the result it is supposed to give me which is
each customer has 7 records because I have 7 months in the system. But when I
count months for each customer, it gives me 49 instead of 7. Why could this
happen?

SELECT [Silver Payment - Loblaws].BannerName, [Silver Payment -
Loblaws].[Customer Name], Count([Silver Payment - Loblaws].Month) AS
CountOfMonth
FROM [Silver Payment - Loblaws]
GROUP BY [Silver Payment - Loblaws].BannerName, [Silver Payment -
Loblaws].[Customer Name];
 
If you only asked for "Month", how many would be in the table? (I'm
guessing 49!)

By the way, "Month" is a reserved word in Access -- you will only confuse
yourself and Access if that is the name of your field.

Good luck

Jeff Boyce
<Access MVP>
 
Thank you for the reply. But I don't understand why the Query returns(prints
or displays) 7 records but tells there are 49 records.

How to fix this problem?

Jeff Boyce said:
If you only asked for "Month", how many would be in the table? (I'm
guessing 49!)

By the way, "Month" is a reserved word in Access -- you will only confuse
yourself and Access if that is the name of your field.

Good luck

Jeff Boyce
<Access MVP>

Minnow said:
Bellow is my query. It gives me the result it is supposed to give me which is
each customer has 7 records because I have 7 months in the system. But when I
count months for each customer, it gives me 49 instead of 7. Why could this
happen?

SELECT [Silver Payment - Loblaws].BannerName, [Silver Payment -
Loblaws].[Customer Name], Count([Silver Payment - Loblaws].Month) AS
CountOfMonth
FROM [Silver Payment - Loblaws]
GROUP BY [Silver Payment - Loblaws].BannerName, [Silver Payment -
Loblaws].[Customer Name];
Click to expand...
Click to expand...
 
Perhaps we don't share the same definition of "records".

If you have seven folks, each one with 7 "month" records, doesn't that make
49 records?

What happens if you only query for "month" info?

Jeff Boyce
<Access MVP>

Minnow said:
Thank you for the reply. But I don't understand why the Query returns(prints
or displays) 7 records but tells there are 49 records.

How to fix this problem?

Jeff Boyce said:
If you only asked for "Month", how many would be in the table? (I'm
guessing 49!)

By the way, "Month" is a reserved word in Access -- you will only confuse
yourself and Access if that is the name of your field.

Good luck

Jeff Boyce
<Access MVP>

Minnow said:
Bellow is my query. It gives me the result it is supposed to give me
Click to expand...
which
is
each customer has 7 records because I have 7 months in the system. But when I
count months for each customer, it gives me 49 instead of 7. Why could this
happen?

SELECT [Silver Payment - Loblaws].BannerName, [Silver Payment -
Loblaws].[Customer Name], Count([Silver Payment - Loblaws].Month) AS
CountOfMonth
FROM [Silver Payment - Loblaws]
GROUP BY [Silver Payment - Loblaws].BannerName, [Silver Payment -
Loblaws].[Customer Name];
Click to expand...
Click to expand...
Click to expand...
 
I give an example to make my question clear:
There is a query 1 based on a table called CustomersItems for customer whose
ID 1001, the query shows me that it has 2 items in Month 1, 2 itmes in Month
2, 2 items in Month 3.

Then I have another query 2 based on the query 1 and a table called
customers, this query 2 is supposed to give me Customer Name and total of the
items it has and it suppose to give 6. But actually it gives me 18. And if I
count the records in query 1, it will give me 9 instead of 3.

Thanks a lot.
 
The query/SQL statement is only half of what you are doing. Please also
post a brief description/outline of the tables/data. An example of real
data would also help.

Regards

Jeff Boyce
<Access MVP>

Minnow said:
I give an example to make my question clear:
There is a query 1 based on a table called CustomersItems for customer whose
ID 1001, the query shows me that it has 2 items in Month 1, 2 itmes in Month
2, 2 items in Month 3.

Then I have another query 2 based on the query 1 and a table called
customers, this query 2 is supposed to give me Customer Name and total of the
items it has and it suppose to give 6. But actually it gives me 18. And if I
count the records in query 1, it will give me 9 instead of 3.

Thanks a lot.

Minnow said:
Bellow is my query. It gives me the result it is supposed to give me which is
each customer has 7 records because I have 7 months in the system. But when I
count months for each customer, it gives me 49 instead of 7. Why could this
happen?

SELECT [Silver Payment - Loblaws].BannerName, [Silver Payment -
Loblaws].[Customer Name], Count([Silver Payment - Loblaws].Month) AS
CountOfMonth
FROM [Silver Payment - Loblaws]
GROUP BY [Silver Payment - Loblaws].BannerName, [Silver Payment -
Loblaws].[Customer Name];
Click to expand...
Click to expand...
 
Back
Top