Access Database question

Regarding INNER JOIN

[Noisehag]

Hi all,

I have an online app in Cold Fusion I'm working on where the query result is too large. I have about 6 INNER JOINS in one query and it gives me the following error.

[Microsoft][ODBC Microsoft Access Driver] Too many fields defined.

Is this a limitation of Access? It's not a big deal because I can break it up into several smaller queries, but I'm curious none the less. And if it is Access, is there another database format I can look to in the future if I have no way around it?

[DaveAtIFG]

This question went unanswered in Website Technology Issues for a few days. Does anyone have any ideas for Noisehag? Help! :)

[coopster]

The Microsoft Jet database engine limits the number of output fields that a query can have to 255 columns. Similar to an Access table, a query can have no more than 255 columns.

Note on

UPDATE

queries:
Note also that as the Microsoft Jet database engine iterates through the records in an

UPDATE

query, it creates a field for the original value and a field for the updated value. When more than 127 fields are selected, it reaches the 255 field limit of a query.

[Noisehag]

Thank you coopster. So the sum of the columns through multiple inner joins cannot exceed 255. That is good to know.

Is this true of all database formats or just Microsoft related?

[coopster]

Database-specific. But that sure is a chunk of columns to be returning, maybe change the design?

[Noisehag]

maybe change the design?

Yes, that is exactly what I did (design of the queries that is). I broke it up into several smaller queries. It's a custom real estate module and there are hundreds of fields representing any given record. Fortunately the main query doesn't require much information to output search results. A single record however...lots of data, mostly related to how the record is displayed based on settings the agent has chosen. I think I've taken Access to it's limit this time and will be looking to upgrade in the very near future.

At the moment I'm using Cold Fusion and Access. I am considering PHP and?. Any suggestions to a solid database format to compliment this would be greatly appreciated.

[coopster]

Well you could always move up from Access to MSSQL [webmasterworld.com].

There are quite a few databases available but MySQL [mysql.com] is often used with PHP and is the world's most popular open source database.