Query returns too many records

[Gem]

Hi
I'm struggling with a query returning too many records.

I have 3 related tables.
tblPatients
tblOperations
tblTargets

Each patient in tblPatients can have more than one operation (one-many)
Each Operation in tblOperations can have many targets (many-many)

The unique key across tables is PtFileNumber

So one patients data would look something like this (only bare bones given)

(tblPatient)
KENROT56 (ie had 3 operations )
 
(tblOperations)
12/01/06 (3 records, one operation on each on these dates )
10/02/06 
15/05/06
 
(tblTarget)
12/01/06 (4 records, ie for one operation there are 2  targets)
12/01/06
10/02/06
10/02/06

Data is entered through forms. The patient ID data is entered seperately to Operation data. Target data is entered seperately to operation data - all are linked via the unique field ptFileNumber.

Not all the data is entered at once but it is always entered in order ie Patient ID followed by Operation followed by Target.

I have one query that finds all Patients that do not (yet) have a corresponding Operation - works fine.

Then I have another query that is supposed to return all patients from the operations table that do not already have a matching date in the target table. Here I get the records I want plus records I don't want for SOME patients. So from my lists above I want to see returned ONLY kenrot56 15/05/06 instead I might get kenrot56 15/05/06 AND 10/02/06 .why?

<<SELECT DISTINCTROW tblOperation.date, tblOperation.PtFileNumber
FROM tblOperation LEFT JOIN tblTargetSites ON tblOperation.PtFileNumber = tblTargetSites.ptFileNumber
WHERE (((tblOperation.date) In (SELECT [date] FROM [tblOperation] where [date] <> [operationdate])))
ORDER BY tblOperation.date;>>

sorry to be long winded Does this make sense?

Cheers
Gem

[MMcCarthy]

Hi
I'm struggling with a query returning too many records.

I have 3 related tables.
tblPatients
tblOperations
tblTargets

Each patient in tblPatients can have more than one operation (one-many)
Each Operation in tblOperations can have many targets (many-many)

The unique key across tables is PtFileNumber

Gem

Before we even look at the query we need to deal with your table structure. Tables cannot have a many to many relationship. This can be resolved by the use of a JOIN table.

tblOperations
OperationID (Primary Key - Unique)

tblTargets
TargetID (Primary Key - Unique)

New table:

tblTargetPerOpe ration
OperationID (Foreign Key referencing tblOperations Primary key)
TargetID (Foreign Key referencing tblTargets Primary key)

Both of these represent the Primary Key of this new table. Neither will be unique in itself but together the combination will be unique.

Check out this tutorial for more information.

Normalisation and Table structures

Mary

[NeoPa]

Are tblOperation and tblTargetSites (I'm using your SQL in preference to your explanation as they contradict each other) really many-to-many or are they one-to-many but underneath the tblPatient table? Is it really possible to have a single tblTargetSites record shared between two separate tblOperation records?

The answer to this is fundamental in knowing what the next step should be.

[Gem]

OK, yes I see, you are both saying the same thing - the tblOperation and tbleTarget is in fact one-to-many (one operation can have many targets). In tblOperation and tblTargesite I had put the default record number as the primary key and in tblpatient the pk is pkPatientFile number. I see where I went wrong. I will put in the join table.

thanks a million
Gem

[Gem]

Hi Guys
It's late and I'm going to ask a really dumb question - Now that I have my Join table how do I populate the thing with the exisitng records? I have 164 records in tblOperation and 463 in tblTargetsites

I tried using an append query based on tblOperation and tblTargetsites but just ended up duplicating the original problem ie. too many records - so that was not good.

Also, how do I ensure that all rew records also appear in this join table. Most of my forms are based on queries do I add this new table to the underlying queries - doesn't feel quite right.

cheers
Gem

[NeoPa]

If it's a one-to-many relationship you don't need a Join Table after all. That's why I asked.
All you need is a PK (one) and a matching FK (many).

Mary's answer was based on what you'd posted earlier.

[Gem]

If it's a one-to-many relationship you don't need a Join Table after all. That's why I asked.
All you need is a PK (one) and a matching FK (many).

Mary's answer was based on what you'd posted earlier.

Hi NeoPa
I'm guilty of not paying attention..... thank you for replying.

Here's what I have now

Primary Table
tblPatient
Record autoNo.
pkPtFileNumber (indexed No duplicates)

Second Table
tblOperation
pkOperationID autoNo. (indexed No duplicates)
fkPtFileNumer (indexed Duplicates OK)

Third Table
tblTargetsites
pkTargetSiteID (indexed No Duplicates)
fkOperationID (indexed Duplicates OK)

in the database relationships window I have enforced referential integrity with cascade update and delete. The join type btwn tblPatient & tblOperation is 2 (because I want to see a complete list of patients whether or not they have operations)
the join btwn tblOperation and tblTargetsites is also 2.

Now, the fkPtFileNumber is fully populated (because I use it in a combobox on frmOperation to ID the patient ) BUT the fkOperationID in tblTargetSites is empty and I have to work out how to match the fk with the 400 records that are already in tblTargetSites.

Gem

[MMcCarthy]

Hi NeoPa
I'm guilty of not paying attention..... thank you for replying.

Here's what I have now

Primary Table
tblPatient
Record autoNo.
pkPtFileNumber (indexed No duplicates)

Second Table
tblOperation
pkOperationID autoNo. (indexed No duplicates)
fkPtFileNumer (indexed Duplicates OK)

Third Table
tblTargetsites
pkTargetSiteID (indexed No Duplicates)
fkOperationID (indexed Duplicates OK)

in the database relationships window I have enforced referential integrity with cascade update and delete. The join type btwn tblPatient & tblOperation is 2 (because I want to see a complete list of patients whether or not they have operations)
the join btwn tblOperation and tblTargetsites is also 2.

Now, the fkPtFileNumber is fully populated (because I use it in a combobox on frmOperation to ID the patient ) BUT the fkOperationID in tblTargetSites is empty and I have to work out how to match the fk with the 400 records that are already in tblTargetSites.

Gem

Do you have this data stored somewhere else Gem like a spreadsheet or something.

Mary

[NeoPa]

Most posters fail even to realise why attention to detail is even required, so a single slip is certainly no great problem.
With reference to your earlier question, this makes the structure hierarchical and so a lot simpler. The wizards should be able to handle your data quite normally and at least give you a basic structure to build on.
I'm not sure how to make the links between your tables if you don't have any existing data to link them with I'm afraid.