Help with query

G

Guest

Hi,

I don't know what this would be called so couldn't really have a discriptive
title....

I have a database where we record all of the IT equipment used in our team
at work. When someone leaves or someone new starts the database gets updated
and we can see the history of the item (who has used it in the past).

I have a table of employees, a table of the items and descriptions and
another table for recording the history.

Everything works fine for my form and most of my reports but I am wanting a
report that shows any items that are currently spare. I cannot seem to get
it to show items that have been used in the past but are now spare.

This is my current query that just shows which items have never been
assigned to someone:
SELECT [Items Details].[Item Model], [Items Details].[Item Serial Number],
[Items Details].[Item BCL Asset Number], [Users/History].[Current User],
[Users/History].[Date Issued], [Users/History].[Date returned], [Items
Details].[Item Description]
FROM [Items Details] LEFT JOIN [Users/History] ON [Items Details].[Item BCL
Asset Number]=[Users/History].ItemID
WHERE ((([Users/History].[Current User]) Is Null));

Does anyone know how I would go about making the query tell me if an item
had a current user but that they also have a returned date and now has no
current user assigned?

Any advice would be appreciated. Please let me know if you need anymore info.

Thanks
 
A

Allen Browne

Presumably the item is in use if the *last* time it was issued, the [Date
returned] is null, or if it has never been issued.

You could use a subquery to give you that info.
Something like this:

SELECT [Items Details].[Item Model],
[Items Details].[Item Serial Number],
[Items Details].[Item BCL Asset Number]
FROM [Items Details]
WHERE (SELECT TOP 1 [Users/History].[Date returned]
FROM [Users/History]
WHERE [Users/History].ItemID =
[Items Details].[Item BCL Asset Number]
ORDER BY [Users/History].[Date Issued] DESC,
[Users/History].[ID]) Is Null;

Replace the ID in the last line with the name of the primary key of
[Users/History].

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

If you need other fields as well, see:
http://www.mvps.org/access/queries/qry0020.htm
 
G

Guest

Hi,

Thanks.

Is the query below the whole query including the sub query or just the sub
query?


SELECT [Items Details].[Item Model], [Items Details].[Item Serial Number],
[Items Details].[Item BCL Asset Number]
FROM [Items Details] LEFT JOIN [Users/History] ON [Items Details].[Item BCL
Asset Number] = [Users/History].ItemID
WHERE ((((SELECT TOP 1 [Users/History].[Date returned]
FROM [Users/History]
WHERE [Users/History].ItemID =
[Items Details].[Item BCL Asset Number]
ORDER BY [Users/History].[Date Issued] DESC,
[Users/History].[HistoryID])) Is Null));


Allen Browne said:
Presumably the item is in use if the *last* time it was issued, the [Date
returned] is null, or if it has never been issued.

You could use a subquery to give you that info.
Something like this:

SELECT [Items Details].[Item Model],
[Items Details].[Item Serial Number],
[Items Details].[Item BCL Asset Number]
FROM [Items Details]
WHERE (SELECT TOP 1 [Users/History].[Date returned]
FROM [Users/History]
WHERE [Users/History].ItemID =
[Items Details].[Item BCL Asset Number]
ORDER BY [Users/History].[Date Issued] DESC,
[Users/History].[ID]) Is Null;

Replace the ID in the last line with the name of the primary key of
[Users/History].

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

If you need other fields as well, see:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Nikki said:
Hi,

I don't know what this would be called so couldn't really have a
discriptive
title....

I have a database where we record all of the IT equipment used in our team
at work. When someone leaves or someone new starts the database gets
updated
and we can see the history of the item (who has used it in the past).

I have a table of employees, a table of the items and descriptions and
another table for recording the history.

Everything works fine for my form and most of my reports but I am wanting
a
report that shows any items that are currently spare. I cannot seem to
get
it to show items that have been used in the past but are now spare.

