Select query numbering results

[Keith K]

I have repair records which list Repair Date,Repair Order number, VIN
Number (Vehicle ID) and a part number. Each repair order may contain up
to 100 part numbers and the part number variations number in the tens of
thousands. I am trying to create a report in which the first 10 part
numbers used are displayed in one row.

Currently the data structure looks like this

RO Number, RO date, VIN Number, Part Number
RO Number, RO date, VIN Number, Part Number
RO Number, RO date, VIN Number, Part Number

I would like to have the data appear this way in the report (single row
per Repair Order)

RO Number, RO date, VIN Number, Part Number1,Part Number2,Part Number3
....

Because of the variations in part numbers crosstab will not work with
the data.

Now to the question is it possible to construct a select query which
would number sequentially each part number record such that when the RO
number changes the sequential numbering starts over. I guess the data
would look like this .

RO Number, RO date, VIN Number, Part Number,1
RO Number, RO date, VIN Number, Part Number,2
RO Number, RO date, VIN Number, Part Number,3

Next RO number

RO Number, RO date, VIN Number, Part Number,1
RO Number, RO date, VIN Number, Part Number,2
RO Number, RO date, VIN Number, Part Number,3
RO Number, RO date, VIN Number, Part Number,4
RO Number, RO date, VIN Number, Part Number,5
RO Number, RO date, VIN Number, Part Number,6

If I had the sequence number I could write the data to a table and then
construct a cross tab query where the across was on the sequence number
and the part numbers would simply be data.

I have worked with Access for a long time but I have no formal training
or education in programming so any help is greatly appreciated Keith
Kaminetzky KK

 

[John Spencer MVP]

Yes it is possible to construct a ranking query that does what you want.
However, you have other options you can use in a report that might work better
for you. The problem with using a crosstab query for a report is that you
must ensure it always returns the same number of columns. If you really feel
that you must use a ranking query to solve this problem, post back.

One option is to use a sub report set to across and down to display the part
numbers. Your main report would be based on a query that only returned one
row for each RO Number, RO Date, VIN Number.

SELECT DISTINCT [RO Number], [RO date], [VIN Number]
FROM YourTable
(Add your where clause to restrict the records)

The sub-report would be based on a query that returned the part numbers
SELECT [RO Number], [RO date], [VIN Number], [Part Number]
FROM YourTable

You would link the report and sub-report on the three fields (unless RO number
is unique, then you would only need to link on RO Number).

Another option is to use a query that uses Duane Hookom's Concatenate function at:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

You will need to download the sample database, copy the Concatenate function
from the module, put it into your database. You may also need to comment out
some of his code and uncomment other lines (read the code to see which lines
are involved).

Then your source query for the report would look like
SELECT DISTINCT [RO Number], [RO date], [VIN Number]
, Concatenate("SELECT [PART Number] FROM [YourTable] WHERE [RO Number]='" &
[RO Number] & "'",",") as Parts
FROM YourTable

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Keith K]

John I'm not sure I understand this. Is the gist of this to join the ro
number to the part number in the concatenate? The requestor of this
data would prefer it be exportable to Excel. My use of the term report
may have been misleading. I will look at the resource you suggested and
circle back. If the resource will not accomplish my goal I'll repost.
If this additional information would change your advice please advise
Thanks KK

Yes it is possible to construct a ranking query that does what you
want. However, you have other options you can use in a report that
might work better for you. The problem with using a crosstab query
for a report is that you must ensure it always returns the same number
of columns. If you really feel that you must use a ranking query to
solve this problem, post back.

One option is to use a sub report set to across and down to display
the part numbers. Your main report would be based on a query that
only returned one row for each RO Number, RO Date, VIN Number.

SELECT DISTINCT [RO Number], [RO date], [VIN Number]
FROM YourTable
(Add your where clause to restrict the records)

The sub-report would be based on a query that returned the part
numbers SELECT [RO Number], [RO date], [VIN Number], [Part Number]
FROM YourTable

You would link the report and sub-report on the three fields (unless
RO number is unique, then you would only need to link on RO Number).

Another option is to use a query that uses Duane Hookom's Concatenate
function at:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16

You will need to download the sample database, copy the Concatenate
function from the module, put it into your database. You may also need
to comment out some of his code and uncomment other lines (read the
code to see which lines are involved).

Then your source query for the report would look like
SELECT DISTINCT [RO Number], [RO date], [VIN Number]
, Concatenate("SELECT [PART Number] FROM [YourTable] WHERE [RO
Number]='" & [RO Number] & "'",",") as Parts
FROM YourTable

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have repair records which list Repair Date,Repair Order number, VIN
Number (Vehicle ID) and a part number. Each repair order may contain
up to 100 part numbers and the part number variations number in the
tens of thousands. I am trying to create a report in which the first
10 part numbers used are displayed in one row.

