Detailed record numbers by group in QUERIES

[acrocker3434]

I have a table that looks like this:

caseID
date
value

I want to create a crosstab query that looks like this:

Date1 Date2 Date3 etc etc
caseID value value value


There are 500 caseIDs in my table. Each caseID has up to 5 corresponding dates/values. Therefore I only want 5 dates as the column headings....listed as 1, 2, 3, 4, 5, etc NOT the actual date (as I then end up with hundreds of column headers).

Does anyone know how to create a detailed number by each group (similar to the runningsum feature in Reports)? I know I need to somehow assign each date a "date number" but I dont' know how to.

Any ideas??

 

[Jon K]

Assuming CaseID is a text field, try these two queries (replacing with the correct table name in Query1).

Query1:-
SELECT CaseID, [Date], Value,
"Date" & DCount("*","TableName","[CaseID]='" & [CaseID] & "' and [Date]<=DateValue('" & [Date] & "')") AS DateNum
FROM [TableName]
ORDER BY CaseID, [Date];

Query2:-
TRANSFORM Sum(Value) AS SumOfValue
SELECT CaseID, Sum(Value) AS [Total Of Value]
FROM Query1
GROUP BY CaseID
PIVOT DateNum;

Run the second query.


If CaseID is a numeric field, you need to remove the two single-quotes surrounding CaseID in Query1, that is

"Date" & DCount("*","TableName","[CaseID]=" & [CaseID] & " and [Date]<=DateValue('" & [Date] & "')") AS DateNum


Note:
Date is a the name of a function. Better avoid using it as a field name.

Using DCount() in a correlated way is inefficient. Running the queries may take time.
.

 

[acrocker3434]

Data mismatch error

THANK YOU for your help. The query works great. One glitch is that now every time I run it I get the error "Data type mismatch in expression". If I keep clicking OK through the error messages the query does eventually run.
Any thoughts?

THANKS

 

[Jon K]

What's the data type of the CaseID field in your table? Text or Number?

As explained in my post, the SQL in Query1 assumes that CaseID is a text field. If CaseID is actually a numeric field, you need to delete the two single-quotes surrounding [CaseID].
.