Access query help

Posted on 2012-09-19
Last Modified: 2012-09-24
SELECT DISTINCT [1_sq3_b_len1].adId, [1_sq3_b_len1].pk_ad_len_output, [1_sq3_b_len1].b_id AS d_id, [1_sq3_b_len1].b_db AS d_db, [1_sq3_b_len1].b_t AS d_t, [1_sq3_b_len1].b_cl AS d_cl, [1_sq3_b_len1].Int1, [1_sq3_b_len1].rer1, [1_sq3_b_len1].Con1, [1_sq3_b_len1].Rab1, t_rt_el.db AS Db1, AS t1, t_rt_el.column AS Col1, [1_sq3_b_len1].Int2, [1_sq3_b_len1].rer2, [1_sq3_b_len1].Con2, [1_sq3_b_len1].Rab2, t_rt_el_1.db AS Db2, AS t2, t_rt_el_1.column AS Col2, [1_sq3_b_len1].Int3, [1_sq3_b_len1].rer3, [1_sq3_b_len1].Con3, [1_sq3_b_len1].Rab3, t_rt_el_2.db AS Db3, AS t3, t_rt_el_2.column AS Col3, [1_sq3_b_len1].Int4, [1_sq3_b_len1].rer4, [1_sq3_b_len1].Con4, [1_sq3_b_len1].Rab4, t_rt_el_3.db AS Db4, AS t4, t_rt_el_3.column AS Col4, [1_sq3_b_len1].Int5, [1_sq3_b_len1].rer5, [1_sq3_b_len1].Con5, [1_sq3_b_len1].Rab5, t_rt_el_4.db AS Db5, AS t5, t_rt_el_4.column AS Col5, [1_sq3_b_len1].Int6, [1_sq3_b_len1].rer6, [1_sq3_b_len1].Con6, [1_sq3_b_len1].Rab6, t_rt_el_5.db AS Db6, AS t6, t_rt_el_5.column AS Col6, [1_sq3_b_len1].Int7, [1_sq3_b_len1].Con7, [1_sq3_b_len1].rer7, [1_sq3_b_len1].Rab7, t_rt_el_6.db AS Db7, AS t7, t_rt_el_6.column AS Col7, [1_sq3_b_len1].Int8, [1_sq3_b_len1].rer8, [1_sq3_b_len1].Con8, [1_sq3_b_len1].Rab8, t_rt_el_7.db AS Db8, AS t8, t_rt_el_7.column AS Col8, [1_sq3_b_len1].Int9, [1_sq3_b_len1].rer9, [1_sq3_b_len1].Con9, [1_sq3_b_len1].Rab9, t_rt_el_8.db AS Db9, AS t9, t_rt_el_8.column AS Col9, [1_sq3_b_len1].Int10, [1_sq3_b_len1].rer10, [1_sq3_b_len1].Con10, [1_sq3_b_len1].Rab10, t_rt_el_9.db AS Db10, AS t10, t_rt_el_9.column AS Col10, [1_sq3_b_len1].Int11, [1_sq3_b_len1].rer11, [1_sq3_b_len1].Con11, [1_sq3_b_len1].Rab11, t_rt_el_10.db AS Db11, AS t11, t_rt_el_10.column AS Col11, [1_sq3_b_len1].Int12, [1_sq3_b_len1].rer12, [1_sq3_b_len1].Con12, [1_sq3_b_len1].Rab12, t_rt_el_11.db AS Db12, AS t12, t_rt_el_11.column AS Co12, [1_sq3_b_len1].Int13, [1_sq3_b_len1].rer13, [1_sq3_b_len1].Con13, [1_sq3_b_len1].Rab13, t_rt_el_12.db AS Db13, AS t13, t_rt_el_12.column AS Col13, [1_sq3_b_len1].Int14, [1_sq3_b_len1].rer14, [1_sq3_b_len1].Con14, [1_sq3_b_len1].Rab14, t_rt_el_13.db AS Db14, AS t14, t_rt_el_13.column AS Col14, [1_sq3_b_len1].Int15, [1_sq3_b_len1].rer15, [1_sq3_b_len1].Con15, [1_sq3_b_len1].Rab15, t_rt_el_14.db AS Db15, AS t15, t_rt_el_14.column AS Col15, [1_sq3_b_len1].Int16, [1_sq3_b_len1].rer16, [1_sq3_b_len1].Con16, [1_sq3_b_len1].Rab16, t_rt_el_15.db AS Db16, AS t16, t_rt_el_15.column AS Col16
FROM (((((((((((((((1_sq3_b_len1 LEFT JOIN t_rt_el ON [1_sq3_b_len1].Rab1 = t_rt_el.pk_el) LEFT JOIN t_rt_el AS t_rt_el_1 ON [1_sq3_b_len1].Rab2 = t_rt_el_1.pk_el) LEFT JOIN t_rt_el AS t_rt_el_2 ON [1_sq3_b_len1].Rab3 = t_rt_el_2.pk_el) LEFT JOIN t_rt_el AS t_rt_el_3 ON [1_sq3_b_len1].Rab4 = t_rt_el_3.pk_el) LEFT JOIN t_rt_el AS t_rt_el_4 ON [1_sq3_b_len1].Rab5 = t_rt_el_4.pk_el) LEFT JOIN t_rt_el AS t_rt_el_5 ON [1_sq3_b_len1].Rab6 = t_rt_el_5.pk_el) LEFT JOIN t_rt_el AS t_rt_el_6 ON [1_sq3_b_len1].Rab7 = t_rt_el_6.pk_el) LEFT JOIN t_rt_el AS t_rt_el_7 ON [1_sq3_b_len1].Rab8 = t_rt_el_7.pk_el) LEFT JOIN t_rt_el AS t_rt_el_8 ON [1_sq3_b_len1].Rab9 = t_rt_el_8.pk_el) LEFT JOIN t_rt_el AS t_rt_el_9 ON [1_sq3_b_len1].Rab10 = t_rt_el_9.pk_el) LEFT JOIN t_rt_el AS t_rt_el_10 ON [1_sq3_b_len1].Rab11 = t_rt_el_10.pk_el) LEFT JOIN t_rt_el AS t_rt_el_11 ON [1_sq3_b_len1].Rab12 = t_rt_el_11.pk_el) LEFT JOIN t_rt_el AS t_rt_el_12 ON [1_sq3_b_len1].Rab13 = t_rt_el_12.pk_el) LEFT JOIN t_rt_el AS t_rt_el_13 ON [1_sq3_b_len1].Rab14 = t_rt_el_13.pk_el) LEFT JOIN t_rt_el AS t_rt_el_14 ON [1_sq3_b_len1].Rab15 = t_rt_el_14.pk_el) LEFT JOIN t_rt_el AS t_rt_el_15 ON [1_sq3_b_len1].Rab16 = t_rt_el_15.pk_el;

