query returning either nothing or multiples of records

[Christopher W via AccessMonster.com]

I’m having an extremely large amount of trouble with a query.

My trouble begins when I try to do a query based on the ‘date of research’
field contained in the ‘cost’ table. The query will only return data when the
Junction table between ‘Industry classification’ and ‘Generic asset
description’ is not included in the relationship query design view. It
doesn’t even matter if there is not a field from this table included in the
query, if the relationship is displayed the query returns nothing. Also when
I take it out the query still only returns the data from a couple of records
multiple amounts of times. Obviously there should be a number of valuations
for each asset but these should all have different cost ID’s yet in the query
there are a number of results for each asset with the same cost ID.

Details:
My database is designed to store information regarding the valuation of plant
and machinery. There is a transaction table that contains specific asset
information (model, description etc). There are several simple attached
tables for manufacturer, client and cost (separated to support the fact that
one asset can have many valuations). The area that I think I may have not
built correctly is the relationship between ‘generic asset description’ table
and the ‘industry classification’ table. These two tables have a Many to many
relationship between each other and the ‘industry classification’ table has a
many to many relationship with the ‘transaction’ table, the ‘generic asset’
table has a standard one to many relationship with the ‘transaction’ table.

Also the cost table links to the transaction table using the transaction ID
not the Cost ID as there will be many Cost ID’s for each Transaction ID. I’m
not sure if this may also be the cause of some of my trouble.

I fear that I may have the Many to Many relationships very wrong Please help
me!!!

 

[Allen Browne]

The lines joining the tables to each other in the upper pane of query design
determine how the data is retrieved.

The most common mistakes are:
- using all inner joins, when you need outer joins, and
- using criteria on the outer tables, and
- using criteria on fields that are Null.

More info in article:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Christopher W via AccessMonster.com]

Thank you very much for your response Allen.

I fear that I may not have given enough detail in my initial posting. I have
tried changing all the join types and making sure that there no fields that
are null. Also I tried the inclusion and exclusion of every field and table
in an effort to identify the cause of my difficulty. The result of this
process was the conclusion that the inclusion of either of the two junction
tables causes huge errors in the data returned. However, I don’t know why.

Thank you very much for your assistance, it really is appreciated

The lines joining the tables to each other in the upper pane of query design
determine how the data is retrieved.

The most common mistakes are:
- using all inner joins, when you need outer joins, and
- using criteria on the outer tables, and
- using criteria on fields that are Null.

More info in article:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

Im having an extremely large amount of trouble with a query.

[quoted text clipped - 43 lines]

help
me!!!

 

[Allen Browne]

Your original question focused on a pair of tables that are giving you
grief. So presumably you have some kind of join between these 2 tables, but
the data does not match?

Tell us more about the relationship between the problem tables:
What data type and Field size are the matching fields?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

Thank you very much for your response Allen.

I fear that I may not have given enough detail in my initial posting. I
have
tried changing all the join types and making sure that there no fields
that
are null. Also I tried the inclusion and exclusion of every field and
table
in an effort to identify the cause of my difficulty. The result of this
process was the conclusion that the inclusion of either of the two
junction
tables causes huge errors in the data returned. However, I dont know why.

Thank you very much for your assistance, it really is appreciated

The lines joining the tables to each other in the upper pane of query
design
determine how the data is retrieved.

The most common mistakes are:
- using all inner joins, when you need outer joins, and
- using criteria on the outer tables, and
- using criteria on fields that are Null.

More info in article:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

Im having an extremely large amount of trouble with a query.

[quoted text clipped - 43 lines]

help
me!!!

 

[Christopher W via AccessMonster.com]

Firstly thank you very much for your response!!

The tables that are giving me grief have slightly changed since my first
posting. I have found now that the trouble tables are two junction tables.

The relationships link is in descending pattern:

Transaction:
Asset ID – Primary Key, Auto No., Indexed (Yes, No Duplicates);
Generic Asset ID – Foreign key, No., Required (Yes), Indexed Yes (duplicates
Ok);

Linked via Generic Asset ID, Generic asset (One) to Transaction (Many)


Generic Asset:
Generic Asset ID – Primary Key, Auto No., Indexed Yes (No Duplicates),

Linked via Generic Asset ID, Generic asset (One) to ANZSIC – Junction (Many)


ANZSIC – Junction: (Problem Table)
ANZSIC – Primary Key, Text, Field size 50, required NO, Indexed Yes
(duplicates OK)
Generic Asset ID – Primary Key, Yes (duplicates OK), required NO

