Combining data from two seperate sources in a query (1 Viewer)

David Ball

Registered User.
Local time
Today, 12:40
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:10
Joined
May 7, 2009
Messages
19,169
you can make use of a Union Query?

for union query to work, both queries
must have same number of columns to return:

select field1, field2 field3 from listToComplete
union all
select field1, field2, field3 from checklist
 

David Ball

Registered User.
Local time
Today, 12:40
Joined
Aug 9, 2010
Messages
230
I will try that thanks
 

Users who are viewing this thread

Top Bottom