Two queries into one report

[icthus123]

Is it possible to create a report using two separate queries?

[CreganTur]

You'd probably be better served by creating a union query that pulls the data you want from the 2 queries you cite. Then build your report based on the new union query.

[icthus123]

You'd probably be better served by creating a union query that pulls the data you want from the 2 queries you cite. Then build your report based on the new union query.

The problem is that I can't do that as I need the fields to tie into one row, is you see what I mean? And I can't for the life of me get the query to work as I want to. Are there any easy ways round this, or do I just need to work of the SQL until I get it right?

[CreganTur]

I need the fields to tie into one row
I'm really not sure what you mean by that... do these 2 querries have a field in common so that you could JOIN them?

[icthus123]

I'm really not sure what you mean by that... do these 2 querries have a field in common so that you could JOIN them?


The problem is a have query:


SELECT tblLocalAuthority.[LA Number], tblSchools.DfES, tblSchools.Pooled, tblSchools.Diocese, tblProjects.School, tblSchools.[School Name], Format(nz(Sum(Switch([tblProjects].[Status]="Pending",[tblProjects].[DFC Y1],[tblProjects].[Status]="Approved" Or "Final" Or "Exceptionally Approved",0)),0), '?#,##0.00') AS [Amount in Progress], Format(nz(Sum(Switch([tblProjects].[Status]="Pending",0,[tblProjects].[Status]="Approved" Or "Final" Or "Exceptionally Approved",[tblProjects].[DFC Y1])),0),'?#,##0.00') AS [Amount Committed]
FROM tblLocalAuthority INNER JOIN ((tblSchools INNER JOIN tblProjects ON tblSchools.[School Number] = tblProjects.School) INNER JOIN [tblDFC/School] ON tblSchools.[School Number] = [tblDFC/School].School) ON (tblLocalAuthority.[Local Authority] = tblSchools.[Local Authority]) And (tblLocalAuthority.[Local Authority] = tblProjects.[Local Authority]) And (tblLocalAuthority.[Local Authority] = [tblDFC/School].[Local Authority])
WHERE (((tblProjects.[Date Submitted]) Between #4/1/2007# And #3/31/2008#))
GROUP BY tblLocalAuthority.[LA Number], tblSchools.DfES, tblSchools.Pooled, tblSchools.Diocese, tblProjects.School, tblSchools.[School Name];


Now this works fine until I add the table tblDFC/School to the query (which I will need as I develop the query). I?ve put a thread in the SQL forum, but no-one replied so I thought I?d try the other method of joining two queries once they were in the report.

I did post on this a while ago, but then I stopped working on it for a while.

[CreganTur]

Right off the bat I see that you're trying to Inner Join multiple tables... this may be the root cause of all of your problems. It's very difficult to JOIN more than 2 tables- it's possible, but requires a lot of work.

Utilize Google to see if you can find any articles on Joining multiple tables... I used to have a good one but I can't find it... I'll try to track it down:sleuth: