Since you asked in a Microsoft Access group, I assume you currently have
this data in an Excel spreadsheet, but you are trying to bring it over into
a correctly normalized Access database?
If so, you will have a table with field like this:
ExpenseID AutoNumber primary key
ProjectID Number relates to a table of projects.
ExpenseTypeID Text tells if it's "A", "B", "C" ...
type expense.
Amount Currency the amount.
Now to get the data from the spreadsheet into the table:
1. Attach the spreadsheet (linked external data.)
2. Create a query using the linked spreadsheet as your souce 'table.'
3. In the first column of the Field row in query design, choose the project.
4. In the 2nd column, type this into the Field row:
ExpenseTypeID: "A"
5. In the next column, enter this into the Field row:
Amount: [A]
and in the Criteria row under this, enter:
Is Not Null
6. Change the query to an Append query (Query menu.)
Access will ask what table you wish to append to.
7. Run the query. It will append the data from the [A] column to the table.
8. Edit the 2nd column, so it reads:
ExpenseTypeID: "B"
and the 3rd column so it reads:
Amount:
9. Run the query. It will append the data from the column.
10. Repeats steps 8 and 9 for the C, D, E, and F columns.
You now have the data from all 6 columns into the normalized table. The
criteria of "Is Not Null" prevents adding blank rows.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
steverv said:
I have a spreadsheet that has hundreds of rows and 6 columns. Each row is a
project and can have spend in any of the 6 columns, either in single
columns
or any combination. Once completed I need to add rows to each project that
has expenditure: e.g.
A B C D E F Total
project 1 £100 £50 £300 £50 £500
This project would need four rows adding below. Is there a formula or
macro
that can do this? I can count the columns with data in them, but then I
cannot work out how to add the rows based on these numbers. Can anyone
help?
