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  3082 
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
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
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@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
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.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.
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 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...
|
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...
|
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...
|
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...
|
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
...
|
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...
|
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...
|
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...
|
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...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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...
|
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,...
| |
