Query - ranking + rows to columns

[Milli]

Hi,

I still consider myself a novice with Access having only been using it properly for around 6 months but I've always been able to solve everything without directly requesting assistance. E.g by using google / the forums, that was until now..

My issue is this. I have a data table (dbo_StatusHistory) presented as below (the attached image shows it better).

Current Data

*dbo_StatusHistory

CaseID sStatusDesc sDate
1 API 01/07/2014
1 LRF 03/07/2014
2 API 02/07/2014
2 DIP 04/07/2014
3 API 02/07/2014
3 QUO 11/07/2014

DESIRED QUERY RESULT:

CaseID STATUS 1 STATUS 2 DATE 1 DATE 2
1 API LRF 01/07/2014 03/07/2014
2 API DIP 02/07/2104 04/07/2014
3 API QUO 02/07/2014 11/07/2014


The original table will always be formatted correctly. So I basically need the 1st three letter status to appear in Status 1, 2nd to appear in Status 2 and the corresponding dates.


Other info:

There could be up to 50 statuses & dates.
It will be part of a bigger query so if it isn't too labour intensive I would like to have it as part of the expression builder.


Hope this makes sense and I would massively appreciate any help that could be given.

Thanks,
Milli

 

[Ranman256]

Did you try a Crosstab qry?
Use the query wizard.

 

[arnelgp]

there are many status you said, but on your sample its a combination of two only, can there be more combination expected?

 

[Milli]

there are many status you said, but on your sample its a combination of two only, can there be more combination expected?

Hi Arnel,

Thanks for your reply.

API will always be the first status but from then on wards the second status can literally be anything, yes.

So it could be

Status 1 Status 2 Status 3
API NLP COM

etc. up to approx 30 statuses in some cases.

 

[Milli]

Did you try a Crosstab qry?
Use the query wizard.

Hi Ranman,

Thanks for your reply.

I have tried a crosstab qry but to no avail.

 

[arnelgp]

i cannot think of anything but VBA can do this:
study the code in Module1.
study the query query1.

 

[Milli]

i cannot think of anything but VBA can do this:
study the code in Module1.
study the query query1.

Hi Arnel,

I will study the module and see if I can get it to work with my db.

Thanks again.

 

[arnelgp]

etc. up to approx 30 statuses in some cases.

i am hardcoding it for 30 status in query3.

 

[Milli]

i am hardcoding it for 30 status in query3.

Hi Arnel,

Thank you for all your help, I greatly appreciate it!!

I have looked into your code and I think I am happy with everything that it is doing, I have amended it to run against my live DB and the query runs fine for a short while before I am presented with the following error:

"AT MOST ONE RECORD CAN BE RETURNED BY THIS SUB QUERY"

In essence there can be multiple of the same Caseid, multiple statuses although the duplicates should always have a different date/time stamp.

See attached which gives a larger sample of what I mean. eg. CaseID 6 & 7. Do you know of a way around this error??


Kind regards,
Milli

 

[jdraw]

You might find some insight in this concat function by Allen Browne

 

[arnelgp]

here is the fix. view the query1 and query3 in SQL view, i added TOP 1 to return only one record.

 

[Milli]

here is the fix. view the query1 and query3 in SQL view, i added TOP 1 to return only one record.

Thanks again Arnel!

I have a meeting to go to now but I will report back tomorrow to let you know how i get on.

Much appreciated.

 

[Milli]

here is the fix. view the query1 and query3 in SQL view, i added TOP 1 to return only one record.

Morning,

The query has been running for 4hrs so far with no signs of being close to completion. Does this seem a normal time for this particular code? The source data table only contains 6000 rows of data and 3 columns.


Attached is exactly how I would resolve this query using Excel formulae. Is it possible to recreate the Index formula I have used within Access or is the attached method the only way? (the source dbo will always be correctly sorted).

If not, then I will either have to just run the Access query overnight (assuming it completes within 12hrs) or create a portion of it within Excel and then transpose to Access.

Thanks again. I have learned a lot in a short space of time!

 

[arnelgp]

yes, ive run it with 6000 records and its just stood there. no fe/be this would be super super slow.
what we need is to try to dump it to temporary table.
let me work on it.

 

[arnelgp]

i made a form for you you might like to try, frmProcessCases.
i dump the final result to tblCasesResult table (linked table).
i do it in external db, so as not to bloat your db.

also make sure you allow all macro to run (macro safety warning).
anyway nothing is rouge on the code.

study frmProcessCases.

if you want to employ this to your actual db, create same linked table.
import module modSpecialFolder and form frmProcessCases.

forgot to mention after the process has reaced 100%, you may close the form and the final result in table tblCasesResult.

goodluck.