This is my current query that just shows which items have never been
assigned to someone:
SELECT [Items Details].[Item Model], [Items Details].[Item Serial Number],
[Items Details].[Item BCL Asset Number], [Users/History].[Current User],
[Users/History].[Date Issued], [Users/History].[Date returned], [Items
Details].[Item Description]
FROM [Items Details] LEFT JOIN [Users/History] ON [Items Details].[Item
BCL
Asset Number]=[Users/History].ItemID
WHERE ((([Users/History].[Current User]) Is Null));

Does anyone know how I would go about making the query tell me if an item
had a current user but that they also have a returned date and now has no
current user assigned?

Any advice would be appreciated. Please let me know if you need anymore
info.

Thanks
Click to expand...
Click to expand...
 
G

Guest

Hi,

Is this query below the whole query including the subquery or just the
subquery?

SELECT [Items Details].[Item Model], [Items Details].[Item Serial Number],
[Items Details].[Item BCL Asset Number]
FROM [Items Details] LEFT JOIN [Users/History] ON [Items Details].[Item BCL
Asset Number] = [Users/History].ItemID
WHERE ((((SELECT TOP 1 [Users/History].[Date returned]
FROM [Users/History]
WHERE [Users/History].ItemID =
[Items Details].[Item BCL Asset Number]
ORDER BY [Users/History].[Date Issued] DESC,
[Users/History].[HistoryID])) Is Null));


Allen Browne said:
Presumably the item is in use if the *last* time it was issued, the [Date
returned] is null, or if it has never been issued.

You could use a subquery to give you that info.
Something like this:

SELECT [Items Details].[Item Model],
[Items Details].[Item Serial Number],
[Items Details].[Item BCL Asset Number]
FROM [Items Details]
WHERE (SELECT TOP 1 [Users/History].[Date returned]
FROM [Users/History]
WHERE [Users/History].ItemID =
[Items Details].[Item BCL Asset Number]
ORDER BY [Users/History].[Date Issued] DESC,
[Users/History].[ID]) Is Null;

Replace the ID in the last line with the name of the primary key of
[Users/History].

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

If you need other fields as well, see:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Nikki said:
Hi,

I don't know what this would be called so couldn't really have a
discriptive
title....

I have a database where we record all of the IT equipment used in our team
at work. When someone leaves or someone new starts the database gets
updated
and we can see the history of the item (who has used it in the past).

I have a table of employees, a table of the items and descriptions and
another table for recording the history.

Everything works fine for my form and most of my reports but I am wanting
a
report that shows any items that are currently spare. I cannot seem to
get
it to show items that have been used in the past but are now spare.

This is my current query that just shows which items have never been
assigned to someone:
SELECT [Items Details].[Item Model], [Items Details].[Item Serial Number],
[Items Details].[Item BCL Asset Number], [Users/History].[Current User],
[Users/History].[Date Issued], [Users/History].[Date returned], [Items
Details].[Item Description]
FROM [Items Details] LEFT JOIN [Users/History] ON [Items Details].[Item
BCL
Asset Number]=[Users/History].ItemID
WHERE ((([Users/History].[Current User]) Is Null));

Does anyone know how I would go about making the query tell me if an item
had a current user but that they also have a returned date and now has no
current user assigned?

Any advice would be appreciated. Please let me know if you need anymore
info.

Thanks
Click to expand...
Click to expand...
 
A

Allen Browne

It's the whole thing.

The subquery is the SELECT statement in the WHERE clause of the main query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Nikki said:
Thanks.

Is the query below the whole query including the sub query or just the sub
query?


SELECT [Items Details].[Item Model], [Items Details].[Item Serial Number],
[Items Details].[Item BCL Asset Number]
FROM [Items Details] LEFT JOIN [Users/History] ON [Items Details].[Item
BCL
Asset Number] = [Users/History].ItemID
WHERE ((((SELECT TOP 1 [Users/History].[Date returned]
FROM [Users/History]
WHERE [Users/History].ItemID =
[Items Details].[Item BCL Asset Number]
ORDER BY [Users/History].[Date Issued] DESC,
[Users/History].[HistoryID])) Is Null));


