Repeating records in Query

[Thorson]

I am pretty new at building databases and I am having the following problem:

I currently have a query set up to pull records from a Union query, a select
query (qryWithdrawalDateOFFLabel) and a regular table. For some reason the
query will repeat the records when run. I checked the record sources (union
query (qryWithdrawalDateOFFLabel), select query and table) that the query
depends on and there are no duplicate records. The joint relationship
properties between the three sources is set up so that all the records in the
Union query appear and only those in the select query and table that match
appear.

When I delete the select query source (qryWithdrawalDateOFFLabel) the query
doesn't repeat the records so I'm assuming it has something to do with this
relationship.

I don't know exactly what is causing this or how to fix it. If any one has
any ideas I'd appreciate it.

 

[Golfinray]

Click on the line that runs between your queries or table and then right
click it. It offers you other types of joins. It is probably one of the
others.

 

[Thorson]

It does offer the three joins that I usually see between the two queries
(qryWithdrawalDatesUnion and qrywithdrawalDateOFFLabel):

1. Only include rows where the joined fields from both tables are equal
2. Include All Records from 'qryWithdrawalDatesUnion' and only those records
from 'qryWithdrawalDateOffLabel' where the joined fields are equal.
3. Include All records from 'qryWithdrawalDateOffLabel' and only those
records from 'qryWithdrawalDatesUnion' where the joined fields are equal.

The second one is currently selected, which is what I want. I want all the
records from qryWithdrawalDatesUnion, there are more records in this query
than the other one.

It may just be that I need to reorganize my entire queries, I was kind of
experimenting with them to get the results I wanted... apparently something
is wrong.

 

[Ron2006]

Don't forget that IF there are multiple records in the
'qryWithdrawalDateOffLabel' that match on the union part alone then it
may look like there are duplicates IF you are not showing enough of
the 'qryWithdrawalDateOffLabel' table record to recognize that it is a
not really a duplicate.


Ron

 

[Thorson]

That is a good point, I just double checked that, but it is really
duplicating records. For example:

in 'qryWithdrawalDateOffLabel' there are 7 records for case# DST2008-0033
however, in 'qryWithdrawalDate' there are 50 records for case# DST2008-0033

in 'qryWithdrawalDateOffLabel' each of the 7 records has a different date
and/or different medicine/health treatment. Each of those 7 records does
have the same case# and same ear tag (since it is the same animal for one
case).

Within 'qryWithdrawalDates' the relationship between
'qryWithdrawalDatesUnion' and 'qryWithdrawalDateOFFLabel' is set up through
the 'EarTag' field. It does not affect the duplication if I change the
relationship to the CaseNumber Field.

 

[Ron2006]

Trying to make sure I understand:

'qryWithdrawalDatesUnion' is the controlling query and has only 1
record for case DST2008-0033

'qryWithdrawalDateOffLabel' is the secondary query and has 7 records
for case DST2008-0033

'qryWithdrawalDates' is the query you are running that seems to show
duplicated records. It has a left join between DatesUnion and
DateOffLabel. In design view you do have a line between the two
queries/tables and are not using criteria to match the two.

Have you tried (for the sake of testing) to run the DatesUnion with
criteria saying only select case DST2008-0033?

Also in design view you do NOT see DateOffLabel twice (once as a
DateOffLabel-1).
You are sure that the count is 50 in the qryWithdrawalDates resulting
query.
A count of 49 would imply 7*7 and imply a reversal of one of those
unions.

Ron

 

[Thorson]

No, 'qryWithdrawalDatesUnion' also has 7 records for case DST2008-0033, Yes
it is the controlling query

Yes,'qryWithdrawalDateOffLabel' is the secondary query and has 7 records
for case DST2008-0033

I tried running the query to select only case DST2008-0033, same results
(duplicated records)

No the query 'qryWithdrawalDateOffLabel' does not appear twice.

I was wrong about the count, it is only 49, not 50; therefore implying 7*7

