If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Repeated Data
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? |
#2
|
|||
|
|||
Repeated Data
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. -- Duane Hookom Microsoft Access MVP "K" wrote: 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? |
#3
|
|||
|
|||
Repeated Data
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. "Duane Hookom" wrote: 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. -- Duane Hookom Microsoft Access MVP "K" wrote: 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? |
#4
|
|||
|
|||
Repeated Data
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. -- Duane Hookom Microsoft Access MVP "K" wrote: 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. "Duane Hookom" wrote: 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. -- Duane Hookom Microsoft Access MVP "K" wrote: 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? |
#5
|
|||
|
|||
Repeated Data
On Mon, 19 Feb 2007 11:22:03 -0800, K
wrote: 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? 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] |
#6
|
|||
|
|||
Repeated Data
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. "Duane Hookom" wrote: 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. -- Duane Hookom Microsoft Access MVP "K" wrote: 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. "Duane Hookom" wrote: 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. -- Duane Hookom Microsoft Access MVP "K" wrote: 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? |
#7
|
|||
|
|||
Repeated Data
That is what I thought. But they do not repeat when run individually.
"John W. Vinson" wrote: On Mon, 19 Feb 2007 11:22:03 -0800, K wrote: 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? 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] |
#8
|
|||
|
|||
Repeated Data
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; -- Duane Hookom Microsoft Access MVP "K" wrote: 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. "Duane Hookom" wrote: 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. -- Duane Hookom Microsoft Access MVP "K" wrote: 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. "Duane Hookom" wrote: 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. -- Duane Hookom Microsoft Access MVP "K" wrote: 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? |
Thread Tools | |
Display Modes | |
|
|