First of all, please let me apologize in advance for my lack of technical knowledge and proper terminology skills when it comes to databases. I have never taken any classes, but have only felt my way around by trial and error, hook and by crook, and mimicry. In other words, I have just enough knowledge to know I don’t know anything and to get me into trouble. But here goes…
I am using Access 2007. I have five fields in a table named TblComponents. The fields are named:
[OpportunityRef] – a company-assigned reference number
[Customer] – Control Source for the 1st of 3 cascading combo boxes called cboCustomers in the bound form named Components
[Division] – Control Source for the 2nd of 3 cascading combo boxes called cboDivisions
[Location] – Control Source for the 3rd of 3 cascading combo boxes called cboLocations
[Program] – text usually consisting of many words
Besides needing these fields as individual pieces of data for each of our products or “components” throughout the database, our company uses a unique tag name to reference each component in all correspondence. This tag name is made up from a string of identifying data and is joined together with dashes, namely [OpportunityRef]–[Customer]–[Division]–[Location]–[Program]. So a tag name might look like this:
0098457–All Star Systems–Technical Services–Dallas, TX–Zebra Flight Struts/Multi-Mission Project
I would like to create a field which is automatically populated from the five table fields, with dashes added in between, which effectively builds the tag name for the user. I would like to be able to access this field from both the table itself and the form.
Is this possible? How would I accomplish this?
I would also like to ask another question. Our company uses a lot of dashes and slashes in our daily written business. Do these characters create problems in Access. If so, is there a work around for that so to speak?
Thanks in advance for any and all help received!
It is possible to do what you request, but rarely recommended as it introduces a level of de-normalisation into the design (See Normalisation and Table structures). Let me know if you feel you absolutely must duplicate this data and I will help anyway.
An alternative approach would be to use an unbound TextBox (or Label even depending on your actual usage) on your form, and any other object that requires this same value, which is made up of the concatenated values of the other controls as you require (similar in many respects to what DPatel suggested).
The Control Source would be something like : - =[OpportunityRef] & '-' &
-
[cboCustomers] & '-' &
-
[cboDivisions] & '-' &
-
[cboLocations] & '-' &
-
[Program]
I've shown it on separate lines just to display easily but in normal usage this would be a single line.
8 4018
You can use the following [OpportunityRef]&"–"&[Customer]&"–"&[Division]&"–"&[Location]&"–"&[Program].
Also, regarding - and /, it all depends on what you are planning to do
Thanks for your quick reply! I hate to sound even more ignorant, but on what, how, and where would I use what you typed? I'm afraid I wasn't kidding about not knowing anything.
Thanks!
NeoPa 32,556
Expert Mod 16PB
It is possible to do what you request, but rarely recommended as it introduces a level of de-normalisation into the design (See Normalisation and Table structures). Let me know if you feel you absolutely must duplicate this data and I will help anyway.
An alternative approach would be to use an unbound TextBox (or Label even depending on your actual usage) on your form, and any other object that requires this same value, which is made up of the concatenated values of the other controls as you require (similar in many respects to what DPatel suggested).
The Control Source would be something like : - =[OpportunityRef] & '-' &
-
[cboCustomers] & '-' &
-
[cboDivisions] & '-' &
-
[cboLocations] & '-' &
-
[Program]
I've shown it on separate lines just to display easily but in normal usage this would be a single line.
@datsandgirl
You can do this either in a new query or if you have a field in a table called "TAG" then do this in an update qry
NeoPa 32,556
Expert Mod 16PB dpatel1682: or if you have a field in a table called "TAG" then do this in an update qry
Or not. This is not recommended for the reasons explained in the link.
@NeoPa
Beautiful, perfect NeoPa! This is just what I needed. The biggest purpose of this request was to provide a way for the user to be able to quickly and easily copy and paste this combersome tag name into correspondence. Thank you and dpatel for all your time and help!
NeoPa 32,556
Expert Mod 16PB
You're very welcome :)
I'm pleased we were able to help.
NeoPa 32,556
Expert Mod 16PB
A new thread has been split from this one and deals with the question of Cascading Filtering.
Any interested parties can find it there.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Ken Fine |
last post by:
I want to take the contents of many fields of various datatypes (varchars
and text) and combine them into a single "junk" field that I will perform
SQL Server free text searching upon, e.g.:
...
|
by: Channing Jones |
last post by:
Hello everyone,
I am trying to store data in a binary field of an SQL-Server table using
ADODB.
So far, I have managed to store a record but not any data in the binary
field.
I only get...
|
by: mojo |
last post by:
We have set up an Access database with tables x,y, & z where we would
like to have multiple people entering data into a table x. Each person
has an identical copy of the database on their PC's. ...
|
by: Pavils Jurjans |
last post by:
Hello,
I have a third-paty application that uses text type field in MSSQL server to
store data of incoming emails (full raw sources, including headers, etc.).
Since the emails come in different...
|
by: Chuck Anderson |
last post by:
I'm pretty much a JavaScript novice. I'm good at learning by example
and changing those examples to suit my needs. That said ....
..... I have some select fields in a form I created for a...
|
by: sellcraig |
last post by:
Microsoft access
2 tables
table "data main" contains a field called "code"
table "ddw1" is created from a make table query of "data main"
Goal-
the data in "code" field in needs to...
|
by: TIonLI |
last post by:
I am working with a table that lists dates and corresponding times in separate fields. For purposes of building a query to calculate elapsed time I would like to combine a field with a field ending...
|
by: sijugeo |
last post by:
Hi,
I Have a MS word template field which have certain data fields. I want to populate the data fields using the data retrived from SQL db. How can I do this?
Thanks in advance
Siju george
|
by: Will |
last post by:
Hi,
This is might be a basic question, but is it possible to hold multiple
pieces of data in one field?
I am adpating a database which compares multiple projects against a
number of criteria....
|
by: Desitech |
last post by:
I want to create a command button on a form that will populate a certain field in a certain table for that record with a hyperlink address (just the text) and add another field from that record to...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
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...
|
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,...
|
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...
| |