grouped query on table with 255 fields

[arno]

Hello,

I have a table with 255 fields. I would like to use a grouped query with all
255 fields in the result but this does not work.

Before I show what I already did, I would like to describe the problem
behind:
I have a table with many invoices to one project (an I have many projects).
I know which invoices is the last one. Now, I would like to make a query
that gives me only the last invoices of each project. My field names are:
Project=nr, highest number in last Invoice=KDSTichtag.

I need:
- all 255 fields
- the field names must be the same as in the original table
- I have to show all data of only the record that has the maximum value in
KDSTichtag

I think that a simple grouped query will not do the job. This is where I am
stuck:

The query looks like this
SELECT last(STICHTAG) as Stichtag, max(KDStichtag) as KDStichtag,
last(s_typ) as s_typ, nr, last(mc) as mc, .....etc....
FROM s_ProjektCo
GROUP BY nr ;

Now I have 2 problems:

1) From Excel using ADO
I get the error "Error -2147467259 Too many fields defined. (Source:
Microsoft JET Database Engine) (SQL State: 3190) (NativeError: -68092944)"

The fields are named correctly, however, I can have only 254 fields!

2) Access Query
In Access I get an error (translated): "Alias 'Stichtag' caused a circular
reference in the SELECT-List of the query definition."

To make the query run, I have to use different aliases AND I must delete one
field so that I query only 254 fields.

Best regards

arno

 

[arno]

Hi!

*PROBLEM SOLVED*

I did not know that it would work that way but it does:

SELECT * FROM s_projektco
WHERE nr&Kdstichtag IN
(Select nr&max(Kdstichtag) from s_projektco group by nr)
ORDER BY nr, kdstichtag ;


Best regards

arno

 

[John Vinson]

Hello,

I have a table with 255 fields. I would like to use a grouped query with all
255 fields in the result but this does not work.

Before I show what I already did, I would like to describe the problem
behind:
I have a table with many invoices to one project (an I have many projects).
I know which invoices is the last one. Now, I would like to make a query
that gives me only the last invoices of each project. My field names are:
Project=nr, highest number in last Invoice=KDSTichtag.

You would find this a GREAT deal easier if you would properly
normalize your tables. Your current structure is a perfectly valid
spreadsheet design, but Access IS NOT A SPREADSHEET!

If you have a many to many relationship, model it *as a many to many
relationship*. This data should have *three* tables: a table of
Projects (with no invoice information); a table of Invoices (with no
product information); and a "resolver" table ProjectInvoices, with
fields ProjectID and InvoiceID. Rather than adding one *field* per
invoice, you would add one *record* per invoice.

 

[arno]

Hi John,

You would find this a GREAT deal easier if you would properly
normalize your tables. Your current structure is a perfectly valid
spreadsheet design, but Access IS NOT A SPREADSHEET!

I know what you mean, however, how can I grouped query have more fields than
the original table when I specify each single field? Anyway, I think now,
that grouping is the wrong way as you can see from my solution.

What I have _is_ a spreadsheet table with I think 8 datasources combined,
the table is the final data source of an excel-report, so linking tables
would not work here.

Best regards

arno

 

[John Vinson]

Hi John,


I know what you mean, however, how can I grouped query have more fields than
the original table when I specify each single field? Anyway, I think now,
that grouping is the wrong way as you can see from my solution.

What I have _is_ a spreadsheet table with I think 8 datasources combined,
the table is the final data source of an excel-report, so linking tables
would not work here.

If you're pushing 255 fields, someday you'll need 258... and you will
be out of luck!

I'd suggest creating *normalized* tables in Access, linking to your
spreadsheets, and running Append queries to move the data into your
five- or six-field tables; you'll then be able to do your grouping
easily.