Date Query help required

[vandy]

Hi All,

I have a database that keeps track of tools in the Tool Store.

Borrowers information

tblnamepk
id,Name,Department


tbltooltransaction
TranID-pk
NameID - FK for tblname
TranToolID - FK for tbltoolmaster
DateTaken - date tool was taken
DateReturned - date tool was returned
Status - available / loaned


tbtoolmaster

ToolID - pk
Toolno
ToolDesc
Manufacturer
ModelNo
SerialNo
Location


Relationship

tblname ~ tbltooltransaction
ID ~ NameID

tbltoolmaster ~ tbltooltransaction
ToolID ~ TranToolID


I am recording the transaction data who burrowed it , when and if the tool
is available or loaned.

When i query for available:


SELECT tbltoolmaster.NumetNo, tbltoolmaster.ToolDesc,
tbltoolmaster.Manufacturer, tbltoolmaster.ModelNo, tblname.Name,
tbltooltransaction.DOI, tbltooltransaction.DOR, tbltoolmaster.Location,
tbltooltransaction.Status
FROM tbltoolmaster INNER JOIN (tblname INNER JOIN tbltooltransaction ON
tblname.ID = tbltooltransaction.NameID) ON tbltoolmaster.ToolID =
tbltooltransaction.TranToolID
GROUP BY tbltoolmaster.NumetNo, tbltoolmaster.ToolDesc,
tbltoolmaster.Manufacturer, tbltoolmaster.ModelNo, tblname.Name,
tbltooltransaction.DOI, tbltooltransaction.DOR, tbltoolmaster.Location,
tbltooltransaction.Status
HAVING (((tbltooltransaction.Status)="Available"))
ORDER BY tbltoolmaster.NumetNo;

than all tools that are available are listed but once I loaned the tool and
run the loaned query it still shows the previous transaction for this tool as
available.
and next transaction as loaned.

eg.

ET-2 10/30/2007 11/01/2007 available

ET-2 11/02/2007 loaned


Loaned qry i get ET-2 as taken on 11/02/2007 and Date of Return is null

and available I get the date taken and returned.

Ideally once the tool is loaned i should have got returned on this date but
now loaned and not in the inventory.

Please help me out in sorting this logic.

thanks in advance

 

[KARL DEWEY]

Do not have Status in tbltooltransaction table. If you must then put it in
the tbtoolmaster. This will require an update each time there is a
transaction.

Better is to use a calculated status based on transaction table. The query
would check tbltooltransaction table for the TranToolID and DateReturned
equal null.

 

[KARL DEWEY]

P.S. You might want to add a field of Estimated_Return to the
tbltooltransaction. You could use in the calculation on tool avaiablity.

 

[vandy]

Thanks Karl for you reply.

I realized that Status should be in the Toolmaster rather than transaction.
Also Estimated_Return might not work since there is no restiriction or time
line in using the tool.

I was thinking in lines of having a LastDateReturned and LastDateIssued in
the ToolMaster.

If the LastDateReturned is null than the Tool is loaned to someone.

I have to update these date feilds with transaction data. Any suggestions as
to how to go about that.

thanks for your help

 

[KARL DEWEY]

Also Estimated_Return might not work since there is no restiriction or time
line in using the tool.
Even with no restrictions it might be nice to to know that the tool should
be back in two days verus two weeks.
No need to have in ToolMaster then have to update.
Not update but back new records for each transaction. Keep a history of
usage. This will help in planning - ordering more stock. You might want to
add tool groupings as I expect you will have more than one 1/4" flat-tip
screwdriver.

These queries will show is tool is in/out.
Tool_OutDate ---
SELECT tbltooltransaction.TranToolID, Max(tbltooltransaction.DateTaken) AS
MaxOfDateTaken
FROM tbltooltransaction
GROUP BY tbltooltransaction.TranToolID;

