Help - Query for Report

[Nikki]

Hi,

I have a database that is to help me keep track of all the computer
equipment in the office. I have created a form that lets me put in the item
and then a subform records the history of the item (who has it, when they
return it and then who it is given to) The have set it up so that the
subform is a tabular type form to enable each new record to be a new line.
This all works fine.....but now I want to create a report that shows me what
items I have spare. This would be that I would want to know which items have
had a return date entered but have not yet been issued with a new user.
I thought I had it by using the following query

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)) OR
((([Users/History].[Date Issued]) Is Not Null) AND (([Users/History].[Date
returned]) Is Not Null));

....But this returns all of the items that have a return date even if they
have been assigned a new user. Is there a way around this?

Let me know if you need more information?

 

[Gary Walter]

I have a database that is to help me keep track of all the computer
equipment in the office. I have created a form that lets me put in the
item
and then a subform records the history of the item (who has it, when they
return it and then who it is given to) The have set it up so that the
subform is a tabular type form to enable each new record to be a new line.
This all works fine.....but now I want to create a report that shows me
what
items I have spare. This would be that I would want to know which items
have
had a return date entered but have not yet been issued with a new user.
I thought I had it by using the following query

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)) OR
((([Users/History].[Date Issued]) Is Not Null) AND (([Users/History].[Date
returned]) Is Not Null));

...But this returns all of the items that have a return date even if they
have been assigned a new user. Is there a way around this?

If I understand correctly, I imagine you want
something like:

SELECT
I.[Item Model],
I.[Item Serial Number],
I.[Item BCL Asset Number],
H.[Current User],
H.[Date Issued],
H.[Date returned],
I.[Item Description]
FROM
[Items Details] As I
LEFT JOIN
[Users/History] As H
ON
I.[Item BCL Asset Number] = H.ItemID
WHERE
H.ItemID IN
(SELECT
q.ItemID
FROM
[Item Model] As q
WHERE
q.ItemId = H.ItemID
AND
Max([q.Date Issued]) > Max([q.Date Returned]));

the correlated subquery is "saying"

when I am looking at a record in the main query
and deciding whether to return it in the result set,
check item table for this ID and see if the oldest
(Max) issue date for that item is older than (>)
the oldest (Max) return date for that item.
If so, return that main query's record.

 

[Gary Walter]

BTW, if you are not too far along in your project,
you really should rethink your object names.

For sure do not use any punctuation in the
table and field names (except for maybe an
underscore which works well in place of a
SPACE).

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

 

[Gary Walter]

I missed detail of your outer join
(not to mention there's no "itemID"
in [Item BCL Asset Number] table...)

SELECT
I.[Item Model],
I.[Item Serial Number],
I.[Item BCL Asset Number],
H.[Current User],
H.[Date Issued],
H.[Date returned],
I.[Item Description]
FROM
[Items Details] As I
LEFT JOIN
[Users/History] As H
ON
I.[Item BCL Asset Number] = H.ItemID
WHERE
I.[Item BCL Asset Number]
IN
(SELECT
q.[Item BCL Asset Number]
FROM
[Item Model] As q
WHERE
q.[Item BCL Asset Number] = I.[Item BCL Asset Number]
AND
Max([q.Date Issued]) > Max([q.Date Returned]));

I have a database that is to help me keep track of all the computer
equipment in the office. I have created a form that lets me put in the
item
and then a subform records the history of the item (who has it, when they
return it and then who it is given to) The have set it up so that the
subform is a tabular type form to enable each new record to be a new
line.
This all works fine.....but now I want to create a report that shows me
what
items I have spare. This would be that I would want to know which items
have
had a return date entered but have not yet been issued with a new user.
I thought I had it by using the following query

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)) OR
((([Users/History].[Date Issued]) Is Not Null) AND
(([Users/History].[Date
returned]) Is Not Null));

...But this returns all of the items that have a return date even if they
have been assigned a new user. Is there a way around this?

If I understand correctly, I imagine you want
something like:

