How to Handle 10-30 Line Queries in Microsoft Access

What Microsoft Access queries can and cannot do with line limits

Microsoft Access queries do not have a built-in hard limit of 10 or 30 lines. The confusion usually comes from one of three places: the query design grid display, the SQL statement itself, or memory constraints when working with very large datasets.

The query design grid — the visual interface where you drag fields and set criteria — can display only a certain number of rows on screen at once, but you can scroll to add more conditions. The underlying SQL statement that Access generates has no fixed line count. What matters instead is the complexity of your query and the size of the data it processes.

If you are hitting a wall at 10 or 30 lines, the problem is usually one of these: you are trying to add too many criteria to a single query, your query is trying to join too many tables, or Access is running out of memory because the dataset is too large.

Key Takeaways

The query design grid shows only a limited number of rows on screen, but you can scroll down to add as many criteria rows as your query needs.
If your query becomes too complex with many joins or conditions, split it into multiple queries or use a subquery instead of stacking everything into one.
Access queries process data in memory, so very large tables may slow down or fail — consider filtering the data first or moving to a larger database like SQL Server.
The SQL view shows the actual query code and has no line limit; you can write queries of any length as long as the logic is sound.

Working with the query design grid when it feels cramped

The query design grid in Access shows a fixed number of criteria rows depending on your screen resolution and zoom level. On a standard monitor at 100% zoom, you typically see 5 to 8 rows at once. This is purely a display issue, not a limit on how many criteria you can add.

To add more criteria beyond what is visible, scroll down in the grid using the scroll bar on the right side, or use the arrow keys to move down. Each row you add represents one additional condition in your WHERE clause. You can add as many rows as your query logic requires — there is no built-in maximum.

If your query becomes difficult to read because you have many criteria, consider using the SQL view instead. Switch to SQL view by right-clicking the query tab and selecting "SQL View", or by clicking the SQL button in the toolbar. In SQL view, you write the query as text, which is often clearer when you have complex logic.

Splitting complex queries into multiple steps

If you find yourself adding 20, 30, or more criteria rows, the real problem is usually that your query is doing too much at once. A better approach is to break it into multiple queries, where each one does a single job and passes its results to the next.

For example, instead of one query that filters by date, sums amounts, joins three tables, and groups by region, create a query that joins the tables first, then create a second query that filters by date and sums, then a third that groups by region. Each query is simpler and easier to debug. Access calls the first query a "base query" and the second a "query on a query".

To create a query based on another query, open the query wizard and select your first query as the data source instead of a table. Access treats it exactly like a table. This approach also runs faster because Access can optimize each step separately.

Using subqueries when you cannot split the work

Sometimes you need all the logic in one place — for example, when you are building a report or a form that pulls from a single query. In that case, use a subquery in SQL view instead of adding row after row in the design grid.

A subquery is a query inside a query. In SQL, it looks like this: SELECT * FROM (SELECT field1, field2 FROM table1 WHERE condition1) AS subquery1 WHERE condition2. The inner query runs first, and the outer query filters or processes its results.

Subqueries are harder to read than split queries, but they are more powerful. You can nest them several levels deep, and they let you do things the design grid cannot express easily. If you are not comfortable writing SQL, stick with splitting your query into multiple steps instead.

Handling memory and performance when queries slow down

Access stores query results in memory while it processes them. If your table has hundreds of thousands of rows and your query is complex, Access may slow down dramatically or run out of memory before finishing.

The first step is to add a filter to your query that reduces the dataset before processing. For example, if you are querying a table of transactions, filter by date range first — only pull the last 12 months instead of all data ever. This shrinks the working dataset and makes the query faster.

The second step is to check whether you are joining tables unnecessarily. Each join adds complexity. If you are joining five tables but only using fields from three, remove the unused joins. In the query design grid, right-click the join line and delete it.

If the query still runs slowly, consider moving the data to a larger database like SQL Server or MySQL. Access is designed for databases under 2 GB; beyond that, performance degrades. You can link Access to a SQL Server database and run queries against it, which gives you much more power without changing your Access forms and reports.

Switching between design view and SQL view

The design grid and SQL view are two ways of looking at the same query. You can switch between them freely, and Access translates your design grid clicks into SQL automatically.

To switch to SQL view, right-click the query tab at the top and select "SQL View". To switch back to design view, right-click and select "Design View". Some queries created in SQL view cannot be displayed in the design grid — for example, if you use a UNION statement or certain functions — but you can still run them.

If you are new to SQL, start in design view and switch to SQL view to see what Access generates. Over time, you will learn the SQL syntax and be able to write queries directly in SQL view, which is faster for complex logic.

Frequently Asked Questions

Why does my query stop letting me add criteria after 10 or 30 rows?

Access itself does not stop you, but your computer may run out of memory or the query may become too slow to work with. Try filtering your data first to reduce the number of rows being processed, or split your query into multiple simpler queries.

Can I write a query with no line limit in SQL view?

Yes. SQL view has no practical line limit. You can write queries of any length as long as the syntax is correct and your computer has enough memory to process the data. SQL view is often clearer for complex queries than the design grid.

What is the difference between adding criteria in the design grid versus writing SQL?

The design grid is visual and easier for beginners, but it can feel cramped with many criteria. SQL view shows the actual code and is more flexible for complex logic. Both produce the same results — the design grid is just a tool that generates SQL behind the scenes.

Will splitting my query into multiple queries make it slower?

No. Multiple simple queries usually run faster than one complex query because Access can optimize each step. The only downside is that you need to manage more query objects, but the performance gain is worth it.

How do I know if my query is too complex?

If it takes more than a few seconds to run, uses more than five joins, or has more than 15 criteria rows, consider splitting it. If you cannot understand what the query does by reading it, it is too complex. Simpler queries are easier to fix when something goes wrong.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.