Solved Combine 2 textbox fields into one and store in record (1 Viewer)

The Rev

Registered User.
Local time
Today, 10:43
Joined
Jan 15, 2003
Messages
118
I have a hopefully easy question...

I have a single form tied to a table. I have a "User_Name" field and an autonumber field. I have a third field called "request_ID" that I would like to concatenate the "User_Name" field and an autonumber field but have that value stored in the table as a unique request_ID. The overall reason is complicated, and honestly unnecessary. Just need to know how to combine these fields, display it in the third text box, and store it in the right record and field.

Thanks

The Rev
 

plog

Banishment Pending
Local time
Today, 09:43
Joined
May 11, 2011
Messages
11,611
The overall reason is complicated, and honestly unnecessary.

Sorry, but we are going to need those reasons. What you want goes against the rules of how databases are to work. And for us to help you break those rules (rather than give you a simple solution that will work instead --which I will give in a minute) requires that we understand why.

So, heres the correct way to accomplish this: You do not store data that can be calculate, therefore request_ID should not exist in a table.. What you should do is use a query to build the [request_ID] field, then you reference that query instead of the table.
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 10:43
Joined
Jan 23, 2006
Messages
15,364
I agree with plog. You may be correct that the reasons will prove to be unnecessary, but at the outset better to have all the facts. I know no details of your project, but it seems you have, at least, Users and Requests.
 

The Rev

Registered User.
Local time
Today, 10:43
Joined
Jan 15, 2003
Messages
118
OK, here's the ugly. We have teams of 4 assessors and 1 lead go to contractor sites with individual copies of my database. Each assessor has their own copy because we are not allowed to connect to the contractor's network, nor are we allowed to network ours together. We have phones that we tether to in order to get access to our corporate email, so I leverage that to do our requests.

At the end of each day, each of us 4 export our documentation request table into an Excel spreadsheet named assessor_name_Doc_request.xlsx. We email them to the lead, who uses an import function to import all 4 spreadsheets into a single update table, then the database runs an update query to add them all to the main table.

There is nothing inherently unique in that output spreadsheet to identify a unique request, so when we do our exports on the next day, I only want it overwrite the same record. I can't use autonumber because all 4 of us will have the same autonumber, causing a conflict in the Primary Key and probably overwriting someone else's requests.

I am trying to force a new primary key based on the assessor's name and the autonumber for that record to attempt to eliminate duplicate entries for the PK. And since the Doc_Request table starts off empty, it won't just do a simple update unless I force the query into a one to many relationship. And when I do that, I can't update any of the existing records because I get PK violations when I try that.

Suggestions?
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:43
Joined
Jan 23, 2006
Messages
15,364
A question based on
"There is nothing inherently unique in that output spreadsheet to identify a unique request "
Do you/could you include the assessor's name or ID in the collected spreadsheet? Along with other identifying info ( to be used subsequently for overall identification of unique records).
I'm not sure if you are saying there can be no unique identification OR currently we have no means to uniquely identify records in that output spreadsheet or the database.
 

The Rev

Registered User.
Local time
Today, 10:43
Joined
Jan 15, 2003
Messages
118
A question based on
"There is nothing inherently unique in that output spreadsheet to identify a unique request "
Do you/could you include the assessor's name or ID in the collected spreadsheet? Along with other identifying info ( to be used subsequently for overall identification of unique records).
I'm not sure if you are saying there can be no unique identification OR currently we have no means to uniquely identify records in that output spreadsheet or the database.

Yeah, their name is included in the spreadsheet. I'm trying to combine their name and a unique number to identify each request as unique, but be able to update it once it is completed, and add more document requests if the need arises.
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:43
Joined
Jan 23, 2006
Messages
15,364
Here is a small sample. I've chosen to use 3 copies of a table to simulate your standard database that each assessor uses. The 3 copies are named Assessor1, Assessor2 and Assessor3 (you could have more, but this is to show the concept)
Each Assessor table has this design.
assessorTable.PNG

Note that in Assessor1 (2,3) table the LocalId is an autonumber PK

The Lead's table has this design
TheLeadsTableDesign.PNG


In the Lead's table the LocalID is a Number , Long Integer
theHiLevelID is the autonumber PK

Each of the Assessor table data can be appended to the Lead's table using a query such as (for my demo)
INSERT INTO AssessorAllCollected (
LocalId
,Assessor
,CollectedDate
,Requestor
,Otherinfo
)
SELECT LocalId
,Assessor
,CollectedDate
,Requestor
,Otherinfo
FROM Assessor3; <------replace the final digit with each of the assessor tables in turn


The Lead's table has all of the assessors' data. Each record in the Leads table/database can be traced back to the assessor table from which it originates.
Hope this is helpful.

Here is sample data for each assessor
assessorData.PNG


Sample of Lead's table after collecting each assessor's data
AssessorAllCollected

HiLevelIDLocalIdAssessorCollectedDateRequestorOtherinfo
1​
1​
Jim
03-Mar-20​
2387rgrvsdcerff
2​
2​
Jim
04-Mar-20​
2348gt ;lqws;ori
3​
3​
Jim
04-Mar-20​
3498nenwnsnnsjdjjdeoeoe
4​
4​
Jim
05-Mar-20​
4536yye8i wppgtg
5​
1​
Bob
04-Mar-20​
2231fllfmnbmop
6​
2​
Bob
04-Mar-20​
3942bdbkefllkrf;k[p5g
7​
3​
Bob
05-Mar-20​
3678hws3uep4i
8​
4​
Bob
06-Mar-20​
2489mmama wuehjbede
9​
1​
Joan
03-Mar-20​
2437qnsnl4jlrij4n scrabble
10​
2​
Joan
05-Mar-20​
9343nsook last one for test
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 07:43
Joined
Oct 29, 2018
Messages
21,358
Yeah, their name is included in the spreadsheet. I'm trying to combine their name and a unique number to identify each request as unique, but be able to update it once it is completed, and add more document requests if the need arises.
Hi. Pardon me for jumping in... Just guessing here, but you're probably thinking you need to store that "made up" unique ID in the table because you're exporting that table into Excel for merging and importing into the main table. If that's correct, then you should be able to create a query based on your table, add the calculated unique ID and then export that query instead of the table. If you're already exporting a query, then just add the calculated unique ID to that query. Will that work?
 

The Rev

Registered User.
Local time
Today, 10:43
Joined
Jan 15, 2003
Messages
118
So, I decided to go with an incrementing text field to add to the assessor's name instead of the autonumber. And on the form, I tell it to ignore the unique number field if it is not null, which keeps the unique number unchanged while allowing additions the next day. It also allows the Lead to import everyone's sheets and the unique number to act as an actual record identifier for changes. I've tested it out with my lead and 2 other assessors this morning, and all went as planned.

Thanks for steering me in the right direction!
 

Users who are viewing this thread

Top Bottom