SELECT tbtoolmaster.ToolID, tbtoolmaster.Toolno, tbtoolmaster.ToolDesc,
IIf([datereturned] Is Not Null,"Tool out","On-hand") AS Available,
IIf([datereturned] Is Not Null,[DateTaken],"") AS [Tool out on]
FROM (tbtoolmaster INNER JOIN tbltooltransaction ON tbtoolmaster.ToolID =
tbltooltransaction.TranToolID) INNER JOIN Tool_OutDate ON
(tbltooltransaction.DateTaken = Tool_OutDate.MaxOfDateTaken) AND
(tbltooltransaction.TranToolID = Tool_OutDate.TranToolID)
GROUP BY tbtoolmaster.ToolID, tbtoolmaster.Toolno, tbtoolmaster.ToolDesc,
IIf([datereturned] Is Not Null,"Tool out","On-hand"), IIf([datereturned] Is
Not Null,[DateTaken],"");

 

[vandy]

Hi Karl,

Thanks for your useful pointers. I am working on your suggestions.


Clarifications:


tblnamepk
id,Name,Department

tbltooltransaction
TranID-pk
NameID - FK for tblname
TranToolID - FK for tbltoolmaster
DateTaken - date tool was taken
DateReturned - date tool was returned



tbtoolmaster

ToolID - pk
Toolno
ToolDesc
Manufacturer
ModelNo
SerialNo
Location
Status - Tool_out/on_hand - (text)
Estimated_Return - estimated return date


Relationship
tblname ~ tbltooltransaction
ID ~ NameID

tbltoolmaster ~ tbltooltransaction
ToolID ~ TranToolID

This is the corrected table structure. I hope this is correct.

Also wanted to understand the query below.
Would you be able to expain this query.
Tool_OutDate is this a table or another query. I would really appreciate if
you could give me a break down of this query to better understand its
implications.


SELECT tbtoolmaster.ToolID, tbtoolmaster.Toolno, tbtoolmaster.ToolDesc,
IIf([datereturned] Is Not Null,"Tool out","On-hand") AS Available,
IIf([datereturned] Is Not Null,[DateTaken],"") AS [Tool out on]
FROM (tbtoolmaster INNER JOIN tbltooltransaction ON tbtoolmaster.ToolID =
tbltooltransaction.TranToolID) INNER JOIN Tool_OutDate ON
(tbltooltransaction.DateTaken = Tool_OutDate.MaxOfDateTaken) AND
(tbltooltransaction.TranToolID = Tool_OutDate.TranToolID)
GROUP BY tbtoolmaster.ToolID, tbtoolmaster.Toolno, tbtoolmaster.ToolDesc,
IIf([datereturned] Is Not Null,"Tool out","On-hand"), IIf([datereturned] Is
Not Null,[DateTaken],"");


Thanks for your time and patience.

line in using the tool.
Even with no restrictions it might be nice to to know that the tool should
be back in two days verus two weeks.
No need to have in ToolMaster then have to update.
Not update but back new records for each transaction. Keep a history of
usage. This will help in planning - ordering more stock. You might want to
add tool groupings as I expect you will have more than one 1/4" flat-tip
screwdriver.

These queries will show is tool is in/out.
Tool_OutDate ---
SELECT tbltooltransaction.TranToolID, Max(tbltooltransaction.DateTaken) AS
MaxOfDateTaken
FROM tbltooltransaction
GROUP BY tbltooltransaction.TranToolID;

SELECT tbtoolmaster.ToolID, tbtoolmaster.Toolno, tbtoolmaster.ToolDesc,
IIf([datereturned] Is Not Null,"Tool out","On-hand") AS Available,
IIf([datereturned] Is Not Null,[DateTaken],"") AS [Tool out on]
FROM (tbtoolmaster INNER JOIN tbltooltransaction ON tbtoolmaster.ToolID =
tbltooltransaction.TranToolID) INNER JOIN Tool_OutDate ON
(tbltooltransaction.DateTaken = Tool_OutDate.MaxOfDateTaken) AND
(tbltooltransaction.TranToolID = Tool_OutDate.TranToolID)
GROUP BY tbtoolmaster.ToolID, tbtoolmaster.Toolno, tbtoolmaster.ToolDesc,
IIf([datereturned] Is Not Null,"Tool out","On-hand"), IIf([datereturned] Is
Not Null,[DateTaken],"");