Both 'qryWithdrawalDatesUnion' and 'qryWithdrawalDateOffLabel' use a common
table to get the 7 records, 'tblTherapeuticIndTreatmentRecords' I orginally
had 'qryWithdrawalDateOffLabel' set up because I needed it as an intermediate
query to perform an equation, however I just tried skipping that step and
directly using 'tblTherapeuticIndTreatmentRecords' instead of
'tblTherapeuticIndTreatmentRecords', it does perform the results I want,
however it still duplicates records.

So I've never dealt with this before... I'm not sure what this means or what
to do. Thank you for helping.

 

[Ron2006]

There is the problem.....

Every time that 'qryWithdrawalDatesUnion' shows a single record for
DST2008-0033 the combined union will show the 7 records in the
secondary query.

the seven primary records will EACH show the seven secondary records.
That is what will happen. There is no way around that if you MUST show
the Seven primary records.

If you really only want to show only one per DST2008-0033 you would
have to create another query that would group by the case numbers (and
other unique fields BUT ONLY resulting in a single record per case
number) and have this be the primary. Without knowing your data/files
it is kind of hard to specifically give you an answer. Somehow you
will have to group and bring forth only enough information to show the
case number once if that is the final product you want.

Ron

 

[Ron2006]

One way or the other you have to either get only one record in the
file on the left to show

OR

Have all of them show but have ONLY ONE record in the file on the
right to show per on the left.

==========================
Possibility.

create a query that is grouped by case number and contains the "First"
or "Last" unique record ID for the Union query and then have this
query be primary and linked to the actual UNION query by this ID and
to the secondary query by the case number, This should get you only
the 7 occurances that you are looking for.

==========================

Ron

 

[Thorson]

Thank you for the help. I will work on re-designing my queries.

 

[Ron2006]

Good Luck....

Ron

 

[Thorson]

Ron,

I am having a terrible time trying to figure out how to do this, for some
reason or another no matter how I set up my queries I will either get
duplicate records, or I can't get the results I want. If you wouldn't mind
could you help me get from my tables to what I want?

I have two separate tables/forms that the user enters in health treatment
records: 'tblPrevenativeHealthRecords' and 'tblTherapeuticHealthRecords'.
each table lists the date the animal receives the medicene (drug), the
animal's ID and a casenumber or GroupID (Multiple animals will receive the
same GroupID; CaseNumbers are given to an animal when they are sick, they
will keep the same case number until they recover, therefore a case number
may have several health records for the same animal and an animal may have
more than one case number, but a case number will never have more than one
animal).

Through my queries these tables and records are joined to another table
'tblDrugList', this table states the withdrawal time for each drug.
'tblTherapeuticHealthRecords' however has an additional option, if the drug
is given off label a box is checked and an "OffLabelWithdrawalDate" is
entered.

I would like to create a query that calculates the withdrawal date for each
time an animal is given any drug, and if the drug is given off label then I
would like it to calculate that time instead of the withdrawal time stated in
the 'tblDrugList'

First I created a union query to join together the two tables
'tblPrevenativeHealthRecords' and 'tblTherapeuticHealthRecords' This query
joins the following fields:
GroupID + CaseNumber
EarTag + EarTag
Date + Date
Product + Drug
Dosage + Dosage
AdmministrationRoute +AdministrationRoute

I then created a separate query 'qryWithdrawalDate', the goal of this query
is to calculate the date the withdrawal is over for each animal. The SQL for
this query is:
SELECT qryWithdrawalDatesUnion.GroupID, qryWithdrawalDatesUnion.eartag,
qryWithdrawalDatesUnion.Date, qryWithdrawalDatesUnion.Product,
qryWithdrawalDatesUnion.Dosage, tblDrugList.WithdrawalTime,
([qryWithdrawalDatesUnion]![Date])+([tblDrugList]![WithdrawalTime]) AS
WithdrawalExpired, tblTherapeuticIndTreatmentRecords.OffLabelWithdrawalTime,
([qryWithdrawalDatesUnion]![Date])+([OffLabelWithdrawalTime]) AS
OffLabelWithdrawalExpired
FROM (qryWithdrawalDatesUnion LEFT JOIN tblDrugList ON
qryWithdrawalDatesUnion.Product = tblDrugList.DrugName) LEFT JOIN
tblTherapeuticIndTreatmentRecords ON qryWithdrawalDatesUnion.Eartag =
tblTherapeuticIndTreatmentRecords.EarTag;

