|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| Quickbooks Transaction Dates |
| Author |
Message |
|
|
| Posted : 2006-07-12 04:24:14 |
Tom,
I am trying to create a query in Access based on the following tables:
Account, JournalEntryCreditLine, JournalEntryDebitLine, BillExpenseLine
The last 3 tables above each have a TxnDate field. How can I relate them so I can get all of my revenues and expenses for our company. I obviously cannot include all of the TxnDate fields from each table. I noticed there was a TimeTracking table. I tried relating this TimeTracking table to the 3 tables (JournalEntryCreditLine, JournalEntryDebitLine, BillExpenseLine) then relating the 3 tables to the Account table but I didn't get any data returned. Any suggestions?
Thanks for your help
|
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-07-12 08:36:27 |
Account is only a holder for the General Ledger Account name, it doesn't contains transactions and it can already be seen in the BillExpenseLine and Journal tables. You should run one query on BillExpenseLine for the expenses and another for the revenue that's been entered via journal entries from your external system. The JournalEntryCreditLine, JournalEntryDebitLine tables can be joined like this:
SELECT JournalEntryCreditLine.TxnDate, JournalEntryCreditLine.TxnID,
JournalEntryCreditLine.JournalCreditLineAccountRefFullName,
JournalEntryCreditLine.JournalCreditLineAmount,
JournalEntryDebitLine.JournalDebitLineAccountRefFullName,
JournalEntryDebitLine.JournalDebitLineAmount,
JournalEntryDebitLine.JournalDebitLineMemo
FROM JournalEntryCreditLine, JournalEntryDebitLine
WHERE JournalEntryCreditLine.TxnID=JournalEntryDebitLine.TxnID
and ((((JournalEntryDebitLine.TxnDate)>={d'2006-01-01'}))
or (((JournalEntryCreditLine.TxnDate)>={d'2006-01-01'})))
|
|
|
|
|