Merging Data from 3 Cells to One Cell

[Upon66]

I'm trying to create a unique identifier for an online store, which includes a products Grade, Weight and UPC and then combined into a single cell (Grade-UPC-Weight). I've been trying to figure out how to get a query to run (properly), but have had no luck. I've figured out how to Concatenate the cells with the following expression:

ID: [Grade] &""& [UPC] &""& [Weight]

I'm not sure if that's what I need or not, but there's one step further.

I have a form, which the user will be entering the Grade and Weight for an item, which will be appended into a second table. Once the data is entered, the users click a button to send the data. On the same click I'd like to run concatenate query in a macro, which I already have in place. This query would generate the data into the second table. I have no problems getting the Grade and Weight appended into the second table, I'm just having issues getting them to both work at the same time, if it's even possible?



Any help is much appreciated.

[June7]

Why? I presume the UPC is a unique value. Each UPC can have several grade and weight values? I really don't like compound keys.

You want the compound value and the grade and weight to all be saved to a new record in second table? Why not just save the 3 values into separate fields? Either save the compound value or the individuals.

The empty string is not needed in the concatenation. If you want a space in the result, then:

ID: [Grade] & " " & [UPC] & " " & [Weight]

[orange]

I don't think that
D: [Grade] &""& [UPC] &""& [Weight]
is what you need or want.
There are no cells in Access. Access is different than Excel.

You should read this article on database principles.
http://forums.aspfree.com/attachment...achmentid=4712

And review Normalization in order to set up your tables to facilitate what you are trying to do.
see the first few topics at
http://www.rogersaccesslibrary.com/forum/topic238.html

[Upon66]

Why? I presume the UPC is a unique value. Each UPC can have several grade and weight values? I really don't like compound keys.

You want the compound value and the grade and weight to all be saved to a new record in second table? Why not just save the 3 values into separate fields? Either save the compound value or the individuals.

The empty string is not needed in the concatenation. If you want a space in the result, then:

ID: [Grade] & " " & [UPC] & " " & [Weight]

The UPC isn't being used as a primary key. There is another source of data that is being used as the primary key, which uniquely identifies each item.

The products are being sold on eBay, and have varying conditions. The Grade-UPC-Weight code was going to be used for a unique identifier so that when the item is sold, we are able to quickly identify/ship the product out. This code is going to be placed on the listing, so that we don't have to reference the database to process the item for shipment.

The Grade-UPC-Weight identifier doesn't necessarily have to be generated into a new field of data in the table. The data could be concatenated into a form, just for a simple copy/paste whenever the item is being listed. Is this even possible to do in a form?

[Upon66]

Was just able to get the data concatenated in the form using this in the control source of a text box

=[Grade] & "-" & [UPC] & "-" & [Weight]

[orange]

Was just able to get the data concatenated in the form using this in the control source of a text box

=[Grade] & "-" & [UPC] & "-" & [Weight]

Because you can do something, doesn't mean you should do it.
A PK is an attribute of a table and is typically used by the underlying data base management system. It's for system use.
There is absolutely no reason to have the "-" in the PK.
It is obvious that this is a field/value for your use, and that is a whole different story.

You can concoct any field or combinations of fields for your use in Queries, Forms or Reports, but
you are just kidding yourself if you think this field in this format is needed by Access.

You should work on getting a clear, defined set of business rules so that you can create and structure your tables and relationships to support your business. This is the critical step in getting an easy to use,easy to maintain useful data base.

As posted before:
You should read this article on database principles.
http://forums.aspfree.com/attachment...achmentid=4712

Good luck with your project.