General Design Advice for Tool Tracking (1 Viewer)

Zydeceltico

Registered User.
Local time
Today, 12:24
Joined
Dec 5, 2017
Messages
843
Hi All -

I work as a QC and Field Inspector for a roll forming company. Airports and Stadiums around the US and some outside of the US utilize our product in the ceilings. We have a special and expensive tool that we supply to the subcontractors of our customers to assist in the installation of our product. Currently we have twenty tools (they are all identical and uniquely numbered with a two-digit serial; 01 - 20). I am responsible for contacting the customer to identify the best jobsite contact to send the tool too. As most of these jobs can take months to install, I have a need to be able to accurately track where they are at any given time and who to contact to ship them or have them returned to our shop.

We do not charge for usage of the tool. It is costed into the jobs. Our jobs all have a discrete, unique identifier (TE5850, TE910, TE6140 - the TE is a constant and the 4-digit suffix increments by 1(one) as sales contracts are finalized).

The typical order of operations is that I see a job coming on the radar that requires this tool. I contact the customer a week in advance of the first delivery of our product to their jobsite. The purpose of this initial contact is to identify the jobsite erector (usually a different contact than the customer - but not always). Then I call the jobsite contact and acquire a firm shipping address. Then I ship the tool. I also get an estimate of how long the erector thinks their install will take so I can set a date to call to remind them to return the tool.

Attached is my initial thought about DB design to track all of this.

I am hoping to be able to accomplish the following:

1. From a form, enter a new job, customer contact info, and estimated date of first product delivery and any notes.
2. From a form, enter the jobsite contact information - which again may or may not be the same as customer contact. We have many repeat customers and they have many repeat subcontractors. This almost always happens weeks or months after the initial "new job entry."
3. From a form, enter shipping to jobsite details, UPS tracking, date of shipment, etc.
4. From a form, enter return shipping details.
5. Run a report that shows all upcoming jobs within a user-defined period by Date.
6. Run a report that shows how many and which tools are in the field and how many are on hand at the shop.
7. Run a report that shows all jobs in the field with "Date Shipped" in order to assess contacting jobsite contact to have tool returned.

Given all of that, does my attached DB appear to be initially designed to accomplish/accommodate my needs? Specifically the relationships and FKs?

And then, I should probably repost over on "Forms" to get some guidance on how to approach my data entry/edit forms.

Thank You So Much,

Tim
 

Attachments

  • Crimp Tool Tracker DB1.accdb
    976 KB · Views: 578
Last edited:

Minty

AWF VIP
Local time
Today, 16:24
Joined
Jul 26, 2013
Messages
10,354
I have only two thoughts, Firstly - I'm never sure storing attachments in a database is a good idea. It can cause the database to grow in size very quickly as the attachments take up significant space. It doesn't up scale well into something like SQL server either.

I would simply store a storage path, possibly in a folder that is the same name as your Job number. All the attachments for that job would be in one place, and you can easily interrogate that location and list the files contained in it, and open them directly from the database. Similarly with your tool attachments, although if it's only 20 pictures you can probably get away with it.

Second - And it's very minor, I would probably add a table called JobNotes, related back to your Job table. This will enable you to have time-stamp, employee record against multiple notes for the job. Adding multiple text lines into a large memo field for notes is messy, and not very elegant to report or display. It's also prone to deletions or partial deletions which can be troublesome.
 

plog

Banishment Pending
Local time
Today, 11:24
Joined
May 11, 2011
Messages
11,611
Overall it looks good. I do see a few issues or things I question:

Transactions - You shouldn't seperate the In/Out data. CheckedOutDate and CheckedInDate should become one field [TransactionDate], then you have an additional field called [TransactionType] which allows you designate what is happening. Additionally, this consolodiates the UPSTracking fields as well.

CrimpTools -I think the AcquiredDate and RetiredDate should come out of CrimpTools and go into Transactions as Transaction records. Also, if a tool needs to be repaired it should get a Transaction record.

Job - fields suffixed with numbers are generally incorrect. Why do you have ProductType1 and ProductType2 fields (same for SQS1 & 2). What does that data represent? Could there ver be ProductType3 or more?
 

Zydeceltico

Registered User.
Local time
Today, 12:24
Joined
Dec 5, 2017
Messages
843
Hi Minty - I see your point regarding storing attachments. Thanks.

And that is also good advice regarding the creation of a table "JobNotes." I see the logic in that given the various potentials for note taking and the time stamp is great and will be most helpful.

Thank You

Second - And it's very minor, I would probably add a table called JobNotes, related back to your Job table. This will enable you to have time-stamp, employee record against multiple notes for the job. Adding multiple text lines into a large memo field for notes is messy, and not very elegant to report or display. It's also prone to deletions or partial deletions which can be troublesome.
 

Zydeceltico

