473,903 Members | 3,324 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Concatenate 2 fields into another field in same table

4 New Member
I am attempting to combine (concate) 2 fields into another field in the same table. Works great in a query and I understand how to do it on a form, but how do I store the return (value) back into my table?

The combination of these two values determine a unique string.... Event:[castype]&""&[casnumber]

This result I use for linking my Main Form to my Subform and will also be used for runnig reports.

Also because the above expression Event:[castype]&""&[casenumber] is placed in the Field line of the query, when I add the table where the information of the subform is to be stored it seems to lock the main form from further data being entered.

When I use the same query and I "remove" the concate expression from the field line and use only the casenumber as my linking reference the subform everything works great!

Unfortunately.. I have to figure out a way to be able to combine both the CaseType and CaseNumber fields. Only the combination of these two numbers can make it unique.
Dec 12 '12
19 19313
ADezii
8,834 Recognized Expert Expert
Going off topic a little, but I am with zmdb on this one as far as Composite Keys go (don't like them). I make every attempt NOT to use them except in the case of a Joining Table of a MANY <==> MANY Relationship.
Dec 13 '12 #11
Allen0926
4 New Member
It will be a unique value but it is not a primary key nor is it required to be one. Rather than creating an update query I thought there may be an afterupdate action as stated by NeoPa I could do that would simply save the concatenated value in the underlying table.
Dec 13 '12 #12
Rabbit
12,516 Recognized Expert Moderator MVP
If it's not being used only for display purposes, there's no need to store it at all. It's better to concatenate them when they need to be displayed in that manner but store them as separate fields.
Dec 13 '12 #13
NeoPa
32,584 Recognized Expert Moderator MVP
From your Opening Post.
Allen0296:
This result I use for linking my Main Form to my Subform and will also be used for runnig reports.
How sure are you that a calculated query field would not do what you require.

This thread is spreading into so many areas that it's getting messy to follow.

Assuming!
You want to have a field in the table as requested :
  1. You would want to create the field (obviously).
  2. You must also ensure that ongoing data entry maintains the data in a consistent fashion. This is covered in post #3.
  3. If you have existing data already, that needs to be converted to match the new format, then you will need to execute the action query represented by the SQL code that ADezii posted in post #2. One would expect this to be a one-off process.

Other points have been raised, regarding Normalisation of your data (See Database Normalisation and Table Structures), which are by no means irrelevant. I'm happy to take the question at face value and assume you have a need for the data to be stored in the format explained. You probably need to consider whether or not this is true. The comments you see here are all by very experienced developers who all have very good understanding of databases.
Dec 13 '12 #14
NeoPa
32,584 Recognized Expert Moderator MVP
I'll post this separately in case this part of the thread needs to be split off at some time (though that's not my feeling at this time) and because I came across it a while after my earlier response.
ADezii:
I make every attempt NOT to use them except in the case of a Joining Table of a MANY <==> MANY Relationship.
I don't even use them for that ADezii (although I'm not fundamentally opposed to their use). I always use an AutoNumber for the PK of Many to Many link tables. Two separate, non-unique, indices for linking to each of the tables, but the PK a separate, and unconnected, field. Indices always work more efficiently when the data is smaller.
Dec 13 '12 #15
ADezii
8,834 Recognized Expert Expert
@NeoPa:
Sorry for going Off-Topic again, but:

Do you feel that two separate, Non-Unique, Indices along with an independant Primary Key is more efficient then a 2-Field Composite, Primary Key to accomplish the same Task, namely Link to each of the Tables in a MANY <==> MANY Relationship?
Dec 13 '12 #16
NeoPa
32,584 Recognized Expert Moderator MVP
Yes. A single composite PK would never work adequately for both tables anyway. Both of the linked tables would need an index sorted for their own data explicitly.

Must rush for now, may expand later.
Dec 13 '12 #17
NeoPa
32,584 Recognized Expert Moderator MVP
So, how the JOIN table is defined depends to a certain extent on your requirement. Sometimes it's necessary to have the list of [TableB]'s values sorted within those for [TableA], and the same for [TableB] values within [TableA]. In this case a composite unique key would be called for for each. Other times, simply having access to a list of [TableA] values and [TableB] values is enough. In such a case non-unique keys for both fields are adequate.

One might think that in the first case one of the composite unique keys could double as a PK. This would work, of course, but the index information stored for every index is based on the PK, so, even assuming the linked fields are both AutoNumbers, all indices would be nearly double the size of using a separate, single, AutoNumber field. This has an impact on processing through the data using any index, and actually ends up taking more space (in most situations) than sticking with the two composite indices alone.

