Relationship Quickbooks Tables

[Pam]

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.

 

[Jeff Boyce]

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]

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.

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

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.

 

[Jeff Boyce]

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

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.

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

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.