need cost broke out by month 1

[4281967]

I have a table (tblListing) that has:
Listing (a number representing an ad)
StartDate (date ad starts)
EndDate (date ad ends)
Cost (price or ad)

most ads run for 12 months (for example start may be something like 2/1/05 and end is 1/31/06)

what I need to do is list by month, the cost of each ad.

To make the math easy, let's just say all ads are 12 months and $1200 per year (which they arent)

Here is what my report needs to have:
(for this example Jan-Dec is 2006 and row 1 is for 4/1/05 to 3/31/06. Row 2 is 8/1/05 to 7/30/06)

AD#----JAN--FEB--MAR--APR--MAY--LUN--JUL--AUG--SEP--NOV--DEC
123----100--100--100
222----100--100--100--100--100--100--100

etc -
the "--" is just to hold the places (not needed on report)

anyway - any ideas on how I can do this?
I have several thousand Ads - and would like to get this into Excel

 

[BoxHead]

4281967,

You're trying to get information out of the system that you haven't put in.

Three choices: first, I'd suggest that you build a second table that would include the fields Listing, actMonth, and adCost. Set up a form that adds a record for each month an ad is active when you book the ad. This gives you some flexibilty when you sell an ad for twelve months "but the last two are free." It also makes the sort of report you're looking for relatively easy with a crosstab query.

Second, you could build a table with the months that you want in the report. I'd recommend a field with the first of each month (myMonthStart) and a field that would be the year and month number (myMonthName) (EG: 2005 06 - for June '05).
Build a query with your existing table and this new table but do not join the tables. The SQL would look like:

SELECT tblMonthList.myMonthStart, IIf([ADSTART]<=[myMonthStart],IIf([ADEND]>=[myMonthStart],Str([adNum]),""),"") AS AdNumber, IIf([ADSTART]<=[myMonthStart],IIf([ADEND]>=[myMonthStart],[adCost]*1,0),0) AS Charges, tblMonthList.myMonthName AS rptMonthName
FROM tblAdCharges, tblMonthList
WHERE (((IIf([ADSTART]<=[myMonthStart],IIf([ADEND]>=[myMonthStart],Str([adNum]),""),""))<>""));

Base a crosstab query on the results of that SELECT query where AdNumber is your Row Heading, Charges (summed) is your value and rptMonthName is your Column Heading.

Third choice: build a query on your existing table only where you manually build each column.

SELECT tblAdCharges.adNum, tblAdCharges.adStart, 
IIf([adstart]<=#1/1/2005#,IIf([adend]>=#1/1/2005#,[adcost],0),0) AS 012005, 
IIf([adstart]<=#2/1/2005#,IIf([adend]>=#2/1/2005#,[adcost],0),0) AS 022005, 
IIf([adstart]<=#3/1/2005#,IIf([adend]>=#3/1/2005#,[adcost],0),0) AS 032005, 
IIf([adstart]<=#4/1/2005#,IIf([adend]>=#4/1/2005#,[adcost],0),0) AS 042005, 
IIf([adstart]<=#5/1/2005#,IIf([adend]>=#5/1/2005#,[adcost],0),0) AS 052005, 
IIf([adstart]<=#6/1/2005#,IIf([adend]>=#6/1/2005#,[adcost],0),0) AS 062005, 
IIf([adstart]<=#7/1/2005#,IIf([adend]>=#7/1/2005#,[adcost],0),0) AS 072005, 
IIf([adstart]<=#8/1/2005#,IIf([adend]>=#8/1/2005#,[adcost],0),0) AS 082005
FROM tblAdCharges;

Long term, the first choice will give you much less maintenance and manual updating to do.

HTH


John

Use what you have,
Learn what you can,
Create what you need.

 

[MajP]

I did this in three steps.
1) I built a table (tblMonth)with one field:
"intMonthNum"
That had the values 1, 2, 3, ...12.
2) I built a query (qryListing) that had two calculated fields
startMonth: Month(startDate)
endMonth: Month(endDate)
These fields return an integer for the month value
3)I joined qryListing using an unequal join to tblMonth

SELECT qryListing.id, qryListing.cost, tblMonth.intMonthNum
FROM qryListing, tblMonth
WHERE (tblMonth.intMonthNum>=[startMonth] And tblMonth.intMonthNum)<=[endMonth]))
ORDER BY qryListing.id, tblMonth.intMonthNum;

4) Okay four steps. Make a crosstab query from the above with the month number as a column heading, id as row heading, and I used min of cost for the data.

TRANSFORM Min(qryAdMonths.cost) AS [The Value]
SELECT qryAdMonths.id
FROM qryAdMonths
GROUP BY qryAdMonths.id
PIVOT qryAdMonths.intMonthNum;
[code]
The results come out looking like yours except the headers are 1,2,3 instead of jan, feb, mar... I tried to get the months as column headers but they cam out in alphabetical order.  Now this does not account for values starting in one year and rolling into another year.  You have to do that part using iif statements in the startMonth and endMonth calculations.
s

 

[BoxHead]

I don't know about you MajP, but I spent about an hour on my answer.

And PHV, AceMan and CautionMP answered this exact same post in another forum.

I think I'll just work on my own stuff for awhile.

John

Use what you have,
Learn what you can,
Create what you need.

 

[MajP]

Boxhead,
Took me about 15min, but I did not check for the overlapping years. I'll let the user figure that part out. I am doing a similar problem on a project so I new a way to do it.

 

[dhookom]

When I have created these in the past, I have use a method similar to MajP's and/or BoxHead's second solution.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]