Re: Select query numbering results

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






John Spencer MVP <spencer@xxxxxxxxx> wrote in
news:exUXulZyJHA.4272@xxxxxxxxxxxxxxxxxxxx:

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 wrote:
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














.

Relevant Pages

  • Re: Select query numbering results
    ... The problem with using a crosstab query for a report is that you must ensure it always returns the same number of columns. ... Your main report would be based on a query that only returned one row for each RO Number, RO Date, VIN Number. ... 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. ...
    (microsoft.public.access.gettingstarted)
  • Re: Need help ASAP Report
    ... Match all three query ... -- Run the query or use it as a source for a report. ... Match Make and Vin, but not customer ... No match on Make and Vin (two queries required one for each table's unique ...
    (microsoft.public.access.reports)
  • Re: Need help ASAP Report
    ... -- Run the query or use it as a source for a report. ... -- Under the Customer field for the first table add the criteria ... No match on Make and Vin (two queries required one for each table's unique ...
    (microsoft.public.access.reports)
  • Need help ASAP Report
    ... What I am looking to do is create a report that shows matching ... to do is match the customer # with the Care make and the vin. ... If the care make and the ...
    (microsoft.public.access.reports)
  • Re: Need help ASAP Report
    ... --Union the two queries together and make one report ... This type of query has to be built in the SQL ... No match on Make and Vin (two queries required one for each table's unique ...
    (microsoft.public.access.reports)
Quantcast