Linked via AZSIC, ANZSIC – Junction (Many) to Industry Classification (One)


Industry Classification:
ANZSIC – Primary Key, Text, Field size 50, required NO, Indexed Yes (No
duplicates)

Linked via AZSIC, Industry Classification (One) to

Transaction and Industry classification - Junction: (Problem Table)
ANZSIC – Primary Key, Text, Field size 50, required NO, Indexed Yes
(duplicates OK)
Asset ID – Primary Key, Number, Indexed Yes (duplicates OK)

Linked via Asset ID, Transaction and Industry classification (Many) to
Transaction (One)

Back to transaction:

Obviously these are not all the fields in each table just those that seem the
primary and foreign keys. As I said the inclusion of either of the two
junction tables has a very big effect on the data returned. The ‘ANZSIC –
Junction’ table results in no data being returned at all, and the
‘transaction and industry classification – Junction’ table results in the
records being more than halved.

Your original question focused on a pair of tables that are giving you
grief. So presumably you have some kind of join between these 2 tables, but
the data does not match?

Tell us more about the relationship between the problem tables:
What data type and Field size are the matching fields?

Thank you very much for your response Allen.

[quoted text clipped - 30 lines]

 

[peregenem]

Obviously these are not all the fields in each table just those that seem the
primary and foreign keys. As I said the inclusion of either of the two
junction tables has a very big effect on the data returned. The 'ANZSIC -
Junction' table results in no data being returned at all, and the
'transaction and industry classification - Junction' table results in the
records being more than halved.

This the spec translated into Access/Jet SQL DDL (data declaration
language) code:

CREATE TABLE [Industry Classification] (
ANZSIC CHAR(4) NOT NULL
PRIMARY KEY);

CREATE TABLE [Generic Asset] (
[Generic Asset ID] INTEGER NOT NULL
PRIMARY KEY);

CREATE TABLE [Transaction] (
[Asset ID] INTEGER NOT NULL
PRIMARY KEY,
[Generic Asset ID] INTEGER NOT NULL
REFERENCES [Generic Asset] ([Generic Asset ID])
ON DELETE CASCADE
ON UPDATE CASCADE);

CREATE TABLE
[Transaction and Industry classification] (
ANZSIC CHAR(4) NOT NULL
REFERENCES [Industry Classification] (ANZSIC)
ON DELETE CASCADE
ON UPDATE CASCADE,
[Asset ID] INTEGER NOT NULL
REFERENCES [Transaction] ([Asset ID])
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (ANZSIC, [Asset ID]));

CREATE TABLE ANZSIC (
ANZSIC CHAR(4) NOT NULL
REFERENCES [Industry Classification] (ANZSIC)
ON DELETE CASCADE
ON UPDATE CASCADE,
[Generic Asset ID] INTEGER NOT NULL
REFERENCES [Generic Asset] ([Generic Asset ID])
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (ANZSIC, [Generic Asset ID]));

What I have that the OP doesn't

- FOREIGN KEY constraints in the relationship ('junction') tables i.e.
to ensure only key values from the relevant tables may be included
(could be the cause of the problem)

- explicit rules for DELETE and UPDATE (always helpful to be explicit,
even if using NO ACTION);

- a quick google search reveals ANZSIC to be significantly less than 50
characters (the default column width when created in the Access UI, I
wager), so I've gone for CHAR(10) to reflect the international SIC
codes.

- in the model, an 'asset' may be assigned many industry classification
codes; in my experience, this is normally restricted e.g. to four.

All that said, from the description I suspect the wrong JOIN type is
being employed by the OP e.g. INNER JOIN in place of OUTER JOIN.

 

[Allen Browne]

Christopher, let's focus on just 3 tables, since that's enough to create the
problem. Since the 2 fields in the [ANZSIC Junction] table cannot both be
*primary* keys, I assume it is the combination of the 2 fields that is the
foreign key.

The 3 tables are:
[Industry Classification] table:
ANZSIC Primary Key, Text (50)

[Generic Asset] table:
[Generic Asset ID] Primary Key, AutoNum

[ANZSIC Junction]: (Problem Table)
ANZSIC Text (50). Foreign key to [Industry
Classification].ANZSIC
Generic Asset ID Number (Long). F.k. to [Generic Asset].[Generic
Asset ID]

Now to debug the query. If you create a query based on the 3 tables, it
returns NO records? Break it down to find out which of the joins has the
problem.

First try creating a query with just 2 tables: [Industry Classification] and
[ANZSIC Junction]. Does it return the expected records?

