Importing Data & Calculated Fields

Question

Hi Guys,

I am an Access “newbie” so would appreciate some guidance on the best way to address my requirements.

Each morning we generate an Excel report from an on-line system. It contains basic user details such as name, country, e-mail and date. We get a full dataset each morning, not just changes. Currently, that dataset is then extended using Excel formulas to calculate some additional details;

• Week – calculated based on date field.
• Month – calculated based on date field.
• Year – calculated based on date field.
• Domain – extracted from the users e-mail address.
• Partner Membership – if their domain is listed in a separate XLS, then mark the partner field as “Yes” if not, mark as “No”. It is basically an IF and vLookup formula. Happy to move the “source” list of domains to a SharePoint 2010 list.
• Type – A series of IF commands. For example, if [Domain] is “abc.com” set value to “Employee”, ElseIF [Partner Membership] = “Yes” set value to “Partner”, ElseIF set value to “Customer”.
• Region – vLookup to separate XLS. Looks at the country and then returns the corresponding region. For example, if the country was United Kingdom, the region would be EMEA. Happy to move this XLS to a SharePoint list as well.

I was thinking that the best approach would be to create a table to dump the daily data into and then use calculated fields to generate the additional details – am I heading in the right direction or should I be running a macro to generate the details?

I would also love some guidance on the “formulas” I should be using to generate my additional fields.

Thanks in advance.

Answer 1

I have now figured out the "Type" field by using a series of IIF statements.

I could really do with some help on the "Partner Membership" field. I need to check to see if the users "Domain" appears in another table and then return a "Yes" or "No".

Answer 2

I have all the fields setup except for the "Partner Membership" and "Type".

For "Partner Membership" I am currently using IF, INSA and VLOOKUP forums in Excel.

For "Type", it is just a series of IF commands.

Appreciate any assistance on the best way to tackle this in a query.

Answer 3

Re SharePoint tables: they are linked tables just like any other linked table, so it's certainly a viable option.

Once attached, these tables can participate in any query.

Yes, sub-queries can further filter data.

Answer 4

Hi,

The database is actually for people that have earned a qualification. I get an XLS report each day from the   company that administers the program.

The main reason why I would prefer to have the "helper" tables on SharePoint is so that they can be referenced and updated by other people. However, if I need to maintain separate tables in Access, that would be ok.

Could you please provide some guidance on how to reference these "helper" tables in an Access query?

Also, if I build a master query that incorporates the new calculated field, I assume that I can then build a sub query that allows me to further filter the data? For example, I would then create a query that only showed people in EMEA.

I will run some reports from Access, but will also leverage the "Get External Data" feature in Excel to build pivots.

Answer 5

First you need to create a database design that can support the requirements. As a good database design it should model real entities. You're saying you're getting "daily basic user details" but your examples are not about users. Maybe membership???

Leave all calculated fields out of your tables. Don't use calculated tables for now.

"Helper" tables will be similar to the Domains list you currently have. I would store it in Access rather than SharePoint, but that's OK too if there are reasons for that.

Calculated fields will be generated in an Access query. For example:

select Month(myDate), Year(myDate) from myTable

This query is then used for (I assume) some reports you are generating on a daily basis.

I feel there is more analysis that needs to get done here, but the above should get you started.