By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,985 Members | 2,877 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

Eliminating records from a query that produces a Cartesian Product

P: n/a
Hello All:

I'm having a problem that's been baffling me for a few days and I seek
counsel here.

I have an Access 2000 DB from which I want to run several reports. These
reports are essentially the same (albeit for minor formatting differences).

To produce these reports, I am drawing from three tables:

o Stores '-- Address info for each grocery store in the country

o CallTags '-- A list of requests for merchandise return labels (Many
requests may come from one store)

o Track-No '-- The actual record for each return label (Many labels could
come from one CallTag record)

These tables are related by the following fields (These make up what we call
the "IDENT"):
o Cust-ID '-- Numeric Customer ID (i.e., Grocery chain)
o Dist-ID '-- Numeric Division ID (i.e., East Coast, Midwest, South, etc.)
o Store-ID '-- Numeric Store ID (Physical store location)
o Prog-ID '-- Char field representing a specific "Sale"

The issue is that I'm getting a cartesian product between the "CallTags" and
"Track-No" records when I produce the report. For example:

o On 12/28/03 I send three labels (Numbered A,B, and C) to Joe at
IDENT "205-000-348-W92"
o On 01/07/04 I send two labels (Numbered X and Y)to Sue at
IDENT "205-000-348-W92"

Thus there are two "CallTags" entries and five "Track-No" entries in the DB
resulting in 10 lines on the report (by way of the cartesian product)

And when I print the report, I get the following results for IDENT
"205-000-348-W92"

o Label A for Joe
o Label A for Sue
o Label B for Joe
o Label B for Sue
o Label C for Joe
o Label C for Sue
o Label X for Joe
o Label X for Sue
o Label Y for Joe
o Label Y for Sue

NOTE: THIS IS AS IT SHOULD BE!!! I know that Access will perform an inner join
automatically.

Here's the rub. When I call the Access report from a VB App, I'm passing in a
WHERE clause of the form "WHERE [Track-No].[Date] = #12/28/03#" That should
give me only three records - those attributed to Joe. Unfortunately, for me,
it doesn't.

I have tried different types of queries (including trying a MAKETABLE for a
temporary respite). I've tried filters, grouping, UNION queries, and many
combinations thereof but I still can't get this little bugger to work.

So my questions are:

o Do I need to index these tables in a certain way to remove the
cartesian product?
o Do I use some mystic combination of filters, queries, etc. to reach the
desired goal?
o Do I scrap the "CallTags" table and put the necessary info directly into
"Track-No" table? (of course this would be bad form since the DB would not
be strictly normalized.

I'm hoping that someone out there in the Ether can provide direction. If anyone
has questions or requires additional info, let me know.

Thanking in advance all who reply,

Eric
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
DFS
Eric,

It would help if you posted the SQL for the report.

When you say "THIS IS AS IT SHOULD BE!" I'm thinking it's not as it should
be. You sent ABC to Joe and XY to Sue, yet your report shows ABCXY going to
both?

You probably just need to specify an INNER JOIN between CallTags and
Track-No.


"Eric Slan" <Sl****@aol.com> wrote in message
news:4b**************************@posting.google.c om...
Hello All:

I'm having a problem that's been baffling me for a few days and I seek
counsel here.

I have an Access 2000 DB from which I want to run several reports. These
reports are essentially the same (albeit for minor formatting differences).
To produce these reports, I am drawing from three tables:

o Stores '-- Address info for each grocery store in the country

o CallTags '-- A list of requests for merchandise return labels (Many
requests may come from one store)

o Track-No '-- The actual record for each return label (Many labels could come from one CallTag record)

These tables are related by the following fields (These make up what we call the "IDENT"):
o Cust-ID '-- Numeric Customer ID (i.e., Grocery chain)
o Dist-ID '-- Numeric Division ID (i.e., East Coast, Midwest, South, etc.) o Store-ID '-- Numeric Store ID (Physical store location)
o Prog-ID '-- Char field representing a specific "Sale"

The issue is that I'm getting a cartesian product between the "CallTags" and "Track-No" records when I produce the report. For example:

