How to filter and open a report based on a total query? (1 Viewer)

Sun_Force

Active member
Local time
Tomorrow, 06:21
Joined
Aug 29, 2020
Messages
396
I'm trying to learn how to work with reports.

I have a report and its record source is based on a total query.

This is the sql of the query:
SQL:
SELECT DISTINCTROW tblParts.Part,
       [tblOrders]![Quantity]*[tblparts_Products]![quantity] AS Q,
       tblOrders.Quantity,
       tblParts_Products.Quantity
FROM (tblParts_Products
      INNER JOIN tblParts ON tblParts_Products.PartFK = tblParts.PartPK)
INNER JOIN tblOrders ON tblParts_Products.ProductFK = tblOrders.OrderProductFK
GROUP BY tblParts.Part,
         tblOrders.Quantity,
         tblParts_Products.Quantity;

If I filter and open the report by:
DoCmd.OpenReport "MyReport",acViewPreview,,"Part='xxx'"
everything's fine. No problem at all.

But If I want to filter it using a field that is not listed in GroupBy fields I receive Enter Parameter Value
DoCmd.OpenReport "MyReport",acViewPreview,,"Barcode='yyy'"

Now my question:
What's the best practice to open a report and filter it using a field of table that is not in GroupBy list?


Thank you.
 

Attachments

  • Database5.accdb
    672 KB · Views: 128
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 14:21
Joined
Oct 29, 2018
Messages
21,358
Hi. Can't verify right now, but I don't think there should be any problems filtering a report using a field that is not in the Group By clause as long as it is in the Select clause

In your sample query, I don't see Barcode as a selected column.

Sent from phone...
 

Sun_Force

Active member
Local time
Tomorrow, 06:21
Joined
Aug 29, 2020
Messages
396
How can I add a field as a selected column that is not listed in Group By clause?
In query design as soon as I add Barcode, it's added as a Group By.

Thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:21
Joined
Oct 29, 2018
Messages
21,358
How can I add a field as a selected column that is not listed in Group By clause?
In query design as soon as I add Barcode, it's added as a Group By.

Thanks.
There's a couple of ways. You can aggregate it, or you can use a subquery.

I can't tell which one will work for you without seeing what you got.

Sent from phone...
 

Sun_Force

Active member
Local time
Tomorrow, 06:21
Joined
Aug 29, 2020
Messages
396
I'm working on collage database and am not allowed to share it, even if it's only for test purpose. I'll read about aggregating, though I have no idea what it is (in query terms), if failed I'll try to put up a simple database and will upload it.

thanks.
 

Sun_Force

Active member
Local time
Tomorrow, 06:21
Joined
Aug 29, 2020
Messages
396
@theDBguy
I wasn't able to find a way to do what I need.
Any kind of further help is appreciated.
 

Sun_Force

Active member
Local time
Tomorrow, 06:21
Joined
Aug 29, 2020
Messages
396
@theDBguy
Thanks for your effort. But unfortunately it's not what I was looking for. Your report is based on a normal SELECT query. I wanted it be based on a Total query.

Open my original file.
Select Part from first combo box
Select any item from the second combo
click Open Report button.
The report is filtered without any error.

If you select Barcode from the first combo and then any item from the second and click the button, you will receive a Parameter dialogue.
Because [Barcode] is not in the Group By fields of the query.

My original question is :
How can I filter a report with a field that is not listed in Group By list of its query.

If my database is confusing you, I appreciate if you put up something that shows this situation.


Many thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:21
Joined
Oct 29, 2018
Messages
21,358
@theDBguy
Thanks for your effort. But unfortunately it's not what I was looking for. Your report is based on a normal SELECT query. I wanted it be based on a Total query.

Open my original file.
Select Part from first combo box
Select any item from the second combo
click Open Report button.
The report is filtered without any error.

If you select Barcode from the first combo and then any item from the second and click the button, you will receive a Parameter dialogue.
Because [Barcode] is not in the Group By fields of the query.

My original question is :
How can I filter a report with a field that is not listed in Group By list of its query.

If my database is confusing you, I appreciate if you put up something that shows this situation.


Many thanks.
Hi. Ignoring for a second that my sample report is based on a regular Select query rather than a Group By. If you select a barcode and open the report, is the information presented correct? If not, what do you expect to see instead?
 

Sun_Force

Active member
Local time
Tomorrow, 06:21
Joined
Aug 29, 2020
Messages
396
I'm just trying to learn.
It's not a real database. It was just a sample I set up for this purpose.
I just want to learn how to filter a total query with a field that is not listed in Group By.

If it's impossible, I'm OK with it. I simply think there should be a way.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:21
Joined
Oct 29, 2018
Messages
21,358
I'm just trying to learn.
It's not a real database. It was just a sample I set up for this purpose.
I just want to learn how to filter a total query with a field that is not listed in Group By.

If it's impossible, I'm OK with it. I simply think there should be a way.
Oh, I see. You can only filter a report based on the information available in the report.

Like I said earlier, you can include the barcode in your Totals query. In your sample database, you can add it to the Group By fields.

When you run into an actual situation where you cannot include the barcode in the Group By but still want your report to filter by it, then we probably can show you how to maybe use a subquery in that case.
 

Sun_Force

Active member
Local time
Tomorrow, 06:21
Joined
Aug 29, 2020
Messages
396
I'll try to see what I come to with a sub query.
Thanks.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:21
Joined
May 7, 2009
Messages
19,169
you may also try this.
there is a code on the Click event of the button..
 

Attachments

  • Database5.accdb
    660 KB · Views: 125

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:21
Joined
Feb 19, 2002
Messages
42,976
I don't know what either of the suggested methods do since they just said "try this" but you can create a select query that selects the totals query and that query will contain all the calculated columns as well as those in the group by. Then use that query as the RecordSource for the report.
 

Users who are viewing this thread

Top Bottom