David Ball
Registered User.
- Local time
- Today, 11:04
- Joined
- Aug 9, 2010
- Messages
- 230
I have information from two different sources about subsystems (areas of an industrial plant). One is a to do list with items that need to be completed and the other is to do with checksheets that need to be completed. The information from each source is completely different but both refer to Subsystem numbers and these are a means of connecting the data.
Management want to see a report that shows, for each subsystem, the outstanding to do items and outstanding checksheets.
It would be a summary report that has one row for each subsystem and a total number for outstanding to do’s and outstanding checksheets.
The problem I am having with the query I currently have to pull this information together is that the data seems to be getting combined. For example, If I have 5 to do items outstanding for a subsystem and 1 outstanding checksheet, the query is showing that I have 5 to do’s and 5 checksheets.
I have a master list of all subsystems in a table and use this as the “spine” of my query. The query is built from the master subsystem table and has all subsystems from this table as the only field initially. I then dragged in the queries from the two separate data sources and joined the “primary key” fields, subsystem, with a join that pulled in all subsystems from the master list and only matching subsystems from the two other queries.
I then dragged in the fields from each of these queries that I wanted to show in my report. I am sure I have built queries like this many, many times before and never had this issue.
If I have six total entries for a subsysyem, 5 to-do’s and 1 checksheet, my query only shows 5 records and all with a ckecksheet as well as a to do. I need it to show 6 records, 5 to-do’s and 1 checksheet.
The results would then be summarized in the report by subsystem.
Can anyone advise as to what it is I am doing wrong here?
Thanks very much
Dave
Management want to see a report that shows, for each subsystem, the outstanding to do items and outstanding checksheets.
It would be a summary report that has one row for each subsystem and a total number for outstanding to do’s and outstanding checksheets.
The problem I am having with the query I currently have to pull this information together is that the data seems to be getting combined. For example, If I have 5 to do items outstanding for a subsystem and 1 outstanding checksheet, the query is showing that I have 5 to do’s and 5 checksheets.
I have a master list of all subsystems in a table and use this as the “spine” of my query. The query is built from the master subsystem table and has all subsystems from this table as the only field initially. I then dragged in the queries from the two separate data sources and joined the “primary key” fields, subsystem, with a join that pulled in all subsystems from the master list and only matching subsystems from the two other queries.
I then dragged in the fields from each of these queries that I wanted to show in my report. I am sure I have built queries like this many, many times before and never had this issue.
If I have six total entries for a subsysyem, 5 to-do’s and 1 checksheet, my query only shows 5 records and all with a ckecksheet as well as a to do. I need it to show 6 records, 5 to-do’s and 1 checksheet.
The results would then be summarized in the report by subsystem.
Can anyone advise as to what it is I am doing wrong here?
Thanks very much
Dave