Registered User.
Local time
Today, 12:24
Joined
Dec 5, 2017
Messages
843
Transactions - You shouldn't seperate the In/Out data. CheckedOutDate and CheckedInDate should become one field [TransactionDate], then you have an additional field called [TransactionType] which allows you designate what is happening. Additionally, this consolodiates the UPSTracking fields as well.

CrimpTools -I think the AcquiredDate and RetiredDate should come out of CrimpTools and go into Transactions as Transaction records. Also, if a tool needs to be repaired it should get a Transaction record.

Job - fields suffixed with numbers are generally incorrect. Why do you have ProductType1 and ProductType2 fields (same for SQS1 & 2). What does that data represent? Could there ver be ProductType3 or more?

Hi plog - Good advice on the In/Out/Transaction data. I get it. Thanks.

I'm glad you question the ProductType fields. Typically (95% of the time) there is only one ProductType on a given job because the scope of most jobs is not that large (5K - 10K square feet). However, we are supplying product for an arena that has 3-1/2 acres of one type of Product and another 20K square feet of another Product - both of which use the tool. It isn't entirely critical to track the ProductType as far as the tool is concerned. There are questions from upper management at our company as to where the tool is being used and under what conditions (which typically is product related). I thought this would be a decent peripheral means of showing tool usage per product per square feet. "SQS" is our nomenclature for squares (=100 square feet in US construction).

Yes - there is some potential for a ProductType3 but it is highly unlikely.
 

plog

Banishment Pending
Local time
Today, 11:24
Joined
May 11, 2011
Messages
11,611
Yes - there is some potential for a ProductType3 but it is highly unlikely

In that case I would build a new table to accomodate that data. That way, when the very unlikely event of having 4 product types occurs there's nothing you have to do to accomodate it.
 

Zydeceltico

Registered User.
Local time
Today, 12:24
Joined
Dec 5, 2017
Messages
843
Are you suggesting a new table similar to the "new" Transactions table except solely for ProductType?

-I just had an "a-ha" light bulb moment. I should likely model the process for ProductType like a Purchase Order - which is basically what it is.

Does that sound correct?
 
Last edited:

plog

Banishment Pending
Local time
Today, 11:24
Joined
May 11, 2011
Messages
11,611
The ProductType table would be in a 1 to many relationship with the Job table.

I don't know what you mean when you compare it to a Purchase Order. Mock something up and I will give it a look. Just post a screenshot of the new Relationship Tool.
 

Zydeceltico

Registered User.
Local time
Today, 12:24
Joined
Dec 5, 2017
Messages
843
I originally had ProductType as a one-to-many with Job but I was unsure how to record the second (3rd, 4th, etc.) ProductTypes. I created a second field in the Job table (ProductType2) to record this data if and when it occurred. The second ProductType table in Relationships is, as I understand it, an alias - the same is true of the CustomerContact versus JobsiteContact.

Is there a different way to accomplish this?

My challenge is knowing how to design for what comes "downstream" when I begin actually creating my data entry forms and report queries. I'm unsure how Access handles various design builds.
 
Last edited:

plog

Banishment Pending
Local time
Today, 11:24
Joined
May 11, 2011
Messages
11,611
For every ProductType of a Job you would add a new record to the ProductType table. 1 to many.

You could do the same for your Contacts, but if you always will have 2 then breaking normalization as you have done is fine.
 

Zydeceltico

Registered User.
Local time
Today, 12:24
Joined
Dec 5, 2017
Messages
843
I think I see what you mean now. Since we have a finite catalogue of ProductTypes - and I don't want to type them in every time, perhaps I have my ProductType table which stores the name of each ProductType. I also create a new table (JobProductTypes) that stores ProductTypes by Job with table Job (1) and table JobProductTypes (many).

Am I overthinking this?
 

plog

Banishment Pending
Local time
Today, 11:24
Joined
May 11, 2011
Messages
11,611
Its your data, I can't really get a handle on it without seeing something. Mock it up and I'll look at it.
 

MarkK

bit cruncher
Local time
Today, 09:24
Joined
Mar 17, 2004
Messages
8,178
I might not put all those foreign keys in the Job table, linking over to Contact and Salesperson. I would be most likely to create a Person table, which would contain everyone, contacts and salespeople, and whoever else. Then I would create a many-to-many relationship between Job and Person, say JobPerson, and in that table I would define the role the person plays in respect to the job.
tJob
JobID (PK)
JobName

tPerson
PersonID (PK)
FirstName
LastName

tJobPerson
JobPersonID (PK)
JobID (FK)
PersonID (FK)
Role
And for Role, it could just be a free text field, you could use a combo with an SQL SELECT DISTINCT statement to draw previously used free text, or you could link to a short list of Roles if you wanted more control, but this way your Job will have a list (maybe a subform) of associated people, AND your people will each have a list (maybe the same subform) of associated jobs. You have fewer tables, fewer fields in each table, and you have by some measures simplified the entire structure, and given it more flexibility.

