locked
Can you use a Cell from a table as a criteria parameter in a Query? RRS feed

  • Question

  • Hello.

    After a 10 year break from using Access, and recent work commitments have given me a small challenge in updating and refining a database.

    I am trying to create a Query that uses data in a table as it's criteria, which in itself is easy. However, what I wish to do is use the value in the first Cell of Row 1 of the table as the parameter.

    What should I type into the criteria box in the Query Design tool to make this happen?

    What I wish to do is to write a Macro that uses one Query (QrySubjects) to populate a holding table (TblSubjects) with EmployeeID, then run a second query within an IIF Statement to record the outstanding training for each EmployeeID in TblSubjects to another table (TblReport), delete the first row of TblSubjects, then repeat until first row is NULL. Once the first row is NULL, I will produce a report based on TblReport, then clear the data from TblReport.

    Any advice? Or, if you can think of a better way to complete this, how else to do it? This is more complex than a usual situation as the training completed and the required training for an employee are stored separately and the requirement is subject to frequent change. This means that the outstanding training needs to be calculated each time the report is ran.


    Saturday, November 23, 2013 4:18 PM

Answers

  • There should be no need for those problematic temporary/holding tables.  You can use a query just about anywhere you can use a table, including within another query.  I suspect that your report only needs to use a single query based on one or two tables as its record source.

    Before we can describe how this query would look, we need to know what fields in which tables are used in the report along with how the tables are related.

    • Marked as answer by George Hua Tuesday, December 3, 2013 3:01 AM
    Saturday, November 23, 2013 7:32 PM