Another question re: Reports


A

Abay

I print Invoice info, & save it to disc for daily transfer to Access97.
The only problem I have is that I get three records/lines for each invoice
as follows:

1. A line which holds the data I want which has the sales price and the
item information.

2. I also get two duplicate lines both with Sales prices of zero and the
rest of the info the same as in 1.

Is there any way that I can get just one record/line per Invoice.

Any help would as always be much appreciated.

Thanks in advance,
Abay
 
Ad

Advertisements

B

Bob Williams

I print Invoice info, & save it to disc for daily transfer to Access97.
The only problem I have is that I get three records/lines for each invoice
as follows:

1. A line which holds the data I want which has the sales price and the
item information.

2. I also get two duplicate lines both with Sales prices of zero and the
rest of the info the same as in 1.

Is there any way that I can get just one record/line per Invoice.

Any help would as always be much appreciated.

Thanks in advance,
Abay
My answer depends on what version of QuickBooks you are using and what
report you are referring to.

I assume you are exporting a report of today's invoices and you are
doing it by printing it to file. This is producing some sort of text
file that you then import into Access.

Why don't you just export it to Excel and do what you will with it
there? The latest versions allow you to do this, at least in
Premier/Pro range.

And I have to ask! Why on earth would you want to export invoice data
to Access? What can it do that you can't do either in QuickBooks
itself with reports or by using Excel?

Bob Williams
 
A

Abay

Thank you for your reply. I agree with you that my process seems backward,
but the following, I hope, helps to explain the reason:

I help out in a small Mom & Pop service operation for a few hours a day.
They have QuickBooks Pro for 2002 installed on one of their two computers.
Service appointments are taken over the phone and are then keyed into QB for
Invoicing.

The other people there are what might be called "Ludites", hard to believe
in this day and age, but they really have an aversion to using computers to
help them do their job. The only reason they have QB installed is that
their accountant needs it. They insist on writing out each appointment
manually first roughly and then in a note book. This is not for me, so I
created an access database to enter and track appointments with the ability
to search by Customer, invoice, date etc. so when an appointment is made for
a recurring customer (75% of the time) I can search by name and just press a
button to copy the info (address, tel no etc.) and not have to write/type it
all out again. The Ladies refuse to use this system, so at the end of each
day I extract all the Invoice info from QB (entered by others) creating a
text file which is then imported into Access using a template and macro,
which makes quick work of the process.

I also use the database for various reports which can't be created in QB. I
don't use Excel as I need a database & Access has superior data manipulation
& linkage capabilities.

I set up a "Memorized Report" to extract the invoice info that I want, but
as I said it lists three lines per invoice when all I want is one .. It is
my understanding that the Invoice template allows for only three line items
per invoice, & it seems the memorized report lists all three lines whether
they are used or not .. that is my problem ..

Any help would be much appreciated.

Abay

PS .. Sorry about the lengthy post, but you did ask:)
 
B

Bob Williams

Thank you for your reply. I agree with you that my process seems backward,
but the following, I hope, helps to explain the reason:

I help out in a small Mom & Pop service operation for a few hours a day.
They have QuickBooks Pro for 2002 installed on one of their two computers.
Service appointments are taken over the phone and are then keyed into QB for
Invoicing.

The other people there are what might be called "Ludites", hard to believe
in this day and age, but they really have an aversion to using computers to
help them do their job. The only reason they have QB installed is that
their accountant needs it. They insist on writing out each appointment
manually first roughly and then in a note book. This is not for me, so I
created an access database to enter and track appointments with the ability
to search by Customer, invoice, date etc. so when an appointment is made for
a recurring customer (75% of the time) I can search by name and just press a
button to copy the info (address, tel no etc.) and not have to write/type it
all out again. The Ladies refuse to use this system, so at the end of each
day I extract all the Invoice info from QB (entered by others) creating a
text file which is then imported into Access using a template and macro,
which makes quick work of the process.

I also use the database for various reports which can't be created in QB. I
don't use Excel as I need a database & Access has superior data manipulation
& linkage capabilities.

I set up a "Memorized Report" to extract the invoice info that I want, but
as I said it lists three lines per invoice when all I want is one .. It is
my understanding that the Invoice template allows for only three line items
per invoice, & it seems the memorized report lists all three lines whether
they are used or not .. that is my problem ..
I see your point.

I tried to make some reports that might work for you and I created two
that might fit.

First one lists all line items - it is a custom summary report
filtered by document type (invoice) and account (all income accounts).
It lists only the line items used - if one line item, one per invoice,
if more than one line, then one per line (QB treats lines as separate
"transactions" with an invoice collecting several together. This has
got rid of the extra lines you don't want.

A better report, but with a problem relating to sorting it first by
customer, is the Customer Detail Report which if filtered by document
type (invoice) produces one line per invoice. The account it addresses
is Accounts Receivable, not the relevant income account.

To eliminate the customer totalling, set Total By in the modify
reports window to Total and tick Name in the select column window.
Bingo! You have a list of invoices with one line.

Then export it as you have been doing - after setting any other
filters, modifications that you need.

To do this I used Enterprise v4.0 Australian edition, but the same
functionality with this report have been available in at least the
Pro/Premier versions of QB since 2002 (using the Australian
nomenclature- v9 on I think). I may be wrong about v9, but it is
certainly available in the current versions.
Any help would be much appreciated.

Abay

PS .. Sorry about the lengthy post, but you did ask:)
<snip>

See if it works.

Bob
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top