If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Relationship Quickbooks Tables
I have imported my data from quickbooks, and want to continue importing new
information, so I have left the tables as quickbooks set them up. the problem I have is trying to establish relationships, which work. There is a whole schema of relationships at qodbc.com, but I do not want to use all of them. I'm simply at this point trying to get the "class" or department to match up with the "customers" but I have to include sales receipts in the process. The relationship I have is [Customer]ListID to [SalesReceipt]customerListID and to [invoice]customerrefid, [class]listid to invoice[classreflistid] and to [salesreceipt] classrefid, but when I run a report or query it brings me up two pages of results, even though I have over 31,000 records. Any help would be appreciated, I am sorry if this does not make much sense, but I am new to this. |
#2
|
|||
|
|||
Relationship Quickbooks Tables
Pam
As I recall, some of the quickbooks data is not particularly well-normalized. Just because it came to you from QuickBooks (or Excel, or a csv file) doesn't mean you have to store it in Access that way. If you want to get good (and easy) use of Access' relationally-oriented features/functions, you need to feed it well-normalized data. Consider creating well-normalized tables in Access, then using queries to "parse" your raw data over into a more Access-friendly structure. Regards Jeff Boyce Microsoft Office/Access MVP "Pam" wrote in message ... I have imported my data from quickbooks, and want to continue importing new information, so I have left the tables as quickbooks set them up. the problem I have is trying to establish relationships, which work. There is a whole schema of relationships at qodbc.com, but I do not want to use all of them. I'm simply at this point trying to get the "class" or department to match up with the "customers" but I have to include sales receipts in the process. The relationship I have is [Customer]ListID to [SalesReceipt]customerListID and to [invoice]customerrefid, [class]listid to invoice[classreflistid] and to [salesreceipt] classrefid, but when I run a report or query it brings me up two pages of results, even though I have over 31,000 records. Any help would be appreciated, I am sorry if this does not make much sense, but I am new to this. |
#3
|
|||
|
|||
Relationship Quickbooks Tables
I don't mean to sound stupid, but what do you mean, or how would I use
queries to parse the data? I'm not totally new to Access, but everything else I've built from scratch, and this has been really difficult. "Jeff Boyce" wrote: Pam As I recall, some of the quickbooks data is not particularly well-normalized. Just because it came to you from QuickBooks (or Excel, or a csv file) doesn't mean you have to store it in Access that way. If you want to get good (and easy) use of Access' relationally-oriented features/functions, you need to feed it well-normalized data. Consider creating well-normalized tables in Access, then using queries to "parse" your raw data over into a more Access-friendly structure. Regards Jeff Boyce Microsoft Office/Access MVP "Pam" wrote in message ... I have imported my data from quickbooks, and want to continue importing new information, so I have left the tables as quickbooks set them up. the problem I have is trying to establish relationships, which work. There is a whole schema of relationships at qodbc.com, but I do not want to use all of them. I'm simply at this point trying to get the "class" or department to match up with the "customers" but I have to include sales receipts in the process. The relationship I have is [Customer]ListID to [SalesReceipt]customerListID and to [invoice]customerrefid, [class]listid to invoice[classreflistid] and to [salesreceipt] classrefid, but when I run a report or query it brings me up two pages of results, even though I have over 31,000 records. Any help would be appreciated, I am sorry if this does not make much sense, but I am new to this. |
#4
|
|||
|
|||
Relationship Quickbooks Tables
Pam
If you create "Append" queries (from raw data, to "final" table structure), that would "parse" the raw data into final version. Regards Jeff Boyce Microsoft Office/Access MVP "Pam" wrote in message ... I don't mean to sound stupid, but what do you mean, or how would I use queries to parse the data? I'm not totally new to Access, but everything else I've built from scratch, and this has been really difficult. "Jeff Boyce" wrote: Pam As I recall, some of the quickbooks data is not particularly well-normalized. Just because it came to you from QuickBooks (or Excel, or a csv file) doesn't mean you have to store it in Access that way. If you want to get good (and easy) use of Access' relationally-oriented features/functions, you need to feed it well-normalized data. Consider creating well-normalized tables in Access, then using queries to "parse" your raw data over into a more Access-friendly structure. Regards Jeff Boyce Microsoft Office/Access MVP "Pam" wrote in message ... I have imported my data from quickbooks, and want to continue importing new information, so I have left the tables as quickbooks set them up. the problem I have is trying to establish relationships, which work. There is a whole schema of relationships at qodbc.com, but I do not want to use all of them. I'm simply at this point trying to get the "class" or department to match up with the "customers" but I have to include sales receipts in the process. The relationship I have is [Customer]ListID to [SalesReceipt]customerListID and to [invoice]customerrefid, [class]listid to invoice[classreflistid] and to [salesreceipt] classrefid, but when I run a report or query it brings me up two pages of results, even though I have over 31,000 records. Any help would be appreciated, I am sorry if this does not make much sense, but I am new to this. |
Thread Tools | |
Display Modes | |
|
|