Dustin Stanley
asked on
MS Access Concatenate Field Strings Together From 2 Separate Tables Into One Field (Merge Combine Join)
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!
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!
Or, writing your Select statement use something like:
skus.sku & products.ConditionID AS skuConditionID
in your fields list
skus.sku & products.ConditionID AS skuConditionID
in your fields list
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
Good Info!
you're welcome, Dustin ~ happy to help
in Products, there would be a unique index on the combination of SkuID and ConditionID
in Products, there would be a unique index on the combination of SkuID and ConditionID
ASKER
So where and how will I be able to make a form with the SKU and Condition codes for the users to see.
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 ~
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 ~
ASKER
Ok
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