o On 12/28/03 I send three labels (Numbered A,B, and C) to Joe at
IDENT "205-000-348-W92"
o On 01/07/04 I send two labels (Numbered X and Y)to Sue at
IDENT "205-000-348-W92"

Thus there are two "CallTags" entries and five "Track-No" entries in the DB resulting in 10 lines on the report (by way of the cartesian product)

And when I print the report, I get the following results for IDENT
"205-000-348-W92"

o Label A for Joe
o Label A for Sue
o Label B for Joe
o Label B for Sue
o Label C for Joe
o Label C for Sue
o Label X for Joe
o Label X for Sue
o Label Y for Joe
o Label Y for Sue

NOTE: THIS IS AS IT SHOULD BE!!! I know that Access will perform an inner join automatically.

Here's the rub. When I call the Access report from a VB App, I'm passing in a WHERE clause of the form "WHERE [Track-No].[Date] = #12/28/03#" That should give me only three records - those attributed to Joe. Unfortunately, for me, it doesn't.

I have tried different types of queries (including trying a MAKETABLE for a temporary respite). I've tried filters, grouping, UNION queries, and many
combinations thereof but I still can't get this little bugger to work.

So my questions are:

o Do I need to index these tables in a certain way to remove the
cartesian product?
o Do I use some mystic combination of filters, queries, etc. to reach the desired goal?
o Do I scrap the "CallTags" table and put the necessary info directly into "Track-No" table? (of course this would be bad form since the DB would not be strictly normalized.

I'm hoping that someone out there in the Ether can provide direction. If anyone has questions or requires additional info, let me know.

Thanking in advance all who reply,

Eric

Nov 12 '05 #2

P: n/a
Hi,

I assume the problem is about how you supply the WHERE criteria. You
are from VB6 and you open a report in Access 2000 through automation? The
whereCondition parameter, the fourth one of DoCmd.OpenReport, should not
have the word WHERE.

Public Sub SomeSub( ) ' in Access, in a standard module

DoCmd.OpenReport ReportName, WhereCondition:=
"IDENT='205-000-348-W92' AND [Date] = #12/28/03# "

End Sub
(Note that in the real case, the where value is probably send as argument of
the subroutine, but doing so that would have make the example more obscure).

The := syntax allows us to name the argument, without having to count the
number of required coma.

Hoping it may help,
Vanderghast, Access MVP

"Eric Slan" <Sl****@aol.com> wrote in message
news:4b**************************@posting.google.c om...
Hello All:

I'm having a problem that's been baffling me for a few days and I seek
counsel here.

I have an Access 2000 DB from which I want to run several reports. These
reports are essentially the same (albeit for minor formatting differences).
To produce these reports, I am drawing from three tables:

o Stores '-- Address info for each grocery store in the country

o CallTags '-- A list of requests for merchandise return labels (Many
requests may come from one store)

o Track-No '-- The actual record for each return label (Many labels could come from one CallTag record)

These tables are related by the following fields (These make up what we call the "IDENT"):
o Cust-ID '-- Numeric Customer ID (i.e., Grocery chain)
o Dist-ID '-- Numeric Division ID (i.e., East Coast, Midwest, South, etc.) o Store-ID '-- Numeric Store ID (Physical store location)
o Prog-ID '-- Char field representing a specific "Sale"

The issue is that I'm getting a cartesian product between the "CallTags" and "Track-No" records when I produce the report. For example:

o On 12/28/03 I send three labels (Numbered A,B, and C) to Joe at
IDENT "205-000-348-W92"
o On 01/07/04 I send two labels (Numbered X and Y)to Sue at
IDENT "205-000-348-W92"

Thus there are two "CallTags" entries and five "Track-No" entries in the DB resulting in 10 lines on the report (by way of the cartesian product)

And when I print the report, I get the following results for IDENT
"205-000-348-W92"

o Label A for Joe
o Label A for Sue
o Label B for Joe
o Label B for Sue
o Label C for Joe
o Label C for Sue
o Label X for Joe
o Label X for Sue
o Label Y for Joe
o Label Y for Sue

NOTE: THIS IS AS IT SHOULD BE!!! I know that Access will perform an inner join automatically.

