Access table question

[loriek]

I've got an access database where someone has made table 1 have a field from table 2 with a lookup such as select field 1, field 2 from table 2.

Is this allowed?

I'm also seeing queries as lookups in a table.

Access 2000.

 

[dhookom]

Lookup fields are a function in Access. However, IMHO they are a mis-feature. Consider reading

http://www.mvps.org/access/lookupfields.htm.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[loriek]

So selecting two fields as in my example is ok?? That sounds preposterous and the table is certainly not functioning well. Shouldn't there be a concatenation symbol such as "&" or perhaps "+" ??

 

[dhookom]

The horrible lookup fields allow you to specify as many fields in the select as you want (just like combo boxes). Only one field/expression can be the Bound Column.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fneily]

dhookom - Have you seen Access 2007's multi-valued fields? Bizarre.

 

[dhookom]

I was on the MS campus in Redmond a couple years ago when these were revealed to MS Access MVPs. The reaction of the MVPs is probably best kept under NDA ;-)

I could see where they will have value to some users. For instance, if you want to track people who might be on various mailing list. A beginner might create a table structure like:
[tt][green]
tblMailList
==============
PersonID
FirstName
LastName
Address
City
State
Zip
Phone
Christmas (y/n)
Family (y/n)
SkiClub (y/n)
Church (y/n)
Scouts (y/n)
Enemies (y/n)
[/green][/tt]

Now the same beginner might set up a multi-value field to store which list(s) a person is a member of. I feel this would be a huge improvement over multiple y/n fields. (I said the same in Redmond)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fneily]

Unfortunately that same beginner may think that they can now string out the names - Joe Smith, Bob Jones, etc. Oh well.

 

[dhookom]

That's where we can step in and charge huge fees for fixing stuff (I think I might have heard that comment in Redmond also).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]