Help! Auto-population?

F1A1G1inDC

Board Regular
Joined
Jun 20, 2003
Messages
74
Not sure how to best describe this...but I will give it my best shot.

I do a global headcount report on a monthly basis. Each region (USA, Canada, Asia, EMEA, etc) send me their headcounts for each region and business unit. Due to data-privacy laws, I only get basic info from EMEA (basically headcount). Is there a way that I could do a table/form/query/macro that would give each region (or me) a spot to enter things like Headcount, Country, Region, BU and have Access auto-populate the "detailed" information? Ultimately, what would work best would be for them to enter in (field to left of ":" and data to the right)

Region: EMEA
Country: Switzerland
BU: MDS
Headcount: 17

And then have the result be:

Region Country BU EmployeeID Headcount
EMEA Switzerland MDS EMEA1 1
EMEA Switzerland MDS EMEA2 1
EMEA Switzerland MDS EMEA3 1

etc, etc, etc

Any thoughts?

TIA,

F1A1G1
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

mdmilner

Well-known Member
Joined
Apr 30, 2003
Messages
1,352
This is going to be deliberately vague however, have you looked at table relationships?

This is all going to be about data structure.

You could, for example, have a table that only holds data that doesn't change such as Item A and Item B - Item C is your headcount data. You'd then have a table that has:

A unique Index and then fields for item A & B (3 fields total)

Your second table would also have 3 fields. The first would be another unique index just for that table, and then a reference from the first tables' unique Index, and then finally Item C. You could add a 4th field that specifies some other condition such as a date/time.

Unfortunately, I can't be more specific without a lot more explanation about the nature of your data. Do you have multiple regions? Do countries exist in multiple regions or just one? Do you have multiple BU's in each country or region? Just how is this organized?



Mike
 

F1A1G1inDC

Board Regular
Joined
Jun 20, 2003
Messages
74
Hi there. There are several countries in each region. What I need to happen is, let's say that there are 17 employees in Ireland...I need row level data for each of those 17 employees (EMEA1 - EMEA17) without having to manually create it (I have 6 days, including weekends) from the time I get the data till I have to get it compiled and to the group Presidents.

Make sense? I don't feel like I am explaining it clearly
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi

If you already have the summary data/information for 17 employees in Ireland, why do you want to create 17 lines of data? Is there another process / report that requires the detailed information?

Lets say we set up a routine to write the 17 lines into the table - without any personally identifiable information, what value is this if the data is repeated but each 'employee' has a unique ID?

Assuming all is ok and we create the data, when we do this next week and there are say 16 or 18 employees in Ireland - what do you want to happen with the extra employee, or the missing employee? How would you want this to work? Would we know which employee is no longer employed?

Andrew
 

F1A1G1inDC

Board Regular
Joined
Jun 20, 2003
Messages
74

ADVERTISEMENT

This is for a month-to-month global headcount. The reason that the line data is needed is so that I can use OLAP (two applications,,,one in an XL pivot and the other is Cognos PowerPlay). So I will need the line item data for that (show overall HC plus BU headcount) so we can track the changes month-to-month.
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi
I believe you almost answered your question in your first post and Mike brought up the issue in his post, which is the data structures. In addition to capturing the country, BU and headcount you just need to capture the period. I'm yet to see why you need the detailed records given your outputs from the other systems are also based on total headcount, albeit by period/BU/region etc but not by individual.
Andrew
 

F1A1G1inDC

Board Regular
Joined
Jun 20, 2003
Messages
74

ADVERTISEMENT

Again, the reason that I need the line level data on each region and country is for OLAP. In order to be able to drill up and drill down you have to have data to drill to/from...otherwise you have a spreadsheet.
 

mdmilner

Well-known Member
Joined
Apr 30, 2003
Messages
1,352
I started to answer this again because the abbreviation OLAP means quite a bit of things. Now please understand, I really want to answer this nicely, however, at this point, I think you did not understand anything that we wrote and do not really understand what OLAP means.

First, check the wikipedia - it has a nice informative article on OLAP's.

OLAP stands for On line analytical processing. It refers to the extraction of data for management from an information system following the ETL (Extract Transform & Load) process of a Datamart/Datawarehouse. Datawarehouses, by their nature are huge - and query speed is of critical importance - so the data is nearly always denormalized to enhance speed.

If you're asking, "is it possible to build an application to transform raw data into a denormalized star schema" - then yes, of course there is. This gets done every day.

If you're asking, "is there an easy way to do this via a form, table, query" - nope. Whatever process you might have to follow to do this manually, you're pretty much going to have to duplicate that process by building it all in code - your ETL process.

At this point, I have a question. Just how much data do you have? I'd guess millions/hundreds of millions of records in a corporate environment is the minimum to need to be denormalized.

Mike
 

F1A1G1inDC

Board Regular
Joined
Jun 20, 2003
Messages
74
I am fully trained in OLAP and OLAP modeling. I know what it is. I know how it works. I know what I need to get it to work. This is not a matter of "why" I need the information this way...I just do. If you are not going to help, I would prefer you not respond at all. I am here to find and give assistance as I can, not for rudeness.

Please consider this topic closed. I have gone to an XL friend of mine to get it done in XL....and she was not a weenie about it.

Pop off.
 

mdmilner

Well-known Member
Joined
Apr 30, 2003
Messages
1,352
I really am sorry you feel that way. I actually re-wrote my response at least twice because I felt it was a bit too harsh. I am, however, willing to stand by my prior assessment which was reinforced by your latest post.

I'm happy you found an answer that will work for you and your task at hand.


I'll only challenge you with one more question.
What does Pop Off. mean?
 

Forum statistics

Threads
1,141,628
Messages
5,707,499
Members
421,511
Latest member
mgroah1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top