Then try a query consisting of tables [Generic Asset] and [ANZSIC Junction].
Does this return the expected records?

When you have created a relationship (Tools | Relationships), did you check
theReferential Integrity box?

There may be records in [Industry Classification] that have no matches in
[ANZSIC Junction], and these records will not be returned. Likewise some
records from [Generic Asset] may not appear in [ANZSIC Junction], and so
will not be returned. If everything is working flawlessly, the query should
return exactly the number of records in [ANZSIC Junction].

Will check back in another hour or so to see if this has made any progress
towards tracing the cause of the problem.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Christopher W via AccessMonster.com]

Thank you both for your responses, I apologise for my ignorance but I have a
very limited understanding of SQL.

In response to Allen

I assume it is the combination of the 2 fields that is the
foreign key.

The Primary keys for the table ANZSIC – Junction are the two Primary Keys
from the other two adjoining tables, is this not correct should I have a
unique number as a third field or have a calculated field as a primary key?

First try creating a query with just 2 tables: [Industry Classification] and
[ANZSIC Junction]. Does it return the expected records?

Yep, that works fine.
Then I added Industry classification and as you suggested the results that I
am getting are all the results that are in the ANZSIC junction table.

Then I included the Transaction and ANZSIC Junction and made no change to the
fields that are included; Generic asset id (ANZSIC Junction table), Generic
Asset Name (Generic asset table), Industry Classification Name (Industry
Classification table). However the number of records has changed from 13 to
21. On closer investigation it appears the increase in records is due to
records being repeated.

This doesn’t change as I add either field’s from the Transaction and ANZSIC
Junction table. HOWEVER when I add the transaction table to the query and
complete the Relationship Circle the data returned is Zero!!

When you have created a relationship (Tools | Relationships), did you check
theReferential Integrity box?

No I didn’t. To be Honest I don’t understand what it does. Should I cascade
update related fields, or, Cascade delete related records?

Christopher, let's focus on just 3 tables, since that's enough to create the
problem. Since the 2 fields in the [ANZSIC Junction] table cannot both be
*primary* keys, I assume it is the combination of the 2 fields that is the
foreign key.

The 3 tables are:
[Industry Classification] table:
ANZSIC Primary Key, Text (50)

[Generic Asset] table:
[Generic Asset ID] Primary Key, AutoNum

[ANZSIC Junction]: (Problem Table)
ANZSIC Text (50). Foreign key to [Industry
Classification].ANZSIC
Generic Asset ID Number (Long). F.k. to [Generic Asset].[Generic
Asset ID]

Now to debug the query. If you create a query based on the 3 tables, it
returns NO records? Break it down to find out which of the joins has the
problem.

First try creating a query with just 2 tables: [Industry Classification] and
[ANZSIC Junction]. Does it return the expected records?

Then try a query consisting of tables [Generic Asset] and [ANZSIC Junction].
Does this return the expected records?

When you have created a relationship (Tools | Relationships), did you check
theReferential Integrity box?

There may be records in [Industry Classification] that have no matches in
[ANZSIC Junction], and these records will not be returned. Likewise some
records from [Generic Asset] may not appear in [ANZSIC Junction], and so
will not be returned. If everything is working flawlessly, the query should
return exactly the number of records in [ANZSIC Junction].

Will check back in another hour or so to see if this has made any progress
towards tracing the cause of the problem.

Firstly thank you very much for your response!!

[quoted text clipped - 48 lines]

transaction and industry classification  Junction table results in the
records being more than halved.

 

[Allen Browne]

Good: the 2-table query works fine.

If the 3-table query returned 21 records because there are 21 records in
[ANZSIC Junction], then that makes sense.

The approach I am suggesting for you to try is to take a mouthful at a time.
If adding the 4th table returns no records, then investigate what happens if
you build up with that table, starting with just 2 tables and watching the
joins. If it suddenly returns no records, you have identified where the
error lies.

It is theoretically possible that none of the entries in [ANZSIC Junction]
have any matching entries related through the other tables, and so there are
no records to return. It is also possible that the joins between the tables
(in the upper pane of the query design window) are incorrect, and that
changing them will solve the problem.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

Thank you both for your responses, I apologise for my ignorance but I have
a
very limited understanding of SQL.

In response to Allen

I assume it is the combination of the 2 fields that is the
foreign key.

The Primary keys for the table ANZSIC  Junction are the two Primary Keys
from the other two adjoining tables, is this not correct should I have a
unique number as a third field or have a calculated field as a primary
key?

