neophyte needs query help - omit paramerter value request window

[dab1477]

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

 

[Trevor G]

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]

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.

 

[UncleCatwoman]

How are you using the end data? Is the source for a report? Do you dump it to Excel?

 

[dab1477]

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]

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?