Reporting category history/philosophical question 1

[RosieMP]

Okay, one more question for the day...

This is a philosophical question, and I've gotten conflicting responses asking around the office, so I'd like to find out what's recommended.

I'm building a table to hold a history of open accounts and report on it. Reporting buckets are based in part on the person who's collecting the account, combined with an account type established in the system. I'm going to use a table to hold the reporting categories and populate them through a query, and use that to build my reports.

So Billy the Collector collecting on a company tagged "01-post petition bankruptcy" goes into the "bankruptcy" reporting bucket. If later they decide to move Billy's account into a new bucket called "post petition bankruptcy", any report generated after that move will report both the history and the current data in the "post petition bankruptcy" bucket.

This means that the "bankruptcy" bucket, still being reported for other accounts, would artificially shrink (it looks like there was a reduction in bankruptcy accounts, when actually it was just that the money was moved into a different bucket.)

This sounds okay to me, but others I've talked to have been bothered by this idea.

What's the accepted practice?

Thanks so much,
Rosie

 

[Remou]

It sounds like data. I hate to lose data.

 

[fneily]

It's not a "philosophical question", it's a normalization question.
Not knowing the difference between "01-post petition bankruptcy" and "post petition bankruptcy" is, seems like your table should look like:
TableID CollectorID CompanyID AcctType SubType Date

CollectorID and CompanyID come from their tables. So AcctType would be Bankruptcy
SubType would Be 01
(next record) Subtype would be 00 (I made this up)

Now you keep track when and where the money transfers among subtypes of the accttype.

 

[RosieMP]

I don't have much flexibility as far as normalizing the data or setting up tables beyond a table to hold the reporting category. I get a text dump with 12000 lines and something like two dozen columns.

I won't have a collector table, or a customer table, it's not going to be normalized. Or pretty.

What I want to do is use a concatenated field in a query (collector code + customer type) to link to a reporting table that will hold a matching code that says 'this is bankruptcy' 'this is post petition bankruptcy'. When I go into that reporting table and change something, it'll be changed on all reports going forward, and it'll change the history too (I'm not storing the reporting category anywhere, or at least, I'm trying to find out whether that's okay.)

Remou, yes, it does sound just like data. But my thinking is that it's not lost data--it's data that's been reviewed under new circumstances. At least, that's the philosophical lens I'm using, and I'm trying to find out whether that's just being data-illiterate or that's good common sense.

 

[RosieMP]

To be clear(-ish), I'll have at least two tables, the raw data (main) table, and the reporting code table....

 

[Remou]

It seems you need this for historical analysis, so I would say that the previous status was of interest. Would a change date and previous status be possible?

 

[Remou]

... in a linking table, that is.

 

[fneily]

Access can normalize the text file for you. But since you don't want to go that way, I would add a little detail to Remou's suggestion. In the reporting table, I wouldn't have the words 'this is bankruptcy' or 'this is post petition bankruptcy'. I'd create a code table. Give all your descriptions a code then use a descipID. Why? Well, looking forward, let's say someone wants to change a description. If you just had the descriptions in the reporting table, that'll be alot of changes. In a code table, you'll just have to make one change.
May sound trivial, but may save some future work.

 

[RosieMP]

Thanks Fniely -- I agree re. codes v. descriptions; the other upside being that its easier to write queries by a field code than by some big honking description field...What I want to use for a code is the concatenated value of the collector and the internal customer type (credit group), so I'd get a code like 01101 (01 being the collector, 101 being the credit group) and then could pull it into a query by concatenating the collector and credit group on the detailed records...That's my theory.

Remou, I'm interested in what you're talking about but I'm having a hard time envisioning it. I guess I'd have to have a record ID in the main table, and then a separate table that stored a record's original reporting category and the date that category was attached to the record...and then I guess if the record were changed, I'd need another record that said the date that record was changed and what it was changed to.

Maybe I need to play with table design a little more....I'm having a hard time time getting a sense of how it is best structured.

I mean, part of me thinks, why? Do we really need to care if post petition bankruptcy and bankruptcy (or whatever -- let's say right now we report 'govt' and next year we decide we want to report 'federal' and 'state'--maybe that's a better example)--anyway, do we really need to care if these shift around? The total value is the same--we were comparing apples to apples and now we're comparing oranges to oranges, but it's still fruit.

Reason number 1232 why no one would hire me as an accountant.

 

[jsteph]

One option--and this results in much more data but is viable in my opinion--is to record these as transactions.

For instance, in our SAP system we *never* move a billing document (invoice record) even if it was in error, say wrong customer, wrong price, whatever. A cancellation record is created and the new correct one is created. This way, any reporting comes up with correct sums because the incorrect one has an equal-but-negative record to wash it out.

In your instance, the post-petition record would be cancelled by a record indicating a 'status change', and then a third record--the new status 'bankruptcy' is created. Now you have a traceable document flow and summary totals based on accounts or status-codes are accurate.

Just another thought...it works for SAP, so it might work for your application,
--Jim

 

[RosieMP]

What about this for a solution:

Rather than a change date for the raw data (and maybe this is what you were saying), what if I include date iterations in the reporting category table?

That way the reporting combo has a date attached--IF I want to do some combination of our reporting codes in November of 07 v. how the exact same data was reported in, let's say, March of 06, I would have both values?

Does this make sense? I can't tell anymore.

 

[Remou]

I think that is the way to go.

Categories
01 Blah
02 More Blah

Rawdata
ID

Category Changes
RawID
CatID
Date

It is a CYA thing. I have never regreted keeping even quite trivial data. Someone is bound to ask what the situation was in July 1802

 

[fneily]

1802, July 4 - At West Point, New York the United States Military Academy opens.

 

[RosieMP]

They might have been around in 1802. They sure have 19th century corporate policies. We got an email telling us women weren't allowed to show their clavicles.

Okay Remou--not sure if we're envisioning the same thing....

Here's what I'm thinking:
tReportCategories
ReportCategoryID
ReportCategoryDesc
An ID field that is actually the concatenated value of these two items, which is what we can match to the main table:
CreditGroup
Collector
ReportCategoryActiveDate

That was one table. Then, the main table, which holds the remaining criminally unnormalized detailed customer account info.

That way, I can build a query by joining to the report category along with an active date for that category. So, I can do, 'show me what we called government accounts in June of 06' should someone bang the drum for that particular piece of data.

 

[Remou]

That is more or less what I meant, but more carefully thought out. I had visualized a separate table for report categories with the 'detail' table you illustrate holding only the ReportCategoryID, however, if there are only a few categories, this little bit of normaization may be overkill.

How are they on ankles?

 

[RosieMP]

Thanks for helping me thinking this out.

Re. ankles: probably the same as clavicles.

(Don't everyone apply at once.)