First try creating a query with just 2 tables: [Industry Classification]
and
[ANZSIC Junction]. Does it return the expected records?

Yep, that works fine.
Then I added Industry classification and as you suggested the results that
I
am getting are all the results that are in the ANZSIC junction table.

Then I included the Transaction and ANZSIC Junction and made no change to
the
fields that are included; Generic asset id (ANZSIC Junction table),
Generic
Asset Name (Generic asset table), Industry Classification Name (Industry
Classification table). However the number of records has changed from 13
to
21. On closer investigation it appears the increase in records is due to
records being repeated.

This doesnt change as I add either fields from the Transaction and
ANZSIC
Junction table. HOWEVER when I add the transaction table to the query and
complete the Relationship Circle the data returned is Zero!!

When you have created a relationship (Tools | Relationships), did you
check
theReferential Integrity box?

No I didnt. To be Honest I dont understand what it does. Should I
cascade
update related fields, or, Cascade delete related records?

Christopher, let's focus on just 3 tables, since that's enough to create
the
problem. Since the 2 fields in the [ANZSIC Junction] table cannot both be
*primary* keys, I assume it is the combination of the 2 fields that is the
foreign key.

The 3 tables are:
[Industry Classification] table:
ANZSIC Primary Key, Text (50)

[Generic Asset] table:
[Generic Asset ID] Primary Key, AutoNum

[ANZSIC Junction]: (Problem Table)
ANZSIC Text (50). Foreign key to [Industry
Classification].ANZSIC
Generic Asset ID Number (Long). F.k. to [Generic Asset].[Generic
Asset ID]

Now to debug the query. If you create a query based on the 3 tables, it
returns NO records? Break it down to find out which of the joins has the
problem.

First try creating a query with just 2 tables: [Industry Classification]
and
[ANZSIC Junction]. Does it return the expected records?

Then try a query consisting of tables [Generic Asset] and [ANZSIC
Junction].
Does this return the expected records?

When you have created a relationship (Tools | Relationships), did you
check
theReferential Integrity box?

There may be records in [Industry Classification] that have no matches in
[ANZSIC Junction], and these records will not be returned. Likewise some
records from [Generic Asset] may not appear in [ANZSIC Junction], and so
will not be returned. If everything is working flawlessly, the query
should
return exactly the number of records in [ANZSIC Junction].

Will check back in another hour or so to see if this has made any progress
towards tracing the cause of the problem.

Firstly thank you very much for your response!!

[quoted text clipped - 48 lines]

transaction and industry classification  Junction table results in
the
records being more than halved.

 

[Christopher W via AccessMonster.com]

Thank you so much for your help i really do appreciate it and sorry I keep
explaining my self poorly.

I understand that the procedure in identifying the problem is to break it
down and then build it back again piece by piece. Having undertaken this
process I am unable to pinpoint one join.

When the generic asset and transaction table are the only two in the query
the query works fine. However when the other three tables are included, the
relationship between generic assets and transaction tables results in no
records being returned.

Also the link between Transaction and ANZSIC Junction Results in the records
returned being increased from 13 to 23, there are 13 records in ANZSIC
junction and 23 records in transaction and ANZSIC Junction

I have gone through and made sure that all tables have records in pertaining
that are in the other tables and should therefore ensure that something is
always returned from the query.

General Question: Can the inclusion of a table and relationship in the upper
pain of the query screen alter the results of a query even though there are
no fields being used in the lower pain of the query design screen?

Good: the 2-table query works fine.

If the 3-table query returned 21 records because there are 21 records in
[ANZSIC Junction], then that makes sense.

The approach I am suggesting for you to try is to take a mouthful at a time.
If adding the 4th table returns no records, then investigate what happens if
you build up with that table, starting with just 2 tables and watching the
joins. If it suddenly returns no records, you have identified where the
error lies.

It is theoretically possible that none of the entries in [ANZSIC Junction]
have any matching entries related through the other tables, and so there are
no records to return. It is also possible that the joins between the tables
(in the upper pane of the query design window) are incorrect, and that
changing them will solve the problem.

Thank you both for your responses, I apologise for my ignorance but I have
a

[quoted text clipped - 93 lines]

 

[Allen Browne]

The answer to your general question is, Definately! The joins dicatate which
data is selected. If there are no matchs, you get no results. If the joins
are wrong, you may get no results.

You say the 2-table query works, but the 5-table query does not. There are
several steps to investigate between these 2 to discover at which point the
problem occurs, i.e. which join yields no answers, or which combination of
joins is the issue.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

