UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Configure Table Field Value As Concatenate Of Other Fields, Access 2010    
 
   
Wardly
post Dec 21 2017, 08:18 PM
Post#1



Posts: 2
Joined: 21-December 17



Hey folks. Novice here.
I am building a database and wish for the PurchaseID text (field4) to represent the rows' associated date field2 and software_name text field3. I need a meaningful field to use as a reference in other tables of the database. I would like field4 to auto generate. [field3]&"-"&[field2]

I found instructions for what appears to work by creating a query, but I am looking for something dynamic. I didn't find a way to trigger the query to run upon every change to related fields and what I found frankly wasn't that elegant. What would you suggest? I will be happy to scrub the floors, I just need to be trained up a bit ;-)
Go to the top of the page
 
RJD
post Dec 21 2017, 08:40 PM
Post#2


UtterAccess VIP
Posts: 8,222
Joined: 25-October 10
From: Gulf South USA


welcome2UA.gif

Well, I think best practice in this case is to create this field in a query when you need it. This way it will always be dynamic and up-to-date. Do not store the concatenated field - just concatenate on need for whatever purpose. Something like ...

PurchaseID: [Software_Name] & "-" & Format([PurchaseDate],"yyyymmdd") ... Design View

[Software_Name] & "-" & Format([PurchaseDate],"yyyymmdd") As PurchaseID ... SQL View

Do note, however, that this will not likely be unique. If the same product is purchased on the same day, how will you differentiate?

You could make this unique by adding the record ID (you should have one, a primary key, say an autonumber called, for example, RecordID). ..

[Software_Name] & "-" & Format([PurchaseDate],"yyyymmdd") & "-" & Format([RecordID],"000")

You might think that using a calculated field might be the way to go with this, but you really should format the date, and this is not allowed in a calculated field.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
MadPiet
post Dec 21 2017, 08:45 PM
Post#3



Posts: 2,412
Joined: 27-February 09



Why? The primary key of a properly designed table implies all the values related to it. What are you really trying to do? Or should I say, why are you trying to do this?

If I do something like...

CODE
CREATE TABLE Person (
         PersonID INT IDENTITY,     /* IDENTITY is analogous to Access AUTONUMBER */
         FirstName VARCHAR(20) NOT NULL,
         LastName VARCHAR(20) NOT NULL
CONSTRAINT pkPerson PRIMARY KEY (PersonID)...);

... the primary key (PersonID) implies all the attributes related to it. If you're doing it because you want to be able to search all the columns from a single "column" then your design is seriously flawed. So, what are you really trying to accomplish by doing this? There's probably a better way to do it.

Apologies sort of for the T-SQL syntax... Access doesn't expose that and it's much easier to understand that design.
Go to the top of the page
 
projecttoday
post Dec 21 2017, 09:58 PM
Post#4


UtterAccess VIP
Posts: 9,361
Joined: 10-February 04
From: South Charleston, WV


You should use an autonumber for your PurchaseID. This is a unique identifier and you can reference a record in other tables by using the PurchaseID. There it is called a foreign key.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
GroverParkGeorge
post Dec 22 2017, 07:23 AM
Post#5


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


Sometimes we do things that we know we should not do, but can't resist the temptation.

Sometimes we don't know better, so we do something we shouldn't without realizing it's going to be a big problem.

Trying to create a "..meaningful field to use as a reference in other tables" in this way is a really good example of the latter. It may seem innocent enough to set up a concatenated field as "a meaningful reference field", but it will lead to nothing good. You should not do it.

So now you do know better and can avoid this trap. Joe, et al have offered alternatives that will work much better and avoid the heart-break of a concatenated value stored in a table.

Your next step ought to be to invest some time in learning about Normalization and the process of valid table design for a relational database. We have an excellent set of starter articles for that purpose.

--------------------
Go to the top of the page
 
Wardly
post Dec 22 2017, 02:30 PM
Post#6



Posts: 2
Joined: 21-December 17



Thank you all for your assistance and advice. The field was intended to be used as a visual aid, so in another table with a field ProdID I could set a combo box, bind to the PK , but display this as a name. I will give the design a bit more thought and appreciate the reminder that though I don't intend to misuse the field I desired, just its presence creates potential for others if the database ever gets another set of hands, (or I get careless). Much thanks for the effort. :-)
Go to the top of the page
 
RJD
post Dec 22 2017, 04:02 PM
Post#7


UtterAccess VIP
Posts: 8,222
Joined: 25-October 10
From: Gulf South USA


Hi again: Just a thought - you could show both fields in the combobox (two columns) without having to concatenate them. Then use the PK (an autonumber, for example) unique to the purchase/record (as the first column, hidden) to link elsewhere.

Good luck with your project,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
GroverParkGeorge
post Dec 22 2017, 07:12 PM
Post#8


UA Admin
Posts: 32,357
Joined: 20-June 02
From: Newcastle, WA


Along the same lines, you could even concatenate the two fields in the row source for the query. I do that from time to time, as in this example.
Attached File  Concatenatedcombo_box.jpg ( 17.97K )Number of downloads: 2

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th April 2018 - 07:43 PM