K
kgusenet
Hi Folks,
Hope you're having a better weekend than me, helping my wife for a
project she's turning in for her job on Monday.
In Access 2007, I am unable to view a report that we just created.
It's based on a a query with 26 fields. Call it the summary query.
Each of these fields are from 13 other queries, ~2 fields from each
query.
The Summary query runs just fine. I get two rows of for the test data
that populate the DB so far.
Some of the data returned have "#Errors" in them as expected I'm doing
some left joins that pull in columns that don't have any data that
match up.
When I try to run view the report I get a "Too many fields" OK
dialog. I compacted the database but that hasn't helped. If I count
all the fields of the 13 tables it's probably close to 255, I guess
over 255.
Anything else I can try?
Can I save the results of the Summary Query to a temp table and have
the report run off that?
I pasted the "query of queries" below (sorry don't have my sql
formatting tool at home to make this sucker more intelligible):
SELECT [A) Master Form].ID, [A) Master Form].[Supplier Name:], [D) Eng
and Project Management Totals].[Project Engineering Rating], [D) Eng
and Project Management Totals].[Project Management Rating], [E)
Injection Molding Capabilities Query].Avg AS [Injection Molding
Capabilities Avg], [E) Injection Molding Capabilities Query].Rating AS
[Injection Molding Capabilities Rating], [F) Blow Molding Capabilities
Query].Avg AS [Blow Molding Capabilities Avg], [F) Blow Molding
Capabilities Query].Rating AS [Blow Molding Capabilities Rating], [G)
Compression Molding Query].Avg AS [Compression Molding Avg], [G)
Compression Molding Query].Rating AS [Compression Molding Rating], [H)
Thermoforming Capabilities Query].Avg AS [Thermoforming Capabilities
Avg], [H) Thermoforming Capabilities Query].Rating AS [Thermoforming
Capabilities Rating], [I) Tube Manufacturing Query].Avg AS [Tube
Manufacturing Avg], [I) Tube Manufacturing Query].Rating AS [Tube
Manufacturing Rating], [J) Glass Blowing Capabilities Query].Avg AS
[Glass Blowing Capabilities Avg], [J) Glass Blowing Capabilities
Query].Rating AS [Glass Blowing Capabilities Rating], [K) Tubular
Glass Forming Query].Avg AS [Tubular Glass Forming Avg], [K) Tubular
Glass Forming Query].Rating AS [Tubular Glass Forming Rating], [M)
Assembly Decoration Capabilities Query].Avg AS [Assembly Decoration
Capabilities Avg], [M) Assembly Decoration Capabilities Query].Rating
AS [Assembly Decoration Capabilities Rating], [N) Tool Making
Automation Query].Avg AS [Tool Making Automation Avg], [N) Tool Making
Automation Query].Rating AS [Tool Making Automation Rating], [O)
Process Control Query].Avg AS [Process Control Avg], [O) Process
Control Query].Rating AS [Process Control Rating], [P) Tool
Qualification Procedures Query].Avg AS [Tool Qualification Procedures
Avg], [P) Tool Qualification Procedures Query].Rating AS [Tool
Qualification Procedures Rating]
FROM ((((((((((([A) Master Form] LEFT JOIN [D) Eng and Project
Management Totals] ON [A) Master Form].ID = [D) Eng and Project
Management Totals].ID) LEFT JOIN [E) Injection Molding Capabilities
Query] ON [A) Master Form].ID = [E) Injection Molding Capabilities
Query].ID) LEFT JOIN [F) Blow Molding Capabilities Query] ON [A)
Master Form].ID = [F) Blow Molding Capabilities Query].ID) LEFT JOIN
[G) Compression Molding Query] ON [A) Master Form].ID = [G)
Compression Molding Query].ID) LEFT JOIN [H) Thermoforming
Capabilities Query] ON [A) Master Form].ID = [H) Thermoforming
Capabilities Query].ID) LEFT JOIN [I) Tube Manufacturing Query] ON [A)
Master Form].ID = [I) Tube Manufacturing Query].ID) LEFT JOIN [J)
Glass Blowing Capabilities Query] ON [A) Master Form].ID = [J) Glass
Blowing Capabilities Query].ID) LEFT JOIN [K) Tubular Glass Forming
Query] ON [A) Master Form].ID = [K) Tubular Glass Forming Query].ID)
LEFT JOIN [M) Assembly Decoration Capabilities Query] ON [A) Master
Form].ID = [M) Assembly Decoration Capabilities Query].ID) LEFT JOIN
[N) Tool Making Automation Query] ON [A) Master Form].ID = [N) Tool
Making Automation Query].ID) LEFT JOIN [O) Process Control Query] ON
[A) Master Form].ID = [O) Process Control Query].ID) LEFT JOIN [P)
Tool Qualification Procedures Query] ON [A) Master Form].ID = [P) Tool
Qualification Procedures Query].ID;
Hope you're having a better weekend than me, helping my wife for a
project she's turning in for her job on Monday.
In Access 2007, I am unable to view a report that we just created.
It's based on a a query with 26 fields. Call it the summary query.
Each of these fields are from 13 other queries, ~2 fields from each
query.
The Summary query runs just fine. I get two rows of for the test data
that populate the DB so far.
Some of the data returned have "#Errors" in them as expected I'm doing
some left joins that pull in columns that don't have any data that
match up.
When I try to run view the report I get a "Too many fields" OK
dialog. I compacted the database but that hasn't helped. If I count
all the fields of the 13 tables it's probably close to 255, I guess
over 255.
Anything else I can try?
Can I save the results of the Summary Query to a temp table and have
the report run off that?
I pasted the "query of queries" below (sorry don't have my sql
formatting tool at home to make this sucker more intelligible):
SELECT [A) Master Form].ID, [A) Master Form].[Supplier Name:], [D) Eng
and Project Management Totals].[Project Engineering Rating], [D) Eng
and Project Management Totals].[Project Management Rating], [E)
Injection Molding Capabilities Query].Avg AS [Injection Molding
Capabilities Avg], [E) Injection Molding Capabilities Query].Rating AS
[Injection Molding Capabilities Rating], [F) Blow Molding Capabilities
Query].Avg AS [Blow Molding Capabilities Avg], [F) Blow Molding
Capabilities Query].Rating AS [Blow Molding Capabilities Rating], [G)
Compression Molding Query].Avg AS [Compression Molding Avg], [G)
Compression Molding Query].Rating AS [Compression Molding Rating], [H)
Thermoforming Capabilities Query].Avg AS [Thermoforming Capabilities
Avg], [H) Thermoforming Capabilities Query].Rating AS [Thermoforming
Capabilities Rating], [I) Tube Manufacturing Query].Avg AS [Tube
Manufacturing Avg], [I) Tube Manufacturing Query].Rating AS [Tube
Manufacturing Rating], [J) Glass Blowing Capabilities Query].Avg AS
[Glass Blowing Capabilities Avg], [J) Glass Blowing Capabilities
Query].Rating AS [Glass Blowing Capabilities Rating], [K) Tubular
Glass Forming Query].Avg AS [Tubular Glass Forming Avg], [K) Tubular
Glass Forming Query].Rating AS [Tubular Glass Forming Rating], [M)
Assembly Decoration Capabilities Query].Avg AS [Assembly Decoration
Capabilities Avg], [M) Assembly Decoration Capabilities Query].Rating
AS [Assembly Decoration Capabilities Rating], [N) Tool Making
Automation Query].Avg AS [Tool Making Automation Avg], [N) Tool Making
Automation Query].Rating AS [Tool Making Automation Rating], [O)
Process Control Query].Avg AS [Process Control Avg], [O) Process
Control Query].Rating AS [Process Control Rating], [P) Tool
Qualification Procedures Query].Avg AS [Tool Qualification Procedures
Avg], [P) Tool Qualification Procedures Query].Rating AS [Tool
Qualification Procedures Rating]
FROM ((((((((((([A) Master Form] LEFT JOIN [D) Eng and Project
Management Totals] ON [A) Master Form].ID = [D) Eng and Project
Management Totals].ID) LEFT JOIN [E) Injection Molding Capabilities
Query] ON [A) Master Form].ID = [E) Injection Molding Capabilities
Query].ID) LEFT JOIN [F) Blow Molding Capabilities Query] ON [A)
Master Form].ID = [F) Blow Molding Capabilities Query].ID) LEFT JOIN
[G) Compression Molding Query] ON [A) Master Form].ID = [G)
Compression Molding Query].ID) LEFT JOIN [H) Thermoforming
Capabilities Query] ON [A) Master Form].ID = [H) Thermoforming
Capabilities Query].ID) LEFT JOIN [I) Tube Manufacturing Query] ON [A)
Master Form].ID = [I) Tube Manufacturing Query].ID) LEFT JOIN [J)
Glass Blowing Capabilities Query] ON [A) Master Form].ID = [J) Glass
Blowing Capabilities Query].ID) LEFT JOIN [K) Tubular Glass Forming
Query] ON [A) Master Form].ID = [K) Tubular Glass Forming Query].ID)
LEFT JOIN [M) Assembly Decoration Capabilities Query] ON [A) Master
Form].ID = [M) Assembly Decoration Capabilities Query].ID) LEFT JOIN
[N) Tool Making Automation Query] ON [A) Master Form].ID = [N) Tool
Making Automation Query].ID) LEFT JOIN [O) Process Control Query] ON
[A) Master Form].ID = [O) Process Control Query].ID) LEFT JOIN [P)
Tool Qualification Procedures Query] ON [A) Master Form].ID = [P) Tool
Qualification Procedures Query].ID;