Join fields into one

[fromthecauseway]

I'm trying to make a document register where the number is made up of various fields. Discipline, type, number (ME-RPT-0001) which is mechanical report number 1.
I have a tbl_documents which prompts for the discipline and type using combo box lookup from tbl_discipline and tbl_type. This is part of frm_documents. This works. What I want to do is join the 3 fields into 1, either just to display on the form or preferably save as a field in its own right which can then be referenced. I've tried this in the table, form and query but rather than ME-RPT-0001 get 1-1-0001. Can anyone offer some guidance?

 

[plog]

I'm trying to make a document register where the number is made up of various fields.

You do this in a query with a calculated field like so:

NewField: [Field1] & " - " & [Field2] & "-" & [Field3]

Then reference the query when you want to use NewField somewhere.

 

[theDBguy]

Hi. Welcome to AWF! Sounds like you may be using a lookup field at the table level. If so, I suggest you don't. Take a look here why.

The Access Web - The Evils of Lookup Fields in Tables

 

[fromthecauseway]

Yes. I have a table tbl_type made up of ID, typecode and typetext (ME and Mechanical). Tbl_documents then gets the value from there with a foreign key joined in relationships. Not sure how else I get the same result

 

[theDBguy]

Yes. I have a table tbl_type made up of ID, typecode and typetext (ME and Mechanical). Tbl_documents then gets the value from there with a foreign key joined in relationships. Not sure how else I get the same result

I would say don't use lookup fields and then use a query to get the result you want (with the text descriptions showing instead of the numbers).

 

[fromthecauseway]

Thanks for the help. Found a previous entry explaining the alternative to using lookup on tables so will try again.

 

[theDBguy]

Thanks for the help. Found a previous entry explaining the alternative to using lookup on tables so will try again.

Hi. Sounds good. Please let us know how it turns out. Good luck!

 

[zeroaccess]

Hi. Welcome to AWF! Sounds like you may be using a lookup field at the table level. If so, I suggest you don't. Take a look here why.

The Access Web - The Evils of Lookup Fields in Tables

You know, I've read this a couple of times, but it just isn't clicking for me. I'm just not convinced it's a big deal if you know what you're doing and it can in fact have some benefits for an admin.

 

[arnelgp]

I agree with you zeroaccess.
you can get those "text" by using DLookup() on your query or on Expression of
ControlSource of a textbox.

 

[Gasman]

You know, I've read this a couple of times, but it just isn't clicking for me. I'm just not convinced it's a big deal if you know what you're doing and it can in fact have some benefits for an admin.

That is the problem, most people that have those, do not, which is why they come here and other forums confused.

I still have one DB with table lookups. It is my Gazette DB, and only one table has the lookups, but every field except the ID field is a lookup, four fields in all. Seemed a good idea at the time. The DB is static now, so I see no reason to change it, but I now do know the drawbacks, and so quite happy not to use them anymore.
For experienced people, it is just a matter of preference perhaps, but for new users it is a minefield I believe.

 

[theDBguy]

You know, I've read this a couple of times, but it just isn't clicking for me. I'm just not convinced it's a big deal if you know what you're doing and it can in fact have some benefits for an admin.

I agree with @Gasman but fair is fair. That website focused on the negative aspects of lookup fields. Maybe you can show the positive aspects by giving us some examples of the "benefits" you experienced. Cheers!

 

[zeroaccess]

It's nice to be able to change a value without having to consult the lookup tables yourself. There are fields on forms I don't allow changes to once a record is completed - there is no control for the "Status" field for example - unless you ask the admin nicely.

 

[theDBguy]

It's nice to be able to change a value without having to consult the lookup tables yourself. There are fields on forms I don't allow changes to once a record is completed - there is no control for the "Status" field for example - unless you ask the admin nicely.

Sorry, I don't follow. Isn't this the job of a Combobox?

 

[Dreamweaver]

The only good thing I found when I used to use them in tables 20 years ish ago before I came here

when you create a field on a form if the table field is a lookup type it will create a combo already setup with rowsource Ect.

As I said I stopped using them when I got to old for them

keep safe mick

 

[Pat Hartman]

One of the downsides not frequently mentioned is that they are essentially one field tables. All my lookups require two columns minimum and my standard "lookup" mini-app uses 5.
ID, SequenceNum, LongName, ShortName, ActiveFlg

This allows me to sort the list in non-alfa sequence if I want to. It also gives me a short value I can use in tight spaces, and most importantly, It allows me to make items as inactive. They stay in the list so you can still see them but they sort to the bottom to get out of the way, and they allow me to write code to prevent people from changing to a no longer active value.

 

[zeroaccess]

Sorry, I don't follow. Isn't this the job of a Combobox?

On the form, no - check out my DB and you'll see what I mean with the status. It's like Northwind.

 

[zeroaccess]

One of the downsides not frequently mentioned is that they are essentially one field tables. All my lookups require two columns minimum and my standard "lookup" mini-app uses 5.
ID, SequenceNum, LongName, ShortName, ActiveFlg

This allows me to sort the list in non-alfa sequence if I want to. It also gives me a short value I can use in tight spaces, and most importantly, It allows me to make items as inactive. They stay in the list so you can still see them but they sort to the bottom to get out of the way, and they allow me to write code to prevent people from changing to a no longer active value.

I'm not sure we're talking about the same thing. All of that is possible whether or not one uses table-level lookups. The table-level lookups are handy for admins who understand them. All it is is an auto-lookup instead of seeing values in your tables. It's an optional convenience feature.

Now, if a new admin is poking around in your DB he/she may not understand the design, but that goes without saying.

 

[Pat Hartman]

You're right. I was thinking of multi-value fields.