create a table from a list of query results

[Guest]

I use an access database for keeping track of hire plant. I have a field that
is set to in or out depending on status. To create a table for servicing I
run a query searching for equipment out. From this query I then manually make
a table and then append to this table again manually to give me locations
based on the latest record for the piece of plant. Is there a way I can set
up to make and append to the table automatically from my original query.

 

[Arvin Meyer [MVP]]

It seems to me that you're working too hard when Access can do this directly
with bound forms (the default). But to answer your question, the easiest
way, without changing anything you already have is to create another query,
(an Append Query) that uses your first query to append to the table. You can
call it from a command button.

 

[Guest]

I don't see that you need to create a table at all. You can return a list of
all equipment out, with its current location in a query. Lets assume you
have a table Plant, which includes the Status column, and a table Hires with
a foreign key column PlantID referencing the primary key of Plant and columns
HireDate, Location. By joining the tables on Plant ID and then restricting
the result to those rows where Status = "Out" and HireDate is the latest date
for each item of plant, which you'd obtain with a subquery, this should give
you rows for all equipment currently out, and its current location, e.g.

SELECT PlantName, Location
FROM Plant INNER Join Hires AS H1
ON Plant.PlantID = H1.PlantID
WHERE Status = "Out"
AND HireDate =
(SELECT MAX(HireDate)
FROM Hires As H2
WHERE H2.PlantID = H1.PlantID);

The aliases H1 and H2 are used to distinguish the two instances of the Hires
table so that the subquery can be correlated with the outer query on the
PlantID column.

If the Hires table also included a ReturnDate column you would not need the
Status column in Plant as a Null ReturnDate in the latest row per PlantID in
Hires would mean that item of plant is out, in which case the query would be:

SELECT PlantName, Location
FROM Plant INNER Join Hires AS H1
ON Plant.PlantID = H1.PlantID
WHERE HireDate =
(SELECT MAX(HireDate)
FROM Hires As H2
WHERE H2.PlantID = H1.PlantID
AND ReturnDate IS NULL);

In fact you could simplify it even more as there should never be more than
one row per item of plant in Hires with a Null ReturnDate, so a simple join
is all that's required, dispensing with the subquery:

SELECT PlantName, Location
FROM Plant INNER Join Hires
ON Plant.PlantID = Hires.PlantID
WHERE ReturnDate IS NULL;

Ken Sheridan
Stafford, England