Design issue

[wal50]

Couldn't find anything like this in the existing reponses.

Monthly records are received from an external vendor. Each record shows
individual monthly expenses (multiple categories) and the individual’s
expense plan for that month (one per guy). This input is out of my control.
There are other internal tables involved that are not relevant.
The goal is getting reports showing the last N months expenses where the
primary level is the expense plan and the secondary one is employee. Easy
enough but the issue occurs when a guy changes plans. The same guy will
appear in two different plans and show the monthly detail that he had in that
plan. I know that is what is supposed to happen but is not what the genius
here wants.
My only idea is to create a new Expense Plan table each month from the new
external file and use that to determine the expense plan.
Any other ideas occur to anyone? The lousy design of the input not included.
wal50

 

[Clifford Bass]

Hi,

It is a little unclear from your description as to what your genius
wants. Is it to report the person as if the person only had the most recent
expense plan? If so, no need to create a separate table. Just use a
self-join with one instance of the table getting the person ID and the most
recent expense plan; and the other instance getting all of the other
information:

SELECT A.Expense_Plan, A.Person_ID, C.Expense_Month_Year, C.Other_Information
FROM tblMonthlyExpenses AS A INNER JOIN tblMonthlyExpenses AS C ON
A.Person_ID = C.Person_ID
WHERE (((C.Expense_Month_Year) Between #1/1/2009# And #12/31/2009#) AND
((A.Expense_Month_Year)=(select Max(Expense_Month_Year) from
tblMonthlyExpenses as B where B.Person_ID = A.Person_ID)));

This assumes that Expense_Month_Year is a date field used to identify
the expense month and year. If this is confusing, post the relavant table
and column names. And if you are doing a summary query.

Hope that helps,

Clifford Bass

 

[Fred]

This is a structure question, which means that the foundaton of the solution
is structure. To help on a structure question requires unambigiously
understanding the nature of the "entities" that you databasing. Your post
uses a lot of terms where the specific meaning is known only to you such as:

"input"
"Expense plan"
"new external file"
"DETERMINING (the plan)"

Could you tell us those terms mean in the context of your application?

Also confriming that "primary level" and "secondary level" refer to grouping
in reports.

 

[wal50]

Thanks for both responses. Sorry for the confusion. The resident genius
wants is to report everyone in their current expense plan group and to show
all monthly history in that group including when they were under a different
plan. (He wants it grouped by plan but not really.) Sounds like what
Clifford described will do it. Thanks to you both.
wal50

For the record some definitions;
Input - I get a CD with expense and emplyee data from a vendor. I meant by
"out of my control" is that it comes in they way they send it out to all
their customers. No choice.

Expense plan - plan codes determine the arithmetic that the vendor uses to
calculate how each individual is charged.

New external file - The current month's vendor CD. I was going to create a
"Current Expense Plan" Table and use that to "determine the expense plan" and
group the report accordingly.

Primay/Secondary level - you are correct. I should have said "groups" and
not "levels"

 

[Clifford Bass]

Hi,

You are welcome. And good luck!

Clifford Bass