Simple Access Setup Question

[calbear81]

So I'm new to Access but I'm trying to transition large tables from Excel over and would love some help on how to set something up rather quickly.

I run some marketing programs and each "placement" i run has a set of data associated it with it daily, so for example "placement a" is under the column "name" and then i have things like "impressions", "clicks", "site", "spend", "statistics date".

I'm trying to build a database to store the daily records and then be able to generate flexible reports sort of like a pivot table in Excel which can aggregate by any of the values in the table. I would also like the flexibility to aggregate specific date ranges into "Week 1, week 2, etc.".

How hard is it to do? What will I need?

Thanks in advance!

 

[Andrew Fergus]

Hello and welcome to MrExcel!

A lot of your informational requirements (eg weekly aggregations etc) can be built using queries - that is provided you have the right data structures. There is a useful webpage here that describes the 'data normalisation' approach you should keep in mind when building your data tables.

It is hard to provide an exact description of the data tables you will need because I suspect there may be some items you haven't told us about and I don't understand the names vs placement vs site relationship. However, that aside, your two main tables may look as follows :

tblPlacements
Placement_ID {primary key}
Placement_Name
Placement_Site
Other static data related to the placement

tblDailyData
Placement_ID {foreign key back to the placements table, but also a joint primary key with Stats_Date}*
Stats_Date {joint primary key with the Placement_ID}*
Impressions
Clicks
Spend
Other daily data (if any)

*A joint primary key means that one placement cannot have more than one entry per day - I have assumed you will need this sort of arrangement.

Post back if you have any queries or get stuck.

HTH, Andrew

 

[calbear81]

Thank you Andrew! That is very helpful information. I'll have a go at it ASAP and let you know how it works out.