Re: Number of left joins allowed in query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Tom,

Thanks for the suggestion. I'll keep it in mind if I need to do this sort
of thing in the future, but for now my flattened table to simulate the view
is all I need.

Rob

"Tom Ellison" <tellison@xxxxxxxxxxx> wrote in message
news:%23j5yIysBGHA.3292@xxxxxxxxxxxxxxxxxxxxxxx
> Dear Rob:
>
> You could also install the MSDE that comes with your Access and just write
> and use a SQL Server query. Set up a database that references your Access
> Jet tables. You may need to buy the SQL Server Developer Edition ($50)
> for the tools it would provide you. You can then use the optimizer and
> probably get this done in just a few seconds.
>
> Tom Ellison
>
>
> "Rob Parker" <robpparker@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:O%23CbOwrBGHA.312@xxxxxxxxxxxxxxxxxxxxxxx
>> Replying to my own post:
>>
>> Patience is a virtue ...
>>
>> My query with 26 left joins did eventually run - it simply took about a
>> minute to do so (with only about 3000) records in my main table. I'd
>> simply closed the "Not responding" Access application too soon in my
>> earlier attempts to run it.
>>
>> And, for the record, to fix this, I'm about to create my "View" as a real
>> table, by running a series of update queries on a copy of the original
>> table. Yes, it won;t be dynamic as an SQL Server view is, but it will
>> work for what I want now.
>>
>> Rob
>>
>> "Rob Parker" <robpparker@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:eg6xU%23pBGHA.4032@xxxxxxxxxxxxxxxxxxxxxxx
>>> Is there a limit to the number of left joins a query can have?
>>>
>>> I'm using Access 2002 , and trying to created the equivalent of a SQL
>>> Server View from a main table which has 26 related lookup tables. I can
>>> design the query in the QBE grid OK, but when I try to run it Access
>>> hangs.
>>>
>>> If there is a limit which I've exceeded, is there a work-around
>>> technique I could use to establish the "view" I need? Note: this is for
>>> design/debug purposes only - I'm not concerned with speed/performance
>>> issues, as the real app is actually built in SQL Server - I'm just
>>> using Access (since I don't have SQL Server at home) to do some
>>> exploratory work.
>>>
>>> TIA,
>>>
>>> Rob
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: Number of left joins allowed in query
    ... Yes, it won;t be dynamic as an SQL Server view is, but it will work ... Rob ... > Is there a limit to the number of left joins a query can have? ... as the real app is actually built in SQL Server - I'm just using ...
    (microsoft.public.access.queries)
  • Re: Number of left joins allowed in query
    ... and use a SQL Server query. ... as the real app is actually built in SQL Server - I'm just using ...
    (microsoft.public.access.queries)
  • Re: Number of left joins allowed in query
    ... As I said, the real application uses SQL Server, so this performance issue ... Rob ... > According to Access Help, you can have up to 32 Tables in a Query. ...
    (microsoft.public.access.queries)
  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Access to SQLServer GCE
    ... Actually, as Larry points out, the Access client does a good job of filtering only information that you need. ... If your query is bound to a report, and you open that report supplying a typical "where" for that report, then Access will NOT pull down all the records, but in most cases Access will only pull down the required records and respect your filter. ... So SQL server will respect the conditions and filtering placed into those queries, and therefore only pull down those records you require. ... The suggestion in these cases is to consider using a pass-through query since all of that summing is done before the row comes down the network pipe. ...
    (comp.databases.ms-access)