--
KARL DEWEY
Build a little - Test a little

Thanks Karl for you reply.

I realized that Status should be in the Toolmaster rather than transaction.
Also Estimated_Return might not work since there is no restiriction or time
line in using the tool.

I was thinking in lines of having a LastDateReturned and LastDateIssued in
the ToolMaster.

If the LastDateReturned is null than the Tool is loaned to someone.

I have to update these date feilds with transaction data. Any suggestions as
to how to go about that.

thanks for your help

 

[vandy]

Hi Karl,

Did not mean to fill up this post but i was trying your solution and have
got the query working.

I now understand that the Status does not need to be stored but can be a
calculated field. I also have added an Estimated_Return in my tbltransaction.

I have a form were i select the tool thru a combo box and have another combo
box for name of the burrower. I have my tbltransaction subform attached on to
this main form. The comboboxnames are linked with the childsubform linked
feilds, NameID,TranToolID

When i select the tool and the burrower and enter the transaction dates than
the record gets saved .

I have a qry_toolstatus based on your post which tells me if the tool is
tool-out or tool-in.

What i want is when i select the tool no from the combo box the
qry_toolstatus should run which will tell me if the tool is in or out. If it
is in than i can select the name of the burrower who is taking it.

How to do this and ensure that the history of the dates and the name of the
burrower is getting recorded.

Should i have the combo box containing the tool nos and transaction data
subform in one and the name of the burrower in another form.

please advise. I have a lot to learn. Thanks for your patience.

line in using the tool.
Even with no restrictions it might be nice to to know that the tool should
be back in two days verus two weeks.
No need to have in ToolMaster then have to update.
Not update but back new records for each transaction. Keep a history of
usage. This will help in planning - ordering more stock. You might want to
add tool groupings as I expect you will have more than one 1/4" flat-tip
screwdriver.

These queries will show is tool is in/out.
Tool_OutDate ---
SELECT tbltooltransaction.TranToolID, Max(tbltooltransaction.DateTaken) AS
MaxOfDateTaken
FROM tbltooltransaction
GROUP BY tbltooltransaction.TranToolID;

SELECT tbtoolmaster.ToolID, tbtoolmaster.Toolno, tbtoolmaster.ToolDesc,
IIf([datereturned] Is Not Null,"Tool out","On-hand") AS Available,
IIf([datereturned] Is Not Null,[DateTaken],"") AS [Tool out on]
FROM (tbtoolmaster INNER JOIN tbltooltransaction ON tbtoolmaster.ToolID =
tbltooltransaction.TranToolID) INNER JOIN Tool_OutDate ON
(tbltooltransaction.DateTaken = Tool_OutDate.MaxOfDateTaken) AND
(tbltooltransaction.TranToolID = Tool_OutDate.TranToolID)
GROUP BY tbtoolmaster.ToolID, tbtoolmaster.Toolno, tbtoolmaster.ToolDesc,
IIf([datereturned] Is Not Null,"Tool out","On-hand"), IIf([datereturned] Is
Not Null,[DateTaken],"");

--
KARL DEWEY
Build a little - Test a little

Thanks Karl for you reply.

I realized that Status should be in the Toolmaster rather than transaction.
Also Estimated_Return might not work since there is no restiriction or time
line in using the tool.

I was thinking in lines of having a LastDateReturned and LastDateIssued in
the ToolMaster.

If the LastDateReturned is null than the Tool is loaned to someone.

I have to update these date feilds with transaction data. Any suggestions as
to how to go about that.

thanks for your help

 

[KARL DEWEY]

Have tool No. combo and Status on the main form. After selecting tool No. do
a refresh of status.
The borrower would be on the subform with transaction dates.
--
KARL DEWEY
Build a little - Test a little

Hi Karl,

