Re: Very slow query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Tina!

Thanks for all the info! I will work on it very soon. Hope I have enough
to be put on the right track!

Alain T.

"tina" <nospam@xxxxxxxxxxx> a écrit dans le message de news:
Ytlkh.584094$QZ1.429578@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
    ... something about your tables design. ... it's that simple - though i never create Lookup fields ... create a new blank database and immediately turn OFF the Name Autocorrect ... reports fall into the user interface category; ...
    (microsoft.public.access.queries)
  • Re: Very slow query
    ... create a new blank database and immediately turn OFF the Name Autocorrect ... remove all the Lookup fields in my main table, ... With 14 secondary tables or less, my query runs in 1 sec or less. ... in reports, base the report on the type of query described above ...
    (microsoft.public.access.queries)
  • Re: Database
    ... recommend to both of you that you do NOT use Lookup fields in any table in ... also recommend that you don't use tables for data entry. ... whether the database is ... Reports use queries to look up the info in the manner you want. ...
    (microsoft.public.access.gettingstarted)
  • Re: SYSTEMS ENGINEER/ VAX-VMS/ CAREER POSITION
    ... System Administration and database admin from NIIT, ... salary details and generate reports and enclosures,and calculates ... Responsibilities: ... Write New programs from user requirements, Programming Changes ...
    (comp.os.vms)
  • Re: Advice needed for a growing Access 2000 project
    ... However, it turned out that quite a few of those were "leftovers" from previous releases, no longer accessible from anywhere but the database window, and, thus, no longer used. ... But that certainly isn't the _norm_ -- without any 'heroic' measures, there are routine reports of split Access DBs ... Finally, in my opinion, for "Windows apps", that is, individual-user applications, modest-sized multiuser applications, and client-server applications of any size, Dot Net does NOT "help along" any of these issues. ... The post I reference was in reference its self to the MS Access Help file under "Microsoft Access database general specifications" ...
    (comp.databases.ms-access)