Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Too many left outer joins (2000/XP)

    I have been asked to produce an output query with the following fields

    Landowner, Plot1, Area1, Description1, Requirements1, Remarks1 through to Plot32, Area32, Description32, Requirements32, Remarks32

    YES 32

    The column headings have to be exactly as written above.

    Landowner, Plot, Area, Description, Requirements and Remarks are column headings. A landowner has many plots, numbered 1 to (potentially) 32 in the Plot column.

    I've written some vba to loop and automatically create 32 queries, called qP1, qP2, qP32 etc.

    I have some more code than creates a super query, utilising the 32 subqeries,each joined to a main table using a left join.

    It starts like this "SELECT tblLandowner.Landowner, qP1.Plot AS Plot1, qP1.Area AS Area1, qP1.Description AS Description1, qP1.Req AS Req1, qP1.Remarks AS Remarks1" and goes on to list the 5 columns for each plot number group finishing at qP32.Plot AS Plot32, qP32.Area AS Area32, qP32.Description AS Description32, qP32.Req AS Req32, qP32.Remarks AS Remarks32

    Trouble is the query is too complicated. I've just checked and found the limit on the number of tables in a query (32) however Access struggles to open the query at around 20 tables. A super query using 16 of sub queries just about opens.

    I know this is hideous but has anyone got any suggestions for a different approach. I'm thinking of accepting the fact it doesn't quite work right. It will need to be exported to excel anyway. Could do it as 2 queries say 1-16 and 17-32 and copy and paste them side by side.

    <img src=/S/meltdown.gif border=0 alt=meltdown width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Too many left outer joins (2000/XP)

    You might design a table with the correct structure and use an append query to populate it with landowners, then 32 update queries to fill the 32 blocks of 5 columns.

    Without knowing how the data are organized it's difficult to suggest something else.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too many left outer joins (2000/XP)

    You know, I think that could be what I intended to to in the first place.

    Any idea how unhappy Access would be with a table with approx 170 fields - hopefully, the fields wouldn't contain much text and there's a fair chance that half or more of them would be empty.

    We've got to potentially accomodate 32 plot numbers though, meaning 32 x 5 fields.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Too many left outer joins (2000/XP)

    A table (or query) can have up to 255 fields, so that is not the problem. The maximum size of all the fields in a record combined (excluding Memo and OLE fields) is 2,000 bytes (characters), so if most of the fields are blank or contain little text, you should be OK. You wouldn't be able to accomodate 161 fields with 20 characters each, though.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too many left outer joins (2000/XP)

    I think I'm going to have to see what happens when I actually get given some more data. I only have a few records to work with at the moment. I remember the first db I was involved in - it worked fine until the users entered data <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>

    I'm glad I've figured out table definition queries which, as the troublesome fields have a sequential numerical suffix, made it pretty easy to write a loop to create the sql for the table.

    Thanks for the advice.

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,329
    Thanks
    1
    Thanked 13 Times in 13 Posts

    Re: Too many left outer joins (2000/XP)

    Access Help does say that the # of characters in a record can be 4000 when Unicode Compression is Yes.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 18 Times in 18 Posts

    Re: Too many left outer joins (2000/XP)

    I think that's for Access 2003 and 2007. In Access 2000 and 2002 (mentioned in the subject of this thread) it was 2,000 characters, as far as I know.

  8. #8
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too many left outer joins (2000/XP)

    Not sure I have quite got it, but, couldn't you just add one field that you populate with a plot counter for each plot for each Landowner, and then when you go to output, use as the column title a concatenation of the field name and the plot counter? Would that work?

    Pat

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •