Re: Very slow query

i think you're making this a lot harder than it is, or else i'm missing
something about your tables design. to get rid of a Lookup field in a table,
open the table in Design view, click on the field you want to change, then
click on the Lookup tab at the bottom and change the DisplayControl setting
to Text Box. AFAIK, it's that simple - though i never create Lookup fields
in the first place, so it's possible there's an issue i'm unaware of.

if you're using Access2000 or newer, make sure that the Name Autocorrect
option is turned OFF. if it's currently turned on in your database, suggest
you do the following:
1. create a new blank database and immediately turn OFF the Name Autocorrect
option.
2. compact the database.
3. import all objects from your current database into the new one you just
created.
4. compact the database.
5. remove all the Lookup fields in your tables, as described in the
paragraph above.
6. compact the database.

run through the first three steps outlined in my previous post to this
thread, and see if your query works any faster than it did when there were
Lookup fields in the data table(s).

hth


"Alain T." <thibault.alain@xxxxxxxxx> wrote in message
news:eFvA0BQKHHA.3872@xxxxxxxxxxxxxxxxxxxxxxx
Hi Tina!

Thanks for the following informations. I will study them in the next few
days.

Meanwhile, as my database is in production for some months now, how will I
replace correctly my existing key fields?

I thought about those steps :
1. create a new fied in the Main table, like cSex
2. replace cSex with text value from secondary table based on the value in
the Main table called Sex (how will I do that?)
3. delete the field Sex in the Main table
4. rename the field cSex to Sex in the Main table
5. Repeat all those steps for my others 20 secondary tables...

After all those operations, modify all my secondary tables to remove all
the
keys and keep only one field ("text value") and link the fields from those
tables to my fieds on the Main table (step 3 below).

What do you think of that?

Alain T.

"tina" <nospam@xxxxxxxxxxx> a écrit dans le message de news:
0sXjh.575904$QZ1.83182@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
As I am not at all an Access expert

well, i'm not an Access "expert" either, Alain, so we have something in
common! ;)

can you guide me a bit please, ie what
do the experts do instead?

experienced Access developers draw a sharp line between database
structure
and user interface. tables fall cleanly into the structure side, and the
focus is on data normalization and data integrity. queries, forms, and
reports fall into the user interface category; the focus here is on
making
the data available to the user for display and interaction, in a format
that
is intuitive, easy to use, and supports the user's work process.
*presenting* the "text values" from secondary tables to the user, is a
user
interface issue, and so it is handled at the query/form/report level -
not
at the table level. recommend you use the following guidelines:

1) make sure your tables' relationships are correctly set up and
referential
integrity enforced, at the table level, using the Relationships window.
2) make sure that table fields that should be indexed, *are*, and fields
that don't need an index, *are not*.
3) in a query, add the "secondary" tables that are needed so that you
can
show the "text value" fields associated with the key values stored in
the
primary data table(s).
4) in reports, base the report on the type of query described above
(ususally).
5) in forms, base the form on the primary data table, and use combobox
or
listbox controls with RowSource property set to the appropriate
secondary
table, so that the user has access to the "text values" stored in the
secondary table.

if you're not familiar with combo box controls, suggest you read up on
them,
and their unique properties, in Access Help so that you have a thorough
understanding of how they work and how to use them.

hth




.

Relevant Pages

  • Re: Very slow query
    ... Alain T. ... it's that simple - though i never create Lookup fields ... create a new blank database and immediately turn OFF the Name ... reports fall into the user interface category; ...
    (microsoft.public.access.queries)
  • RE: Combo boxes work, then fail
    ... Select Row Source Type row and select Table/Query ... When I set up the field, using the Properties box, Data tab and RowSource ... If I close the database and re-open it, ... When the primary combo box is working, several lookup fields on the parent ...
    (microsoft.public.access.forms)
  • RE: Combo boxes work, then fail
    ... Open the form in design view and select the combo box. ... Select Row Source Type row and select Table/Query ... If I close the database and re-open it, ... When the primary combo box is working, several lookup fields on the parent ...
    (microsoft.public.access.forms)
  • RE: Combo boxes work, then fail
    ... Just a though - is the combox's Auto Expand set to = Yes. ... If I close the database and re-open it, ... characters of one of the names in the form' underlying table. ... When the primary combo box is working, several lookup fields on the parent ...
    (microsoft.public.access.forms)
  • Re: Relational database design
    ... in terms of using lookup fields in tables...... ... I have developed a database for recording 'alcohol related ... details on incident location and on last place of drink. ...
    (microsoft.public.access.tablesdbdesign)