Help - Query for Report

G

Guest

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?
 
G

Gary Walter

Nikki said:
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?
Click to expand...
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.
 
G

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
 
G

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]));

Gary Walter said:
Nikki said:
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?
Click to expand...
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.
Click to expand...
 
G

Guest

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?

Gary Walter said:
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]));

Gary Walter said:
Nikki said:
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?
Click to expand...
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.
Click to expand...
Click to expand...
 
G

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?
Click to expand...
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?
 

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 with query 6
IIf statement in query criteria 3
Access Need help in making asset history in my current database. 1
Sum and Join 1
Query with multiple criteria 1
Update checkbox on all but most recent Transaction 1
Latest due dates 1
removing duplicates using a criteria 3

Top