Thank you so much for your help i really do appreciate it and sorry I keep
explaining my self poorly.

I understand that the procedure in identifying the problem is to break it
down and then build it back again piece by piece. Having undertaken this
process I am unable to pinpoint one join.

When the generic asset and transaction table are the only two in the query
the query works fine. However when the other three tables are included,
the
relationship between generic assets and transaction tables results in no
records being returned.

Also the link between Transaction and ANZSIC Junction Results in the
records
returned being increased from 13 to 23, there are 13 records in ANZSIC
junction and 23 records in transaction and ANZSIC Junction

I have gone through and made sure that all tables have records in
pertaining
that are in the other tables and should therefore ensure that something is
always returned from the query.

General Question: Can the inclusion of a table and relationship in the
upper
pain of the query screen alter the results of a query even though there
are
no fields being used in the lower pain of the query design screen?

Good: the 2-table query works fine.

If the 3-table query returned 21 records because there are 21 records in
[ANZSIC Junction], then that makes sense.

The approach I am suggesting for you to try is to take a mouthful at a
time.
If adding the 4th table returns no records, then investigate what happens
if
you build up with that table, starting with just 2 tables and watching the
joins. If it suddenly returns no records, you have identified where the
error lies.

It is theoretically possible that none of the entries in [ANZSIC Junction]
have any matching entries related through the other tables, and so there
are
no records to return. It is also possible that the joins between the
tables
(in the upper pane of the query design window) are incorrect, and that
changing them will solve the problem.

Thank you both for your responses, I apologise for my ignorance but I
have
a

[quoted text clipped - 93 lines]

the
records being more than halved.

 

[Christopher W via AccessMonster.com]

Sorry I really feel like I am banging my head against a wall with this, would
it be possible to e-mail a copy of the database to you and you could have a
quick look at the tables and relationships? I know that’s a lot to ask and I
completely understand if you are not willing to do that!! I appreciate all
the help you have been gracious enough to give me.

The answer to your general question is, Definately! The joins dicatate which
data is selected. If there are no matchs, you get no results. If the joins
are wrong, you may get no results.

You say the 2-table query works, but the 5-table query does not. There are
several steps to investigate between these 2 to discover at which point the
problem occurs, i.e. which join yields no answers, or which combination of
joins is the issue.

Thank you so much for your help i really do appreciate it and sorry I keep
explaining my self poorly.

[quoted text clipped - 52 lines]

 

[peregenem]

Sorry I really feel like I am banging my head against a wall with this

We have the table structure. Please now post some sample data, your
expected results and, if you can, your existing SQL.

 

[Allen Browne]

Christopher, I can't really accept everyone's databases and work on them for
free. Would get no work done that way.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

Sorry I really feel like I am banging my head against a wall with this,
would
it be possible to e-mail a copy of the database to you and you could have
a
quick look at the tables and relationships? I know thats a lot to ask and
I
completely understand if you are not willing to do that!! I appreciate all
the help you have been gracious enough to give me.

The answer to your general question is, Definately! The joins dicatate
which
data is selected. If there are no matchs, you get no results. If the joins
are wrong, you may get no results.

You say the 2-table query works, but the 5-table query does not. There are
several steps to investigate between these 2 to discover at which point
the
problem occurs, i.e. which join yields no answers, or which combination of
joins is the issue.

Thank you so much for your help i really do appreciate it and sorry I
keep
explaining my self poorly.

[quoted text clipped - 52 lines]

the
records being more than halved.

 

[Christopher W via AccessMonster.com]

Sorry for the delayed reply, I have been out of the office all day yesterday.

No, look I completely understand, I shouldn’t have asked!!

Would it be possible to just leave the tables out of a query, instead have
separate querys

I’ve worked out that by changing some of the join properties and the fields
included in the query and by leaving out the problem tables I can create
successful queries. However will this cause me problems later down the track?

Christopher, I can't really accept everyone's databases and work on them for
free. Would get no work done that way.

Sorry I really feel like I am banging my head against a wall with this,
would

[quoted text clipped - 22 lines]

 

[Allen Browne]

Creating different queries that work, saving them, and then using them as
input "tables" into another query? That is often a good solution. No
problem.

Another more involved approach is to use subqueries, but that's a whole new
topic, so we won't confuse you by explaining those. If you want to follow up
on the idea, MS has an introduction here:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[peregenem]

No, look I completely understand, I shouldn't have asked!!

I'll repeat *my* offer: post some test data and your expected results
and I'll take another look for you.