Tooling Tracking

[Jamie Dickerson]

My company has just begun tracking our tooling and I have been put in charge
of the tooling log. Right now the log is done in Excel which I find extremly
slow and cumbersome. I have had tons of success transferring other trackable
data to Access, even with my limited knowldege and experience, but this one
has me stumped.

We would like to track each tool, its home location, current location,
customer and days in use. Here is my plan, would someone tell me if I am on
the right track. I fear I may have bitten off more than I can chew and will
have to suffer with Excel.

I have begun by creating a table with the Home Location (primary key),
tooling #, and customer. I set the HomeLoc as the primary key as this is the
only information that will not have duplicates. I will then create a second
table that links to the first to track the other information such as current
location, signed out day, signed in day, and other relevant information. Of
course I will need queries and forms to make entering and sorting the data
easier.

What I would like to see happen is I will open a form, choose/enter the home
location, enter the date the tool was taken from the shelf, where it was
taken to, etc. Upon the tools return, I would like to open the same form,
choose/enter the home location, then enter the return date information. I
then want to be able to generate reports that list how many days a tool was
in use (monthly, quarterly, yearly) and the tools current location.

 

[Clifford Bass]

Hi Jamie,

What exactly is "home location"? A uniquely assigned location where
one and only one tool is stored? Or a more general location such as a room
in a building? What happens when a particular tool is
lost/stolen/retired/sold/etc.? Does a new tool take its place in the exact
same location? Do I take it correctly that tool numbers can be reassigned?
Perhaps when one is replaced? If so, perhaps not a good idea for tracking
purposes. When a tool is checked out, and you record its current location,
what exactly is that? Does it differ in type from the home location? For
example: A current location of "customer xyz at address such and such" as
opposed a home location of "tool slot #123". Is there other information that
would be helpful in understanding your situation?

Clifford Bass

 

[Damon Heron]

You are right in focusing on the tables first, here is how I would set it
up:

tblTools
ToolID (autonumber - PK)
ToolName
LocID (FK)

tblLocation
LocID
LocName

tblCustomers
CustID
CustName
etc.....

tblToolUse
UseID
ToolID (FK)
LocID (FK)
CustID (FK)
DateOut
DateIn

Setup a form with the tblTool as recordsource and have a subform with tblUse
(or a query adding locname and customername) as source. Link on ToolID.
Of course you will have a Location form and a Customer form, (which could
use the same subform as frmTools) which would be used infrequently.

Damon

 

[Jamie Dickerson]

Thank You for your help. It is greatly appreciated.

 

[Orlando]

You are right in focusing on the tables first, here is how I would set it
up:

tblTools
ToolID (autonumber - PK)
ToolName
LocID (FK)

tblLocation
LocID
LocName

tblCustomers
CustID
CustName
etc.....

tblToolUse
UseID
ToolID (FK)
LocID (FK)
CustID (FK)
DateOut
DateIn

Setup a form with the tblTool as recordsource and have a subform with
tblUse (or a query adding locname and customername) as source. Link on
ToolID.
Of course you will have a Location form and a Customer form, (which could
use the same subform as frmTools) which would be used infrequently.

Damon