Re: Very slow query

Hi Tina!

Thanks for the information provided! And I thought I used the best
methodology to be efficient...

As I am not at all an Access expert, can you guide me a bit please, ie what
do the experts do instead?

Thank you!

Alain T.


"tina" <nospam@xxxxxxxxxxx> a écrit dans le message de news:
y9Bjh.269382$Fi1.238621@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
sounds like you're using Lookup fields in your Main table. if so,
recommend
you get rid of them - see
http://home.att.net/~california.db/tips.html#aTip8
for more information. as you have already found out, using the Lookup
fields
obscures what data is actually being stored in the table - leaving you to
find out the hard way, when you export the data - and does nothing to
simplify writing a query to show the "display" data from secondary tables
rather than the stored data in the main table.

per Access 2003 Help, the number of enforced relationships allowed in a
query are "32 per table minus the number of indexes that are on the table
for fields or combinations of fields that are not involved in
relationships". unless you've indexed a number of non-linked fields,
you're
well under that limit (though the limit may change for different versions
of
Access; check Help in your version by searching on "specifications").

hth


"Alain T." <thibault.alain@xxxxxxxxx> wrote in message
news:%23cHIWM3JHHA.2140@xxxxxxxxxxxxxxxxxxxxxxx
Hi!

I built a Main table, normalized, containing about 800 records. It will
grow to about 1600-2000 records. 21 secondary tables (code, description)
are linked to the Main table. All the keys are indexed. On the Main
table,
all secondary tables are link in the way showed in this sample :
SELECT TB_Groupes_Age.Code, TB_Groupes_Age.Description FROM
TB_Groupes_Age
ORDER BY TB_Groupes_Age.Description;
When I display the content of the Main table, all the fields that contain
the foreign key display correctly the description instead of the code.

In the query, if I only chose the Main table, it is very fast. When I
run
that query in Access, the fields that contains the foreign keys display
correctly the information from the secondary tables (for example, instead
of
displaying the code 123, It would display "Emergency"). But if I get the
data in Excel, I get the code "123" instead of "Emergency", which is not
useful for people looking at the stats. To solve that problem, in the
query, I display the Main table and all the secondary tables so that all
the
data are also transfered in Excel. It works very fast if I use 15
secondary
tables or less. More than that, big problem (after 90 minutes, it is
still
not finished...)!

Below, you see a sample of the query with the main table and three
secondary
ones (the construction is the same, but just bigger, with all the
secondary
tables added) :

SELECT SCPU.Dte_Visite, SCPU.Période, TB_Accompagnements.Description,
TB_Centres_Référants.Description, TB_Dangerosités.Description
FROM TB_Dangerosités INNER JOIN (TB_Centres_Référants INNER JOIN
(TB_Accompagnements INNER JOIN SCPU ON TB_Accompagnements.Code =
SCPU.Accompagné) ON TB_Centres_Référants.Code = SCPU.Centre_Référant) ON
TB_Dangerosités.Code = SCPU.Dangerosité;

Is there a way to speed up the query?

Thanks a lot for any information provided.

Alain T.






.

Relevant Pages

  • Re: Very slow query
    ... sounds like you're using Lookup fields in your Main table. ... simplify writing a query to show the "display" data from secondary tables ... the foreign key display correctly the description instead of the code. ...
    (microsoft.public.access.queries)
  • Very slow query
    ... I built a Main table, normalized, containing about 800 records. ... When I display the content of the Main table, ... the foreign key display correctly the description instead of the code. ... In the query, if I only chose the Main table, it is very fast. ...
    (microsoft.public.access.queries)
  • Re: Sum of numbers
    ... I do think a Union query is the way to go but you will probably need to ... To create a Union query, go to Query, New Query, but don't add any tables to ... Click on this Union Query and choose Insert, Report. ... I may just have to try to find a MS Access Expert in the Fort ...
    (microsoft.public.access.reports)
  • Re: Limit on Access where clause?
    ... Microsoft Access query specifications Attribute Maximum ... Number of enforced relationships 32 per table minus the number of ... Number of characters in a cell in the query design grid 1024 ...
    (microsoft.public.dotnet.languages.vb)
  • Re: load a filtered form based on user login
    ... the easiest way expecially because I'm not an access expert. ... Is the following code an alternative to creating the query or something I ... Also, I created a group(Supervisors) and added they supervisors to this group, ... you'd want the recordsource to be restricted to ...
    (microsoft.public.access.tablesdbdesign)
Loading