"I want to see each item and all its parts on one line". - User

[johnnymac43]

Hi All,

Many of us have have had to respond to a user request to see all of the data for an item and its parts on one line of a screen or report.

Often, those without relational design skills build their tables to accommodate the user requirement. Some of these later encounter problems with other user requests that would be easily met with a normalised data structure.

Many with a structured database have trouble complying with this type of request. While a crosstab query can sometimes provide an acceptable solution, it is not always the best answer.

By way of example, consider the subject requirement based on the data set shown below. (Note that this data set could originate from a single table, a one to many relationship between two tables, or a many to many relationship via a link table).

ItemName PartName

Car        Body
Car        Engine
Car        Wheel
Tank       Body
Tank       Gun
Tank       Track
Truck      Cab
Truck      Chassis
Truck      Tray
Truck      Wheel

A crosstab will give the following unhelpful result (and could extend to an unmanageable number of columns on 'real' data):

ItemName Body Cab Chassis Engine Gun Track Tray Wheel

Car      Body            Engine               Wheel
Tank     Body                  Gun Track
Truck         Cab Chassis                Tray Wheel

The desired result, as shown below, can be achieved by a set of queries if the maximum number of parts for an item is known (or if a limit that covers most items can be set).

ItemName PartName1 PartName2 Partneme3 PartName4

Car      Body     Engine    Wheel
Tank     Body     Gun       Track
Truck    Cab      Chassis   Tray     Wheel

The steps required to achieve this are:

1. Gather the required data into a single table (tblItem) (as per the above example) and add a logical field to record when each line has been processed.

2. Run a grouping Make Table query with the following fields:
> Item identifying field/s, eg. ItemName;
> The Min value of the part identifying field for each item, eg. PartName (call this field PartName1);
> Empty named fields for each additional part for an item (PartName2 to PartNamenn).

3. Run an Update query on the source table, linked to the expanded table on the item identifying field/s, and where tblItemartName = PartName1, to update the processed flag.

4. Run a grouping Append query to place the item identifying field/s and the Min value of the part identifying field (in unprocessed records) on the expanded table (in field PartName2).

5. Run the Update query (modified to match with PartName2).

6. Run the Append query (modified to populate field PartName3) and then the Update query (PartName3).

7. Repeat the Append and Update queries for each PartName up to the maximum (PartNamenn).

8. Run a grouping Make Table query on the item identifying field/s of the expanded table, selecting the Max of each PartName field.

The result will be as shown above.

If you are interested, take a look at Thread701-505829 for some sample SQL code. If you don't know how to use code to create a query, have a read of FAQ181-3317.

NOTE: While preparing this post I have noticed that it is sometimes necessary to make adjustments in the query design screen after pasting the SQL for a grouping query. Just another of the joys of Access!



Enjoy, or not, as you feel inclined.

John

 

[sprite]

John,
Im trying this and I cant get pass step 1. How do I add a logical field to the table to record when each line processed?
Thanks,
Sprite

 

[gol4]

this can also be done by adding an autonumber field to the table or appending the records to a table with an autonumber field named recid
then 1 query like this named groupinqry

SELECT "part " & Count(*) AS Partnum, mytable.Itemname, a.Partname
FROM mytable AS a INNER JOIN mytable ON a.Itemname = mytable.Itemname
WHERE (((mytable.recid)>=[a].[recid]))
GROUP BY mytable.Itemname, a.Partname;

then another crosstab
like this

TRANSFORM Max(groupinqry.Partname) AS [The Value]
SELECT groupinqry.Itemname
FROM groupinqry
GROUP BY groupinqry.Itemname
PIVOT groupinqry.Partnum;