By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 2,753 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

How do I Find Customers Not Billed This Month

Seth Schrock
Expert 2.5K+
P: 2,941
I have a billing database that is used to bill customers every six months. All customers are billed at the same time. I would like to make a report that tells me if a customer didn't have a bill for a certain period. I have tblInvoices and tblCustomer. I need to run through every customer to see if it has a match in the tblInvoices for a specified billing period. How do I do this? I'm guessing that it is a query, but not like one that I've ever created. tblInvoices and tblCustomer are related using the CustomerID fields in both tables with InvoiceID being the PK for tblInvoices and CustomerID being the PK for tblCustomer.
Jan 6 '12 #1

✓ answered by Rabbit

I guess Access doesn't allow that type of join condition. No matter, a subquery can be used as a workaround.
Expand|Select|Wrap|Line Numbers
  1. SELECT company  
  2. FROM companyTable  
  3. LEFT JOIN (
  4.    SELECT companyID 
  5.    FROM billsTable
  6.    WHERE YEAR(billDate) = YEAR(DATE())   
  7.       AND MONTH(billDate) = MONTH(DATE()) 
  8. ) bt
  9. ON companyTable.companyID = billsTable.companyID   
  10.    AND WHERE bt.companyID IS NULL  

Share this Question
Share on Google+
22 Replies


Rabbit
Expert Mod 10K+
P: 12,366
The question in your title and the question in the body are different, I'm not sure which one you're actually asking about.

If you want to return all customers that had a bill for the current month, you can do
Expand|Select|Wrap|Line Numbers
  1. select *
  2. from sometable
  3. where year(billdate) = year(date())
  4.    and month(billdate) = month(date())
However, if you're asking for all customers who are due for this month, you can do
Expand|Select|Wrap|Line Numbers
  1. select customer
  2. from sometable
  3. group by customer
  4. having dateadd("mm", 6, max(billdate)) <= date()
Jan 6 '12 #2

Seth Schrock
Expert 2.5K+
P: 2,941
Now that you mention it, the title is confusing. I was meaning if they had a bill as in I wanted those that did not have a bill. Sorry.

