Max Date on the Child Table

[kparadise]

Hello,

I have two queries. One query is my large query connecting the Parent (Issues) and Child (Action Plans) tables. This is the query that will give many action plans to one issue. This is the query that my report is hooked up to.

My second query is just pulling in the MAX AP Target Date. Because there can be multiple AP's for one Issue; we just want to know the max date associated with each issue.

SELECT Max(tbl_ACTION_PLANS.[Action Plan Target Date]) AS [Final AP Target Date], tbl_ACTION_PLANS.[Issue ID]
FROM tbl_ACTION_PLANS
GROUP BY tbl_ACTION_PLANS.[Issue ID];

Is there a way to put that SQL into my big query, instead of having two queries? I am trying o keep this database as simple and clean as possible.

 

[Joe4]

Is there a way to put that SQL into my big query, instead of having two queries? I am trying o keep this database as simple and clean as possible.

Having it in two queries is perfectly fine and acceptable, and probably actually is the simpler/cleaner way. Queries themselves really do not chew up much memory, so don't think that you need to reduce them at all costs. Many times you run into trouble if you try to do too much in a single query (and it will affect the performance of that query).

If you are planning on using these queries in Forms or Reports, you will join these two queries together in a Form/SubForm or Report/SubReport relationship.

 

[james_lankford]

make a new database

link the tables in the old databse to the new database --- link, not import

put the query in the new database

this keeps the old database as data storage only -- nice and clean

and the new database is only for queries

whether or not it's worth for just one query is up to you
but I have lots of queries pulling from many differnet linked databases
so the strategy works well for me