Allen Browne said:
Presumably the item is in use if the *last* time it was issued, the [Date
returned] is null, or if it has never been issued.

You could use a subquery to give you that info.
Something like this:

SELECT [Items Details].[Item Model],
[Items Details].[Item Serial Number],
[Items Details].[Item BCL Asset Number]
FROM [Items Details]
WHERE (SELECT TOP 1 [Users/History].[Date returned]
FROM [Users/History]
WHERE [Users/History].ItemID =
[Items Details].[Item BCL Asset Number]
ORDER BY [Users/History].[Date Issued] DESC,
[Users/History].[ID]) Is Null;

Replace the ID in the last line with the name of the primary key of
[Users/History].

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

If you need other fields as well, see:
http://www.mvps.org/access/queries/qry0020.htm

Nikki said:
Hi,

I don't know what this would be called so couldn't really have a
discriptive
title....

I have a database where we record all of the IT equipment used in our
team
at work. When someone leaves or someone new starts the database gets
updated
and we can see the history of the item (who has used it in the past).

I have a table of employees, a table of the items and descriptions and
another table for recording the history.

Everything works fine for my form and most of my reports but I am
wanting
a
report that shows any items that are currently spare. I cannot seem to
get
it to show items that have been used in the past but are now spare.

This is my current query that just shows which items have never been
assigned to someone:
SELECT [Items Details].[Item Model], [Items Details].[Item Serial
Number],
[Items Details].[Item BCL Asset Number], [Users/History].[Current
User],
[Users/History].[Date Issued], [Users/History].[Date returned], [Items
Details].[Item Description]
FROM [Items Details] LEFT JOIN [Users/History] ON [Items Details].[Item
BCL
Asset Number]=[Users/History].ItemID
WHERE ((([Users/History].[Current User]) Is Null));

Does anyone know how I would go about making the query tell me if an
item
had a current user but that they also have a returned date and now has
no
current user assigned?

Any advice would be appreciated. Please let me know if you need
anymore
info.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

That's what i thought. It displays 65 records thought. There are 55 in the
database so it must be displaying some twice.

There is only 3 items that have no current user.

Any ideas?

Allen Browne said:
It's the whole thing.

The subquery is the SELECT statement in the WHERE clause of the main query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Nikki said:
Thanks.

Is the query below the whole query including the sub query or just the sub
query?


SELECT [Items Details].[Item Model], [Items Details].[Item Serial Number],
[Items Details].[Item BCL Asset Number]
FROM [Items Details] LEFT JOIN [Users/History] ON [Items Details].[Item
BCL
Asset Number] = [Users/History].ItemID
WHERE ((((SELECT TOP 1 [Users/History].[Date returned]
FROM [Users/History]
WHERE [Users/History].ItemID =
[Items Details].[Item BCL Asset Number]
ORDER BY [Users/History].[Date Issued] DESC,
[Users/History].[HistoryID])) Is Null));


Allen Browne said:
Presumably the item is in use if the *last* time it was issued, the [Date
returned] is null, or if it has never been issued.

You could use a subquery to give you that info.
Something like this:

SELECT [Items Details].[Item Model],
[Items Details].[Item Serial Number],
[Items Details].[Item BCL Asset Number]
FROM [Items Details]
WHERE (SELECT TOP 1 [Users/History].[Date returned]
FROM [Users/History]
WHERE [Users/History].ItemID =
[Items Details].[Item BCL Asset Number]
ORDER BY [Users/History].[Date Issued] DESC,
[Users/History].[ID]) Is Null;

Replace the ID in the last line with the name of the primary key of
[Users/History].

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

If you need other fields as well, see:
http://www.mvps.org/access/queries/qry0020.htm

Hi,

I don't know what this would be called so couldn't really have a
discriptive
title....

I have a database where we record all of the IT equipment used in our
team
at work. When someone leaves or someone new starts the database gets
updated
and we can see the history of the item (who has used it in the past).

I have a table of employees, a table of the items and descriptions and
another table for recording the history.