Currently the data structure looks like this

RO Number, RO date, VIN Number, Part Number
RO Number, RO date, VIN Number, Part Number
RO Number, RO date, VIN Number, Part Number

I would like to have the data appear this way in the report (single
row per Repair Order)

RO Number, RO date, VIN Number, Part Number1,Part Number2,Part
Number3 ...

Because of the variations in part numbers crosstab will not work with
the data.

Now to the question is it possible to construct a select query which
would number sequentially each part number record such that when the
RO number changes the sequential numbering starts over. I guess the
data would look like this .

RO Number, RO date, VIN Number, Part Number,1
RO Number, RO date, VIN Number, Part Number,2
RO Number, RO date, VIN Number, Part Number,3

Next RO number

RO Number, RO date, VIN Number, Part Number,1
RO Number, RO date, VIN Number, Part Number,2
RO Number, RO date, VIN Number, Part Number,3
RO Number, RO date, VIN Number, Part Number,4
RO Number, RO date, VIN Number, Part Number,5
RO Number, RO date, VIN Number, Part Number,6

If I had the sequence number I could write the data to a table and
then construct a cross tab query where the across was on the sequence
number and the part numbers would simply be data.

I have worked with Access for a long time but I have no formal
training or education in programming so any help is greatly
appreciated Keith Kaminetzky KK

 

[John Spencer MVP]

The concatenate function would return a comma-separated list of the parts
associated with the RO Number. If you want to export this result to Excel and
you want the parts in separate cells then you can probably use the concatenate
function and change the separator to the tab character Chr(9). In the query
that will show all the parts in one field with a square (the tab character)
between the parts. When you export to Excel I ***THINK*** that will put the
parts in separate fields.

SELECT DISTINCT [RO Number], [RO date], [VIN Number]
, Concatenate("SELECT [PART Number] FROM [YourTable] WHERE [RO Number]='" &
[RO Number] & "'",Chr(9)) as Parts
FROM YourTable

If you use the alternative of a crosstab query based on a ranking query the
speed may be slow.

The ranking query might look like:
SELECT A.[RO Number], A.[RO date], A.[VIN Number], A.[Part Number]
, 1 + COUNT(B.[Part Number]) as Rank
FROM YourTable as A INNER JOIN YourTable as B
ON A.[RO Number] = B.[RO Number]
AND A.[Part Number] > B.[Part Number]
GROUP BY A.[RO Number], A.[RO date], A.[VIN Number], A.[Part Number]

The crosstab SQL might look like:
TRANSFORM First([Part Number]
SELECT [RO Number], [RO date], [VIN Number]
FROM [The Ranking Query]
GROUP BY [RO Number], [RO date], [VIN Number]
PIVOT "Part " & Rank

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Keith K]

John let me try this and I'll circle back when I get stuck. Many Thanks
This is one of several pokers I have in the fire. I think I understand
where this is going. Keith Kaminetzky KK ;-)

The concatenate function would return a comma-separated list of the
parts associated with the RO Number. If you want to export this
result to Excel and you want the parts in separate cells then you can
probably use the concatenate function and change the separator to the
tab character Chr(9). In the query that will show all the parts in
one field with a square (the tab character) between the parts. When
you export to Excel I ***THINK*** that will put the parts in separate
fields.

SELECT DISTINCT [RO Number], [RO date], [VIN Number]
, Concatenate("SELECT [PART Number] FROM [YourTable] WHERE [RO
Number]='" & [RO Number] & "'",Chr(9)) as Parts
FROM YourTable

If you use the alternative of a crosstab query based on a ranking
query the speed may be slow.

The ranking query might look like:
SELECT A.[RO Number], A.[RO date], A.[VIN Number], A.[Part Number]
, 1 + COUNT(B.[Part Number]) as Rank
FROM YourTable as A INNER JOIN YourTable as B
ON A.[RO Number] = B.[RO Number]
AND A.[Part Number] > B.[Part Number]
GROUP BY A.[RO Number], A.[RO date], A.[VIN Number], A.[Part Number]

The crosstab SQL might look like:
TRANSFORM First([Part Number]
SELECT [RO Number], [RO date], [VIN Number]
FROM [The Ranking Query]
GROUP BY [RO Number], [RO date], [VIN Number]
PIVOT "Part " & Rank

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John I'm not sure I understand this. Is the gist of this to join the
ro number to the part number in the concatenate? The requestor of
this data would prefer it be exportable to Excel. My use of the term
report may have been misleading. I will look at the resource you
suggested and circle back. If the resource will not accomplish my
goal I'll repost. If this additional information would change your
advice please advise Thanks KK