help with database design required

[vandy]

Hello Everyone,

I have a system that has to track the movement of tools from in and out of
the stores.

Borrower Details:
tblname: id-PK,name,department

tbltoolmaster
ToolID-PK
Toolno
ToolDesc
Location

tbltooltransaction
Tranid-PK
TrantoolID - FK
NameID - FK
DateTaken
DateReturned

what i am attempting to do is for every tool borrowed from the tool store i
need to be able to record the date taken and date returned.

If the date returned is not null - Tool is Available

Once the tool is burrowed by a person A on a given date than the status
should be updated to loaned and when he returns it should be updated to
available.

How do i do this .Do i store status in tbltoolmaster. how to update this
with every transaction ?

In the end of the day i should be able to account for how many tools are
loaned and how many are available in the store and who burrowed them.

how do i accomplish this. I would really appreciate your inputs .

thanks for your patience in reading this post.

 

[OldPro]

Hello Everyone,

I have a system that has to track the movement of tools from in and out of
the stores.

Borrower Details:
tblname: id-PK,name,department

tbltoolmaster
ToolID-PK
Toolno
ToolDesc
Location

tbltooltransaction
Tranid-PK
TrantoolID - FK
NameID - FK
DateTaken
DateReturned

what i am attempting to do is for every tool borrowed from the tool store i
need to be able to record the date taken and date returned.

If the date returned is not null - Tool is Available

Once the tool is burrowed by a person A on a given date than the status
should be updated to loaned and when he returns it should be updated to
available.

How do i do this .Do i store status in tbltoolmaster. how to update this
with every transaction ?

In the end of the day i should be able to account for how many tools are
loaned and how many are available in the store and who burrowed them.

how do i accomplish this. I would really appreciate your inputs .

thanks for your patience in reading this post.

You can change the status of the record in tbltoolmaster in the
AfterUpdate event of tbltooltransaction. Your fieldnames are
confusing. I will use some of my own in the following example...
BTW, you will have to create a boolean field called "Available". This
assumes that me.toolno is a numeric value.

dim db as dao.database
set db = currentdb()
db.execute "UPDATE tbltoolmaster SET [Available]=" & format(NOT
isnull(DateReturned)) & WHERE [ToolNo] = " & me!toolno
db.close
set db=nothing

 

[mscertified]

Your table design looks ok.
Don't store the status! It is already indicated by the state of your
transaction table i.e. if any tool has no return date then it is not
available.

-Dorian

 

[vandy]

Thanks for your reply.

My tool no is numeric like ET-2 how should i change this code.

Also you want the available feild to be a yes/no feild in the tblmaster
table is that correct.
thanks

Hello Everyone,

I have a system that has to track the movement of tools from in and out of
the stores.

Borrower Details:
tblname: id-PK,name,department

tbltoolmaster
ToolID-PK
Toolno
ToolDesc
Location

tbltooltransaction
Tranid-PK
TrantoolID - FK
NameID - FK
DateTaken
DateReturned

what i am attempting to do is for every tool borrowed from the tool store i
need to be able to record the date taken and date returned.

If the date returned is not null - Tool is Available

Once the tool is burrowed by a person A on a given date than the status
should be updated to loaned and when he returns it should be updated to
available.

How do i do this .Do i store status in tbltoolmaster. how to update this
with every transaction ?

In the end of the day i should be able to account for how many tools are
loaned and how many are available in the store and who burrowed them.

how do i accomplish this. I would really appreciate your inputs .

thanks for your patience in reading this post.

You can change the status of the record in tbltoolmaster in the
AfterUpdate event of tbltooltransaction. Your fieldnames are
confusing. I will use some of my own in the following example...
BTW, you will have to create a boolean field called "Available". This
assumes that me.toolno is a numeric value.

dim db as dao.database
set db = currentdb()
db.execute "UPDATE tbltoolmaster SET [Available]=" & format(NOT
isnull(DateReturned)) & WHERE [ToolNo] = " & me!toolno
db.close
set db=nothing

 

[mscertified]

See my reply. You should not be storing the status, it is superfluous!

-Dorian

Thanks for your reply.

My tool no is numeric like ET-2 how should i change this code.

Also you want the available feild to be a yes/no feild in the tblmaster
table is that correct.
thanks

Hello Everyone,

I have a system that has to track the movement of tools from in and out of
the stores.

Borrower Details:
tblname: id-PK,name,department

tbltoolmaster
ToolID-PK
Toolno
ToolDesc
Location

tbltooltransaction
Tranid-PK
TrantoolID - FK
NameID - FK
DateTaken
DateReturned

what i am attempting to do is for every tool borrowed from the tool store i
need to be able to record the date taken and date returned.

If the date returned is not null - Tool is Available

Once the tool is burrowed by a person A on a given date than the status
should be updated to loaned and when he returns it should be updated to
available.

How do i do this .Do i store status in tbltoolmaster. how to update this
with every transaction ?

In the end of the day i should be able to account for how many tools are
loaned and how many are available in the store and who burrowed them.

how do i accomplish this. I would really appreciate your inputs .

thanks for your patience in reading this post.

You can change the status of the record in tbltoolmaster in the
AfterUpdate event of tbltooltransaction. Your fieldnames are
confusing. I will use some of my own in the following example...
BTW, you will have to create a boolean field called "Available". This
assumes that me.toolno is a numeric value.

dim db as dao.database
set db = currentdb()
db.execute "UPDATE tbltoolmaster SET [Available]=" & format(NOT
isnull(DateReturned)) & WHERE [ToolNo] = " & me!toolno
db.close
set db=nothing

 

[OldPro]

See my reply. You should not be storing the status, it is superfluous!

-Dorian

To correct it, add single quotes like so:

dim db as dao.database
set db = currentdb()
db.execute "UPDATE tbltoolmaster SET [Available]=" & format(NOT
isnull(DateReturned)) & WHERE [ToolNo] = ' " & me!toolno & " ' "
db.close
set db=nothing

Dorian is correct in that it is a general rule of thumb to not
duplicate values in a database. It creates the potential for
competing values when one is in error. However, in this case the
choice is between having an explicit field that shows the current
status for each tool, or inferring the status from whether the return
date is missing from the latest record in a transaction table. The
later is more complicated and has the potential of error if any of the
assumptions for any reason become false, either presently or with any
future code additions. I would choose the explicit [Available] field;
just make sure it gets updated properly. And yes, it should be a Yes/
No field.