Auto add new field

[Jennifer]

Every month I use macros to automatically take an excel spreadsheet with
billing data with customers and cost centers and upload it into Access. I
then use a series of queries to create sums for all the months for each
customer under each cost center. I then take these sums and insert them into
a summary worksheet that calculates what I will bill for the next month.
This is an example of the output:

Customer Cost Center1 Cost Center2
C1 1158869.68 45153.19113
C2 0 0
C3 0 885.3651461

However, in the next month I will be adding new cost centers. So my
question is: How do I automatically add new fields when using a macro to
transfer a spreadsheet? (If I tried right now without doing anything, it
says field doesn't exist in destination table)

 

[KARL DEWEY]

Your table structure is that of a spreadsheet but should look like this --
Customer Cost Center Value
C1 1 1158869.68
C1 2 45153.19113
C3 2 885.3651461

Customer 2 has no entries.
Use a crosstab query to output if you want the see a spreadsheet view.

 

[Jennifer]

I'm fairly new to Access and need a little bit more of an explanation on how
to do this. When I transfer the spreadsheet from excel to Access it looks
like what I showed you: Cost Centers as column headings, customers as row
headings, and then the values. How do I change the structure of the table?

 

[Cute Techie]

Hi Jennifer,
This seems very interesting. Could you share the code you use to do this
initial phase of this?

Thanks

 

[Jennifer]

You want the code for which part?

Just to clarify (because I'm thinking you want code for automatically
uploading data into access) Access doesn't necessarily automatically upload
anything, I open the Access file and press a button on a form and it runs a
macro. I can give you the code (or whatever is behind the macro) if you
would like that, but I'm pretty sure its elementary/novice Access stuff.

 

[KARL DEWEY]

SELECT Customer, 1 AS [Cost Center], [Cost Center1] AS Value
FROM YourTable
UNION ALL SELECT Customer, 2 AS [Cost Center], [Cost Center2] AS Value
FROM YourTable;

 

[Cute Techie]

Sure, I'll take the code behind the macro. I'm just learning new scenarios
and yours seemed very interesting. Thanks a bunch

 

[Jennifer]

Uhm, how do I get the code behind the macro?

Sure, I'll take the code behind the macro. I'm just learning new scenarios
and yours seemed very interesting. Thanks a bunch

 

[Jennifer]

Okay, I figured out how to add a new field but now my problem is that once a
new field is added there aren't values for the previous months and therefore
it can't calculate the sum. How do I fix this?

SELECT Customer, 1 AS [Cost Center], [Cost Center1] AS Value
FROM YourTable
UNION ALL SELECT Customer, 2 AS [Cost Center], [Cost Center2] AS Value
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little

I'm fairly new to Access and need a little bit more of an explanation on how
to do this. When I transfer the spreadsheet from excel to Access it looks
like what I showed you: Cost Centers as column headings, customers as row
headings, and then the values. How do I change the structure of the table?

 

[Cute Techie]

I am confused. I know you can probably put the code in VBA, but I'm lost.