Too Many Fields Defined in Query

[Guest]

Hi,

I have created a table from several queries results ( about 230 fields).
Then I created a query using all of the fields from this table with some
other fileds from another table, however I get "Too Many Fields Defined"
error.

Is there a limitation to the number of fields? If yes, how can I get around
this?

The query is basically getting about 30 data elements for each month per
employee.

for example, hours worked from Nov04 through June05, hours billed from
nov04 through June05 etc.

I would eventually need to have 12 months data for these data elements.

Any help would be greatly appreciated.

Thanks

 

[Rick B]

Yes, you can have (I think 255 fields). Check the help fiel and look for
"specifications".

You should never need more than about 20 or 30 fields if your databse
structure is corect.


Seems like your query would be only about 3 fields and two queries.

I could see one table (hours worked) contining these fields...
EMPLOYEE
DATE
HOURSWORKED

I could see a billed table contianing these fields.
EMPLOYEE
DATE
HOURSBILLED
CLIENTNUMBER


Really, I'd most likely do all this in one table as...
EMPLOYEE
DATE
HOURSWORKED
BILLED Y/N
CLIENTNUMBER


I can't imagine how you are getting 200+ fields out of this.

 

[Rick B]

Okay, I just reread you post. Why did you create a table from your query?

What is your data structure and what are you trying to get?

If you have thirty fields in your table, then you should have thirty fields
in your query. (Not sure why you'd have thirty fields in one table though -
are you using Access as a relational database and storing related data in
separate tables like you should, or are you just using Access like it is a
big spreadsheet and putting redundant data in your tables?)

If you want to combine the data and report monthly totals, then I'd think
you'd create a crosstab query.

You'd have to give us some spcifics though.

It sounds like your data structure is badly flawed.

 

[Van T. Dinh]

A Query can have up to 255 Fields defined. However, during the session,
IIRC, the Field count is not reset if you delete a Field / Column so Access
still counts these deleted Columns untill you exit from the Query Design and
re-open it.

OTOH, if your Table Structure is correctly designed and implemented, you
should never come any close to the 255 limits. Even with very complex
Queries, I don't think I got one with more than 50 Columns in DesignView.
Cross-Tab Queries in DatasheetView are the exceptions since the Columns come
from data and not the Table / Query design.

 

[Guest]

I am linking to an external database and extracted the data I needed into a
table

the Table has the fields:
Employee
Month
hoursworked
hoursbilled
collectionsonworkhrs
collectiononbillhrs
stdval
extval
etc


I then have several crosstab queries that I linked to create a final table
with data that shows a separate field for each month, that is,

jan hrsworked
feb hrsworked
mar hrsworked
apr hrsworked
etc

jan hrsbilled
feb hrsbilled
mar hrsbilled
apr hrsbilled
etc

and so on for all the fields -

with just these two data elements I have 8 fields (now that June is being
added the number of fields exceeds 255)

The reason I am doing this is to produced a report for each employee as
follows:

Emplyee Name Jan Feb Mar Apr May June etc Total
Hours Worked 20 40 60 100 50 100
Hours Billed
std val
ext val

Hope this makes sense


Thanks

 

[Duane Hookom]

Why create such an un-normalized table? Apparently you understand how to
create a crosstab.

 

[Guest]

Should I normalized my table using the month?

or create the crosstab with a "temp" table that has the month?

and wouldn't this still give me more than 255 fields?

I guess my main problem is showing the months as columns and the data
elements as rows.

 

[Duane Hookom]

There are several solution to creating your report based on a crosstab. The
first thing I would do rather than creating a less normalized table is to
create a more normalized table that has HoursWorked, HoursBilled, etc as
HoursType field values rather than as separate fields. You can then create a
crosstab that has just the months as column headings. HoursType becomes a
Row Heading.

 

[Guest]

Thanks Duane - I will try your suggestion -

I am assuming this will not exceed the 255 fields as I have at least 25 dat
elements(hoursworked, hrsbilled etc) so

 

[Duane Hookom]

Your new table should not have separate fields for HoursWorked and
HoursBilled. This should create two records where once there was one.

Employee Month HoursWorked HoursBilled
Duane 7 160 250

Should be changed to the following possibly using a union query.

Employee Month HoursType Hours
Duane 7 Worked 160
Duane 7 Billed 250

 

[Guest]

Hello Duane,
I am able to create the table as you suggested that is

Employee Month HoursType Hours
Duane 7 Worked 160
Duane 7 Billed 250

However, I am having a problem with my report that I am trying to create
from the new table.

I need to arange the "hoursType" field and also make calculations on
specific values.

for example - I need to calculate the ytd realization which is the total hrs
billed divided by total hours worked.

Some fields will need to be substracted from another etc

How can I accomplished this.

Thanks for your help.

Deo.

 

[Duane Hookom]

Reply in newer thread