Crosstab doesn't allow more than one value? 2

[Apollo6]

I am running Access97. I have a table which contains the following fields:

item
desc
ndc#
qty
amt
date

Basically, it contains historical information about drug purchases. For example:

item 12345
desc aloxi
ndc# 1234-123-1234
qty 25
amt $24,000.00
date 1/1/05

The table has a different record for previous months, one record per month, per drug, which has a different qty and amt. The end report I am trying to get to would look very similar to the results of a crosstab query, where the drugs would be listed down the left side(rows),the months would be listed across the top(columns), and the qty/amt would populate the grid. The problem I am having is that a crosstab will only allow one value, say qty, in the grid.

Does anyone know how to "trick" the crosstab query into allowing both values to be used/displayed?

Thanks for any ideas...

 

[dhookom]

How can I get a crosstab to have more than one value faq701-4524

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Apollo6]

Thanks... I actually found the FAQ after posting the question...

 

[Apollo6]

I have the crosstab working correctly, as far as, pulling the two values I want to look at, QTY and AMT. One additional question I have is how can I order the query results to where for each item, in this case, drug, get the QTY and AMT columns to display next to each other for the month reported. As it is now, for each drug, let's say for 5 months, the first 5 columns are QTY, the second 5 columns are the corresponding AMT? Here is the SQL statement:

TRANSFORM Sum(IIf([tblOS_Type]![type]="QTY",[tblos_usage_history]![qty],CCur([tblos_usage_history]![amt]))) AS DaVal
SELECT tblos_usage_history.item, tblos_usage_history.desc, tblos_usage_history.ndc
FROM tblos_usage_history, tblOS_Type
GROUP BY tblos_usage_history.item, tblos_usage_history.desc, tblos_usage_history.ndc
ORDER BY tblos_usage_history.desc
PIVOT [tblOS_Type]![type] & " - " & Format([tblos_usage_history]![os_date],"mmm-yy");

Thanks again for the help...

 

[Apollo6]

Figured it out, column heading needed to have date first concatenated by type...

Thanks for FAQ, works great!