Question

Query for all Journal Entry data for QuickBooks

Forum|Forum|5 years ago
February 1, 2021
1 reply
1 view

I am looking to get all Journal Entry data for QuickBooks. I am already pulling all Manual Journal Entries with the query below. I could do with some help from an expert on the system.

Our existing MJE data is created using the following example.

SELECT

*

FROM

[qb].[journalentryline] LT
LEFT OUTER JOIN [qb].[account] GL ON GL.[ListID] = LT.[JournalLineAccountRefListID]

WHERE

LT.[IsAdjustment] = 1
AND LT.TXNDATE >= @FROMDATE
AND LT.TXNDATE <= @TODATE

I did originally hope removing the LT.[IsAdjustment] = 1 from the where clause would work, however only manual journal entries are still showing. Can you advise on the correct table to find this info? Or what my query should be changed to?

QuickBooks Desktop

S

SarahannC

Level 9

Hello there, kmullins131.

Let me help you get all the journal entry data in QuickBooks. You can run the Journal report to view all the entries recorded in the program. Even if the entries are manually recorded or not, you can still view them. Here's how:

Go to the Company menu.
Select Make General Journal Entries.
From the Make General Journal Entries window, click Reports,
Click the Entries Entered drop-down arrow, then select a reporting period All.

Here's a couple of articles that you can check for more details about handling reports in QuickBooks Desktop:

I'm just a post away if you have additional questions about this or any concerns related to QuickBooks. Take care always and have a good one.

K

kmullins131Author

Thanks for the suggestion, but I am pulling data into a data factory using a ODBC driver. So I have to query it using SQL.

M

MirriamM

QuickBooks Team

Thanks for getting back, kmullins131.

You can create custom reports in ODBC compatible applications like Microsoft Access, Excel, pr Crystal reports. Let me show you how:

To connect with Microsoft Excel:

Tap the Data menu from Excel.
Choose From Other Sources.
Click From Microsoft Query.
Select Browse to locate the file datasource. It’s located in the same folder as your company file. This file has a 'DSN' extension and has the same name as your company file.
Once you select the file, you will be prompted to authenticate. Use the credentials of the ODBC user that you created to connect to the database.

After you connect, you can start selecting different report views and pull your QuickBooks data into Excel.

To connect with Microsoft Access:

Open Microsoft Access, then open a blank database.
Tap the External Data menu and click More.
Choose ODBC Database.
Select the Import the source data into a new table in the current database.
Click OK, then find the file data source that is needed to make the connection.
Select the folder icon in the Look in: section.
Go to the location of your company file and select the DSN file and enter the credentials of the ODBC user you created.

To connect with Crystal Reports:

Create a new report from the Crystal report.
Open the Database Expert.
Click Create New Connection and select ODBC (RDO).
Pick Find File DSN.
Select the button next to the File DSN field to locate the file datasource.
Go to the location of your company file and select the DSN file and enter the credentials of the ODBC user you created.

For more information about this process, see this article: Open Database Connectivity (ODBC) Driver FAQs for QuickBooks Desktop.

In case you have any other questions about reporting, feel free to visit this link: Custom Reporting Guide.

Feel free to message us again if you have other questions. We are always here to help. Have a good one.

Sign up

Login with SSO

Let's get you signed in

Login with SSO

Scanning file for viruses.

This file cannot be downloaded