Solved Query to count records (2 Viewers)

Emma35

Registered User.
Local time
Today, 08:48
Joined
Sep 18, 2012
Messages
455
Hi Everyone,
I've got a really old database (customers and orders) which only has one table, but it works so i'm going to leave it as it is. I've been asked to add a query which counts how many times each customer hasn't paid for an item on time. The table is roughly as follows

RecordID (PK)
Material (txt)
DeliveryDate (Date)
Customer (txt)
OrderPaid (Yes/No)

What i'd like to do is base a report on this query which displays a list of customers and how many times they haven't paid (like below)

Customer Material Not Paid
Microsoft PC's 4
Apple Phones 3
Samsung Tablets 5

Any help greatly appreciated guys
Em
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:48
Joined
Sep 21, 2011
Messages
14,044
Haven't paid, or haven't paid on time?
You would need a paid date to compare against some other date (delivery date + 10 perhaps?) if they are allowed 10 days to pay for items?
Haven't paid would just be OrderPaid = No or not Yes ?
 

Emma35

Registered User.
Local time
Today, 08:48
Joined
Sep 18, 2012
Messages
455
It's just haven't paid on time. There's no allowance of a particular amount of days after the delivery date....so it's "Paid by delivery date" (Yes or No)
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:48
Joined
Sep 21, 2011
Messages
14,044
It's just haven't paid on time. There's no allowance of a particular amount of days after the delivery date....so it's "Paid by delivery date" (Yes or No)
So create a Totals query (use the query design window) with criteria on Paid by <> Yes (unless you definitely know they would be No)
Group by customer and Count records.
 

Emma35

Registered User.
Local time
Today, 08:48
Joined
Sep 18, 2012
Messages
455
So create a Totals query (use the query design window) with criteria on Paid by <> Yes (unless you definitely know they would be No)
Group by customer and Count records.
I did try that and for some reason it won't group the totals together. My results look like below

Apple Phones 1
Apple Phones 1
Apple Phones 1
Samsung Tablets 1
Samsung Tablets 1
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:48
Joined
Sep 21, 2011
Messages
14,044
You are not grouping on the first field?
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:48
Joined
Sep 21, 2011
Messages
14,044
Just created this query on NG data he uploaded last night
Code:
SELECT tblWIP.FLDR_OWNER, Count(tblWIP.FOLDER_NUMBER) AS CountOfFOLDER_NUMBER
FROM tblWIP
GROUP BY tblWIP.FLDR_OWNER;

Add the Customer field first, then click the totals, and it will change to group by, then add the rest of the fields.
Unless you particularly want to know how many IPads the just count on item, else group on item as well.

You do not even have a customer name there, which you would need to group on?

1631612608052.png
 

Emma35

Registered User.
Local time
Today, 08:48
Joined
Sep 18, 2012
Messages
455
Ok gave it a try and it's grouping by the customer but not totalling the amount of times they didn't pay. I'm also getting a data type mismatch error when i run the query now.....i'm counting on the OrderPaid field. I should also mention that there are a couple of criteria also in the query. OrderPaid is set to "No" and DeliveryDate is set to ">1/1/21"
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:48
Joined
Sep 21, 2011
Messages
14,044
Ok gave it a try and it's grouping by the customer but not totalling the amount of times they didn't pay. I'm also getting a data type mismatch error when i run the query now.....i'm counting on the OrderPaid field. I should also mention that there are a couple of criteria also in the query. OrderPaid is set to "No" and DeliveryDate is set to ">1/1/21"
Now would be the time to show the SQL generated. You should be using Count on any of the fields in the record as the criteria is OrderPaid is No (False)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:48
Joined
Feb 19, 2002
Messages
42,970
Ok gave it a try and it's grouping by the customer but not totalling the amount of times they didn't pay. I'm also getting a data type mismatch error when i run the query now.....i'm counting on the OrderPaid field. I should also mention that there are a couple of criteria also in the query. OrderPaid is set to "No" and DeliveryDate is set to ">1/1/21"
I misplaced my Karnac the Magnificent hat. We cannot see your problem. You need to post the query string and preferably the database.

