Eliminating records from a query that produces a Cartesian Product

[Eric Slan]

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

 

[Michel Walsh]

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

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

 

[Eric Slan]

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]

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.

 

[Michel Walsh]

Hi,


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


Hoping it may help,
Vanderghast, Access MVP