Need TRANSFORM statement without aggregate values

[M Skabialka]

I need something like a TRANSFORM statement that doesn't use aggregate
values.
I have a vehicle inventory where the parts have serial numbers. There are
multiple cases of the same part on a vehicle.
I need a report of all serial numbered parts on the vehicle - different
vehicles have different parts being tracked.
e.g - serial numbers on these three vehicles:
Wheel Door Mirror
Vehicle 1 123 645
Vehicle 1 124 574
Vehicle 1 125
Vehicle 1 126
Vehicle 2 789 345 568
Vehicle 2 790 854
Vehicle 3 843 736

When I do a TRANSFORM statement I have to choose an aggregate function, so
it must be count, or sum, or first, etc. But for this report I need all
serial numbers.
I tried FIRST and ended up with only one line for each vehicle. Serial
numbers can't be counted, summed etc.
Not knowing what parts, or how many serial numbered parts per vehicle, how
can I create a summary report as above?

Mich

 

[KARL DEWEY]

One way is to create Ranking in a Group query where each wheel serial number
for each vehicle has a number like this --
Wheel Rank
Vehicle 1 123 1
Vehicle 1 124 2
Vehicle 1 125 3
Vehicle 1 126 4

Then in the crosstab you have the Rank as a row not displayed.

One drawback is that the Ranking in a Group query must be used to make a
temporary table for the crosstab as the aliases created in the query cannot
be recognized by the crosstab.

 

[Duane Hookom]

There is a generic Concatenate() function that can be used to pull multiple
values into a crosstab (or elsewhere) at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16.

 

[M Skabialka]

I like this idea, but can't figure out to to apply ranking. Is this done in
a query, or are you using code to write records to a temp table, numbering
as you go?
Mich

 

[KARL DEWEY]

Sample query --
SELECT Q.Group, Q.Item_no, Q.Points, (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

 

[Duane Hookom]

I haven't had much luck with using subqueries like this in the source of a
crosstab. An alternative (other than the Concatenate function) is a ranking
query like the following from the Employees table in Northwind.mdb:

SELECT Employees.Title, Employees.LastName, Count(Employees_1.EmployeeID) AS
RankOrder
FROM Employees LEFT JOIN Employees AS Employees_1 ON Employees.Title =
Employees_1.Title
WHERE (((Employees.EmployeeID)<=[Employees_1].[EmployeeID]))
GROUP BY Employees.Title, Employees.LastName
ORDER BY Employees.Title;
--
Duane Hookom
Microsoft Access MVP

Sample query --
SELECT Q.Group, Q.Item_no, Q.Points, (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

I like this idea, but can't figure out to to apply ranking. Is this done in
a query, or are you using code to write records to a temp table, numbering
as you go?
Mich

 

[KARL DEWEY]

I use a temp table between the ranking query and the crosstab.

I haven't had much luck with using subqueries like this in the source of a
crosstab. An alternative (other than the Concatenate function) is a ranking
query like the following from the Employees table in Northwind.mdb:

SELECT Employees.Title, Employees.LastName, Count(Employees_1.EmployeeID) AS
RankOrder
FROM Employees LEFT JOIN Employees AS Employees_1 ON Employees.Title =
Employees_1.Title
WHERE (((Employees.EmployeeID)<=[Employees_1].[EmployeeID]))
GROUP BY Employees.Title, Employees.LastName
ORDER BY Employees.Title;
--
Duane Hookom
Microsoft Access MVP

Sample query --
SELECT Q.Group, Q.Item_no, Q.Points, (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

I like this idea, but can't figure out to to apply ranking. Is this done in
a query, or are you using code to write records to a temp table, numbering
as you go?
Mich

One way is to create Ranking in a Group query where each wheel serial
number
for each vehicle has a number like this --
Wheel Rank
Vehicle 1 123 1
Vehicle 1 124 2
Vehicle 1 125 3
Vehicle 1 126 4

Then in the crosstab you have the Rank as a row not displayed.

One drawback is that the Ranking in a Group query must be used to make a
temporary table for the crosstab as the aliases created in the query
cannot
be recognized by the crosstab.

:

I need something like a TRANSFORM statement that doesn't use aggregate
values.
I have a vehicle inventory where the parts have serial numbers. There
are
multiple cases of the same part on a vehicle.
I need a report of all serial numbered parts on the vehicle - different
vehicles have different parts being tracked.
e.g - serial numbers on these three vehicles:
Wheel Door Mirror
Vehicle 1 123 645
Vehicle 1 124 574
Vehicle 1 125
Vehicle 1 126
Vehicle 2 789 345 568
Vehicle 2 790 854
Vehicle 3 843 736

When I do a TRANSFORM statement I have to choose an aggregate function,
so
it must be count, or sum, or first, etc. But for this report I need all
serial numbers.
I tried FIRST and ended up with only one line for each vehicle. Serial
numbers can't be counted, summed etc.
Not knowing what parts, or how many serial numbered parts per vehicle,
how
can I create a summary report as above?

Mich