VBA or Cross-tab with business rules

[Rx_]

Question: Would a custom function instead of a crosstab be best for this solution?

I can't seem to create a crosstab to meet this need. So maybe a function is in order?
The left side is a View. The Right side is the desired result.

For each ID: if a unique (APD status & Status & Date APD submitted) exist for either state or fed - create a row.
Note Status with blank count as a unique value.
Note blank DT APD Submitted counts as unique

see ID 115 Extension - if multiple (FedStCO & APD_Status & APD_APV & dates) only latest dates (but this does not apply to blank dates). Forcing the blanks to show is required because it shows transistions.

Spent time trying to create a cross tab solution. Probably could have written a function by now. This table largest size will only be 20,000 records (fairly small).

Any suggestions in general or detailed would be appreciated.

 

[vbaInet]

I don't get the "and dates" part in this sentence, "see ID 115 Extension - if multiple (FedStCO & APD_Status & APD_APV & dates) only latest dates (but this does not apply to blank dates)." I thought the criteria was to check only those first three fields?

 

[Rx_]

Sorry for the delay, not really that sorry - it was great sking in Colorado:
Showing the latest date, plus a spacial case if a record is not complete to show that record as well.

The managers meeting this morning brough up a lot of questions that (as usual) will change my requirements yet again.
The balance between a "simple report for a daily meeting" vs details about transitions.

The a VBA function will most likely be necessary. Cross tabs are good for consistant logic rules. This is a situation where they seem to need a trend, but have multiple "if then" reporting requirements for high-profile situations.

The lesson is to have clients provide a clear idea for complex reports with multiple conditional outputs.

There is probably some point where VBA is more efficient than using cross-tabs.

 

[vbaInet]

Nice!!! I've not yet indulged in that sport. Maybe sometime next year.

So it looks like things are changing?

I wasn't thinking of a crosstab query. I was thinking along the lines of 2 or 3 select queries.