hth
Mark
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:24
Joined
Feb 28, 2001
Messages
26,999
I should likely model the process for ProductType like a Purchase Order - which is basically what it is.

Just to toss in another few cents into the arena: You are using Access to "model" the flow of your business. If an item IS of type X, it should be in the MODEL as type X.

It is always your decision, but the closer your model is to reality, the easier it is to maintain at the conceptual and data flow level. Because when you want to know how it should behave, it simply should behave as closely as possible (logically speaking) to reality. So I am saying that I agree with your idea that if it looks like a duck, quacks like a duck, and waddles like a duck, model it as a duck.

You are also doing something already for which you have my commendation. I usually give people a couple of practical rules regarding Access design.

Old Programmer's Rule #1: If you can't do it on paper, you can't do it in Access.

You are designing up front, enumerating the things you hope to do, dealing with how to get data into your system with your forms and out of your system with reports. Don't stop this design process until you have visited all of your intended abilities. Because once it is designed well enough that you know ON PAPER how you want it to work, you CAN do it in Access. Hope you see my point. I want to reinforce and emphasize the value of the up-front design work. I've never NOT seen it pay off.

While you are in this design stage, here's the other rule.

Old Programmer's Rule #2: Access can't tell you anything you didn't tell it first - or at least tell it how to figure out what to tell you.

One of the "sanity" tests for your design is to go over every one of the outputs you expect to see and verify that every element has a data source or a computation source. This might sound like you are working backwards, and perhaps to some extent, you are. But just verify that you have a way to define or default everything you want to see in your list of outputs. That also includes on-screen stuff computed for display purposes in forms.

You've got other great helpers on this problem so I'll not get in their way. But I wanted to give you the moral support that I think you are asking good questions at the right time. Keep it up!
 

Zydeceltico

Registered User.
Local time
Today, 12:24
Joined
Dec 5, 2017
Messages
843
Hi again everybody -

I would've quoted all the helpful info but there was a lot from a lot of folks - - so ---- Here's what I've distilled from all the above super helpful info.

I redesigned it using junction tables (thx plog and Mark) after realizing that I should model it to directly reflect my work flow (thx Doc) - et voila - it suddenly made sense. I'm pretty sure you will find it much cleaner ad straight ahead and reflective of how I actually enter and retrieve the data I need.

I'm looking forward to more comments and suggestions on the basic design. I'm sure it can be polished and improved upon via more of your experience.

Given that I will likely be using a form (frmJob) to add new jobs - I'll begin asking questions about data entry/subforms/and lookup tables (and how they differ from lookup fields as googling that question just makes me more confused). I'm thinking, for instance of the user being able to select from a drop down menu to populate Contacts and Product Types - - I am assuming that is a question for a different area of the forum? C'est correcte?

Thanks,

Tim
 

Attachments

  • Crimp Tool Tracker DB1.accdb
    1.6 MB · Views: 538
Last edited:

Minty

AWF VIP
Local time
Today, 16:24
Joined
Jul 26, 2013
Messages
10,354
Amongst all the good advice - don't use Lookup Fields - they are confusing in the extreme, http://access.mvps.org/access/lookupfields.htm , stick with lookup tables.

If you are ever likely to migrate to a SQL server backend, make sure they have a primary key field. I would normally just use an autonumber.
 

Zydeceltico

Registered User.
Local time
Today, 12:24
Joined
Dec 5, 2017
Messages
843
Amongst all the good advice - don't use Lookup Fields - they are confusing in the extreme , stick with lookup tables.

I'm already confused! :)

Kidding aside, are you able to point me at a means/procedure/example of using a lookup table? How to implement one. Everything I find on the internet so far either appears to be the nefarious lookup field or isn't clear enough for me to tell the difference.

I guess a different way of asking this question is: My table JobProducts has a one to many relationship with table ProductType. JobProducts matches JobID with one or more product types which exist as a simple static list in table ProductType. To enter information about a Job, I will want to open a form called frmJobProducts where I will "tell" JobID (via junction table JobProducts) which ProductTypes it is associated with. This will almost certainly happen on a form with a subform (frmJobProducts; subform ProductTypes

When the user is ready to enter the ProductType I want them to select from a dropdown rather than type.

I have been experimenting with the Lookup Wizard and it appears to do what I want (on the surface). But after all I have googled I am really unclear if I am utilizing a Lookup Table or Field.

I do know that when I open table JobProducts in Design View and examine field ProdType_FK, on the Lookup tab I have combo box selected; Row/Source = Table?Query and Row Source = SELECT [ProductType].[ProdTypId], [ProductType].[ProductType] FROM ProductType ORDER BY [ProductType];

Like I said, it appears to work for my needs but I have no earthly idea if this is the dreaded Lookup Field versus Lookup Table.

Thanks so much!

Tim
 

Users who are viewing this thread

Top Bottom