Cleanup of XL import

[P51D]

I administer 14 Federal grants that provide housing for disabled people.
I have 3 years worth of monthly invoices (listing ConsumerName, Grant,
InvoiceMonth, ConsumerID, SubsidyAmt, TenantRentAmt) combined into one large
workbook.
I have figured out how to import the spreadsheet into Access and make a
table of unique records of the Consumers, using ConsumerID as my PK.
The next step is to clean up the subsidy info as much as I can. I need to
select each consumer, and for each grant they may have been charged to,
select the oldest date value and put it in GrantStart, select the newest
date and put it into a new field called GrantEnd. I guess I could leave the
grantend field blank if it's currently being charged the subsidy amount.

From the Excel import:
ConsumerName, Grant, ConsumerID, InvoiceMonth, SubsidyAmt,TenantRentAmt
John Doe, SPC200, 1111, 09/04, 500,
150
JohnvDoe, SPC187, 1111, 08/04, 500,
150
MaryRoe, HUD64, 2222, 09/04, 600,
250
Etc, etc for 12,905 rows from the XL import.

Cleaned:
Grant, ConsumerID, GrantStart, GrantEnd, SubsidyAmt,TenantRentAmt
TransactionID (autonumber)
SPC200, 1111, 08/04, 09/04, 500,
150 xxxx
For each consumer, the grant the subsidy is charged to may change over time.
GrantStart would be the MIN value for the dates a particular grant was
charged, GrantEnd the MAX value. ConsumerID is unique and not reused.

I'm not sure how to approach this-any pointers would be VERY greatly
appreciated.

 

[Michel Walsh]

Hi,


Something like:

-----------------------------
SELECT LAST(Grant),
ConsumerID,
MIN( CDate( Left(InvoiceMonth, 2), "/1/", Right(InvoiceMonth, 2))),
MAX( CDate( Left(InvoiceMonth, 2), "/1/", Right(InvoiceMonth, 2))),
SUM(SubsidyAmt),
AVG(TenantRentAmt)

FROM myTable

GROUP BY ConsumerID
-----------------------------


Note that I assumed the InvoiceMonth has always 2 characters for the month,
and 2 for the year.

I also used SUM and AVG for illustration.

Since Grant change, and you did not say which value you want to keep, I
elect to use ANY one that did appear for the "group", with the aggregate
LAST.




Hoping it may help,
Vanderghast, Access MVP