Join fields into one (1 Viewer)

fromthecauseway

New member
Local time
Today, 07:41
Joined
Apr 9, 2020
Messages
3
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

Banishment Pending
Local time
Today, 02:41
Joined
May 11, 2011
Messages
11,613
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.
 

fromthecauseway

New member
Local time
Today, 07:41
Joined
Apr 9, 2020
Messages
3
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

I’m here to help
Staff member
Local time
Today, 00:41
Joined
Oct 29, 2018
Messages
21,358
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

New member
Local time
Today, 07:41
Joined
Apr 9, 2020
Messages
3
Thanks for the help. Found a previous entry explaining the alternative to using lookup on tables so will try again.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:41
Joined
Oct 29, 2018
Messages
21,358
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

Active member
Local time
Today, 02:41
Joined
Jan 30, 2020
Messages
671

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:41
Joined
May 7, 2009
Messages
19,169
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

Enthusiastic Amateur
Local time
Today, 07:41
Joined
Sep 21, 2011
Messages
14,050
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

I’m here to help
Staff member
Local time
Today, 00:41
Joined
Oct 29, 2018
Messages
21,358
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

Active member
Local time
Today, 02:41
Joined
Jan 30, 2020
Messages
671
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

I’m here to help
Staff member
Local time
Today, 00:41
Joined
Oct 29, 2018
Messages
21,358
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

Well-known member
Local time
Today, 07:41
Joined
Nov 28, 2005
Messages
2,466
The only good thing I found when I used to use them in tables 20 years ish ago before I came here :sneaky:

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:ROFLMAO:

keep safe mick
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:41
Joined
Feb 19, 2002
Messages
42,981
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

Active member
Local time
Today, 02:41
Joined
Jan 30, 2020
Messages
671
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

Super Moderator
Staff member
Local time
Today, 03:41
Joined
Feb 19, 2002
Messages
42,981
You're right. I was thinking of multi-value fields.
 

Users who are viewing this thread

Top Bottom