If there is no discipline in this database and it sounds like there isn't, then using <> Yes may not pull up all the unpaid records. If "No" is not ALWAYS "No" but sometimes null or "" then you need to use one of the following:

Where NOT(OrderPaid = "yes")
...
Where OrderPaid & "" <> "Yes"

and if OrderPaid is actually a y/n data type then you need to use True (no quotes) instead of "Yes"

My personal choice would be
Where NOT (OrderPaid = True) --- assuming an actual y/n data type

just FYI, the logic of this escapes me. Rather than using a Y/N field which gives little information, PaidDate would have been a far better choice. That way you can not only tell what is not paid but you can also identify the customers who pay late rather than on time.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:48
Joined
May 7, 2009
Messages
19,169
Code:
SELECT Table1.Customer, Table1.Material, Count(Table1.RecordID) AS [Not Paid]
FROM Table1
WHERE (((Table1.OrderPaid)=False) AND ((Table1.DeliveryDate)>=#1/1/2021#))
GROUP BY Table1.Customer, Table1.Material;
 

Emma35

Registered User.
Local time
Today, 08:48
Joined
Sep 18, 2012
Messages
455
Thanks for the suggestions guys....we're having a little trouble with our shared drives at the moment so i can't open the database to make any changes.
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:48
Joined
Sep 21, 2011
Messages
14,044
Thanks for the suggestions guys....we're having a little trouble with our shared drives at the moment so i can't open the database to make any changes.
FE should really be on your local drive, or in a folder on a network drive, personal to you only at the worst?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:48
Joined
Feb 19, 2002
Messages
42,970
we're having a little trouble with our shared drives at the moment so i can't open the database to make any changes.
I never develop on the server. I always keep my development local. It avoids corruption from network blips and extreme slowness during C&R.
 

Emma35

Registered User.
Local time
Today, 08:48
Joined
Sep 18, 2012
Messages
455
I misplaced my Karnac the Magnificent hat. We cannot see your problem. You need to post the query string and preferably the database.

If there is no discipline in this database and it sounds like there isn't, then using <> Yes may not pull up all the unpaid records. If "No" is not ALWAYS "No" but sometimes null or "" then you need to use one of the following:

Where NOT(OrderPaid = "yes")
...
Where OrderPaid & "" <> "Yes"

and if OrderPaid is actually a y/n data type then you need to use True (no quotes) instead of "Yes"

My personal choice would be
Where NOT (OrderPaid = True) --- assuming an actual y/n data type

just FYI, the logic of this escapes me. Rather than using a Y/N field which gives little information, PaidDate would have been a far better choice. That way you can not only tell what is not paid but you can also identify the customers who pay late rather than on time.
Sorry Pat i know you guys don't have a lot to go on and yes, this database is old and badly designed but i'm not guilty on that count. I'll definitely keep your recommendations in mind thanks
 

Emma35

Registered User.
Local time
Today, 08:48
Joined
Sep 18, 2012
Messages
455
FE should really be on your local drive, or in a folder on a network drive, personal to you only at the worst?
Thanks for your help Gasman....i understand it's difficult when your doing this from a distance. Good advice for my next project
 

Emma35

Registered User.
Local time
Today, 08:48
Joined
Sep 18, 2012
Messages
455
Code:
SELECT Table1.Customer, Table1.Material, Count(Table1.RecordID) AS [Not Paid]
FROM Table1
WHERE (((Table1.OrderPaid)=False) AND ((Table1.DeliveryDate)>=#1/1/2021#))
GROUP BY Table1.Customer, Table1.Material;
Are you an alien ? That code works perfect with a little tweak. Thank you again for your help !
 

Users who are viewing this thread

Top Bottom