Did not mean to fill up this post but i was trying your solution and have
got the query working.

I now understand that the Status does not need to be stored but can be a
calculated field. I also have added an Estimated_Return in my tbltransaction.

I have a form were i select the tool thru a combo box and have another combo
box for name of the burrower. I have my tbltransaction subform attached on to
this main form. The comboboxnames are linked with the childsubform linked
feilds, NameID,TranToolID

When i select the tool and the burrower and enter the transaction dates than
the record gets saved .

I have a qry_toolstatus based on your post which tells me if the tool is
tool-out or tool-in.

What i want is when i select the tool no from the combo box the
qry_toolstatus should run which will tell me if the tool is in or out. If it
is in than i can select the name of the burrower who is taking it.

How to do this and ensure that the history of the dates and the name of the
burrower is getting recorded.

Should i have the combo box containing the tool nos and transaction data
subform in one and the name of the burrower in another form.

please advise. I have a lot to learn. Thanks for your patience.

Also Estimated_Return might not work since there is no restiriction or time

line in using the tool.
Even with no restrictions it might be nice to to know that the tool should
be back in two days verus two weeks.

I was thinking in lines of having a LastDateReturned and LastDateIssued in the ToolMaster.

No need to have in ToolMaster then have to update.

I have to update these date feilds with transaction data. Any suggestions as to how to go about that.

Not update but back new records for each transaction. Keep a history of
usage. This will help in planning - ordering more stock. You might want to
add tool groupings as I expect you will have more than one 1/4" flat-tip
screwdriver.

These queries will show is tool is in/out.
Tool_OutDate ---
SELECT tbltooltransaction.TranToolID, Max(tbltooltransaction.DateTaken) AS
MaxOfDateTaken
FROM tbltooltransaction
GROUP BY tbltooltransaction.TranToolID;

SELECT tbtoolmaster.ToolID, tbtoolmaster.Toolno, tbtoolmaster.ToolDesc,
IIf([datereturned] Is Not Null,"Tool out","On-hand") AS Available,
IIf([datereturned] Is Not Null,[DateTaken],"") AS [Tool out on]
FROM (tbtoolmaster INNER JOIN tbltooltransaction ON tbtoolmaster.ToolID =
tbltooltransaction.TranToolID) INNER JOIN Tool_OutDate ON
(tbltooltransaction.DateTaken = Tool_OutDate.MaxOfDateTaken) AND
(tbltooltransaction.TranToolID = Tool_OutDate.TranToolID)
GROUP BY tbtoolmaster.ToolID, tbtoolmaster.Toolno, tbtoolmaster.ToolDesc,
IIf([datereturned] Is Not Null,"Tool out","On-hand"), IIf([datereturned] Is
Not Null,[DateTaken],"");

--
KARL DEWEY
Build a little - Test a little

Thanks Karl for you reply.

I realized that Status should be in the Toolmaster rather than transaction.
Also Estimated_Return might not work since there is no restiriction or time
line in using the tool.

I was thinking in lines of having a LastDateReturned and LastDateIssued in
the ToolMaster.

If the LastDateReturned is null than the Tool is loaned to someone.

I have to update these date feilds with transaction data. Any suggestions as
to how to go about that.

thanks for your help



:

P.S. You might want to add a field of Estimated_Return to the
tbltooltransaction. You could use in the calculation on tool avaiablity.
--
KARL DEWEY
Build a little - Test a little


:

Hi All,

I have a database that keeps track of tools in the Tool Store.

Borrowers information

tblnamepk
id,Name,Department


tbltooltransaction
TranID-pk
NameID - FK for tblname
TranToolID - FK for tbltoolmaster
DateTaken - date tool was taken
DateReturned - date tool was returned
Status - available / loaned


tbtoolmaster

ToolID - pk
Toolno
ToolDesc
Manufacturer
ModelNo
SerialNo
Location


Relationship

tblname ~ tbltooltransaction
ID ~ NameID

