Multi field relationship issue...

[btj]

I am trying to figure out the best way to show two pieces of interrelated data in a table.

Currently, I have one field that holds a Description and a Abbreviation - i.e. "Firewall - FW". Unfortunately, this is causing me problems with some of my work in forms and reports.

What I think I want is a way to have a field for Description (i.e. "Firewall&quot and a field for Abbreviation ("FW&quot. Then, I want a combination field which combines the two other fields (would be similar to my current/original field).

Two questions:
1. Is this the best way to go about this issue?
2. If so, how? If not, what would you recommend?

Please help me out...I am stuck on this and really need to find a solution.

- Ben

 

[67peshawar294]

ben,
Sometimes I hesitate to answer these because I'm not a "programmer." I have used Access since it first came out and muddled through till I have my db the way I want it.

I would think it would depend on the way you want to use the field. You could, if the abbreviation is two characters, use a textbox with the control source set to:
=Right([descriptandabrev],2)
to extract the abreviation. And another textbox with:
=mid([descriptandabrev],1,len([descriptandabrev])-2)
to extract the description.
If the abrev is not two characters then you need a common factor (like the dash or a space) to create a starting point in the mid and right function.

Hope this is what you need.
Jim

 

[btj]

Jim,
Thanks for the input. The list of Abbreviations are not based upon the first two characters of the Descriptions. There is no consistency between the Abbreviations. Additionally, the Abbreviations of the Descriptions currently range between two and five characters.

Example:
Description Abbreviation
Firewall FW
Access Lists ACL
Management MGMT

What do you think? Is my solution still ideal or is there something that more aptly addresses this issue?

- Ben

 

[67peshawar294]

Ben,
Sounds like a lot of work to make a new field and then populate it with the abb. If there is a space between the descript and abbrev then you can use it as a starting or ending for the Mid function.
Jim

 

[btj]

That makes sense...

As a naive follow-up, how do I ensure that the correct Description and Abbreviation match? The concept of using Mid makes sense, I just don't want the fields to be mismatched.

My users want to see both Description and Abbreviation in certain forms. Yet, other times I want to only reference the Abbreviation (ex: I need to generate a new case ID based upon what the Abbreviation is).

Thanks for your input!

- Ben

 

[67peshawar294]

Ben,
In your orig msg you said that the one field held both descript and abrev.
Make the first txtbox txtdescript and the second one txtabrev.
As long as you reference the same field in the mid or right or left function the two new txt boxes will be matched.

 

[JimHorton]

Ben,
another opinion...
I would suggest going with your first thought--breaking the table into the 2 fields. This is the 'normalized' way, and though it takes some work in the short run, in the long run you'll have a far more efficient system.

If you have the 2 fields Abbreviation (you might want to rename this field to 'Code', ie) and 'Description', then when you want to change the description, you just change it, the 'Code' stays the same--which is highly desirable when you have existing data in other tables, printed documents, or burned into the memory of your users, that references the original 'Code' (abbreviation).
--Jim

 

[MoGryph]

I'd have to aggree with JimHorton.

And to continue to continue on with his comment, but add to it, in relation to the second part of your question Ben, all you'd have to do to display the data differently is to use string concatination.

Now, my only hope is that your users aren't looking at the data in the table Directly through the table. They should be accessing it through Reports, Queries, and Forms. Reports and Forms mostly, and the queries would mostly be for yourself to manipulate, and link your other tables and such - the results of which will populate what's seen in the Reports and Forms.

Now, to show your data in different formats on the reports, etc. just pack the different fields togethor wherever you'd want to see them in that form, using a formula like:

Code & " - " & Description

Or however you want it displayed

-MoGryph
[8O)

 

[btj]

MoGryph,
Thanks your advice. I agree with Jim, as well and have fixed this issue.

- Ben