I'm not sure I've properly communicated what I'm trying to accomplish. I
don't think a crosstab query will produce the desired result. Basically,
the first column needs to hold the customer number, the second column
needs to hold the most recent order number, the third column needs to hold
the second most recent order number, etc., up to the sixth column, which
needs to hold the fifth most recent order number, assuming there are that
many orders for the customer. e.g.,
Column Headings: CustNum, OrdNum1, OrdNum2, OrdNum3, OrdNum4, OrdNum5
12345, 850093, 848892, 848884
12346, 850011, 850001
12347, 861234, 859893, 859722, 859701, 859622
12348, 861246
Using your Concatenate function as a base, I wrote a custom function that
takes a customer number and a digit from 1 to 5, representing the desired
most recent order. 1 would be the most recent order; 5 would be the fifth
most recent order. The function executes a SQL statement using the
specified customer number. It then uses a counter to loop through the
result set until the specified digit is reached or EOF. If EOF is not
reached, the value in the current record's field is returned as the
function's value. I call the function like this:
SELECT DISTINCTROW Customers.CustNum,
ReturnOrderNum([Customers].[CustNum], 1) AS OrderNum1,
ReturnOrderNum([Customers].[CustNum],2) AS OrderNum2,
ReturnOrderNum([Customers].[CustNum],3) AS OrderNum3,
ReturnOrderNum([Customers].[CustNum], 4) AS OrderNum4,
ReturnOrderNum([Customers].[CustNum],5) AS OrderNum5
FROM Customers INNER JOIN Orders ON Customers.CustNum = Orders.CustNum
ORDER BY Customers.CustNum;
Note the increasing digit passed to the ReturnOrderNum function as a
parameter: 1, 2, 3, 4, 5.
This works, but seems overly complex. And if I want to return (say) the
*15* most recent orders, the length of the SQL statement would multiply by
a factor of three. In addition, I'm wondering if it's wise to have the SQL
string embedded within a function. Will this upscale to SQL Server without
too much difficulty?
Is this do-able any other way? Can a crosstab query produce this?
Thanks, so much for your expertise with this. I appreciate your comments
and suggestions.
Duane Hookom said:
Your first step is to create the "ranking" query. There are lots of
messages posted regarding how to do this.
Your crosstab will create a column headings that use the ranking column
value to create Order1Num etc.
--
Duane Hookom
MS Access MVP
Chris said:
Thanks, again. I will look into DCount().
Just to be clear (and for my own sanity), the names of the column
*headings* will still be meaningful; i.e., not renamed to be the
resulting values, right? e.g.,
Column heading names: CustNum, Order1Num, Order2Num, Order3Num ...
First Row of data: 12345, 848884, 848892, 850093, ...
I imagine there's another function/tool altogether to actually create an
empty table and have the names of the column headings generated on the
fly.
You need to create a calculated column in your query that numbers your
records for each CustNum. This is usually done with DCount(). You can
then use the resulting value as a column heading for a crosstab query.
--
Duane Hookom
MS Access MVP
That's great, Duane; thanks!
I wasn't very clear on my desired result. Rather than return the top
five child records as one concatenated field, I'd like to return up to
five columns, with each column containing the value from a child
record.
So the result of the query would be:
Result of Query
CustNum OrderNum OrderNum OrderNum
CustNum OrderNum
CustNum
CustNum OrderNum OrderNum OrderNum OrderNum OrderNum
CustNum OrderNum OrderNum
CustNum
CustNum OrderNum
with each space-delimited value being a separate column.
Is this possible?
You should be able to use the generic concatenate function from
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
You expression would be something like:
OrderNums:Concatenate("SELECT TOP 5 [OrderNum] FROM Orders WHERE
CustNum=""" & CustNum & """ ORDER BY SomeDateFieldNotIdentified
DESC")
The above assume CustNum is text, if it is numeric, try:
OrderNums:Concatenate("SELECT TOP 5 [OrderNum] FROM Orders WHERE
CustNum=" & CustNum & " ORDER BY SomeDateFieldNotIdentified DESC")
--
Duane Hookom
MS Access MVP
For each record in a Customers table, there can be zero or more
associated records in an Orders table. I'm interested in the order
numbers of the five most recent orders for each customer. I'm trying
to write a query that will return the customer number and zero or
more columns--the latter columns containing the order numbers.
Customers (parent)
CustNum
Orders (child)
CustNum
OrderNum
Result of Query
CustNum OrderNum OrderNum OrderNum
CustNum OrderNum
CustNum
CustNum OrderNum OrderNum OrderNum OrderNum OrderNum
CustNum OrderNum OrderNum
CustNum
CustNum OrderNum
...
...
I'm stumped on how to do this. Any thoughts? Thanks!
