Refer to a query in qry of rpt

[Dee]

Ok... I'm trying to filter data in a report, but still have access to some
other pertinent fields. I want to have a query in a report that refers
mainly to the table "data" so that I can have access to all of the fields in
that table, but I would like to filter the records based on the query
"Discrepancies in Unit Price". Here is the statement I would like to put in
the field "MANUFACTURER" to filter the records:

In (SELECT [MANUFACTURER] FROM [Discrepancies in Unit Price] As Tmp GROUP BY
[MANUFACTURER],[MFG #] HAVING Count(*)>1 And [MFG #] = [Discrepancies in
Unit Price].[MFG #])

BUT I don't know how to refer to the query itself... it goes looking for it.
For example, to refer to a field on a form, I would refer to it as:
Forms![Orders]![EmployeeID].

Or is there a better way to do this through parameters or something? I
originally had two queries:

Discrepancies in Unit Price (to group the records)

SELECT Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE]
FROM Data
WHERE (((Data.Month)=#10/1/2006#))
GROUP BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE]
ORDER BY Data.MANUFACTURER, Data.[MFG #];

Then I created another query based on the grouped records to filter only
data with duplicate unit prices for the same Manufacturer and MFG # called
"Find duplicates for Discrepancies in Unit Price":

SELECT [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER, [Discrepancies in Unit Price].DESCRIPTION,
[Discrepancies in Unit Price].[UNIT PRICE]
FROM [Discrepancies in Unit Price]
WHERE ((([Discrepancies in Unit Price].MANUFACTURER) In (SELECT
[MANUFACTURER] FROM [Discrepancies in Unit Price] As Tmp GROUP BY
[MANUFACTURER],[MFG #] HAVING Count(*)>1 And [MFG #] = [Discrepancies in
Unit Price].[MFG #])))
ORDER BY [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER;

Problem with doing it that way is that I still need all of the other fields
in the report:

ID, INVOICE NUMBER, TYPE, INVOICE DATE, BILL-TO, COST CENTER, PRACTICE NAME,
ADDRESS, CITY, STATE, ZIP, MFG #, MANUFACTURER, ITEM #, DESCRIPTION, UNIT,
QTY SHIP, UNIT PRICE, AMOUNT, MONTH

So I thought if I do a query in a report and filter the records on the query
that included all of the fields by that one field Manufacturer using the
"Discrepancies in Unit Price" query, that might work.... How can I properly
refer to this query in the main query??

Please advise if you can )

Thank you very much for your help!!! I really appreciate it ).

 

[KARL DEWEY]

Open your query in design view and add the query like you would a table.
Use an INNER JOIN. You do this by clicking on the field in the table and
draging to the related field of the query.

The resulting query will pull only records that are in both this way.

Ok... I'm trying to filter data in a report, but still have access to some
other pertinent fields. I want to have a query in a report that refers
mainly to the table "data" so that I can have access to all of the fields in
that table, but I would like to filter the records based on the query
"Discrepancies in Unit Price". Here is the statement I would like to put in
the field "MANUFACTURER" to filter the records:

In (SELECT [MANUFACTURER] FROM [Discrepancies in Unit Price] As Tmp GROUP BY
[MANUFACTURER],[MFG #] HAVING Count(*)>1 And [MFG #] = [Discrepancies in
Unit Price].[MFG #])

BUT I don't know how to refer to the query itself... it goes looking for it.
For example, to refer to a field on a form, I would refer to it as:
Forms![Orders]![EmployeeID].

Or is there a better way to do this through parameters or something? I
originally had two queries:

Discrepancies in Unit Price (to group the records)

SELECT Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE]
FROM Data
WHERE (((Data.Month)=#10/1/2006#))
GROUP BY Data.MANUFACTURER, Data.[MFG #], Data.DESCRIPTION, Data.[UNIT PRICE]
ORDER BY Data.MANUFACTURER, Data.[MFG #];

Then I created another query based on the grouped records to filter only
data with duplicate unit prices for the same Manufacturer and MFG # called
"Find duplicates for Discrepancies in Unit Price":

SELECT [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER, [Discrepancies in Unit Price].DESCRIPTION,
[Discrepancies in Unit Price].[UNIT PRICE]
FROM [Discrepancies in Unit Price]
WHERE ((([Discrepancies in Unit Price].MANUFACTURER) In (SELECT
[MANUFACTURER] FROM [Discrepancies in Unit Price] As Tmp GROUP BY
[MANUFACTURER],[MFG #] HAVING Count(*)>1 And [MFG #] = [Discrepancies in
Unit Price].[MFG #])))
ORDER BY [Discrepancies in Unit Price].[MFG #], [Discrepancies in Unit
Price].MANUFACTURER;

Problem with doing it that way is that I still need all of the other fields
in the report:

ID, INVOICE NUMBER, TYPE, INVOICE DATE, BILL-TO, COST CENTER, PRACTICE NAME,
ADDRESS, CITY, STATE, ZIP, MFG #, MANUFACTURER, ITEM #, DESCRIPTION, UNIT,
QTY SHIP, UNIT PRICE, AMOUNT, MONTH

So I thought if I do a query in a report and filter the records on the query
that included all of the fields by that one field Manufacturer using the
"Discrepancies in Unit Price" query, that might work.... How can I properly
refer to this query in the main query??

Please advise if you can )

Thank you very much for your help!!! I really appreciate it ).