Import Invoices to Quickbooks from Access 1

[thankgodfortektips]

Hey all,

I have just setup a scheduling program which I wish to integrate with Quickbooks. Has anyone done this? I have a full listing of the clients etc in my scheduling program so all the data is already there, I just need to make a way to take the data from Access to Quickbooks.

I only need to import into the invoices area. Anybody got any information???

Thanks guys!

 

[JoeAtWork]

You will need to find out if Quickbooks has an import function for invoice data (most accounting programs do). If it does, it is most likely in the form of a CSV file. You would need to create a query in Access that matches that CSV format, and export it as CSV.

 

[thankgodfortektips]

Thanks JoeAtWork, I see an import option in quickbooks, and from what I have read up the excel option is the best. So I suppose what I should now ask is if anyone has a sample excel file that I can get the formatting off...

Thanks in advance!

 

[JoeAtWork]

Wouldn't you know it, I actually have an Access project from a few years back that creates Invoice import files for Quickbooks. I don't know if this format is still true for the current version of Quickbooks, but my function creates a text file with an .iif extension. It would seem to be a tab delimited text file, here is the contents of one of my .iif files.

!TRNS	TRNSTYPE	ACCNT	AMOUNT	DATE	DUEDATE	DOCNUM	NAME	TERMS	INVMEMO	EXTRA	TRNSID	ADDR1	ADDR2	ADDR3
!SPL	TRNSTYPE	ACCNT	AMOUNT	DATE	DUEDATE	DOCNUM	NAME	TERMS	INVMEMO	EXTRA	MEMO	PRICE	QNTY	INVITEM
!ENDTRNS
TRNS	INVOICE	Accounts Receivable	148.48	11/6/04	11/16/04	8750	Canworld Foods Ltd.	Net 10	we value your business		3800	Canworld Foods Ltd.	10 Shorncliffe Road, Unit 2	Toronto ON, M9B 3S3
SPL	INVOICE	Cold Storage	-138.77	11/6/04	11/16/04	8750		Net 10			Cold Storage	0.04	-3469.2	Cold Storage
SPL	INVOICE	GST Payable	-9.71	11/6/04	11/16/04	8750		Net 10	we value your business	GST
ENDTRNS

According to my old documentation, the TRANSTYPE, ACCNT, and AMOUNT columns are the three required fields (I guess everything else is optional).

Again, I should mention that was the format used in 2001 when I wrote the function, so I do not know if it is still supported.

 

[thankgodfortektips]

wow! thanks JoeAtWork! this is a great starting point, but when I read up on the net about the imports it said that the .iif import does not run any data integrity checks, so I was kinda hoping for an excel file.

but I will def use the .iif to start!

thanks again!

 

[JoeAtWork]

Probably the most important data integrity check you can do yourself is to make sure that the sum of all positive numbers balances with the sum of all negative numbers (i.e. adding them all up should equal zero).

 

[thankgodfortektips]

not quite sure if I understand what you mean... I dont have any figures to add up as a balance is only applied in the quickbooks...

 

[JoeAtWork]

If you look at the example .iif file above you will see that there are entries being made to 3 accounts. One positive amount of 148.48 is going Accounts Receivable, and two negative amounts of -138.77 and -9.71 to Cold Storage and GST Payable.

Notice that the sum of the negative amounts equals -148.48, so adding all the amounts together equals zero.

 

[thankgodfortektips]

OK, with you now, but this method will not work for me as I will only have A/R records in my access database to put into quickbooks. All A/P is entered straight into quickbooks.

 

[JoeAtWork]

In accounting there is always a positive and a negative entry, or in accounting terms debits and credits.

You can't just increase the AR account, you must decrease one or more accounts as well by the same amount.

 

[lespaul]

Joe is exactly right! I was an accountant for 10 years before moving to IT.

Here's what happens with A/R:

You make a sale:

Credit (negative entry) Sales Account (usually starts with a 4 in most accounting systems)
Debit (positive entry) Accounts Receivable Account (usually starts with a 1 in most accounting systems)

As your customers make their payment, you need to remove that balance from the A/R Account (they don't owe you any more)

Debit Cash when check is deposited (again another 1 account)
Credit A/R Account

this way once all your customer pay their bill, the balance of the A/R account is zero.

Double entry accounting at its finest!

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi