Query returns 4, not the 2 records that it should..

[chrismpc]

Hello,

I have a table, with 3 other tables linked to it:

Main Table
ID

SubTable1
MainTableID

SubTable2
MainTableID

SubTable3
MainTableID

My query is instructed to retrieve records where the maintableID's
match the maintable 'id' field

I have 1 record in subtable 1
I have 2 records in subtable 2
I have 2 records in subtable 3

(in all 3 the maintableID is matching, and I have checked that it
actually does!)

According to my query, and this works up until I add the last table in,
there are 2 records, which is correct. However the 3rd table just
duplicated the records, for absolutley no reason that is at least
obvious to me!

If anyone has any clue as to why it would be much appreicated...

Many thanks in advance.

Chris.

 

[Guest]

Hi Chris - are you saying that each of the subtables only contain an ID
field? Why? If not, what data do the 3 tables contain. Also it would help to
have the SQL code for the query, cause from what you wrote it looks like it
is doing exactly what you asked for.

 

[Ron2005]

If you have the 3 tables linked in a row ie:

table 2 linked to 1 by table ID and then table 3 linked to table 2 by
table ID. then you will receive 4 records back because the link logic
says:

get all records in 1 that match 2 (which is two)
but then it says when look at EACH record in 2 get me all records
in 3 that match table 2 which is also 2 for EACH

The end result is that you receive 4 records back in the query.

You will have to change your logical relationship if all you want is 2
records returned.

Ron

 

[Guest]

A query is just a step to getting to a form. A common pitfall is to mistake
a query for an Excel spreadsheet.

However, there is a simple way to see what is happening in your query. The
database is not duplicating records. You are simply not showing all of the
fields that are being used for your query. Go into the design view of your
query, and list the other fields from your subtables. You will find that
there are some unique combinations of your ID and the fields from the
subtables.

For instance:

MainTable
MainTableID (= 1,2)

SubTable1
SubTable1ID (=1)

SubTable2
SubTable2ID (=1,2)

SubTable3
SubTable3ID (=1,2)

A query using the MainTable, SubTable1, SubTable2, and SubTable3 would produce

MainTableID SubTable1ID SubTable2ID SubTable3ID
1 1 1 1
1 1 2 1
1 1 1 2
1 1 2 2

If you only combined MainTable, SubTable1, and SubTable2, you would have

MainTableID SubTable1ID SubTable2ID
1 1 1
1 1 2