associate items with 2nd names

[Infinite]

Hello, I have items, but some of them have multiple names for one item. Is there any way to tell Ms access that that item has those other names? I have 5 fields, named "Model" "2nd Model Names" "3rd Model Names" "4th Model Names" and "5th Model Names."

How would I got about doing this? Thanks!

 

[CJ_London]

the normal way to do it is to have a table called say tblAlias or tblAlternative which has two fields, one is the 'normal' name and the other the 'alias'

A bit like the table you used for a many to many relationship

To use, lookup a name against the alias and it can return the 'normal' name

 

[Infinite]

OK....how would I use that? Like, could I have that in a query, for instance, I have item 1 called abc, and its also called cba, could I have a drop down menu with the integrity kept, would I then be able to use cba or abc?

 

[CJ_London]

could I have that in a query

yes

I have item 1 called abc, and its also called cba, could I have a drop down menu with the integrity kept, would I then be able to use cba or abc?

from my example, change the 'normal' name field for the uniqueID of the items table then in principle yes - if you don't know about it yet, google 'many to many relationships' to get an understanding of what they are and how they work

 

[Infinite]

OK, the real thing I wanted it for is to have a form that I edit the items in, I want to not see the other model names, but to be able to still search for them, or when I import a list of items, it will changed them to the main name, and not the 2nd, 3rd, and so on names. Is that possible?

 

[CJ_London]

yes - create a link from your imported name in your import table to the alias in this table to lookup the id that is used in your main table

 

[Infinite]

ok, I think I understand what your talking about, im going to try that some time in the next 1-2 hours. ill get back to you on this thread if it doesnt work. thanks!

 

[CJ_London]

I'm off for a Thai meal in an hour or so, so will respond some time over the weekend

 

[Infinite]

ok, I tried it, but it will only accept the 1st row as items to input. How do I tell it to also accept items from the other 3 rows in the drop down?

 

[Solo712]

ok, I tried it, but it will only accept the 1st row as items to input. How do I tell it to also accept items from the other 3 rows in the drop down?

I created an aliasing table which served me well on several occasions. Started with my audio collection where I wanted to alias names so I could retrieve tracks from an artist from different bands in which he or she played. So, eg. typing Paul McCartney wold return tracks by Paul McCartney, The Beatles and Wings.

The table was sth like:

PK
RecordID (which in my case was a composite of the Album or PlayList ID / and Track No.)
Name (the aliasable or aliased name of the record)
Primary (Boolean, TRUE if this was the name of the artist who released the song)

Applying this to your scheme, your records would have varying number of aliases, or simply just one primary name. As you add the aliases, and a dropdown list that shows them grows but they all are a reference to the same record. The 'primary' switch gives you the option to query on default name of the item.

Best,
Jiri

 

[Infinite]

Thing is, I want to import my sales items (that have different names for each site, show, etc, etc) and have MS access know that item 1, is item 2 without having to rename all the item 1s, to item 2s.

 

[CJ_London]

Solo's suggestion is what I would suggest - I would reread it again and try to understand

tblShows
ShowPK autonumber
ShowLocation text

tblAliases
AliasPK autonumber
ShowFK long
ShowAlias text
Primary Boolean

note - no showname in tblshows

Data
tblShows
ShowPK..ShowLocation
1 New Jersey
2 Florida

tblAliases
AliasPK..ShowFK..ShowAlias.............Primary
1..........1............West Milford.........True
2..........1............WM showground....False
3..........1............Long Pond............False
4..........2............Port Orange..........False
5..........2............Daytona Beach.....True

so your combobox for shownames is the ShowFK and ShowAlias columns from tblAliases with showFK as the controlsource - if the user selects either Port Orange or Daytona Beach, the ShowFK is 2. And your query to show the name would include a link from tblshows to tblAliases 'where Primary=true'

 

[Infinite]

Hey, I have and NO luck with doing those Aliases so far, and I need them to be working. It is starting to become a HUGE hassle now that the shows have started for this year. In the attached data base I have the item, and the show sales were I would import or put in the show sales manually. I just need some one to take it, make it so I can import a 22 pistol has a 22 pistol or a 22 cowboy pistol. If some one can do that, and explain so I can use that in the future, it would be a HUGE help to me. I will also be making another thread if this one goes unanswered. Not sure if this has been to long without a post in it or not. Thanks!