Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access Concatenate Field Strings Together From 2 Separate Tables Into One Field (Merge Combine Join)

Posted on 2016-09-28
10
Medium Priority
?
63 Views
Last Modified: 2016-09-28
I have searched for hours and have not completely understood this or something.  I am needing to Concatenate Field Strings Together From 2 Separate Tables Into One Field caled ProdConSku.

Tables are called SKUs and Products

The Fields are SKU in the SKUs Table and ConditionID in the Products Table.

Say CSGWJ77Q9B4 was in the SKU field in the SKUs table

And

NSOP was in the ConditionID field in the Products table.

I want it to say CSGWJ77Q9B4NSOP in the ProdConSku field in the Products table.

I have gotten close with the SELECT Query but not combined into one field. Thank you!
0
Comment
Question by:Dustin Stanley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 22
ID: 41820652
make a query. Add the SKUs table, the Products table, and the Conditions table. Since relationships are set up, Access will automatically join the tables.

in the grid:

field --> ProductSKU: SKU & CondCode

what comes before the : is the name of the column -- everything has to have a name and if you don't create one, Access will use Expr1, Expr2, etc

the first column should probably be the ProductID and it will be hidden from users to see
0
 
LVL 12

Expert Comment

by:Missus Miss_Sellaneus
ID: 41820665
Or, writing your Select statement use something like:

skus.sku & products.ConditionID AS skuConditionID

in your fields list
0
 

Author Comment

by:Dustin Stanley
ID: 41820692
ok the field --> ProductSKU: SKU & CondCode works and it opens the query fine. How do I run it to on click as an example to populate the field ProdConSKu in the Products Table in my form.
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 22

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 2000 total points
ID: 41820714
change the query into an UPDATE query (on the DESIGN ribbon, you will see that SELECT is chosen in the queries group)

add ProdConSKu to the grid

in the UpdateTo cell below that field, put the equation after the colon.

However, realize you are duplicating information by doing this.  SKU is already in the SKUs table and ConditionID is already in the products table so information can be joined anytime -- better to do that so there is no chance for the SKU to  go out of sync.
0
 

Author Comment

by:Dustin Stanley
ID: 41820721
Ok thank you I will look into that tomorrow. I just need a way and visual or something to join the two fields to make the PRODUCT. I thought that would be ok and set it indexed no duplicate because each SKU plus Condition code is Unique. No duplicates. Thank you.
0
 

Author Closing Comment

by:Dustin Stanley
ID: 41820722
Good Info!
0
 
LVL 22
ID: 41820735
you're welcome, Dustin ~ happy to help

in Products, there would be a unique index on the combination of SkuID and ConditionID
0
 

Author Comment

by:Dustin Stanley
ID: 41820741
So where and how will I be able to make a form with the SKU and Condition codes for the users to see.
0
 
LVL 22
ID: 41820752
I am assuming we are talking  about a form based on ProductDetails so that users can pick a product? The combobox would store ProductID and show what is in the second column.

for the select query you made to show the joined information, modify it to put ProductID first.  
This will be the RowSource for a combobox.
Name --> ProductID
ControlSource --> ProductID
ColumnCount --> 2
ColumnWidths --> 0;1.5"
ListWidth --> 1.7"

To edit the product information, I would make a mainform based on SKUs that is a single form, and a subform based on Products that is a continuous form where ConditionID is a combobox and the concatenation of Sku and Condition is also displayed. LinkMasterFields and LinkChildfields for the subform control will be SkuID.

This is another question though ~
0
 

Author Comment

by:Dustin Stanley
ID: 41820759
Ok
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question