a tricky view to create in Access; SQL is welcome !

Hi I am now sitting for almost ages in front of the screen, and may be
someone can help me...

Here's the thing

I have a table that looks like the following:

Mat-Nr|Package-Rule|Package-Class|Package-Material|Package-Nr|Amount|

Mat-Nr: There are several million different materials
Package-Rule: there are only in total 2 different package rules
Package-Class: there are in total 9 different classes
Package-Material: there are 4 different package materials
Package-Nr: each package material has 1 or more package-numbers
Amount: there is at least 1 material in the amount

An example looks like this:

Mat-Nr|Package-Rule|Package-Class|Package-Material|Package-Nr|Amount|
102 01 01 V 003 10
102 01 01 Z 045 03
116 01 01 V 056 23
116 01 01 Z 067 20

My problem is now, I have to create a query or view in Access 2000 so
that the following information is in ONE row:

Mat-Nr|Package-Rule|Package-Class|Package-Material|Package-Nr|Amount|
102 01 01 V 003 10
..
..
..
Package-Material|Package-Nr|Amount|
Z 045 03
..
..
..

thank you very much
Stefanal_capone@.web.de (Stefan) wrote in message news:<249f98b3.0407070650.75f1ead5@.posting.google.com>...
> Hi I am now sitting for almost ages in front of the screen, and may be
> someone can help me...
> Here's the thing
> I have a table that looks like the following:
> Mat-Nr|Package-Rule|Package-Class|Package-Material|Package-Nr|Amount|
>
> Mat-Nr: There are several million different materials
> Package-Rule: there are only in total 2 different package rules
> Package-Class: there are in total 9 different classes
> Package-Material: there are 4 different package materials
> Package-Nr: each package material has 1 or more package-numbers
> Amount: there is at least 1 material in the amount
> An example looks like this:
> Mat-Nr|Package-Rule|Package-Class|Package-Material|Package-Nr|Amount|
> 102 01 01 V 003 10
> 102 01 01 Z 045 03
> 116 01 01 V 056 23
> 116 01 01 Z 067 20
>
> My problem is now, I have to create a query or view in Access 2000 so
> that the following information is in ONE row:
> Mat-Nr|Package-Rule|Package-Class|Package-Material|Package-Nr|Amount|
> 102 01 01 V 003 10
> .
> .
> .
> Package-Material|Package-Nr|Amount|
> Z 045 03
> .
> .
> .
> thank you very much
> Stefan

Hello Stefan,

Just one question. Are you dealing with only two records for each
Mat-Nr field data? Meaning 102, 102, 166, 166 etc.?

If so, then perhaps you can make a grouping query to get
your data result like the following.

Create a query using your source table with all the fields.

Next, change it to a grouping/summing query.

Then, edit your last three fields to be like the example below.

PackMat01: Package-Material
PackNr01:Package-Nr
Amount01: Amount

Now, change Grouping to First

Then add 3 more fields accordingly.

PackMat02: Package-Material
PackNr02:Package-Nr
Amount02: Amount

Now, change Grouping to Last

Run the query. What this should show is the First record for 102
and the last record for 102 in your grouping query for the 01 and
02 field groups according to your requirements.

Good luck!

Regards,

Ray