Cost Code Report from Pastel

[jacovz]

Hi, I'm trying to pull a cost code report from Pastel into Excel via a data base query. The table "History Headers" with "Ledger Transactions" used to work. However as soon as an invoice has various items with different cost codes, it does not label the document with a cost code at all. I tried "History Lines" combined with the "Ledger Transactions", but some how it duplicates the data. Any ideas...

 

[SkipVought]

What driver?

What SQL code?

Are you using MS Query or ADODB of something else?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jacovz]

Using System DSN

Pervasive ODBC Engine Interface

 

[jacovz]

MS Query

 

[SkipVought]

Please post your SQL code.

You state, "However as soon as an invoice has various items with different cost codes, it does not label the document with a cost code at all. I tried "History Lines" combined with the "Ledger Transactions", but some how it duplicates the data"

Please post an example of

1. the RESULT, the demonstrates the above stated condition.
2. the SOURCE DATA that you accessed and
3. the SQL CODE that produced the result.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jacovz]

Sorry do not have an SQL code or just don't know what you mean by that. I'm just a user trying to build a cost code report via a database query and then into a Pivot.
The source data that I access comes from Pastel. The table I use were the History Headers and the Ledger Transactions with the internal link "Ref" to the "Doc Number". I used this to generate the cost code report, as the History Header brings in the totals of documents and the Ledger Transactions the rest. If I check the profit per Pastel it agreed to the profit per the report generated from the query into a Pivot Table.
This worked until one invoice has more than one item on with more than one cost code. The total profit still agreed, however as soon as one invoice or documents have more than one cost code on, the total amount of that document cannot link to a cost code (as theer are more than one), thus the info does not reference to a cost code at all and just reference to N/A# - thereby not generating a accurate cost code report.
Sorry if I cant get you the technical info - but will appreciate it if you can help in any way.

 

[SkipVought]

Sorry. Your vague response, with a dredful dearth of useful information, is not going to get you very far.

Maybe you should get some assistance from your help desk.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]