Finding portion of a string (1 Viewer)

Hi All -

I am designing a db for the manufacturing plant where I work in QC.

I'm trying to make a decision about how to store/record our job numbers.

A typical job number looks like this: A591005001

Here's the thing there are actually 3 pieces of information contained in that string.

It is broken up as follows: A5910 | 050 | 01.

"A5910" is the actual project/job identifying number.

"050" is what we call the "Task." Large jobs are managed through smaller tasks which are usually driven by customers scheduling. There are usually multiple tasks associated with a given job number. So job A5910 might have A591005001, A591006001, A591006003, etc. But they are all the same project.

"01" is the resource. There can be multiple resources under a given Task. In other words, the "01" could also be "02" through "99".

Typically though there are only a few resources allotted to a given task.
AND.....in the QC department we are rarely concerned about the resource. There is almost always more of a focus on the task.

In any regard, I want to record the entire number and have been breaking it up by Job, Task, Resource as fields in tblJobNumber but I think this is inefficient.

The reason I have been doing this is that I want to be able to query the entire job at some point. I'm thinking though that it will be a better design to record the entire 10 character string in a single field as that number is used repeatedly throughout multiple process inspections and the db would be better served by recording an ID rather than the entire number every time it is recorded.

And though I know it is possible to parse the first 5 characters (e.g., "A5910") and use this string in a query - I don't know how to do it.

So - - - In general how would I approach a query where I wanted to find all records for A5910 to include all tasks and resources assuming the following tables and ID fields:
tblJobTaskResource (PK = JTR_PK, autonumber & a field called JobTaskResource which contains the 10 character string)
tblInspections (FK = JTR_FK, number)

Thank You!

Tim

in a query add these fields:

proj: left([jobnum],5)
task: mid([jobnum],6,3)
resource: right([jobnum],2)

Reading through what you wrote, I believe you've made quite a few errors in your understanding of how databases should relate.

tblJobTaskResource (PK = JTR_PK, autonumber & a field called JobTaskResource which contains the 10 character string)

Click to expand...

So tblJobTaskResource uses an autonumber as well as another field as its primary key? That's not correct. You simply need the autonumber as the primary key. The sole purpose of a primary key is to be unique. That's guaranteed with the autonumber alone, no need for the additional field.

tblInspections (FK = JTR_FK, number)

Click to expand...

...the entire 10 character string in a single field as that number is used repeatedly...

Click to expand...

Does that mean the foreign key is the autonumber or the 10 character field or both? The correct answer is the foreign key is just the autonumber value of whatever table it links to. That 10 digit string should only appear in one table.

Also, that 10 character string that contains 3 pieces of data should be 3 fields. Discrete pieces of data need to be stored discretely. Doing so would allow you to make sure each piece is valid upon entering it, putting strings together is a lot easier, efficient and less error prone than extracting them out, allows you to JOIN on the individual fields themselve if the need arises and makes querying just the values you want simpler. Keep them seperate.

plog said:

So tblJobTaskResource uses an autonumber as well as another field as its primary key? That's not correct. You simply need the autonumber as the primary key. The sole purpose of a primary key is to be unique. That's guaranteed with the autonumber alone, no need for the additional field.

Does that mean the foreign key is the autonumber or the 10 character field or both? The correct answer is the foreign key is just the autonumber value of whatever table it links to. That 10 digit string should only appear in one table.

Also, that 10 character string that contains 3 pieces of data should be 3 fields. Discrete pieces of data need to be stored discretely. Doing so would allow you to make sure each piece is valid upon entering it, putting strings together is a lot easier, efficient and less error prone than extracting them out, allows you to JOIN on the individual fields themselve if the need arises and makes querying just the values you want simpler. Keep them seperate.

Click to expand...

Hi again plog - No - There is only one primary key in tblJobTaskResource - Only One - the autonumber.

Yes - the 10 digit string only appears in one table.

The 10 digit string containing 3 discrete pieces of data - and how I should handle that is precisely why I posted in the first place.

Until very recently - in fact since you told me in a different post that I should have a single field for Date and Time versus a separate field for each - I had 3 discrete fields for each piece of data - - - - -

This post is actually based on my understanding of your advice about Date/Time.

It seemed to me that if it is the case to store Date and Time in a single field then it would follow that maybe Job Task Resource should be stored in the same manner.

But that's just how I took your former advice.

Thanks as always!

Tim

ps. Maybe what I should practice is writing clearer posts.

The 10 digit string containing 3 discrete pieces of data - and how I should handle that is precisely why I posted in the first place.

Until very recently - in fact since you told me in a different post that I should have a single field for Date and Time versus a separate field for each - I had 3 discrete fields for each piece of data - - - - -

Click to expand...

I think that is an Apples to Oranges comparison. They are not the same thing. A date field in Access is actually a date time field so it is efficient to store them in one field. Storing a bunch of different types of data in one field and then having to dissect it is super inefficient. Go back to three fields. PKs really should not contain datat, but be efficient for joining data.

MajP said:

PKs really should not contain datat, but be efficient for joining data.

Click to expand...

Thanks. Again - the PK does not contain data. It is an autonumber. I really must have not been clear in my earlier post. I apologize.

As of right now, there are four fields in tblJobTaskResource: JTR_ID which is the PK and autonumber. The other three fields are discretely: JobNumber, Task, and Resource. Each of those are string datatypes.

I believe I will take everyone's advice and leave them as is in three fields.

Thank You,

Tim