Yeah, I think i have read almost everything Spolski has written for the internet.

Extracting data from the Database is straight forward. If the DB is well organized.


If the data source were to be a Daily Campaign Report via Facebook with a row for every active ad (exceeding 1000 per day once scaled up) - would it be best that data be continually added to a single table?
Yes, but see below

If all that data were accumulated in one table, could/would the programming code be set to list the averages in the next Historical Performance table, with a "cycle range" column displaying the dates the average took data from - and continually update that Historical Performance table, adding new rows and new averages for all active ads each 3 days?
I may be mistaken, but I thought you wanted Daily averages for the previous three days. Yes I would just use Queries for the Cycle report and Daily overview.

I would keep the daily averages in a separate Table for historical purposes just to not need to perform such heavy a load on Access. Do the math once, then store the answer.

The Database:

  • One Client has many Campaigns
  • One Campaign has have many Ads
  • One Ad can has many Daily Reports


(Anything else? One Copy Writer has many Campaigns and many Ads? One Ad has many outlets?)

That suggest three Tables

  • Client Table, PrimaryKey:=ClientID
  • Campaign Table, PrimaryKey:=CampaignID, SecondaryKey:=ClientID
  • Ads Table, PrimaryKey:=AdID, SecondaryKey:=CampaignID, TertiaryKey:=DailyReportDate


I would consider an ActiveAd Table, PKey:=AdID, Fields:=(Start As Date, Killed As Date, LastRun As Date, Active As Boolean.) LastRun lets you "Pause" an active ad without losing it, plus, electrons are cheap; you can buy a TeraByte of them for less than $100.

You might want these Relationship (Index) Tables: CopyWriter To Campaign, CopyWriter To Ad, Outlet to Ad.

That would let you "drill Down" to just about anything. For Example; Client A wants to know which ad in his Ikea Campaign ran on FM 93.9 on Dec 12, and who wrote the darn thing. Client B needs to stop his Chevrolet Campaign in Louisiana until the Listeria scare blows over, Etc.

In order to keep size down and speed up, after business hours each night, I would duplicate all daily transactions into a duplicate DB. After that, I would use aging to delete inactive accounts, 7 years for Clients, three years for Campaigns, one quarter for "killed" ads, and one year for Inactive Ads.