Question Monthly/Weekly

[cdoyle]

Hi,
I've been asked to create a database, to store information about campaigns that go out.

One of the criteria is they want to be able to run different reports, that show all campaigns that go out Quarterly/Monthly/Weekly

So I'm trying to decided how to store data like this.

I started with the Quarterly, created a new table tbl-quarterly with
campaignID << ID from the campaign table
QuarterID << ID from a lookup table Quarter (Q1, Q2, Q3, Q4)

Then I created a subform on my main form, where the users can select their options.

I guess this works, but I wonder if there is a better way.

Then looking at the monthly, I would have to do something similar create a table to store the monthly info, then a month table to store the month names. Create another subform, where they can select each month.

Not sure if this is the best way to go about it, so thought I would check to see if anyone has any better ideas.

Thanks!

 

[Isskint]

Are the campaigns identified as Quarterly/Monthly/Weekly OR do you want to see a report that shows campaigns by Quarter OR Month OR Week?

I would only have the 1 campaign table. If a campaign is secifically identified as Quarterly/Monthly/Weekly then include a field to record this. In this case a query would key in on this value. If it is just a case of displaying a report by Quarterly/Monthly/Weekly, use the various date format options presented to you when creating a report.

 

[pr2-eugin]

cdoyle, sounds to me you are going over and beyond.. All you need is a simple column in your table that denotes what subscription the customer holds, along with start date.. So..

subsID    custName    subsDate    subsTerm
1        Paul        09/02/2013        Monthly
2        Mark        05/03/2013        Weekly
3        Jon         06/03/2013        Monthly

Then it will be a simple select query, based on combo box.. to change the recordsource of the Subform..

 

[cdoyle]

Are the campaigns identified as Quarterly/Monthly/Weekly OR do you want to see a report that shows campaigns by Quarter OR Month OR Week?

I would only have the 1 campaign table. If a campaign is secifically identified as Quarterly/Monthly/Weekly then include a field to record this. In this case a query would key in on this value. If it is just a case of displaying a report by Quarterly/Monthly/Weekly, use the various date format options presented to you when creating a report.

There are going to be separate reports, 1 report would be for Quarterly view.
It will need to show all campaigns that are sent in Q1, Q2, Q3, Q4. Campaigns could be for only 1 quarter or all 4 quarters.

The next report would be the monthly, Jan-Dec. Campaigns could be run 1 month, several, or all.

Then the week report, would need to show campaigns that are run in W1, W2, W3, or W4. Some campaigns only run in W1,W2,W3, or all 4 Weeks.

I have a 'campaign' table, but right now it only stores the campaign name and a description. My other data is stored in other tables, and linked to this table. I'm trying to keep my database as flexible as possible in case they decide to add something else later on.

 

[Isskint]

So you would just need a Quarter report formatted to display by quarters, a Montly report formatted to display by Months and ... you can guess the weekly

When you create a report, use the date as a grouping field. You can then choose how to display the dates (Quarter, Month, Week)

 

[cdoyle]

hmm,
I guess I'm not seeing how this will work.

Here is an example of the information the user will be entering.

Campaign1, Goes out in January, March, and September.
During the 1st and 4th week.

Campaign 2, goes out Monthly, 1st and 4th week

Campaign 3, Will only go out 1 time in July on the 2nd Week. Campaign is expired after this.

I'm having a hard time figuring out how to capture data like this.

 

[Isskint]

Sorta goes back to my first question. My suggestion would work if you had a LaunchDate field. You would see reports grouped by Quarter, Month or Week on when campaigns had started.

HOWEVER forget all that. This is gonna be a little long in explanation:banghead: but shorter in doing, believe me

With the 3 sets of data you describe, common to each is CampaignName, CampaignType (Qtr, Mnth, WK). With Qtr you need the months and weeks of month. With Mnth you need week numbers. With Weekly you need month and week. Some may have multiple launches (Campaign 2, goes out Monthly, 1st and 4th week). So maybe as well as your 'campaign' table you could do with a CampaignSettings table. You would have;
1, a SettingsID (PK),
2, a (FK) = to 'campaign' table ID (PK)
3 , CampaignType - (I would go with a number and create a lookup table for the different types, this way you get that flexibilty if a different campaign type comes up later!)
4, MonthField as a number - to record the month the campaign starts
5, Week field - to record the week the campaign starts

So with your examples you would have;

SettingsID	CampaignID	CampType	CampMonth	CampWeek
1			1	QTR		1		1
2			1	QTR		1		4
3			1	QTR		3		1
4			1	QTR		3		4
5			1	QTR		9		1
6			1	QTR		9		4
7			2	MNT		1		1
8			2	MNT		2		1
9			2	MNT		3		1
10			2	MNT		4		1
11			2	MNT		5		1
12			2	MNT		6		1
13			2	MNT		7		1
14			2	MNT		8		1
15			2	MNT		9		1
16			2	MNT		10		1
17			2	MNT		11		1
18			2	MNT		12		1
20			2	MNT		1		4
21			2	MNT		2		4
22			2	MNT		3		4
23			2	MNT		4		4
24			2	MNT		5		4
25			2	MNT		6		4
26			2	MNT		7		4
27			2	MNT		8		4
28			2	MNT		9		4
29			2	MNT		10		4
30			2	MNT		11		4
31			2	MNT		12		4
19			3	WK		7		2

Monthly campaigns would need to be entered once for each month.
Like i said CampType would be better as a number (FK to a lookup table), so you could add/change the Campaign Types (although reporting would be another problem).

With this you would create 3 different queries to provide the data for your reports. I would use calculated fields in the queries to convert the month week numbers into something more useful (month name and perhaps week number of year - thats up to you).
The QTR query would filter in only the QTR records, The MNT only monthly etc... You would then have a series of data detailing the campaign launches.
You would have a main report to display campaign name and description, then a sub report showing the corresponding settings for each campaign.
In this mock up i have them all on 1 report, but you would have 1 for each.

If you do not want to tidy up the data with something more useful (month names etc) you could get away with just 1 query, 1 report & 1 subreport. Just supply the query with the type you want to report on (QTR MNT WK etc).