A Cry (No A Scream) For Help

[Steve]

Hi Folks

It's very early on a Sunday morning and I'm still battling after days of
"pratting around" trying to work out what must be an idiotically simple MS
Access problem. I've posted here before (Can't find the original thread) and was
pointed to the problem of Cartesian Products but I've looked at the suggestions
and to be honest (and this hurts me to say) I'm completely lost.

So I'll try and explain exactly what I'm trying to do (excuse the what is
probably excessive detail but I’m trying not to confuse) and if somebody can get
back to be quickly (I need this tomorrow) it would be so very much appreciated.

Firstly and I've tried this on a very simple Table/Query combination and it
works no problem. What I did was to generate and save, two excel worksheets
Worksheet 1 Equip.xls) comprises of 5 records as follows with the first line
being the filed descriptors:

Equip Description
PMP-200 BIG PUMP
PMP-201 ANOTHER BIG PUMP
PMP-202 SMALL PUMP
PMP-203 TINY PUMP
PMP-204 NEW PUMP

Worksheet 2 Tag.xls is as follows

Equip Tag Service
PMP-200 200-FT200 Pump Flow
PMP-200 200-PT-200 Pump Pressure
PMP-201 201-FT201 Pump Flow
PMP-201 201-PT-201 Pump Pressure
PMP-202 202-FT202 Pump Flow
PMP-202 202-PT-202 Pump Pressure
PMP-203 203-FT203 Pump Flow
PMP-203 203-PT-203 Pump Pressure
PMP-205 205-PT-205 Pump Pressure

I then linked these 2 worksheets into an Access (2003) database. With table
names “Equip” and “Tag”.

I then generated a relation ship between the 2 tables using the “Equip” field
from each table. The join properties were No 2 i.e. “Include all records from
“Tag” and only those from “Equip” where the joined fields are equal.

Finally I generated a simple query in Design View in the format (and I hope this
displays OK on Usenet):

Equip Description Tag Service
Tag Equip Tag Tag

I then ran the query and got:

Equip Description Tag Service
BIG PUMP PMP-200 200-FT200 Pump Flow
BIG PUMP PMP-200 200-PT-200 Pump Pressure
ANOTHER BIG PUMP PMP-201 201-FT201 Pump Flow
ANOTHER BIG PUMP PMP-201 201-PT-201 Pump Pressure
SMALL PUMP PMP-202 202-FT202 Pump Flow
SMALL PUMP PMP-202 202-PT-202 Pump Pressure
TINY PUMP PMP-203 203-FT203 Pump Flow
TINY PUMP PMP-203 203-PT-203 Pump Pressure
PMP-205 205-PT-205 Pump Pressure

This is EXACTLY what I expected and wanted i.e. 9 records (all records in the
TAG table) with one field missing in the last record as there was no matching
record in the “Equip" table. There was also (as expected) no reference to the
last record in the “Equip” table as there was no “matching” record in “TAG” for
the field Equip

I do hope that all made sense and that at least some of you have stayed with me.

OK so now I try exactly the same thing with the Real worksheets they both have
more fields and more records with Tag having 963 records and Equip with 370.
There are more instances in both tables where there is no matching field in the
other table but otherwise the philosophy looks the same (well to be honest there
must be a significant difference but I can’t see it) Anyway when I run the query
I get 1693 records with many duplicates included.

So a very simple question WHY and more importanty how do I stop it?

I really do hope that somebody can point me in the right direction cause this
isn’t doing my mental state any good

Thanks (In anticipation)

Steve

 

[Steve]

Earlier posting really didn't display at all well. I've edited below adding a
"," between each field to help clarify

Hope this helps and my apologies

Steve

 

[Rick Brandt]

Earlier posting really didn't display at all well. I've edited below adding a
"," between each field to help clarify

Hope this helps and my apologies

Steve

That is a puzzler. I know of no way for a query to output more rows than there
are in the largest source table except for when there is no join and you get a
Cartesian result.

Just to check for a forehead slapper...When you say there are 963 records in the
Tag linked table are you looking at the datasheet of the link in Access or are
you going by what you saw in Excel? There could have been 963 rows, then a gap,
then a bunch more rows that you didn't see. The link would include all of those
rows.

 

