neophyte needs query help - omit paramerter value request window

dab1477

Board Regular
Joined
Jul 30, 2002
Messages
65
Thanks in advance. I am an intermediate user of Access and have the following issue:

How do I omit the parameter windows show for SUMofPLANNEDSCREENS SUMofActualScreens SUMofACTUALMnhrs as part of Query A, that pull the data from Query B?

I have a query that pulls data from a Query B. Query B has a Date1 field that uses the Between function, where the query wants required Start Date and End Date inputs. When running Query A that pulls from Query B, a parameter window pops up and I enter the Start date, then the End date. This works as expected.
The issue is that the SAME Query A wants a parameter input for the additional 3 fields form Query B, that DON'T require input. These fields are expected to simply PULL data that gets summed from Query B. I use these 3 fields to populate 2 expressions from the Initial query.

Query B looks Like this:
DATE_1 Shift Cell DaysWorked PlannedScreens ActualScreens ActualMnhrs CurrentwKSPERF CURRENTWKSLOST CURRENTWKSLBRHRS


Query A looks like this:
Date_1 SUMofPLANNEDSCREENS SUMofActualScreens SUMofACTUALMnhrs Perf% U/LH

This query wants inputs for START DATE, END DATE, SUMofPLANNEDSCREENS SUMofActualScreens SUMofACTUALMnhrs. The latter 3 are NOT data inputs, but are pulled from Query B.

The latter 2 fields are calculated as follows:

Perf %: [SumOfActualScreens]/[SumOfPlannedScreens]
U/LH: [Sum Of Actual Screens built]/[Sum Of Actual Manhours worked]

These calculated fields are not associated with any Parameter Window.


How do I omit the parameter windows show for SUMofPLANNEDSCREENS SUMofActualScreens SUMofACTUALMnhrs as part of Query A, that pull the data from Query B?

Thanks
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,708
Office Version
  1. 2016
Platform
  1. Windows
Not sure if you have resloved this? Have you tried a make table from the first query and then use the results as building the second query.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
You could work out some of those problems in a form (where you can fiddle with the SQL text and parameters and store values in hidden fields, etc.).
Also possible is just a second version of the query or basically two versions of the query, one for entering all five inputs and one for using with the the other query instead.

Also possible with a little more thought is setting up the parameters to be stored in a table (or again in a form, even a hidden form). So that whether you input the values manually or not, the result is to store the parameters. Then the actual query that uses the parameters gets them from your stored values. So basically you only need to worry about how to get the values into your stored location, and the query runs the same no matter how those values are populated. This is along the same lines as the previous post.
 
Last edited:

dab1477

Board Regular
Joined
Jul 30, 2002
Messages
65
The parameter requests stem from the first query. This query runs a report. To make a table each time I run report seems odd. I'd have hundreds of tables after a few months, or I'd need to do table deletions quite often. Issue not yet resolved. Open to other suggestions. Thank you.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
That was not the only suggestion, plus you wouldn't have to create a *new* table every time you run the report. Why don't you post your queries, that would help to make it clear how the two queries work together. Because you say query B pulls from query A. But precisely how does it do that? Is query A being used as a subquery? Is it a query of a query? Are you running these in a form or just raw running queries?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,110
Messages
5,768,156
Members
425,458
Latest member
Jaspal1996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top