Append query for recurring invoices (1 Viewer)

snehal0909

Registered User.
Local time
Today, 22:21
Joined
Feb 4, 2008
Messages
74
Hi,

I am looking to make an invoicing database.
I create 30 invoices a month. each client gets billed the same amount each month (for example Customer A gets billed $100 every month, Customer B gets charged $200 each month). Only two things get changed on the invoice-'Description' (for example the description would be 'services rendered for July 2014' for July invoice) and 'Invoice Date'.

Now please see the image attached, I have an append query that combines information and creates invoices for all 30 clients.

The problem is, the 'Invoice No' field in the invoice table stays empty, because I do not know how to start numbering at a specific point (for example invoice numbering should start at 14150001) and I want it to add the number (+1) automatically when this append query adds data to the invoice table.

I am not very experienced user of access, macro & vb, so please explain or show example if you can.
 

Attachments

  • 2014-07-08_155247.jpg
    2014-07-08_155247.jpg
    71.2 KB · Views: 627

CJ_London

Super Moderator
Staff member
Local time
Today, 14:21
Joined
Feb 19, 2013
Messages
16,553
For a start. I would change your invoiceNo field from short text to number, and given the size of the number it will need to be of type long

Then to determine the number you need two parts to the calculation. The first part determines the last number that has already been created and the second part calculates how much to add.

To determine the first part, your sql will be:

Code:
(SELECT Max(InvoiceNo) FROM InvTable AS TMP)
then to add the next part the sql will be

Code:
(SELECT Count(*) FROM ReInvoicesTable AS TMP WHERE RInvoiceID<=ReInvoicesTable.InvoicesID)
So to combine them it is just

Code:
(SELECT Max(InvoiceNo) FROM InvTable AS TMP)+
(SELECT Count(*) FROM ReInvoicesTable AS TMP WHERE RInvoiceID<=ReInvoicesTable.InvoicesID)
This assumes that none of your customers stop have an invoice, or if they do the record is deleted from the ReInvoicesTable.
 

snehal0909

Registered User.
Local time
Today, 22:21
Joined
Feb 4, 2008
Messages
74
when I run the query, it adds 30 invoices to the invoices table. where does this code go?
 

snehal0909

Registered User.
Local time
Today, 22:21
Joined
Feb 4, 2008
Messages
74
Trying a different solution.
I put this expression in the append query & selected to apend it to 'InvoiceNo' field.
Expr1: Nz(DMax("[InvoiceNo]","tblInvoices"),"Tables![tblInvoices]![InvoiceNo]")+1

This puts the same invoice number to all 30 invoices.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:21
Joined
Feb 19, 2013
Messages
16,553
when I run the query, it adds 30 invoices to the invoices table. where does this code go?
It is the calculation for your invoice number so in your query grid you would have something like

Code:
Expr1:(SELECT Max(InvoiceNo) FROM InvTable AS TMP)+
(SELECT Count(*) FROM ReInvoicesTable AS TMP WHERE RInvoiceID<=ReInvoicesTable.InvoicesID)
Why don't you post your append SQl in full so I can look at it
 

snehal0909

Registered User.
Local time
Today, 22:21
Joined
Feb 4, 2008
Messages
74
here is the append query

INSERT INTO tblInvoices ( Customer, BillingAddress1, BillingAddress2, BillingAddress3, InvoiceDate, Description, Terms, PONo, BillingEmail1, BillingEmail2, TotalAmount, GST )
SELECT tblAccounts.CompanyName, tblAccounts.BillingAddress1, tblAccounts.BillingAddress2, tblAccounts.BillingAddress3, tbltemp.InvoiceDate, tbltemp.Description, tblAccounts.Terms, tblAccounts.PONo, tblAccounts.BillingEmail1, tblAccounts.BillingEmail2, tblAccounts.CPM, tblAccounts.GST
FROM tblAccounts, tbltemp
WHERE (((tblAccounts.AccountActive)="Yes") AND ((tblAccounts.EnableRecurringInvoice)="Yes"));

I have renamed Recurring Invoices table to tblAccounts.
There is nothing that appends to 'Invoice No' field (this is the bit I am trying to figure out)
Thank you for your time CJ London. I appreciate it.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 14:21
Joined
Feb 19, 2013
Messages
16,553
OK, need a bit of an explanation to clarify your query


1. What is in tblTemp? Is it just a single row with the invoicedate and description? If it is more than one row it needs to be joined to tblAccounts on something like customerID

