Too many fields defined.... but not (1 Viewer)

JamesMcS · Jan 25, 2011

Morning all - I have an annoying problem, as usual... I've got a query with about 80 fields in it. When I run the query it comes up saying there's too many fields defined - I've done a cmopact/repair but it hasn't changed the result. Any ideas?

The query is based upon two other queries (originally it was just one, but there were about 280 fields in it, so I've had to split them up). The main query sums up data from fields in the two subqueries, reducing it from 280 to 80. The subqueries run OK by themselves.

vbaInet · Jan 25, 2011

It's still counting it as over 255. You would most likely need to break the Sum() into two queries.

How come you have over 280 fields in one table/query?

JamesMcS · Jan 25, 2011

Ah - I thought 'fields defined' was columns in the query design grid.

I'm doing quarterly sales figures for 27 countries for this and the last 2 years, so more than 280, thinking about it. Here's how it's set up at the moment:

Europe Query
Product ID, 12 markets, 11 fields per market plus a sum of all markets (1+(13x11) = 143 fields)

Asia Query
Product ID, 15 markets, 11 fields per market plus a sum of all markets (1+(16x11) = 176 fields)

All Markets Query
Product ID, Sum of Europe, 4 main markets from Europe, Sum of Asia (1+11+(4x11)+11 = 67 fields)

The Europe and Asia queries work fine by themselves, but the all markets one doesn't... totally annoying! I've changed the subqueries into make tables, running now, so we'll see if I can base the all markets query on those. 750,000 records in each subquery though, probably report back tomorrow

It's just so strange that the two larger queries work OK, but the smaller one doesn't. I might try importing everything into another DB to see if it's just stupid Access corruption again.

JamesMcS · Jan 25, 2011

And I may also convince my boss to put everything in SQL Server, one day...

JamesMcS · Jan 25, 2011

Wow, query design and PC power both better than I thought. I've replace the subqueries with tables and it seems to be working... just running it now so we'll see the results after cigarette and coffee break

vbaInet · Jan 25, 2011

How did you define the subquery? Select * From ...?

In some cases it takes into consideration the number of fields from the underlying sources especially if you're aggregating. It's not corruption, just the way it is.

I wouldn't go down the make-table path. As you know, you're breaking normalization rules and obviously doubling the task. If multiple users are trying to get this info then this process will break.

JamesMcS · Jan 25, 2011

It's a pretty complicated process, but no, selecting individual fields and giving them each an alias.

Yeah a bummer about normalization, but it's only ever going to be me using this particular database. Basically there's five or six ways that the users want the base data split - rolling year on year, calendar year on year, rolling 24 monthly, quarterly and so on. I'm trying to make it so that the front end only ever looks at tables, rather than the users having to wait for queries to run. So a massive amount of time spent repopulating all the tables every month but minimal user time spent getting hold of the data. So there's a ton of make tables, and their copy of the front end only ever looks at them like.

vbaInet · Jan 25, 2011

It is always much more effecient reading from a query anyway, that's what they are built for.

See how it goes.

JamesMcS · Jan 25, 2011

Seems to work OK now. I don't really want users having to hang around while the database compiles millions of records together, they'd never get anything done - so I went with the 'snapshot' plan - big back end but fast front end like.

Cheers for the replies though!

Brianwarnock · Jan 25, 2011

As I understand things, and I seem to remember it being confirmed in a discussion involving MVPs, the compiler always looks at all the cascading and sub queries that will run to produce the final result in an attempt to optimize the code, so sometimes the creation of temp tables may be necessary.

Brian

JamesMcS · Jan 25, 2011

I'm sure there's a better way of doing it - but that'll be for version 2, hopefully in SQLS....

Too many fields defined.... but not (1 Viewer)

JamesMcS

Keyboard-Chair Interface

vbaInet

AWF VIP

JamesMcS

Keyboard-Chair Interface

JamesMcS

Keyboard-Chair Interface

JamesMcS

Keyboard-Chair Interface

vbaInet

AWF VIP

JamesMcS

Keyboard-Chair Interface

vbaInet

AWF VIP

JamesMcS

Keyboard-Chair Interface

Brianwarnock

Retired

JamesMcS

Keyboard-Chair Interface

Similar threads

Users who are viewing this thread