Here is an example of what I'm wanting. If I have three companies ABC, DEF, and GHI and companies ABC and DEF had bills but GHI didn't (because they hadn't bought anything) I want company GHI to be returned in the query. Does that make more sense?
Jan 6 '12 #3

Rabbit
Expert Mod 10K+
P: 12,366
I'm even more confused. If they haven't bought anything, why would they have a bill? Or why would you want to send them a bill?
Jan 6 '12 #4

Seth Schrock
Expert 2.5K+
P: 2,941
I don't. I just want to know who they are. So I will have a stack of invoices and a list of those that didn't get one and between the two I will have every customer in my database listed.
Jan 6 '12 #5

Rabbit
Expert Mod 10K+
P: 12,366
I assume you have some sort of company table?
Expand|Select|Wrap|Line Numbers
  1. SELECT company
  2. FROM companyTable
  3. LEFT JOIN billsTable
  4. ON companyTable.companyID = billsTable.companyID
  5. WHERE billsTable.companyID IS NULL
  6.    AND YEAR(billDate) = YEAR(DATE()) 
  7.    AND MONTH(billDate) = MONTH(DATE())
This returns all companies that did not have a bill in the current year and month.
Jan 6 '12 #6

Seth Schrock
Expert 2.5K+
P: 2,941
Great! I'll give that a try when I go back to work on Monday. Sorry for all the confusion.
Jan 6 '12 #7

Seth Schrock
Expert 2.5K+
P: 2,941
Okay. I can get it to work if I leave off the date criteria. So this works:

Expand|Select|Wrap|Line Numbers
  1. SELECT CustomerName
  2. FROM tblCustomer 
  3. LEFT JOIN tblInvoices ON tblCustomer.CustomerID = tblInvoices.CustomerID
  4. WHERE tblInvoices.CustomerID IS NULL
If I add the following to the bottom of that:
Expand|Select|Wrap|Line Numbers
  1. AND Year(BeginDate) = Year(Date())
  2. AND Month(BeginDate) = Month(Date())
  3.  
I get nothing. It just so happens that right now I have only one record in tblInvoices. I also tried the following which is closer to what I want:

Expand|Select|Wrap|Line Numbers
  1. SELECT CustomerName
  2. FROM tblCustomer 
  3. LEFT JOIN tblInvoices ON tblCustomer.CustomerID = tblInvoices.CustomerID
  4. WHERE tblInvoices.CustomerID IS NULL
  5. AND EndDate = Forms!frmHome!cboDateSelect
  6.  
Still nothing. Here is the structure of the two tables:

Expand|Select|Wrap|Line Numbers
  1. tblCustomer
  2. CustomerID
  3. CustomerName
  4. Address
Expand|Select|Wrap|Line Numbers
  1. tblInvoices
  2. InvoiceID
  3. CustomerID
  4. BeginDate
  5. EndDate
  6.  
Using the BeginDate and EndDate I'm able to calculate the billing period (which is always six months). The combo box referenced in my last section of code (Forms!frmHome!cboDateSelect) uses a SELECT DISTINCT query on tblInvoices to pull the EndDate values that have been used. I have even tried to make it not equal instead of equal on the off chance that we were thinking of it wrong, but that didn't help.
Jan 9 '12 #8

Rabbit
Expert Mod 10K+
P: 12,366
Sorry, I made a mistake. Try putting those date criteria in the join's ON clause rather then the WHERE clause.
Jan 9 '12 #9

Seth Schrock
Expert 2.5K+
P: 2,941
I'm not sure how to do that.
Jan 9 '12 #10

Rabbit
Expert Mod 10K+
P: 12,366
Expand|Select|Wrap|Line Numbers
  1. SELECT company 
  2. FROM companyTable 
  3. LEFT JOIN billsTable 
  4. ON companyTable.companyID = billsTable.companyID  
  5.    AND YEAR(billDate) = YEAR(DATE())  
  6.    AND MONTH(billDate) = MONTH(DATE())
  7. WHERE billsTable.companyID IS NULL 
Jan 9 '12 #11

Seth Schrock
Expert 2.5K+
P: 2,941
I'm getting an error message that says "Join expression not supported" and then it highlights
Expand|Select|Wrap|Line Numbers
  1. YEAR(BeginDate) = YEAR(DATE())
Just for fun I deleted that line and then I got the same error message and it highlighted the MONTH portion of the code.
Jan 10 '12 #12

NeoPa
Expert Mod 15k+
P: 31,494
If you're creating the SQL from within VBA then your code to produce the string could be :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim datEOM As Date
  3.  
  4. datEOM = DateAdd("m", 1, Date)
  5. datEOM = DateAdd("d", -Days(datEOM), datEOM)
  6. strSQL = "SELECT [Company] " & _
  7.          "FROM   [CompanyTable] AS [tCT]" & _
  8.          "       LEFT JOIN" & _
  9.          "       [BillsTable] AS [tBT]" & _
  10.          "  ON   (tCt.CompanyID = tBT.CompanyID)" & _
  11.          " AND   (tCT.BillDate Between #1/%F#" & _
  12.          "                     And     #%T#) " & _
  13.          "WHERE  (tBT.CompanyID Is Null)"
  14. strSQL = Replace(strSQL, "%F", Format(Date, "m\/yyyy"))
  15. strSQL = Replace(strSQL, "%T", Format(datEOM, "d\/m\/yyyy"))
It looks more complicated than it needs to be, but handling dates has many gotchas involved and this handles them all (hopefully).
Jan 10 '12 #13

Seth Schrock
Expert 2.5K+
P: 2,941
I wasn't using VBA. I guess I could.

This might be a stupid question, but can you base a report off of a query built in VBA? If so, I'm not sure how.
Jan 10 '12 #14

NeoPa
Expert Mod 15k+
P: 31,494
Not exactly, but if you are opening the report from VBA you can leave the date filtering out of the base query and apply it as the WhereCondition parameter of the DoCmd.OpenReport() call.
Jan 10 '12 #15

Rabbit
Expert Mod 10K+
P: 12,366
What's the SQL look like that gave you the error?
Jan 10 '12 #16

Seth Schrock
Expert 2.5K+
P: 2,941
Expand|Select|Wrap|Line Numbers
  1. SELECT CustomerName
  2. FROM tblCustomer LEFT JOIN tblInvoices ON tblCustomer.CustomerID = tblInvoices.CustomerID
  3. AND EndDate = Forms!frmHome!cboDateSelect
  4. WHERE tblInvoices.CustomerID IS NULL
  5.  
I have to type it in everytime because it won't let me save it with the added criteria in the ON part.

@NeoPa, I'll give that a try.
Jan 10 '12 #17

Rabbit
Expert Mod 10K+
P: 12,366
I guess Access doesn't allow that type of join condition. No matter, a subquery can be used as a workaround.
Expand|Select|Wrap|Line Numbers
  1. SELECT company  
  2. FROM companyTable  
  3. LEFT JOIN (
  4.    SELECT companyID 
  5.    FROM billsTable
  6.    WHERE YEAR(billDate) = YEAR(DATE())   
  7.       AND MONTH(billDate) = MONTH(DATE()) 
  8. ) bt
  9. ON companyTable.companyID = billsTable.companyID   
  10.    AND WHERE bt.companyID IS NULL  
Jan 10 '12 #18

Seth Schrock
Expert 2.5K+
P: 2,941
I tried the subquery like you had it and I get a syntax error highlighting the table name in the subquery. So instead of having a subquery, I created a separate query to fix the date problem and then joined the main query to it instead of billsTable.
Jan 10 '12 #19

NeoPa
Expert Mod 15k+
P: 31,494
FYI. The combination would be a query of :
Expand|Select|Wrap|Line Numbers
  1. SELECT tC.CustomerName
  2.        tI.BillDate
  3. FROM   [tblCustomer] AS [tC]
  4.        LEFT JOIN
  5.        [tblInvoices] AS [tI]
  6.   ON   tC.CustomerID = tI.CustomerID
  7.  AND   tI.EndDate = Forms!frmHome!cboDateSelect
  8. WHERE  tI.CustomerID IS NULL
The calling code would incorporate :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim datEOM As Date
  3.  
  4. datEOM = DateAdd("m", 1, Date)
  5. datEOM = DateAdd("d", -Days(datEOM), datEOM)
  6. strSQL = "([BillDate] Between #1/%F# And #%T#)"
  7. strSQL = Replace(strSQL, "%F", Format(Date, "m\/yyyy"))
  8. strSQL = Replace(strSQL, "%T", Format(datEOM, "d\/m\/yyyy"))
I've assumed [BillDate] is a field in [tblInvoices] and it's a separate field from [EndDate], which I've also assumed is in [tblInvoices].

PS. I'm just off shortly, but I've just noticed why Rabbit moved away from this idea. It doesn't work well for your requirement, which is to find missing records. I jumped in late and interpreted what was already there without considering the whole situation clearly enough. If I get a chance to later, I'll have a proper think about it, but I expect you'll find Rabbit's proposed solution is required.
Jan 10 '12 #20

NeoPa
Expert Mod 15k+
P: 31,494
I've had a quick look back and see that the fields for [tblInvoices] was actually posted at one point. I don't see any explanation that actually ties in the two date fields to your question though. Is one or other of them the one you want to test? Is there some sort of overlap issue that you haven't mentioned? Important information for the first post I would suggest.
Jan 10 '12 #21

Rabbit
Expert Mod 10K+
P: 12,366
@Seth, that's fine, you can do it in its own separate query if you wish. The problem you had with the subquery was probably a conflicting alias.
Jan 10 '12 #22

Seth Schrock
Expert 2.5K+
P: 2,941
@NeoPa, Together, the two date fields make up the billing period, but I was planning on just testing EndDate. I'll try to remember to make sure my first post contains enough information. Thanks for the tip.

@Rabbit, I didn't have an alias which might be why it had the problem. In my translation to my fields/tables, I missed that detail. I'll test it when I get back to work. Thanks.
Jan 10 '12 #23

Post your reply

Sign in to post your reply or Sign up for a free account.