Eliminating records from a query that produces a Cartesian Product

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

re: Eliminating records from a query that produces a Cartesian Product

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" <Slanej@aol.com> wrote in message
news:4b0d19dc.0401071059.45771866@posting.google.c om...[color=blue]
> 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[/color]
differences).[color=blue]
>
> 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[/color]
could[color=blue]
> come from one CallTag record)
>
> These tables are related by the following fields (These make up what we[/color]
call[color=blue]
> the "IDENT"):
> o Cust-ID '-- Numeric Customer ID (i.e., Grocery chain)
> o Dist-ID '-- Numeric Division ID (i.e., East Coast, Midwest, South,[/color]
etc.)[color=blue]
> 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"[/color]
and[color=blue]
> "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[/color]
DB[color=blue]
> 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[/color]
join[color=blue]
> automatically.
>
> Here's the rub. When I call the Access report from a VB App, I'm passing[/color]
in a[color=blue]
> WHERE clause of the form "WHERE [Track-No].[Date] = #12/28/03#" That[/color]
should[color=blue]
> give me only three records - those attributed to Joe. Unfortunately, for[/color]
me,[color=blue]
> it doesn't.
>
> I have tried different types of queries (including trying a MAKETABLE for[/color]
a[color=blue]
> 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[/color]
the[color=blue]
> desired goal?
> o Do I scrap the "CallTags" table and put the necessary info directly[/color]
into[color=blue]
> "Track-No" table? (of course this would be bad form since the DB would[/color]
not[color=blue]
> be strictly normalized.
>
> I'm hoping that someone out there in the Ether can provide direction. If[/color]
anyone[color=blue]
> has questions or requires additional info, let me know.
>
> Thanking in advance all who reply,
>
> Eric[/color]

re: Eliminating records from a query that produces a Cartesian Product

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" <Slanej@aol.com> wrote in message
news:4b0d19dc.0401071059.45771866@posting.google.c om...[color=blue]
> 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[/color]
differences).[color=blue]
>
> 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[/color]
could[color=blue]
> come from one CallTag record)
>
> These tables are related by the following fields (These make up what we[/color]
call[color=blue]
> the "IDENT"):
> o Cust-ID '-- Numeric Customer ID (i.e., Grocery chain)
> o Dist-ID '-- Numeric Division ID (i.e., East Coast, Midwest, South,[/color]
etc.)[color=blue]
> 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"[/color]
and[color=blue]
> "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[/color]
DB[color=blue]
> 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[/color]
join[color=blue]
> automatically.
>
> Here's the rub. When I call the Access report from a VB App, I'm passing[/color]
in a[color=blue]
> WHERE clause of the form "WHERE [Track-No].[Date] = #12/28/03#" That[/color]
should[color=blue]
> give me only three records - those attributed to Joe. Unfortunately, for[/color]
me,[color=blue]
> it doesn't.
>
> I have tried different types of queries (including trying a MAKETABLE for[/color]
a[color=blue]
> 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[/color]
the[color=blue]
> desired goal?
> o Do I scrap the "CallTags" table and put the necessary info directly[/color]
into[color=blue]
> "Track-No" table? (of course this would be bad form since the DB would[/color]
not[color=blue]
> be strictly normalized.
>
> I'm hoping that someone out there in the Ether can provide direction. If[/color]
anyone[color=blue]
> has questions or requires additional info, let me know.
>
> Thanking in advance all who reply,
>
> Eric[/color]

re: Eliminating records from a query that produces a Cartesian Product

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

re: Eliminating records from a query that produces a Cartesian Product

"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message news:<umBWyod1DHA.2972@TK2MSFTNGP09.phx.gbl>...[color=blue]
> 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
>
>[/color]

re: Eliminating records from a query that produces a Cartesian Product

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.

re: Eliminating records from a query that produces a Cartesian Product

Hi,


What is the SQL text of the query that you use?


Hoping it may help,
Vanderghast, Access MVP


"Eric Slan" <Slanej@aol.com> wrote in message
news:4b0d19dc.0401081251.53a66397@posting.google.c om...[color=blue]
> 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.[/color]

re: Eliminating records from a query that produces a Cartesian Product

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" <Slanej@aol.com> wrote in message
news:4b0d19dc.0401081241.59305b10@posting.google.c om...[color=blue]
> 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[/color]