Open in new window

When I try to run the below query, I get an Access error message that the result size exceeds 2GB. I know that that's not true with this since it was run in the past. Can you please see what's wrong with this?
Question by:kris_sk2012
    1 Comment
    LVL 30

    Accepted Solution

    How many records do you have in tables: 1_sq3_b_len1  and t_rt_el?
    Tried to understand the query, using alias names for tables.

    SELECT DISTINCT a.adId, a.pk_ad_len_output, a.b_id AS d_id, a.b_db AS d_db, a.b_t AS d_t, a.b_cl AS d_cl, a.Int1, a.rer1, a.Con1, a.Rab1, b.db AS Db1, AS t1, b.column AS Col1, a.Int2, a.rer2, a.Con2, a.Rab2, b_1.db AS Db2, AS t2, b_1.column AS Col2, a.Int3, a.rer3, a.Con3, a.Rab3, b_2.db AS Db3, AS t3, b_2.column AS Col3, a.Int4, a.rer4, a.Con4, a.Rab4, b_3.db AS Db4, AS t4, b_3.column AS Col4, a.Int5, a.rer5, a.Con5, a.Rab5, b_4.db AS Db5, AS t5, b_4.column AS Col5, a.Int6, a.rer6, a.Con6, a.Rab6, b_5.db AS Db6, AS t6, b_5.column AS Col6, a.Int7, a.Con7, a.rer7, a.Rab7, b_6.db AS Db7, AS t7, b_6.column AS Col7, a.Int8, a.rer8, a.Con8, a.Rab8, b_7.db AS Db8, AS t8, b_7.column AS Col8, a.Int9, a.rer9, a.Con9, a.Rab9, b_8.db AS Db9, AS t9, b_8.column AS Col9, a.Int10, a.rer10, a.Con10, a.Rab10, b_9.db AS Db10, AS t10, b_9.column AS Col10, a.Int11, a.rer11, a.Con11, a.Rab11, b_10.db AS Db11, AS t11, b_10.column AS Col11, a.Int12, a.rer12, a.Con12, a.Rab12, b_11.db AS Db12, AS t12, b_11.column AS Co12, a.Int13, a.rer13, a.Con13, a.Rab13, b_12.db AS Db13, AS t13, b_12.column AS Col13, a.Int14, a.rer14, a.Con14, a.Rab14, b_13.db AS Db14, AS t14, b_13.column AS Col14, a.Int15, a.rer15, a.Con15, a.Rab15, b_14.db AS Db15, AS t15, b_14.column AS Col15, a.Int16, a.rer16, a.Con16, a.Rab16, b_15.db AS Db16, AS t16, b_15.column AS Col16
    FROM (((((((((((((((1_sq3_b_len1 As a LEFT JOIN t_rt_el AS b ON a.Rab1 = b.pk_el) LEFT JOIN t_rt_el AS b_1 ON a.Rab2 = b_1.pk_el) LEFT JOIN t_rt_el AS b_2 ON a.Rab3 = b_2.pk_el) LEFT JOIN t_rt_el AS b_3 ON a.Rab4 = b_3.pk_el) LEFT JOIN t_rt_el AS b_4 ON a.Rab5 = b_4.pk_el) LEFT JOIN t_rt_el AS b_5 ON a.Rab6 = b_5.pk_el) LEFT JOIN t_rt_el AS b_6 ON a.Rab7 = b_6.pk_el) LEFT JOIN t_rt_el AS b_7 ON a.Rab8 = b_7.pk_el) LEFT JOIN t_rt_el AS b_8 ON a.Rab9 = b_8.pk_el) LEFT JOIN t_rt_el AS b_9 ON a.Rab10 = b_9.pk_el) LEFT JOIN t_rt_el AS b_10 ON a.Rab11 = b_10.pk_el) LEFT JOIN t_rt_el AS b_11 ON a.Rab12 = b_11.pk_el) LEFT JOIN t_rt_el AS b_12 ON a.Rab13 = b_12.pk_el) LEFT JOIN t_rt_el AS b_13 ON a.Rab14 = b_13.pk_el) LEFT JOIN t_rt_el AS b_14 ON a.Rab15 = b_14.pk_el) LEFT JOIN t_rt_el AS b_15 ON a.Rab16 = b_15.pk_el;

    Open in new window

    Copy the query and use the above code as the sql. Check if no errors in the query.
    You may upload the 2 queries with the 2 tables for investigation.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now