tbltoolmaster ~ tbltooltransaction
ToolID ~ TranToolID


I am recording the transaction data who burrowed it , when and if the tool
is available or loaned.

When i query for available:


SELECT tbltoolmaster.NumetNo, tbltoolmaster.ToolDesc,
tbltoolmaster.Manufacturer, tbltoolmaster.ModelNo, tblname.Name,
tbltooltransaction.DOI, tbltooltransaction.DOR, tbltoolmaster.Location,
tbltooltransaction.Status
FROM tbltoolmaster INNER JOIN (tblname INNER JOIN tbltooltransaction ON
tblname.ID = tbltooltransaction.NameID) ON tbltoolmaster.ToolID =
tbltooltransaction.TranToolID
GROUP BY tbltoolmaster.NumetNo, tbltoolmaster.ToolDesc,
tbltoolmaster.Manufacturer, tbltoolmaster.ModelNo, tblname.Name,
tbltooltransaction.DOI, tbltooltransaction.DOR, tbltoolmaster.Location,
tbltooltransaction.Status
HAVING (((tbltooltransaction.Status)="Available"))
ORDER BY tbltoolmaster.NumetNo;

than all tools that are available are listed but once I loaned the tool and
run the loaned query it still shows the previous transaction for this tool as
available.
and next transaction as loaned.

eg.

ET-2 10/30/2007 11/01/2007 available

ET-2 11/02/2007 loaned


Loaned qry i get ET-2 as taken on 11/02/2007 and Date of Return is null

and available I get the date taken and returned.

Ideally once the tool is loaned i should have got returned on this date but
now loaned and not in the inventory.

Please help me out in sorting this logic.

thanks in advance

 

[vandy]

I have designed a form which has a tool no combo box. when i select the tool
no and click a button called status the toolstatus_qry runs and tells me if
the tool is availble or not. Status is calculated but not stored as a feild
in the toolmaster table .

Is this correct.

Also how to combine burrower which is from tblname and transaction dates
which is from another table tbltransaction into one subform and link it to
the main form. I have created a form based on name.combobox and opened
subform based on transaction.

How to establish a connection between main form containing Tool id and
subform containing burrower and the corresponding childform transaction.

Have tool No. combo and Status on the main form. After selecting tool No. do
a refresh of status.
The borrower would be on the subform with transaction dates.
--
KARL DEWEY
Build a little - Test a little

Hi Karl,

Did not mean to fill up this post but i was trying your solution and have
got the query working.

I now understand that the Status does not need to be stored but can be a
calculated field. I also have added an Estimated_Return in my tbltransaction.

I have a form were i select the tool thru a combo box and have another combo
box for name of the burrower. I have my tbltransaction subform attached on to
this main form. The comboboxnames are linked with the childsubform linked
feilds, NameID,TranToolID

When i select the tool and the burrower and enter the transaction dates than
the record gets saved .

I have a qry_toolstatus based on your post which tells me if the tool is
tool-out or tool-in.

What i want is when i select the tool no from the combo box the
qry_toolstatus should run which will tell me if the tool is in or out. If it
is in than i can select the name of the burrower who is taking it.

How to do this and ensure that the history of the dates and the name of the
burrower is getting recorded.

Should i have the combo box containing the tool nos and transaction data
subform in one and the name of the burrower in another form.

please advise. I have a lot to learn. Thanks for your patience.

Also Estimated_Return might not work since there is no restiriction or time
line in using the tool.
Even with no restrictions it might be nice to to know that the tool should
be back in two days verus two weeks.

I was thinking in lines of having a LastDateReturned and LastDateIssued in the ToolMaster.
No need to have in ToolMaster then have to update.

I have to update these date feilds with transaction data. Any suggestions as to how to go about that.
Not update but back new records for each transaction. Keep a history of
usage. This will help in planning - ordering more stock. You might want to
add tool groupings as I expect you will have more than one 1/4" flat-tip
screwdriver.

These queries will show is tool is in/out.
Tool_OutDate ---
SELECT tbltooltransaction.TranToolID, Max(tbltooltransaction.DateTaken) AS
MaxOfDateTaken
FROM tbltooltransaction
GROUP BY tbltooltransaction.TranToolID;

SELECT tbtoolmaster.ToolID, tbtoolmaster.Toolno, tbtoolmaster.ToolDesc,
IIf([datereturned] Is Not Null,"Tool out","On-hand") AS Available,
IIf([datereturned] Is Not Null,[DateTaken],"") AS [Tool out on]
FROM (tbtoolmaster INNER JOIN tbltooltransaction ON tbtoolmaster.ToolID =
tbltooltransaction.TranToolID) INNER JOIN Tool_OutDate ON
(tbltooltransaction.DateTaken = Tool_OutDate.MaxOfDateTaken) AND
(tbltooltransaction.TranToolID = Tool_OutDate.TranToolID)
GROUP BY tbtoolmaster.ToolID, tbtoolmaster.Toolno, tbtoolmaster.ToolDesc,
IIf([datereturned] Is Not Null,"Tool out","On-hand"), IIf([datereturned] Is
Not Null,[DateTaken],"");

--
KARL DEWEY
Build a little - Test a little


:


Thanks Karl for you reply.

I realized that Status should be in the Toolmaster rather than transaction.
Also Estimated_Return might not work since there is no restiriction or time
line in using the tool.

I was thinking in lines of having a LastDateReturned and LastDateIssued in
the ToolMaster.

If the LastDateReturned is null than the Tool is loaned to someone.

I have to update these date feilds with transaction data. Any suggestions as
to how to go about that.

thanks for your help



:

P.S. You might want to add a field of Estimated_Return to the
tbltooltransaction. You could use in the calculation on tool avaiablity.
--
KARL DEWEY
Build a little - Test a little


:

Hi All,

I have a database that keeps track of tools in the Tool Store.

Borrowers information

tblnamepk
id,Name,Department


tbltooltransaction
TranID-pk
NameID - FK for tblname
TranToolID - FK for tbltoolmaster
DateTaken - date tool was taken
DateReturned - date tool was returned
Status - available / loaned


tbtoolmaster

ToolID - pk
Toolno
ToolDesc
Manufacturer
ModelNo
SerialNo
Location


Relationship

tblname ~ tbltooltransaction
ID ~ NameID

tbltoolmaster ~ tbltooltransaction
ToolID ~ TranToolID


I am recording the transaction data who burrowed it , when and if the tool
is available or loaned.

When i query for available:


SELECT tbltoolmaster.NumetNo, tbltoolmaster.ToolDesc,
tbltoolmaster.Manufacturer, tbltoolmaster.ModelNo, tblname.Name,
tbltooltransaction.DOI, tbltooltransaction.DOR, tbltoolmaster.Location,
tbltooltransaction.Status
FROM tbltoolmaster INNER JOIN (tblname INNER JOIN tbltooltransaction ON
tblname.ID = tbltooltransaction.NameID) ON tbltoolmaster.ToolID =
tbltooltransaction.TranToolID
GROUP BY tbltoolmaster.NumetNo, tbltoolmaster.ToolDesc,
tbltoolmaster.Manufacturer, tbltoolmaster.ModelNo, tblname.Name,
tbltooltransaction.DOI, tbltooltransaction.DOR, tbltoolmaster.Location,
tbltooltransaction.Status
HAVING (((tbltooltransaction.Status)="Available"))
ORDER BY tbltoolmaster.NumetNo;

than all tools that are available are listed but once I loaned the tool and
run the loaned query it still shows the previous transaction for this tool as
available.
and next transaction as loaned.

eg.

ET-2 10/30/2007 11/01/2007 available

ET-2 11/02/2007 loaned


Loaned qry i get ET-2 as taken on 11/02/2007 and Date of Return is null

and available I get the date taken and returned.

Ideally once the tool is loaned i should have got returned on this date but
now loaned and not in the inventory.

Please help me out in sorting this logic.

thanks in advance