CODEWITHSUNDEEP
excelms-accessimportexcel-2010ms-access-2010
user2272584
user2272584

Reputation: 1

Excel to Access import, column determines field value

I am trying to create an Access database to track income and expenditures for a number of accounts.

My difficulty is that my boss insists the people responsible for reporting expenditures on these accounts should only use the existing Excel spreadsheet to do so. I can import from Excel, but I'm locked into the existing format for the sheet.

In simplified form, the sheet has these columns:

Account ID || Expense Type A || Expense Type B || Expense Type C (etc)

I anticipate needing to add new types of expense in the future, so the tables need to accommodate that.

I had thought to structure the Expenses table like so:

Account_ID || Expense_Type || Expense_Date || Amount

with more info on Type stored in its own table. But I can't figure out how to map the columns of the spreadsheet to a type classification in the record. There are about 300 accounts with 5 expense types at present, and both may grow.

Can anyone help me with this? I'll entertain any sort of solution: Excel tricks or changing my database setup or whatever else might work.

PS. I'm a novice with VBA - I've only used it in the sense of recording macros.

Upvotes: 0

Views: 392

Answers (2)

pnuts
pnuts

Reputation: 59495

Call up the 'old' Pivot Table wizard with Alt+D and P, select Multiple consolidation ranges and PivotTable, Next, select I will create the page fields, Next, enter Range, Next, Finish then double click on Grand Totals intersection.

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123849

After you've imported the spreadsheet into a temporary table in Access you could run the following query to "de-crosstab" the data

SELECT [Account ID] AS Account_ID, "Expense Type A" AS Expense_Type, 
        [Expense Type A] AS Amount
    FROM TempTable WHERE [Expense Type A] IS NOT NULL
UNION ALL
SELECT [Account ID] AS Account_ID, "Expense Type B" AS Expense_Type, 
        [Expense Type B] AS Amount
    FROM TempTable WHERE [Expense Type B] IS NOT NULL
UNION ALL
SELECT [Account ID] AS Account_ID, "Expense Type C" AS Expense_Type, 
        [Expense Type C] AS Amount
    FROM TempTable WHERE [Expense Type C] IS NOT NULL

Upvotes: 1

Related Questions