What is wrong here? :(

[David_Skov]

I have a table with the following info:

Market, Segment, Revenue & Period

The periode is given by this number:

200308

This is year 2003 and month August. Each row can contain the same periode depending on the different Market and Segment. I have the periods 200301-200409 which in all is 1050 rows

I need to retrieve revenue Year To Date for 2003 and 2004 (accumulated revenue for 200301-200309 and 200401-200409), Revenue Full Year 2003 (accumulated revenue 200301-200312), finally I need to get revenue for periods 200309 & 200409.

I created 5 queries which gives me the info needed above. But I can't seem to combine them so that I can use them in a report. I don't know what to do. As an example how I created the query YTDLY (Year To Date Last Year) I pasted the SQL below:

SELECT MarketID.Market, [ACNielsen data].SegmentID, Sum([ACNielsen data].Oms) AS YTDLY
FROM MarketID INNER JOIN [ACNielsen data] ON MarketID.MarketID = [ACNielsen data].Market
GROUP BY MarketID.Market, [ACNielsen data].SegmentID, IIf([Periode]<=(DMax("[Periode]","[ACNIelsen data]")-100) And [Periode]>((DMax("[Periode]","[ACNIelsen data]")-100)-Right(DMax("[Periode]","[ACNIelsen data]"),1))=True,"YTD","-")
HAVING (((IIf([Periode]<=(DMax("[Periode]","[ACNIelsen data]")-100) And [Periode]>((DMax("[Periode]","[ACNIelsen data]")-100)-Right(DMax("[Periode]","[ACNIelsen data]"),1))=True,"YTD","-"))="YTD"));

For YTDTY (Year To Date This Year, SQL is:

SELECT MarketID.Market, [ACNielsen data].SegmentID, Sum([ACNielsen data].Oms) AS YTDTY
FROM MarketID INNER JOIN [ACNielsen data] ON MarketID.MarketID=[ACNielsen data].Market
GROUP BY MarketID.Market, [ACNielsen data].SegmentID, IIf([Periode]<=DMax("[Periode]","[ACNIelsen data]") And [Periode]>(DMax("[Periode]","[ACNIelsen data]")-Right(DMax("[Periode]","[ACNIelsen data]"),1))=True,"YTD","-")
HAVING (((IIf([Periode]<=DMax("[Periode]","[ACNIelsen data]") And [Periode]>(DMax("[Periode]","[ACNIelsen data]")-Right(DMax("[Periode]","[ACNIelsen data]"),1))=True,"YTD","-"))="YTD"));

THe above queries gives me 4 coloumns, were the last row only indicates what period the row belongs to. When I gruop by the last coloumn I get the desired info.

But I can't seem to find a way to combine the queries so that I use them in a report together. In the report I wants to show the reults for YTDLY and YTDTY in the same report.

Hope You can help, maybe give me a better way to get desired info etc.

Plz ask if I'm not clear. And I will reply asap :wink:

In advance tx

 

[David_Skov]

I have found a solution were I use Append queries instead...Follwing the example of Andrew93 here:

http://www.mrexcel.com/board2/viewtopic.php?t=111549

By doing that I get what I want. Maybe another solution is doable and that I don't mind so plz answer

@Andrew: As always You add good and understandable solutions to this forum :wink:

 

[Andrew Fergus]

Thank you for your comments David.

I take it from your 2nd post that you have solved this?

Given you are using many dimensions (time, stores, segments etc.) you are best breaking it up into smaller bits that can be easily managed. If you are using append queries, once you have some new data remember to clear out the table before you run the append queries again (or use a yes/no variable to indicate the data that has / hasn't been transferred into the table), otherwise you will get duplicate entries.

Andrew.