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
7  3092 
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.goo gle.com... 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
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.OpenRepor t, should not
have the word WHERE.
Public Sub SomeSub( ) ' in Access, in a standard module
DoCmd.OpenRepor t 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.goo gle.com... 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
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
"Michel Walsh" <vanderghast@Vi rusAreFunnierTh anSpam> wrote in message news:<um******* *******@TK2MSFT NGP09.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.OpenRepor t, should not
have the word WHERE.
Public Sub SomeSub( ) ' in Access, in a standard module
DoCmd.OpenRepor t 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
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.
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.goo gle.com... 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.
Eric,
I think you're misunderstandin g 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.goo gle.com... 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Ed_No_Spam_Please_Weber |
last post by:
Hello All & Thanks in advance for your help!
Background:
1) tblT_Documents is the primary parent transaction table that has 10
fields and about 250,000 rows
2) There are 9 child tables with each having 3 fields each, their own
PK; the FK back to the parent table; and the unique data for that
table. There is a one to many relation...
|
by: shumaker |
last post by:
I'm wondering how/why this query works. Trying to get my head wrapped
around SQL. Basically the Query deletes from the Import table all
records that are already in FooStrings so that when I do an insert from
the FooStringsImport table into the FooStrings table, then I won't get
primary key violations.
DELETE FROM FooStringsImport
WHERE...
|
by: jimh |
last post by:
I'm not a SQL expert. I want to be able to write a stored procedure
that will return 'people who bought this product also bought this...'.
I have a user table that links to a transaction table that links to a
transaction items table that links to the products table:
(User Table)
UserID
Other user data
|
by: starman7 |
last post by:
I have a query that seems to kill the db for our website (causes the
'Too many connections' error).
I would like to avoid this! Is there a way to test the query before the
db goes down? I do not have access to a staging environment. I can
access the db via Linux command line, or phpMyAdmin - however I can
only select, update, and delete.
...
|
by: Brenda J. |
last post by:
Hello all
I'm sure this is a relatively simple query to create, but I can't seem
to get my head around it.
Here is an example of the two tables I am using:
tblAccountTypes
AccountTypeID AccountType AccountNumberRange
| | |
by: manning_news |
last post by:
Using A2K. I've got a database with client info and each client has a
subform which contains types of income and the amount they each
receive. The record source of the subform is a cartesian product of
the client ID and the types of income of which there is currently 16
types. So every client has 16 records.
What I'd like to do is to...
|
by: Christoph Zwerschke |
last post by:
In Python, it is possible to multiply a string with a number:
>>> "hello"*3
'hellohellohello'
However, you can't multiply a string with another string:
>>> 'hello'*'world'
Traceback (most recent call last):
File "<interactive input>", line 1, in ?
|
by: The Frog |
last post by:
Hello Everyone,
I have been asked to try and create a single SQL query to retrieve
product information from a database. The way that data is arranged is
that in some tables there are user defined "attributes" or
"dimensions" that in turn connect to the actual product table via a
many-many (using a linking table). In each linking table there...
|
by: bob laughland |
last post by:
Hi All,
I have a SQL query like this (I have tried to break the problem down
to simplify it),
select rowid from table where name in ('a', 'b', 'd') group by rowid
Here is an example of data in the table
rowid name
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |
