poor design??

[Guest]

Hi everyone,

Just curious but is it considered poor design to have a query in backtable
lookup field? Meaning, I created a lookup field from another table in the
backend and in this lookup field, I went in a modified the lookup by making a
query that filters out certain things I don't want in the list or having the
data sort itself...something along those lines. Or would it be better to do
this through a separate query that sits outside the table and use that as the
lookup source?

Thanks,

Alan

 

[Amy Blankenship]

Hi everyone,

Just curious but is it considered poor design to have a query in backtable
lookup field? Meaning, I created a lookup field from another table in the
backend and in this lookup field, I went in a modified the lookup by
making a
query that filters out certain things I don't want in the list or having
the
data sort itself...something along those lines. Or would it be better to
do
this through a separate query that sits outside the table and use that as
the
lookup source?

Lookup fields are always poor design. You should use forms for these kinds
of operations.

HTH;

Amy

 

[Guest]

Hmm, ok I'm a bit confused.

For example if you have a table that is employee info (ie. empID, first
name, last name, department) and another table that shows quotations issued
with a field showing who issued the quote that's a lookup field based on the
employee table, this is considered poor design??

Alan

 

[Amy Blankenship]

I've rearranged your post so it is not top posted and, hence, makes sense to
respond to:

Hmm, ok I'm a bit confused.

For example if you have a table that is employee info (ie. empID, first
name, last name, department) and another table that shows quotations
issued
with a field showing who issued the quote that's a lookup field based on
the
employee table, this is considered poor design??

Yes. This is considered poor design. Access, for whatever reason, operates
in such a way that it encourages you to do this, but it is poor design.

The _proper_ design is to simply have a foreign key to the Employee table
(empID) and use a combobox on a form to fill it.

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

If you have any further questions, please post them at the _bottom_ of this
reply

 

[John W. Vinson]

For example if you have a table that is employee info (ie. empID, first
name, last name, department) and another table that shows quotations issued
with a field showing who issued the quote that's a lookup field based on the
employee table, this is considered poor design??

Yes.

As Amy says, most serious Access users consider Lookup Fields to be poor
design. Some don't; some consider them a vile invention of the Evil One sent
to mislead the unwary. See

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

A Lookup Field in a Table has only one good feature: it makes it a couple of
mouseclicks quicker to insert a Combo Box on to a Form. If you're using table
datasheets (with lookup fields or without them) to edit or enter data...
*don't*. Table Datasheets are for debugging only; all interaction with your
data should be done on Forms.

And it's easy (just a couple of mouseclicks extra <g>) to create a Combo Box
on a Form to look up an EmployeeID.


John W. Vinson [MVP]

 

['69 Camaro]

Everyone please note that Aaron Kem.pf is attempting to impersonate one of
our regular posters again. Tom would never post such a message.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Tom Wickerath]

I disagree

if your database gets confused using lookup fields, then move to ADP

with ADP i can always see the real values by using Query Analyzer or SQL
Server Management Studio

 

[BruceM]

Note that a lookup table and a lookup field are different things. If you
base the combo box row source on the Employee table, and use the combo box
to select an employee (and thus store EmployeeID in the Quotations table),
that is sound design (assuming the relationships are set up properly). If
you open the table directly and see a combo box in a field, that shows it is
a lookup field. It is also identified as such in table design view.

 

[Guest]

Ok, then I believe that's what I have here. The properties for the field show
it as a combo box that has the row source type based off of a "Table/Query"
which only stores the empID. That sounds like what you described below I
believe.