Repeated Data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good day,

I have two query’s that are linked together to generate information from two
different tables and return the information into one query.

Table 1 = Job History
Table 2 = Downtimes

I link these two tables through a query that has an outer join (Show all
records from Job History and only those records from Downtimes where the join
fields are equal.)

In some cases in my report from these two queries the information is exactly
the same and shows twice (repeats).

In both queries when run independently the transactions do not repeat.

Any reason why this would be happening?
 
Normally the linking in the final query will involve unique values on one
side of the join (one to many). I expect this is not true in your query
resulting in duplicates.
 
Yes that is true.

The job history query is to sum of the machine time over a specified period
of time by the machine. The link is a concatenation or Date / cost center /
employee number / to the Downtime query (Same link in Downtime query). All I
am asking it is to show all records form Job History and those from downtime
that are equal. Why would it repeat it. It only does this some times and not
always. It is like an anomaly.
 
What field(s) are on either side of your join? Are these field(s) unique?
Does the Job History query contain only one record per machine? Is this the
linking field? It's ultra difficult to make guesses when we don't know your
query sql or how your data is grouped and joined.
 
Good day,

I have two query’s that are linked together to generate information from two
different tables and return the information into one query.

Table 1 = Job History
Table 2 = Downtimes

I link these two tables through a query that has an outer join (Show all
records from Job History and only those records from Downtimes where the join
fields are equal.)

In some cases in my report from these two queries the information is exactly
the same and shows twice (repeats).

In both queries when run independently the transactions do not repeat.

Any reason why this would be happening?
Click to expand...

You'll get Job History data repeating if there are two matching
records in Downtimes, or vice versa. That's just how queries work:
each record in the "one" side table in the query will be repeated as
many times as there are matching records in the other table.

Might you have duplicate data in the Downtimes table?

John W. Vinson [MVP]
 
Duane, sorry about that your right.

When I run the queries individually the tansaction from Job History does not
repeat niether does it in the Dowtimes. Only when they are run linked to each
other.

I am asking my query to show me all the transactions for a period of time
and find show me any downtime that when the links are matched. My links in
the queries are

The Link is a concatenation of WorkOrder & Employee Number & Date & Operation
Sequence. This link makes the record unique to iteself.

JobHistory Link
643380712012/16/200720

Dowtime Link
643380712012/16/200720

Hope this makes more sense. I dont know how else to explain it.
 
Take each of your two source queries individually and group by your fields
used for linking and count any other like this to confirm only on of the base
queries returns records.

SELECT WorkOrder, EmployeeNumber, DateField, OperationSequence, Count(*) as
NumOf
FROM qselYourQuery
GROUP BY WorkOrder, EmployeeNumber, DateField, OperationSequence
HAVING Count(*) >1;
 
Back
Top