In the second case we have no unique indices anyway, so a separate PK is mandated (although one could also handle this situation by creating a composite PK for one of the required foreign indices. This would not be advised).
Dec 13 '12 #18
ADezii
8,834 Recognized Expert Expert
@NeoPa:
Just out of curiosity, the Order Details Table of the Northwind Sample Database is the JOIN Table between the Products and Orders Table. It consists of a Composite, Primary Key consisting of [OrderID] and [ProductID]. Do you think that changing this Join Strategy to that of yours (Unique Indexes/Primary Key) would be justified? I'm not questioning your Logic, it is simply intellectual curiosity.
Dec 14 '12 #19
NeoPa
32,584 Recognized Expert Moderator MVP
I've never played with Northwind ADezii, but certainly I would expect it to work more efficiently if that is the case as a JOIN table. I wouldn't change it myself. It's a teaching database. It works best when both the teacher and the student work with the same tools available to them.

I certainly wouldn't expect MS teaching tools to be examples of best practices. I've seen far too many code snippets on their web pages to think that. The code may illustrate points, but a lot of it is rubbish code.
Dec 14 '12 #20

Sign in to post your reply or Sign up for a free account.

Similar topics

0
2304
by: Ray Holtz | last post by:
Is it possible to autofill a field based on what is entered into another field in a form? My form has an employee field, and department field. In an Items Table, I have fields FldEmployee, and FldDepartment. FldEmployee is looked up from an TblEmployee Table which also has fields FldEmployee and FldDepartment. This FldDepartment field is looked up from a TblDepartment table that just has FldDepartments. I hope I explained that OK The...
9
5613
by: geronimo_me | last post by:
Hi, I am atempting to compare part of a field with the whole of another field in access. Is this possible? Basically I have 2 tables with the following info: Table1 Field1 = MR.
2
6803
by: Kevin | last post by:
Hello, How do I concatenate two fields in the same table... For example, I have two tables, first one is called familynametable that has a familyID and familyname field. This table is connected to a individualnametable that contains three fields: individualnameID, FamilyID (to link to familytable) and FirstName. In the familytable, FamilyID=1, FamilyName=Doe, In the individualtable there are two records:
0
1230
by: Bennett Haselton | last post by:
It seems whenever I add a new field to a database table that's queried by DataAdapters in my project, I get run-time errors because I don't go back and re-generate all the DataAdapters to read the new field. When I first generate the DataAdapter using the wizard, the list of fields in the database table gets hard-coded into the SelectCommand: SELECT ID, username, password_hash, email_address, datetime_created FROM wbuser
2
1290
by: surya | last post by:
hi i have emp table in one database , how to retrieve same table another database through query analizer give code ,,,
6
2841
by: drazenj | last post by:
Hi , sorry for my english ... I work in high school as a teacher and try to make CLASS SCHEDULE (TIME schedule) for my collegues and for students . My problem is that I don't know how to concatenate several text field values from diferent RECORDS into one record. I tried to solve this with CROOSTAB query ,but I can't aggregate "text" values but only "COUNT" the number of (such) values from records (fields). I'll try to explain by example :...
3
1463
by: seangibson | last post by:
Here's the scenario: I have a list of transactions. I am a middle man in the transaction, so I collect a fee from both the buyer and the seller. My fields are Date,Buyer, Seller, Buyer Fee, Seller Fee. Buyer and Seller fields are both looked up from a separate table serving as purely a lookup table. How can I write a query wich sums both Buyer and Seller Fees for each customer (who can be a buyer or seller for any transaction) for a given...
1
2150
by: jmstur2 | last post by:
How do I update a field in a record based on the value of another field in another record - in the same table? Specifically, I want to update the value in the AMOUNT field of record1 with the value of the AMOUNT field in record 2 when the value in the YEAR field of record 2 meets a certain criteria. In addition, the records also have to match on 2 other fields Thanks.
2
12107
AccessIdiot
by: AccessIdiot | last post by:
Apologies if this has been answered before - I searched but couldn't find anything. I have a listbox that is populated with a query. I need to enable the user to select multiple items (simply set listbox Multi Select property to Simple yes?) and then I'd like to commit the choices to a single text field in a table, concatenated and separated by commas. So in other words if the listbox has these values: BRN CM XD RG
2
2170
by: Wilcox | last post by:
Hi There, I'm new to access and trying to build a simple customer database for my massage business. I am trying to do the following. Client Name First Name Last Name I want the client name field to auto populate by combining the first name and last name fields. I want to use the client name field in other other tables for reference as I build the rest of the database. Invoicing and medical records.
0
9846
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11279
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10872
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10499
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9675
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8047
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7205
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6087
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3324
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.