Auto add new field

  • Thread starter Thread starter Jennifer
  • Start date Start date
J

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)
 
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.
 
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?
 
Hi Jennifer,
This seems very interesting. Could you share the code you use to do this
initial phase of this?

Thanks
 
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.
 
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;
 
Sure, I'll take the code behind the macro. I'm just learning new scenarios
and yours seemed very interesting. Thanks a bunch :)
 
Uhm, how do I get the code behind the macro?

Cute Techie said:
Sure, I'll take the code behind the macro. I'm just learning new scenarios
and yours seemed very interesting. Thanks a bunch :)
Click to expand...
 
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?

KARL DEWEY said:
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


Jennifer said:
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?
Click to expand...
Click to expand...
 
Back
Top