Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-07-2014, 11:57 PM   #1
snehal0909
Newly Registered User
 
Join Date: Feb 2008
Posts: 74
Thanks: 4
Thanked 0 Times in 0 Posts
snehal0909 is on a distinguished road
Append query for recurring invoices

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.
Attached Images
File Type: jpg 2014-07-08_155247.jpg (71.2 KB, 582 views)

snehal0909 is offline   Reply With Quote
Old 07-08-2014, 12:47 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,463
Thanks: 40
Thanked 3,380 Times in 3,275 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Append query for recurring invoices

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.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-08-2014, 09:00 PM   #3
snehal0909
Newly Registered User
 
Join Date: Feb 2008
Posts: 74
Thanks: 4
Thanked 0 Times in 0 Posts
snehal0909 is on a distinguished road
Re: Append query for recurring invoices

when I run the query, it adds 30 invoices to the invoices table. where does this code go?

snehal0909 is offline   Reply With Quote
Old 07-08-2014, 11:28 PM   #4
snehal0909
Newly Registered User
 
Join Date: Feb 2008
Posts: 74
Thanks: 4
Thanked 0 Times in 0 Posts
snehal0909 is on a distinguished road
Re: Append query for recurring invoices

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.
snehal0909 is offline   Reply With Quote
Old 07-09-2014, 12:25 AM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,463
Thanks: 40
Thanked 3,380 Times in 3,275 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Append query for recurring invoices

Quote:
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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-09-2014, 12:44 AM   #6
snehal0909
Newly Registered User
 
Join Date: Feb 2008
Posts: 74
Thanks: 4
Thanked 0 Times in 0 Posts
snehal0909 is on a distinguished road
Re: Append query for recurring invoices

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 by snehal0909; 07-09-2014 at 12:47 AM. Reason: forgot to mention something imp.
snehal0909 is offline   Reply With Quote
Old 07-09-2014, 01:40 AM   #7
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,463
Thanks: 40
Thanked 3,380 Times in 3,275 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Append query for recurring invoices

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 ( InvoiceNo, Customer, BillingAddress1, BillingAddress2, BillingAddress3, InvoiceDate, Description, Terms, PONo, BillingEmail1, BillingEmail2, TotalAmount, GST )
SELECT (SELECT Max(InvoiceNo) FROM tblInvoices AS TMP)+
(SELECT Count(*) FROM tblAccounts AS TMP WHERE CompanyName<=tblAccounts.CompanyName AND AccountActive='Yes' AND EnableRecurringInvoice)='Yes'), 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.

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
snehal0909 (07-09-2014)
Old 07-09-2014, 02:53 AM   #8
snehal0909
Newly Registered User
 
Join Date: Feb 2008
Posts: 74
Thanks: 4
Thanked 0 Times in 0 Posts
snehal0909 is on a distinguished road
Re: Append query for recurring invoices

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
Attached Files
File Type: zip Database1 - Copy.zip (40.2 KB, 50 views)
snehal0909 is offline   Reply With Quote
Old 07-09-2014, 03:01 AM   #9
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,463
Thanks: 40
Thanked 3,380 Times in 3,275 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Append query for recurring invoices

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
snehal0909 (07-09-2014)
Old 07-09-2014, 03:20 AM   #10
snehal0909
Newly Registered User
 
Join Date: Feb 2008
Posts: 74
Thanks: 4
Thanked 0 Times in 0 Posts
snehal0909 is on a distinguished road
Re: Append query for recurring invoices

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.
snehal0909 is offline   Reply With Quote
Old 07-09-2014, 03:33 AM   #11
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,463
Thanks: 40
Thanked 3,380 Times in 3,275 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Append query for recurring invoices

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
snehal0909 (07-09-2014)
Old 07-09-2014, 03:49 AM   #12
snehal0909
Newly Registered User
 
Join Date: Feb 2008
Posts: 74
Thanks: 4
Thanked 0 Times in 0 Posts
snehal0909 is on a distinguished road
Re: Append query for recurring invoices

Happy guy I am.
Thank you!

Do you design databases for small businesses?
snehal0909 is offline   Reply With Quote
Old 07-09-2014, 04:32 AM   #13
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,463
Thanks: 40
Thanked 3,380 Times in 3,275 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Append query for recurring invoices

Quote:
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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 07-09-2014, 08:41 AM   #14
snehal0909
Newly Registered User
 
Join Date: Feb 2008
Posts: 74
Thanks: 4
Thanked 0 Times in 0 Posts
snehal0909 is on a distinguished road
Re: Append query for recurring invoices

Can you please give me your email address in a private message? I can email you some material to explain what I am after.

snehal0909 is offline   Reply With Quote
Reply

Tags
append , increment number , recurring invoices

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to ignore Microsoft Access can't append all the records in the append query DNASok Queries 7 03-21-2013 06:22 AM
How to Create a Query for Outstanding Invoices Sunnylei Queries 2 07-18-2011 09:17 AM
Append Query that can append to multiple tables JuniorWoodchuck24 Queries 3 05-25-2011 12:56 AM
Query: Retrieve Invoices between certain dates richard05 Queries 5 03-27-2009 12:48 AM
Recurring Invoices rnickels Tables 3 03-04-2008 11:16 AM




All times are GMT -8. The time now is 04:17 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World