Assuming it is a single row then your query needs to look like this - changes in red and blue, the bit in red is what I previously advised corrected for table and field names, the bit in blue is because you have provided some more information about tblAccounts.

Code:
INSERT INTO tblInvoices ( [COLOR=red]InvoiceNo,[/COLOR] Customer, BillingAddress1, BillingAddress2, BillingAddress3, InvoiceDate, Description, Terms, PONo, BillingEmail1, BillingEmail2, TotalAmount, GST )
SELECT [COLOR=red](SELECT Max(InvoiceNo) FROM tblInvoices AS TMP)+
(SELECT Count(*) FROM tblAccounts AS TMP WHERE CompanyName<=tblAccounts.CompanyName [COLOR=blue]AND AccountActive='Yes' AND EnableRecurringInvoice)='Yes'[/COLOR]), [/COLOR]tblAccounts.CompanyName, tblAccounts.BillingAddress1, tblAccounts.BillingAddress2, tblAccounts.BillingAddress3, tbltemp.InvoiceDate, tbltemp.Description, tblAccounts.Terms, tblAccounts.PONo, tblAccounts.BillingEmail1, tblAccounts.BillingEmail2, tblAccounts.CPM, tblAccounts.GST
FROM tblAccounts, tbltemp
WHERE (((tblAccounts.AccountActive)="Yes") AND ((tblAccounts.EnableRecurringInvoice)="Yes"))

Some other points.
  • AccountActive and EnableRecurringInvoice appear to be text, if they are boolean (e.g. a check box) then you should be using =True (no quotes)
  • I would suggest that within TblAccounts you should have an account number and not be using companyname as a key - if the company name is changed or corrected you will lose any link with previous records - particularly if you are linking tblInvoices to tblAccounts.
 

snehal0909

Registered User.
Local time
Today, 22:21
Joined
Feb 4, 2008
Messages
74
tblTemp only has two fields. 'InvoiceDate' and 'Description'. I change these two fields each month. (I created tblTemp as I didn't know how to append some unbound text ('Invoice Date' & 'Description') from a form to a table) in the append query.

AccountActive and EnableRecurringInvoice are text fields (not yes/no)

tblAccounts has 'AccountId' as primary key. here is the database attached. I think that is worth thousand words. (DB made in Access 2013, sorry it doesn't allow me to save/downgrade to older versions)

It works! Thank you. But it skips numbers. Please try it and see
 

Attachments

  • Database1 - Copy.zip
    40.2 KB · Views: 105

CJ_London

Super Moderator
Staff member
Local time
Today, 14:21
Joined
Feb 19, 2013
Messages
16,553
Because you are not using the code I posted - review my post, you have not got the bit in blue.

Have just noticed there is an extra ) after EnableRecurringInvoice which should not be there which you need to remove
 

snehal0909

Registered User.
Local time
Today, 22:21
Joined
Feb 4, 2008
Messages
74
Used your code and it is working. there was an extra ')' (as you said above) so it wasn't letting me save the query. That is why I had to modify it. Any idea why it creates invoice numbers backwards? for example it takes customer 1, customer 2, customer 3 & gives them invoice numbers 2010, 2009, 2008 (instead of 2008 to Customer 1, 2009 to Customer 2 & so on).

I am happy with the solution, but just asking.
Thank you again for your help. Really appreciate it.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:21
Joined
Feb 19, 2013
Messages
16,553
Code:
Any idea why it creates invoice numbers backwards? for example it takes customer 1, customer 2, customer 3
Because it is counting where the customername<=... and alphabetically Test Company (customer 2) is less than Test Company 6 (customer 1)

As previously suggested, if you used AccountID instead of customername in this part of the query, then they would be in the 'correct' order
 

snehal0909

Registered User.
Local time
Today, 22:21
Joined
Feb 4, 2008
Messages
74
Happy guy I am.
Thank you!

Do you design databases for small businesses?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:21
Joined
Feb 19, 2013
Messages
16,553
Do you design databases for small businesses?
Yes - everything from one man bands to international companies, from simple office automation to 'enterprise scale' solutions. Most of my clients are in the UK but I have a few who are based abroad
 

snehal0909

Registered User.
Local time
Today, 22:21
Joined
Feb 4, 2008
Messages
74
Can you please give me your email address in a private message? I can email you some material to explain what I am after.
 

Users who are viewing this thread

Top Bottom