Bizarre Table request! (1 Viewer)

[--amadeus--]

Hi,

First time on here, so hello all!

I have a very strange request from a client who wants to do something simple that they can't because of poor database design (not mine!) . I can't think of an elegant solution so I'm wondering if anyone on here can?

They have multiple tables containing address information, for example they have one containing information about certain buildings. They can use the softcopy of the database to retrieve information about the building but for backup they now need a hardcopy.

Now part of the building information includes the alternative names for buildings (for example "City Museum" and "Art Muesum" might be different names for teh same building). The hard copy needs to have a line entry for both names (proper and alternative) and this is where the difficulty lies.

Using a simple example the table might be:

colour fruit
red apple
orange
yellow banana

The output I would need in the report is therefore:

Red (Apple)
Apple (Red)
Orange ()
Yellow (banana)
Banana (Yellow)

Now I may be being dense but I can't see how I can do this! All help very gratefully recieved

 

[Simon_MT]

I would consider having:

Keyword
Alternatives

For example Flowers East is an art gallery. Not a florist. So if Flowers was a KeyWord then it would be pointless to obtain the record if you are looking for a floral tribute.

So I would be inclined to use instead

ID Flowers East
KeyWord Art
Alternative Art

ID Flowers East
Keyword Art
Alternative Paintings

ID Flowers East
Keyword Art
Alternative Artists

The idea is that you does mix apples with pears.
The Keyword defines the scope for searching.

Then use concatenation of the Key Words

[KeyWord] & "(" & [Alternative] & ")"
[Alternative] & "(" & [Keyword] & ")"

I'm not sure if this will work for you?

Simon