Everything works fine for my form and most of my reports but I am
wanting
a
report that shows any items that are currently spare. I cannot seem to
get
it to show items that have been used in the past but are now spare.

This is my current query that just shows which items have never been
assigned to someone:
SELECT [Items Details].[Item Model], [Items Details].[Item Serial
Number],
[Items Details].[Item BCL Asset Number], [Users/History].[Current
User],
[Users/History].[Date Issued], [Users/History].[Date returned], [Items
Details].[Item Description]
FROM [Items Details] LEFT JOIN [Users/History] ON [Items Details].[Item
BCL
Asset Number]=[Users/History].ItemID
WHERE ((([Users/History].[Current User]) Is Null));

Does anyone know how I would go about making the query tell me if an
item
had a current user but that they also have a returned date and now has
no
current user assigned?

Any advice would be appreciated. Please let me know if you need
anymore
info.
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Will have to leave you to debug the details, Nikki.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Nikki said:
That's what i thought. It displays 65 records thought. There are 55 in
the
database so it must be displaying some twice.

There is only 3 items that have no current user.

Any ideas?

Allen Browne said:
It's the whole thing.

The subquery is the SELECT statement in the WHERE clause of the main
query.

Nikki said:
Thanks.

Is the query below the whole query including the sub query or just the
sub
query?


SELECT [Items Details].[Item Model], [Items Details].[Item Serial
Number],
[Items Details].[Item BCL Asset Number]
FROM [Items Details] LEFT JOIN [Users/History] ON [Items Details].[Item
BCL
Asset Number] = [Users/History].ItemID
WHERE ((((SELECT TOP 1 [Users/History].[Date returned]
FROM [Users/History]
WHERE [Users/History].ItemID =
[Items Details].[Item BCL Asset Number]
ORDER BY [Users/History].[Date Issued] DESC,
[Users/History].[HistoryID])) Is Null));


:

Presumably the item is in use if the *last* time it was issued, the
[Date
returned] is null, or if it has never been issued.

You could use a subquery to give you that info.
Something like this:

SELECT [Items Details].[Item Model],
[Items Details].[Item Serial Number],
[Items Details].[Item BCL Asset Number]
FROM [Items Details]
WHERE (SELECT TOP 1 [Users/History].[Date returned]
FROM [Users/History]
WHERE [Users/History].ItemID =
[Items Details].[Item BCL Asset Number]
ORDER BY [Users/History].[Date Issued] DESC,
[Users/History].[ID]) Is Null;

Replace the ID in the last line with the name of the primary key of
[Users/History].

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

If you need other fields as well, see:
http://www.mvps.org/access/queries/qry0020.htm

Hi,

I don't know what this would be called so couldn't really have a
discriptive
title....

I have a database where we record all of the IT equipment used in
our
team
at work. When someone leaves or someone new starts the database
gets
updated
and we can see the history of the item (who has used it in the
past).

I have a table of employees, a table of the items and descriptions
and
another table for recording the history.

Everything works fine for my form and most of my reports but I am
wanting
a
report that shows any items that are currently spare. I cannot seem
to
get
it to show items that have been used in the past but are now spare.

This is my current query that just shows which items have never been
assigned to someone:
SELECT [Items Details].[Item Model], [Items Details].[Item Serial
Number],
[Items Details].[Item BCL Asset Number], [Users/History].[Current
User],
[Users/History].[Date Issued], [Users/History].[Date returned],
[Items
Details].[Item Description]
FROM [Items Details] LEFT JOIN [Users/History] ON [Items
Details].[Item
BCL
Asset Number]=[Users/History].ItemID
WHERE ((([Users/History].[Current User]) Is Null));

Does anyone know how I would go about making the query tell me if an
item
had a current user but that they also have a returned date and now
has
no
current user assigned?

Any advice would be appreciated. Please let me know if you need
anymore
info.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help - Query for Report 5
Access Need help in making asset history in my current database. 1
IIf statement in query criteria 3
Adding query results to a table field 2
Query 3
Using IN( ) function in a query 7
Append Query Issue 0
Date questions in query 12

Top