Here's the rub. When I call the Access report from a VB App, I'm passing in a WHERE clause of the form "WHERE [Track-No].[Date] = #12/28/03#" That should give me only three records - those attributed to Joe. Unfortunately, for me, it doesn't.

I have tried different types of queries (including trying a MAKETABLE for a temporary respite). I've tried filters, grouping, UNION queries, and many
combinations thereof but I still can't get this little bugger to work.

So my questions are:

o Do I need to index these tables in a certain way to remove the
cartesian product?
o Do I use some mystic combination of filters, queries, etc. to reach the desired goal?
o Do I scrap the "CallTags" table and put the necessary info directly into "Track-No" table? (of course this would be bad form since the DB would not be strictly normalized.

I'm hoping that someone out there in the Ether can provide direction. If anyone has questions or requires additional info, let me know.

Thanking in advance all who reply,

Eric

Nov 12 '05 #3

P: n/a
DFS:

Thanks for your rapid reply.

The problem is that Access is doing the inner join automatically.
(That was why I wrote "this is as it should be.", because it should).
I would have expected the cartesian product to produce these results.

My issue is that I want to ELIMINATE the duplication, whether through
filtering ,querying, or grouping (or combinations thereof), I just
can't seem to do that. I'll post the SQL Text of the query soonest for
your prerusal.

Again, Thanks in advance.

Eric
Nov 12 '05 #4

P: n/a
"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message news:<um**************@TK2MSFTNGP09.phx.gbl>...
Hi,

I assume the problem is about how you supply the WHERE criteria. You
are from VB6 and you open a report in Access 2000 through automation? The
whereCondition parameter, the fourth one of DoCmd.OpenReport, should not
have the word WHERE.

Public Sub SomeSub( ) ' in Access, in a standard module

DoCmd.OpenReport ReportName, WhereCondition:=
"IDENT='205-000-348-W92' AND [Date] = #12/28/03# "

End Sub
(Note that in the real case, the where value is probably send as argument of
the subroutine, but doing so that would have make the example more obscure).

The := syntax allows us to name the argument, without having to count the
number of required coma.

Hoping it may help,
Vanderghast, Access MVP

Nov 12 '05 #5

P: n/a
Michel:

Thanks for the quick response.

I misrepresented that WHERE clause in my original post. I don't have
the WHERE keyword in the string being passed to the DB.

So on the OpenReport method I provide "[Track-No].[Tracking-Dt] =
#12/29/03#".

What I'm missing is the ELIMINATION of the inner join. That's where
you experts can help.

If you need further info or have more questions, let me know.

Again, thanks in advance.

E.
Nov 12 '05 #6

P: n/a
Hi,
What is the SQL text of the query that you use?
Hoping it may help,
Vanderghast, Access MVP
"Eric Slan" <Sl****@aol.com> wrote in message
news:4b**************************@posting.google.c om...
Michel:

Thanks for the quick response.

I misrepresented that WHERE clause in my original post. I don't have
the WHERE keyword in the string being passed to the DB.

So on the OpenReport method I provide "[Track-No].[Tracking-Dt] =
#12/29/03#".

What I'm missing is the ELIMINATION of the inner join. That's where
you experts can help.

If you need further info or have more questions, let me know.

Again, thanks in advance.

E.

Nov 12 '05 #7

P: n/a
DFS
Eric,

I think you're misunderstanding INNER JOIN (two fields matching on values).
An inner join query will usually reduce the # of records returned, compared
to a cartesian product.

Again, it would help if you posted the SQL for the query / report.

"Eric Slan" <Sl****@aol.com> wrote in message
news:4b**************************@posting.google.c om...
DFS:

Thanks for your rapid reply.

The problem is that Access is doing the inner join automatically.
(That was why I wrote "this is as it should be.", because it should).
I would have expected the cartesian product to produce these results.

My issue is that I want to ELIMINATE the duplication, whether through
filtering ,querying, or grouping (or combinations thereof), I just
can't seem to do that. I'll post the SQL Text of the query soonest for
your prerusal.

Again, Thanks in advance.

Eric

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.