[David F Cox]

My first suspicion is that you have records with duplicate keys in both
datasets. have you got the key fields that you are using set to no
duplicates?

 

[Steve]

If I click the limked tables from within Access they report 963 & 370 records
respectively

Steve

 

[Steve]

I'm probably going to sound extremely thick but how do I set up keys in tables
that are linked to external worksheets (Access 2003)?

Thanks and apologies for being dumb

Steve

 

[Rick Brandt]

If I click the limked tables from within Access they report 963 & 370
records respectively

Post the SQL of your query.

 

[Rick Brandt]

I'm probably going to sound extremely thick but how do I set up keys
in tables that are linked to external worksheets (Access 2003)?

You cannot. To enforce uniqeness on a field it would have to be an databse
table rather than an Excel link.

Thanks and apologies for being dumb

David raised the point I had missed. If you have a left join on a field and
that field has duplicates in either tables then you will end up with more rows
in your output than you have in either of the input tables.

You need to build a "Find Duplicates" query against both links to find all of
the duplicate entries.

 

[Steve]

Thanks to you both, somehow the fact that neither of you has come up with an
imediate response gives me some comfort (probably soon to be dashed when I'm
told I've done something so very stupid that neither of you could have been
expected to guess). I've battled on for a few more hours but to be honest I am
now just trying things just for the sake of it. The data is such that I can't
see how their would be duplicates (well certainly not all fields in a particular
record)

In trying to narrow the problem down I have

1. Imported the data into "stand alone" tables instead of linking it
2. Removed all columns from tables that aren't in the query (with the exception
of the ID field that was generated during the import)
3. Deleted some records from the "Tags" table that may have confused matters
I've reduced the records from 963 to 894 but when I now run the query the total
records generated is 1624.

In SQL View the query reads as follows:

SELECT Tags.Equip, Tags.Tag, Equip.Descrition, Tags.Service
FROM Tags LEFT JOIN Equip ON Tags.Equip = Equip.Equip;

Here's hoping

Steve

 

[David F Cox]

It is not duplicate records that I was referring to, but duplicate keys.

Equip Tag Service
PMP-200 200-FT200 Pump Flow
PMP-200 200-PT-200 Pump Pressure

if you are using equip as a key PMP-200 has duplicate keys.

a problem will arise if PMP-200 appears more than once in tags. You will get
number of PMP-200's in equip * number of PMP-200'2 in Tags records returned
for that part.

 

[Rick Brandt]

Thanks to you both, somehow the fact that neither of you has come up
with an imediate response gives me some comfort (probably soon to be
dashed when I'm told I've done something so very stupid that neither
of you could have been expected to guess). I've battled on for a few
more hours but to be honest I am now just trying things just for the
sake of it. The data is such that I can't see how their would be
duplicates (well certainly not all fields in a particular record)

There is no requirement for entire rows to be duplicated. All that is needed to
see this problem is to have duplicates on the field you are using for the join.

 

[Steve]

David & Rick

A big thanks for putting up with my stupidity. I've managed to resolve the issue
(Duplicates) I thought I was OK if no 2 records were identical of course it was
identical fields that was causing the problem.

Trouble with Access is unlike any of the other components of the MS Suite I get
so little exposure to it just he occasional tweak of an existing db. I really
need to settle down and learn from the begining. Would that be a Dummies book?

Once again thanks to you both for your patience

Steve

 

[John Vinson]

Trouble with Access is unlike any of the other components of the MS Suite I get
so little exposure to it just he occasional tweak of an existing db. I really
need to settle down and learn from the begining. Would that be a Dummies book?

I've looked at Access for Dummies, and it's not bad... for people who
will be USING existing Access databases, or creating very very simple
ones. It wimps out pretty quick for anything more advanced.

I'd suggest John Viescas' _Access 2003 Inside Out_; or go to a good
bookstore with a big technical shelf and pull a half-dozen of the
Access books off the shelf. Browse through them and see which fit your
learning style; perhaps check the Index to see if you can look up some
technique (The Not In List Event for example).

John W. Vinson[MVP]