Self-referencing Table with different data types

[TurnipEntropy]

So I'm not entirely sure I actually need a self-referencing table per se, as much as a table that takes data from multiple fields and combines it in another field in the same table. That's all vague, so I'm going to go into exactly what I'm trying to do.

I have a field that has redundant information with other fields. Redundant in the sense that it'll say Date-Location ID-individual ID number-file ID, and I already have fields that contain that information. At first I made a form that just had the user entering that information twice. My boss wants me to get rid of that redundancy on the user's end.

So what I have to do is create a form that looks kind of like one in which you insert your phone number ( blank box-blank box-blank box-blank box).

Here's the tricky part. Let's say I enter in that series of boxes on the form 23APR2012-AEGF-0251-7YHA. When I insert that data from the form into table1 of the database, I need Field1 to be all of that information, Field 2 to pull AEGF from a drop down menu linked to table 2, and field 3 to pull just 7Y (out of 7YHA) from a drop down menu linked to table 3.

That means field 1 is straight text, field 2 is a lookup field, and so is field 3. I'm hoping this is a many-to-many relationship in the same table, because that's the only thing I can think of that might work here. This is in the modules and VBA section because I figure that might not be the case and I'll have to do some coding.

Thank you everyone,

TE

 

[plog]

I need Field1 to be all of that information, Field 2 to pull AEGF from a drop down menu linked to table 2, and field 3 to pull just 7Y (out of 7YHA) from a drop down menu linked to table 3.

First, your boss is correct. Second, what you have proposed above is exactly not what he wants, nor what you should do.

In a database you should store each distinct piece of data in its own field. That means Field1 is wholly unnecessary and your proposed table structure is overly complex.

Can you provide some sample data? Don't tell me about the form or how you want drop downs to work--forget all that. What does some sample data look like?

 

[Steve C]

Users shouldn't have to enter the same data twice - indeed the same data shouldn't exist twice.

Nevertheless, if you want data entered in one field1 to appear in field2 go to the After Update field in the properties window for Field1. Change it to say

Private Sub Field1_AfterUpdate ()
Field2 = Field1
End Sub

The first and third lines of this appear already for you.

Note. I am new to access - can't promise this answer will be right.

 

[TurnipEntropy]

Sorry for not providing more information on that. My internship is such that I can't provide a ton of information. My boss does want field 1 though. She wants the form to compile field 1 from multiple fields on the form. I can get some dummy data set up later and send that.

You make a good point about field 1, however, now that I think about it. If I have it send to different fields, then you can just query the multiple fields to essentially be the same thing as searching what would be in the combined field. I can also order the fields so that they read exactly like field 1 would have. I'll talk to my boss to see if that's an alternative. If it isn't, I'll have the data up here as soon as I hear back.

Thanks again,

TE

 

[Steve C]

Plog is right. I didn't see his answer before posting. I was sort of imagining your boss needed an answer (the right answer may be above your pay grade LOL)

Getting the left two characters from a field e.g. 7Y from 7YHA is a Left Instring function.

In a query column (design view) have a field as

Expr:Left([Field3]),2)

View the query to check Column "Expr:" has the Left two characters of Field3

 

[bob fitz]

Steve C
I think you mean:
Expr:Left([Field3],2)