Report not displaying query data

[compooper]

Hi folks!

I am trying to create a report that displays information from multiple queries.

I have set my report's recordsource to:

SELECT qryINVESTaddexp.*, qryINVESTCredit.*, qryINVESTDrFees.*, qryINVESTDup.*, qryINVESTKms.*, qryINVESTLag.* FROM qryINVESTaddexp, qryINVESTCredit, qryINVESTDrFees, qryINVESTDup, qryINVESTKms, qryINVESTLag;

I have dragged the multiple fields and placed them all over my report.
When I click print preview however, none of the data is displayed.
Only the field names.



What have I done wrong?

[rpeare]

That record source is the problem. different queries but you are not relating the queries nor do I think the syntax is correct. What I would do, were I you, is build a query that shows the data you want, then create the report based on that singular query rather than trying to do this the way you are.

[compooper]

I don't understand. I built that SQL statement using the SQL builder provided.The syntax should be correct.
What do you mean by "you are not relating the queries"?
All of the queries should have the same CompanyID, InvType, InvDate if thats what you mean by relating.
But the data itself, like client name, etc should be and will be different.

[rpeare]

SELECT qryINVESTaddexp.*, 
qryINVESTCredit.*, 
qryINVESTDrFees.*,  
qryINVESTDup.*, 
qryINVESTKms.*, 
qryINVESTLag.* 
FROM qryINVESTaddexp,  
qryINVESTCredit, 
qryINVESTDrFees, 
qryINVESTDup, 
qryINVESTKms,  
qryINVESTLag;

This is your SQL statement. I've added carriage returns to make it more readable. What you are saying in this query is this
SELECT ALL records from qyrInvestaddexp
AND
SELECT ALL records from qryInvestCredit
AND
SELECT ALL records from qryInvestDrFees
AND
SELECT ALL records from qryInvestDup
AND
SELECT ALL records from qryInvestKms
AND
SELECT ALL records from qryInvestLag

You are referencing 6 different queries in this SQL statement without linking them. Let's say you have 10 records in each query, you are going to end up with a query result that has 10x10x10x10x10x10 results or 1,000,000 records. You have to link the records by a 1 to 1 or 1 to many relationship for this query to work.

Let's say the common item among the tables is a customerID, you would have to left join, right join etc each table to one another on that customerID field. Which is why I suggested building a query that shows the records you want, how you want, rather than trying to build this query on the fly when you run your report.

[compooper]

Okay thanks.

I ended up using subreports and it luckily worked.