SELECT
I.[Item Model],
I.[Item Serial Number],
I.[Item BCL Asset Number],
H.[Current User],
H.[Date Issued],
H.[Date returned],
I.[Item Description]
FROM
[Items Details] As I
LEFT JOIN
[Users/History] As H
ON
I.[Item BCL Asset Number] = H.ItemID
WHERE
H.ItemID IN
(SELECT
q.ItemID
FROM
[Item Model] As q
WHERE
q.ItemId = H.ItemID
AND
Max([q.Date Issued]) > Max([q.Date Returned]));

the correlated subquery is "saying"

when I am looking at a record in the main query
and deciding whether to return it in the result set,
check item table for this ID and see if the oldest
(Max) issue date for that item is older than (>)
the oldest (Max) return date for that item.
If so, return that main query's record.

 

[Nikki]

Hi,

I am a little confused about what if any parts I am needing to change within
your query example you gave me? Do I leave it exactly as you gave it or do I
need to change parts to fit my query?

I missed detail of your outer join
(not to mention there's no "itemID"
in [Item BCL Asset Number] table...)

SELECT
I.[Item Model],
I.[Item Serial Number],
I.[Item BCL Asset Number],
H.[Current User],
H.[Date Issued],
H.[Date returned],
I.[Item Description]
FROM
[Items Details] As I
LEFT JOIN
[Users/History] As H
ON
I.[Item BCL Asset Number] = H.ItemID
WHERE
I.[Item BCL Asset Number]
IN
(SELECT
q.[Item BCL Asset Number]
FROM
[Item Model] As q
WHERE
q.[Item BCL Asset Number] = I.[Item BCL Asset Number]
AND
Max([q.Date Issued]) > Max([q.Date Returned]));

I have a database that is to help me keep track of all the computer
equipment in the office. I have created a form that lets me put in the
item
and then a subform records the history of the item (who has it, when they
return it and then who it is given to) The have set it up so that the
subform is a tabular type form to enable each new record to be a new
line.
This all works fine.....but now I want to create a report that shows me
what
items I have spare. This would be that I would want to know which items
have
had a return date entered but have not yet been issued with a new user.
I thought I had it by using the following query

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)) OR
((([Users/History].[Date Issued]) Is Not Null) AND
(([Users/History].[Date
returned]) Is Not Null));

...But this returns all of the items that have a return date even if they
have been assigned a new user. Is there a way around this?

If I understand correctly, I imagine you want
something like:

SELECT
I.[Item Model],
I.[Item Serial Number],
I.[Item BCL Asset Number],
H.[Current User],
H.[Date Issued],
H.[Date returned],
I.[Item Description]
FROM
[Items Details] As I
LEFT JOIN
[Users/History] As H
ON
I.[Item BCL Asset Number] = H.ItemID
WHERE
H.ItemID IN
(SELECT
q.ItemID
FROM
[Item Model] As q
WHERE
q.ItemId = H.ItemID
AND
Max([q.Date Issued]) > Max([q.Date Returned]));

the correlated subquery is "saying"

when I am looking at a record in the main query
and deciding whether to return it in the result set,
check item table for this ID and see if the oldest
(Max) issue date for that item is older than (>)
the oldest (Max) return date for that item.
If so, return that main query's record.

 

[Gary Walter]

"Nikki"wrote:

I am a little confused about what if any parts I am needing to change
within
your query example you gave me? Do I leave it exactly as you gave it or
do I
need to change parts to fit my query?

Sorry...my previous responses were not my
proudest moments...

Please start a new query,
go to SQL View,
and paste the following
into SQL View window:

SELECT
I.[Item Model],
I.[Item Serial Number],
I.[Item BCL Asset Number],
H.[Current User],
H.[Date Issued],
H.[Date returned],
I.[Item Description]
FROM
[Items Details] As I
INNER JOIN
[Users/History] As H
ON
I.[Item BCL Asset Number] = H.ItemID
GROUP BY
I.[Item Model],
I.[Item Serial Number],
I.[Item BCL Asset Number],
H.[Current User],
H.[Date Issued],
H.[Date returned],
I.[Item Description]
HAVING
Max([H.Date Issued]) > Max([H.Date Returned]));

Does this return the records you wanted?