Need your expertise to solve this problem :p

[David_Skov]

I succeded in making my database (with help from you in here :wink but I have a small issue that you might be able to help me with.

I have a query that looks like this:

SELECT TOP 250 test.ButiksID, test.Butiksnavn, Sum(test.Q2) AS [Total Q2], Sum(test.Q1) AS [Total Q1], Sum(test.Q4) AS [Total Q4], Sum(test.Q3) AS [Total Q3], Sum([q2]+[q1]+[q4]+[q3]) AS Total, Kædetabel.Kæde
FROM test INNER JOIN Kædetabel ON test.ButiksID=Kædetabel.ButiksID
GROUP BY test.ButiksID, test.Butiksnavn, Kædetabel.Kæde
ORDER BY Sum([q2]+[q1]+[q4]+[q3]) DESC;

Each quarter of a year I enter new data into my database. Among the data is 'Year' (not seen or used in the query) and 'Quarter' (Q1-Q4 above). In my reports I only need the last four quarters. So now I need these quarters:

Q204 - Q104 - Q403 - Q303 (already in the database and in my query above)

When September is finished I get new quarterly data, and therefor my reports should only include:

Q304 - Q204 - Q104 - Q403

So Q303 will not be needed in this query anymore, instead Q304 is needed. In January 2005 the query should only include:

Q404 - Q304 - Q204 - Q104

Etc.

I have no real good idea to solve this puzzle but assume it can be handlede some way in a query or?

Anyway, any help is really appreciated

Inadvance tx ray:

 

[David_Skov]

I don't fully understand this bit :

I then build a new query from that cross_tab which again enables me to make the report that is required.

Is the new query a copy of the first query but saved as something else?
Or is the new query sourcing it's data from the first query?

As You might remember from my previous posts, I have my database with storedata, then a Cross_tab and a query build on the cross_tab. I applied Your idea to my setup and it gave me 4 rows in the cross_tab, one for each quarter. So I started all over and build a new query which had the part with 20033, 20034, etc. quarters in it. You might say I reconstructed my database to fit needs. I then made my cross-tab query so that the columns was labeled 20033, 20034, etc. instead of Q3, Q4.

Finally I made a query from the Cross-tab query. That made me capable of adding a totals to my query that I can sort by in descending order as well as making a Top 250

Hope that explains else I try again

And if you could give a few more details for this bit please :

But the part below I don't get. Or can't make it work

In what way is it not working?

Well perhaps it is working but in my setup it didn't work since my cross-tab was screwed up. But it's not needed either. I think what I need for the quarter thing to work is a critiria that limits to the last four quarters, and more important, that will work across the other queries

Or if the new query is sourcing it's data from the old query then the "kvartal" variable will no longer be available as a variable (because the variable names will change to something like 20033 or 20034 etc.). <-- Yes

Cheers, Andrew. [/quote]

 

[David_Skov]

I got what I wanted, but ended up with another problem

Here is my query in SQL view:

SELECT Butiksdata.ButiksID, Kædetabel.Kæde, Butiksdata.Butiksnavn, Butiksdata.[Omsætning kr], Butiksdata!År & Right(Butiksdata!Kvartal,1) AS Kvartal
FROM Butiksdata INNER JOIN Kædetabel ON Butiksdata.ButiksID = Kædetabel.ButiksID
GROUP BY Butiksdata.ButiksID, Kædetabel.Kæde, Butiksdata.Butiksnavn, Butiksdata.[Omsætning kr], Butiksdata!År & Right(Butiksdata!Kvartal,1)
HAVING ((([Butiksdata]![År] & Right([Butiksdata]![Kvartal],1))>=(DMax("[År]","[Butiksdata]") & Right(DMax("[Kvartal]","[Butiksdata]","[År] = " & DMax("[År]","[Butiksdata]")),1))-9));

As You can see, the last part gives me the last four quarters. The '-9' - part was a good idea

So when I make a cross-tab query I get the last four quarters as I want, so that part is great!

But now I have two other issues. How do I get back my top 250 list? and can I make the report dynamic so that I don't have to remake a new report each quarter?

Regards

David

 

[David_Skov]

I was able to get my Top250 back

I made a Query that found the Top 250 stores based on my Test2 Query (see SQL view above). I then created my Cross-tab and linked my Cross-tab to the top 250. It works perfect

Here is the SQL view for that:

TRANSFORM Sum(Test2.[Omsætning kr]) AS Kvartalsomsætning
SELECT Test2.ButiksID, Test2.Kæde, Test2.Butiksnavn, Top250.[SumOfOmsætning kr] AS Totalomsætning
FROM Test2 RIGHT JOIN Top250 ON Test2.ButiksID = Top250.ButiksID
GROUP BY Test2.ButiksID, Test2.Kæde, Test2.Butiksnavn, Top250.[SumOfOmsætning kr]
ORDER BY Top250.[SumOfOmsætning kr] DESC , Test2.Kvartal DESC
PIVOT Test2.Kvartal;


But! isn't there any way to make a report based upon the cross-tab query that changes when my Cross-tab changes to a new set of Quarters?

 

[Andrew Fergus]

Hi David,

Je comprends!

We can't use the crosstab query as the basis for the top 250 query - it needs to be the other way around. So we need to know the largest 250 customers over the last 4 quarters first and then present that information in a crosstab query.

Starting from fresh (all new queries) :

Query number 1 : - call it whatever you like
Add data table, in the first field enter:
Last : Max([year]&Right([Quarter],1))
Save
This query will find the last quarter.

Query number 2 :
Add data table and query number 1, don't link them, View Totals
Field 1 = Store ID
Field 2 = Store Name
Field 3 = Revenue, Sum
Field 3 = Period : [Year]&Right([Quarter],1), criteria for field 3 : <=[Last] And >=[Last]-9
Field 4 = [Last]
Save
This query will find all of the revenue amounts in the main table for the last four quarters.

Query number 3 :
Add query 2, View Totals, Query -> Properties -> TopValues = 250
Field 1 = TotRevenue : SumOfRevenue, Sum, Descending
Field 2 = store id
Save
This query will give you the top 250 stores

Query number 4 :
Add query 2, add query 3, link them on the Store ID, Query -> Crosstab
Field 1 = Store ID (query 2), Row Heading
Field 2 = Store Name (query 3), Row Heading
Field 3 = TotRevenue (query 3), Row Heading, Descending
Field 4 = Period (query 2), Column Heading, Ascending
Field 5 = SumOfRevenue, Sum, Value
Save
Voila! This is your crosstab query for the top 250 clients and it will automaticaly update every time you have a new quarter of sales data.

I hope this works! I tested it on another PC (not this one) and was reading it over my shoulder as I was typing it into this forum. I have checked it for typing errors and can't see any (yet!) - so hopefully this is ok.

A.

 

[Andrew Fergus]

I started my last post before you did your last two so it looks like they crossed over.

It looks like the problem is solved but be aware of the double DMAX - I'm not sure how this would work in this situation : is the double DMAX going to give a result of 20054 when you do the first quarter for 2005? i.e. the 2005 comes from the 2005 Q1 but the Q4 will be the max from 2004?

BTW, I have difficulty comprehending the SQL statements given I don't use it - that's why I laid it out the way I did.

A.

 

[David_Skov]

Tx very much Andrew! You have been very! helpfull. Even though I made things a little different from time to time, You gave me the ideas and I'm greatful for that!

About the double DMax. I just tried it out and it works. If I add 2005 Q1 to my database, I get one coloumn with 20051 and one with 20042 which is those with data that full fill the criteria

The criteria should take the higest Q in the higest Year.

Now I only need two things. I need to make my database a little different, so that I have a table for each store instead of one table with all. I think I can manage that (else I ask ). The other problem is the reports, that I would really like to be more dynamic. Is there anyway I can make the report so that it changes the quarters whenever the cross-tab table changes?

Kind Regards

David

I started my last post before you did your last two so it looks like they crossed over.

It looks like the problem is solved but be aware of the double DMAX - I'm not sure how this would work in this situation : is the double DMAX going to give a result of 20054 when you do the first quarter for 2005? i.e. the 2005 comes from the 2005 Q1 but the Q4 will be the max from 2004?

BTW, I have difficulty comprehending the SQL statements given I don't use it - that's why I laid it out the way I did.

A.

 

[Andrew Fergus]

Tx very much Andrew! You have been very! helpfull.

You're welcome and it's my pleasure. I'm pleased that we can get there in the end given the differing time zones and a potential language barrier.

What you are trying to do is very similar to what I did with my 1.5+ million record database. My database had product sales by store by day with volumes, values and a customer reward programme. That's why I keep suggesting having the most flexible data structures possible given the volume of data you will be handling plus I guarantee the end users are going to want differing reports etc to those that you create - so flexibility will be the key for a successful database.

I need to make my database a little different, so that I have a table for each store instead of one table with all.

In my opinion, I don't think you should have a separate table for each store given there are at least 250 stores. It is going to make any analysis across the stores impossible (e.g. store vs store) and an analysis of total sales for all stores almost impossible. Plus you are going to need a separate query / report for every store due to each one having a different table. I wouldn't recommend the approach you suggested.

Is there anyway I can make the report so that it changes the quarters whenever the cross-tab table changes?

I think I answered this in my 2nd to last post - you have to get the top 250 first and then do the crosstab. Firstly, as I'm sure you're aware, you can't do "TopValues" on a crosstab query. And secondly, given the period names are going to change every 3 months then you need to do it the other way round, like I suggested 2 posts ago. Work out the top 250 first and then do the crosstab - not the other way round.

Andrew.

 

[David_Skov]

I think I answered this in my 2nd to last post - you have to get the top 250 first and then do the crosstab. Firstly, as I'm sure you're aware, you can't do "TopValues" on a crosstab query. And secondly, given the period names are going to change every 3 months then you need to do it the other way round, like I suggested 2 posts ago. Work out the top 250 first and then do the crosstab - not the other way round.

Andrew.

Actually I think You misunderstand me, 'cause I have made the top250 before I make the Cross-tab and the cross-tab can handle the Quarters exactly as I need. But when I wants to make a report that I can print, the layout seems to be fixed. So if the quarters changes the report gives an error saying that the quarters doesn't match. I don't know how to make the report so that it adapts the new quarters

 

[Andrew Fergus]

My apologies David - I did misunderstand the last bit about the report.

In your crosstab query, you can change the column heading from "Period" (per my previous post) to something like this :

Quarter : "Q" & Right([Period],1)

This will give you Q1, Q2, Q3 and Q4 which you can use as fixed variable names on your report. However, you might not want to use this because the quarters will always be in that sequence (i.e. 1 to 4) unless you include the year, but that will cause problems with your report (as you have found out). So, if the last period is 20042, then the sequence on the report will be 20041, 20042, 20033 followed by 20034 labelled as Q1, Q2, Q3 then Q4. This is going to be confusing for anyone reading the report so there is another way of doing this. {So try it to see what I mean but I don't recommend you use it}

We can change the sequence of the quarters in the crosstab query by changing the way we interpret the quarters. If you add the "Last" period (per my query 1 or query 2 above) to the crosstab query (for example, 20042, or whatever is your last period, will show on every row in the crosstab query) then we can use this to get the periods back into sequence. If we assume that Q4 is the latest period (e.g. 20042) and Q1 is the earliest (e.g. 20033), then change the "Period" formula in your crosstab query to this (remember to add "Last" to the crosstab query) :

Quarter : "Q" & IIf(Right([Period], 1) -Right([Last], 1) >0, Right([Period], 1) -Right([Last], 1), Right([Period], 1)- Right([Last], 1) +4)

This will relabel the quarters for the example I gave you so that 20033 is Q1, 20034 is Q2, 20041 is Q3 and 20042 is Q4 - once again giving you fixed variable names on your report.

HTH, Andrew.

 

[Andrew Fergus]

Oh and I forgot to mention that whilst the data will be passed through the query onto your report as Q1, Q2, Q3 and Q4 - you can apply the correct year and period labels to the report above the corresponding columns of data. In other words, you don't have to use the labels Q1 to Q4. In fact, it might be less confusing to refer to the last 4 quarters as periods and label them P1 to P4 instead.

For example, in my previous post, Q4 on the report is actually period 20042 - you can still use this label (i.e. 20042 above the Q4 data on the report) provided it is in the crosstab query (like I suggested a few posts ago).

You could also "unpack" the period number back into the correct format by using something like :

=Left([last],4)&" Q"&Right([last],1)

so that 20042 now looks like "2004 Q2" and you can use that as the label above Q4 (or P4).

Plus, with the right maths and logic you can get the correct period labels, by using the "last" variable, above columns Q1, Q2 and Q3 on the report too.

e.g. the label for Q3 (or P3) might look something like this (based on the Q4 label looking like "2004 Q2", not tested) :

=Iif(Right([Q4label],1)=1, Left([Q4label],4)-1 & " Q4", Left([Q4label],6) & Right([Q4label],1)-1)

HTH, Andrew.