Combining 2 Queries in a Report

[nicks60610]

I am trying to combine 2 queries in to one report and when I use the Wizard, I can pull the info from the first query, but as soon as I hit the 2nd query, it shows me no fields even though there are fields in the 2nd qry. I am wondering if the problem is that the 1st qry is a Crosstab qry? I am trying to do this in Access 97, and both queries do have a unique field that they share (each qry has the field).

Any ideas?? Thanks!

 

[PHV]

Have you tried to base you report on a query joining your 2 queries ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nicks60610]

It's not my DB, but I looked at Qry 2 and it is a combo of 2 tables...yuck, such a mess, but the report they want does not allow me anything else, but I will try your idea, combining those 3 queries...

Thanks!

 

[nicks60610]

I tried creating a new query and that did not work, so the next thing I tried was to create a sub report on the report, but the same thing happens; as soon as I select the query (which is built on 2 crosstab queries) it does not let me select any fields from that query, is this because this query prompts the user to enter a month and year in order to run the query??

 

[lespaul]

can you post the SQL of the two queries?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[nicks60610]

They are a bit "messy" due to calculations:

1st qry:

SELECT [Complaint Report Summary Data Complaints_Crosstab].[Product Group],
[Complaint Report Summary Data Complaints_Crosstab].Name,

([Complaint Report Summary Data Complaints_Crosstab]![Period]/[Complaint Report Summary Data Pieces_Crosstab]![Period])*1000000 AS [Period CPM],

(([Complaint Report Summary Data Complaints_Crosstab]![Period]+[Complaint Report Summary Data Complaints_Crosstab]![YTD])/([Complaint Report Summary Data Pieces_Crosstab]![Period]+[Complaint Report Summary Data Pieces_Crosstab]![YTD]))*1000000 AS [YTD CPM]


FROM [Complaint Report Summary Data Pieces_Crosstab] INNER JOIN [Complaint Report Summary Data Complaints_Crosstab] ON [Complaint Report Summary Data Pieces_Crosstab].[Product Group] = [Complaint Report Summary Data Complaints_Crosstab].[Product Group];


2nd qry (this is the one the report is based on)

TRANSFORM Count([Complaint Report Raw Data].[Complaint Id]) AS [The Value]

SELECT [Complaint Report Raw Data].[Product Group],

[Complaint Report Raw Data].Name,

[Complaint Report Raw Data].[Short Description],

Count([Complaint Report Raw Data].[Complaint Id]) AS [Total Of Complaint Id], -((Sum([Time Period]="Period"))+Sum([Time Period]="YTD")) AS [Year To Date]

FROM [Complaint Report Raw Data]

WHERE ((([Complaint Report Raw Data].Exclude)=No))

GROUP BY [Complaint Report Raw Data].[Product Group], [Complaint Report Raw Data].Name, [Complaint Report Raw Data].[Short Description], [Complaint Report Raw Data].Exclude

PIVOT [Complaint Report Raw Data].[Time Period] In ("Period","YTD","Last Year");