| Missing Data |
| Author |
Message |
|
|
| Posted : 2006-04-18 09:37:31 |
I am extracting sales data for a data migration to another system. When balancing the migration I found that 2006 was intact but beginning in Nov. 2005, data begins to be missed.
By June 2005 to late 2003, most data in missing. Only 6 invoices from 2004 come over in the extract from Quickbooks. I am using the invoice and invoiceline tables in Quickbooks for the extraction. I do not think size is an issue and I have tried breaking up the data into sections but this was no help. When I run sales reports in Quickbooks I can see all the 2004 invoices with full detail. Do you know why the QODBC seems not be tramsmitting data? I have looked for another table to use but did not find one that provided full detail like invoice and invoiceline do. I am extracting from Quickbooks 2003 using QODBC version 6. |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-04-18 10:19:08 |
| Can you actually view the missing Invoices in QuickBooks? If you have Archived and Condensed the transactions for that period in QuickBooks, their detail will no longer be available to view in the Invoice and InvoiceLine tables. |
|
|
|
|
|
| Posted : 2006-04-19 04:50:26 |
Tom, thanks for the reply.
Yes, I can see the data in Quickbooks. In QB, I ran the sales by customer summary and drilled down throught sales by customer detail to the invoice image itself. I have lots of data in QB for 2004 but the extract only give me 4 invoices.
I contacted by client and they believe that their accountant may have used the archive option. I have asked them to call him to confirm and have not yet received confirmation. If the data has been archived, how do I extract it?
I find it interesting that the archive seems to have left most of 2003 and all prior years to 1997 intact.
thanks,
Mark Goodfellow
951-295-1469 (my cell phone if you have questions) |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-04-19 08:10:18 |
Try running the following query:
SELECT * from InvoiceLine unoptimized WHERE Txndate >= {d '2004-01-01'}
|
|
|
|
|
|
| Posted : 2006-04-20 09:30:16 |
Tom,
I am using MS Access to extract. I added the sql statement to a macro but it did not like the syntax of the date selection. Since I do not think size is an issue, I tried the sql without the date selection (i.e. SELECT * from InvoiceLine unoptimized).
This gave the same result as the extract without the macro.
My client called me this morning and said that the archive/condense option has not been used.
Thanks for your help,
mark |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-04-20 09:46:02 |
| Please use VB Demo to debug this issue and try re-running the following query:
SELECT * from InvoiceLine unoptimized WHERE Txndate >= {d '2004-01-01'}
See: How do I use the VB Demo for testing? for more.
|
|
|
|
|
|
| Posted : 2006-04-21 02:40:53 |
Tom, using VB Demo, I get what appears to be all of 2004.
How do I get this data into a CSV file?
thanks,
mark |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-04-21 07:56:57 |
Ok, if the data is there after all, let's resync your optimized InvoiceLine table by running:-
sp_optimizefullsync InvoiceLine
in VB Demo. Once that's done your Access extraction should be correct too! |
|
|
|
|
|
| Posted : 2006-04-22 02:17:13 |
Thanks, Tom.
That worked.
I am restoring my clients data to my laptop to do the extraction work.
Should I run this resync optimization step for every table that I plan
to extract?
thanks,
mark |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-04-22 11:23:26 |
| No, your historical transactions from 2004 will always be right now. QODBC will continue to automoatically update the .opt file now. |
|
|
|
