Re: Select query numbering results

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

  • Create Report from VBA Code
    ... query that has different numbers of columns depending on ... one field in the query, called "Sequence". ... I'm trying to create a report from VBA code using DAO to ...
    (microsoft.public.access.reports)
  • Re: Select query numbering results
    ... My use of the term report ... If you really feel that you must use a ranking query to ... only returned one row for each RO Number, RO Date, VIN Number. ... Another option is to use a query that uses Duane Hookom's Concatenate ...
    (microsoft.public.access.gettingstarted)
  • RE: Splitting Out a listing tables
    ... I thought you asked only for a report;-). ... You first need to sequence your ... The following is a query [qselRankTitle] of the Employees ...
    (microsoft.public.access.queries)
  • Re: Importing a file becomes out of sequence
    ... particular order when looking at a table regardless of how that table was ... Use a query, form, or report with the sorting you want applied to ... then you can sort on your sequence number to put the data in ...
    (microsoft.public.access.externaldata)
  • 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