By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,949 Members | 2,038 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,949 IT Pros & Developers. It's quick & easy.

Query returns too many records

P: 6
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)

Expand|Select|Wrap|Line Numbers
  1. (tblPatient)
  2. KENROT56 (ie had 3 operations )
  3.  
  4. (tblOperations)
  5. 12/01/06 (3 records, one operation on each on these dates )
  6. 10/02/06 
  7. 15/05/06
  8.  
  9. (tblTarget)
  10. 12/01/06 (4 records, ie for one operation there are 2  targets)
  11. 12/01/06
  12. 10/02/06
  13. 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?

Expand|Select|Wrap|Line Numbers
  1. <<SELECT DISTINCTROW tblOperation.date, tblOperation.PtFileNumber
  2. FROM tblOperation LEFT JOIN tblTargetSites ON tblOperation.PtFileNumber = tblTargetSites.ptFileNumber
  3. WHERE (((tblOperation.date) In (SELECT [date] FROM [tblOperation] where [date] <> [operationdate])))
  4. ORDER BY tblOperation.date;>>

sorry to be long winded Does this make sense?

Cheers
Gem
Feb 11 '07 #1
Share this Question
Share on Google+
8 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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:

tblTargetPerOperation

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
Feb 11 '07 #2

NeoPa
Expert Mod 15k+
P: 31,492
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.
Feb 11 '07 #3

P: 6
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
Feb 11 '07 #4

P: 6
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
Feb 12 '07 #5

NeoPa
Expert Mod 15k+
P: 31,492
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.
Feb 12 '07 #6

P: 6
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
Feb 13 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Feb 13 '07 #8

NeoPa
Expert Mod 15k+
P: 31,492
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.
Feb 13 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.