When to use text vs. Number for field

[bigalpha]

To me if it looks like a Number then it is probally a Number. Treat it as such.

You have three component that form the end code. Each of which should be stored in separate Fields. Each one of those should be lookups to another table for Normalisation purposes.

Sorting searching etc can then be done on any of the three components or any combination of the three.

Concatenation and Formating should be done last as part of the Record Source for the Form or Report.

Best wishes for which ever way you go. The one important point I have made is to do with the Normalisation of the table. I am surprised that ChrisO was the only person to pick up on this important issue.

My data is separated into different tables, with relationships between them, using PKs and FKs. Each level has it's own table.

For example:
tblcat1
Cat 1 ID PK
Cat 1 ID
Cat 1 Description

tblCat2
Cat 2 ID PK
Cat 2 ID
Cat 2 Description
Cat 1 ID FK

tblCat3
Cat 3 ID PK
Cat 3 ID
Cat 3 Description
Cat 1 ID FK (I realize this probably didn't need to be added in here)
Cat 2 ID FK

 

[bigalpha]

I hope all of that stuff makes sense and sheds a little more light on things. I didn't necessarily intent to get this in-the-weeds with it, but I really do appreciate all the help and advice.

 

[RainLover]

My data is separated into different tables, with relationships between them, using PKs and FKs. Each level has it's own table.

For example:
tblcat1
Cat 1 ID PK
Cat 1 ID
Cat 1 Description

tblCat2
Cat 2 ID PK
Cat 2 ID
Cat 2 Description
Cat 1 ID FK

tblCat3
Cat 3 ID PK
Cat 3 ID
Cat 3 Description
Cat 1 ID FK (I realize this probably didn't need to be added in here)
Cat 2 ID FK

I think you have a good approach. Your original question was about data type and I believe that this should be number.

With your tables above, I would remove all the Foreign Keys and create a Fourth Table with the three FKs in separate fields.

Then you just need to format and concatenate the result. Do you have a problem with that. If so I can post the string.

 

[bigalpha]

Sorry for the delay.

Why would you put all of the FKs in one table, instead of the way they are now? Does it lend a benefit? I'd appreciate it if you could post the string.

Thanks!

 

[RainLover]

The Fourth table would contain additional information. That is for you to work out what that information that is. Within that table you would have three fields that hold the foreign keys to the other 3 tables. This will allow you to build any combination that you want. I did not mean to suggest to put every possible combination into a fourth table.

Attached is a sample Database with some queries that demonstrate the Formating of the three fields.

 

[jdraw]

I think ChrisO was asking what exactly you have done and where do the pieces fit. I have a feeling you have not understood his post or may not be aware of things like
http://www.epa.gov/osw/inforesources/data/br91/na_apb-p.pdf

I could very well be way off, but it seems you may not have told us some of what you are working with - that could be useful when communicating with readers.

 

[bigalpha]

I see that I haven't adequately explained what we're doing. I've attached the three tables of data that we will be using as identification data.