Up to here is where my problem is, so far, after this I can figure
everything out. The problem is I can't figure out how to have one table only
have 1 group and casenumber and still make the query work correctly. I tried
setting it to pull up the max date etc., but I couldn't get the queries to
work.

I did come up with something that might work, but it is against
normalization, it would create a field that would always be blank in table
'tblPrevenativeHealthRecords'. If I add "OffLabelWithdrawalTime" to the
table and then add it to the union query for each table I can treat them
differently in my next query and therefore eliminate duplicates, but as I
said, this creates a blank field in 'tblPrevenativeHealthRecords'

If you wouldn't mind helping me out with this problem that would be great.
I understand this is long and confusing, thank you so much for any help you
can offer.

 

[Ron2006]

I will try to let all of this sink in and think about it this evening.
(never got to look at questions yesterday.)

Ron

 

[Thorson]

Thank you

 

[Ron2006]

A quick question:

Is this off label indicator and (time?) a function of the drug list.
In other words is that indicator in the drug list and is this second
time period there also?

Or is it a function of (part of) the drug list or is this offlabel
time a function of the specific application. Is hopefully the offlabel
date essentially always the same in so far as it represents a standard
time but just different that what is normally assigned to the drug?

Ron

 

[Thorson]

The OffLabel Indicator is part of a table/Form separate from the Drug List.

I'm not sure that I completely understand your question, but I will give it
a shot. The OffLabel Indicator is a number of any given amount representing
days, this will vary depending on the amount of drug given and type etc, it
will not always be the same for a given drug, it will always be different
than what is normally assigned to the drug.

The time normally assigned to the drug will always be the same for that drug
(this is from the Drug List).

Does that answer the question?

 

[Ron2006]

Sorry about no reply yesterday. Too many fires to put out.

What I think I am suggesting is that you develop a separte query that
includes the drug list and the off label table. The end result would
be a single time frame for the given drug (dosage?). make it so that
you will always have the drug and then if there is no offlabel record
then the time is from the drug list but if there is an offlabel
record, then you would output the offlabel time.

Then use this as a substitute for the druglist in your query. This
will get one of the complications out of the already complicated
query.

I will try to take another look at the problem this afternoon - time
permitting.

Ron

 

[Thorson]

So I did previously respond to this question... I don't know why it didn't
show up.

I think this answers the question, but not sure:

No the indicator in the drug list and the second time period are not both in
the tblDrugList, only the original indicator for each drug.

The off label time indicator is located in another form/table in the
database, frmTherapeuticIndTreatmentRecord, it is not always the same number.
When a user has an off label time they check a box in that form and then a
combo box allows them to enter in the number (in days) for the OffLabel
Withdrawaltime. Even though the offlabel indicator is not always the same
the original indicator in the druglist table (also in days) is always the
same.

I currently calculate when the animal would be safe to sell for meat (the
withdrawal time) by simply taking the time it was given the drug and adding
adding the days of the withdrawal time from the table. I would like to
override this by using the offlabel withdrawal time, if one is present. The
problem I am having is that one Therapeutic Case could have several treatment
records (all would have the same case number and eartag), but only 1 or 2 of
the treatment records for that case might have an off label withdrawal time.
That's why I have it set up as I do, but as you know, it is repeating records.

Thank you for any suggestions or help you can offer.

 

[Thorson]

So I was really confused this morning... I didn't realize you could have
multiple pages on here, I thought my last post didn't go up, so it is posted
twice. you can ignore my post at 6:06am this morning.

Thanks for your suggestions, I'll try it out and play with it some and let
you know how it works out. I don't know why I'm having such difficultly with
this, I guess it just takes time, practice and experience.

 

[Thorson]

Ok, so my only problem is that if there is an off label time it is related to
the drug but it is also specifically related to the case record, animal and
specific treatment that animal receives. So a drug could be given to two
different animals on the same day with one receiving an extra dosage and
therefore having an off label time while the other one receives the regular
dosage and therefore has the regular withdrawal time.

If I am understanding you right the table would only have one or the other
times, not both. I need the off label time to be linked to the CaseNumber,
the specific treatment record as well as the drug.