Too many left outer joins (2000/XP)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

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.

Reply | Quote

Access Help does say that the # of characters in a record can be 4000 when Unicode Compression is Yes.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote