Alternative to Cross-Tab queries

[Vidar13]

Okay, here's the scenario:

I want to display sales in a query by year, side by side. I always pick six years, though they are subject to change. Those six possible years are stored in a single-record "profile" table that can be updated by the end user.

The table structure is simply this:

FieldName Type Keyfield
============== ===== ========
TransactionID Autonumber Yes
Year Text No
Sales Currency No

This table actually contains 20 years of sales, but I only want to report on the 6 possible as defined in my profile table.

The catch is, that I want those six years of sales, shown side by side as a single query record.

A cross-tab query won't work for me, because basing a report on a cross-tab query has it's own set of problems, especially when you introduce after-sorting, etc.

 

[nomus1]

This may not be the cleanest way to do this, but you can put the same table in your query six times.
Pull down the sales record for each year separatly.
If the years are fixed, hard code the respective year under the table, if the years are variable set the first year as a paramater [enter year] and the subsequent years as [enter year]+ 1, etc.

Hope this helps

 

[Vidar13]

This won't unionize a single recordset for six different records.

What I'm trying to do is the exact opposite of a union query where you would create a linear recordset of several (like) fields into multiple records.

 

[nomus1]

Maybe I am not understanding the question, or maybe I did a poor job of explaining what I was saying. I think I have done something similar to report on production by department, and this is how I accomplished it.


I took a table called Sales that only has 3 fields as described above, puts it in a query 6 times, prompts the user for the beginning year, and display the sales for that year and the next five

SELECT Sales.Sales AS Year1, Sales_1.Sales AS year2, Sales_2.Sales AS year3, Sales_3.Sales AS year4, Sales_4.Sales AS year5, Sales_5.Sales AS year6
FROM Sales, Sales AS Sales_1, Sales AS Sales_2, Sales AS Sales_3, Sales AS Sales_4, Sales AS Sales_5
WHERE (((Sales.Year)=[Enter Starting Year]) AND ((Sales_1.Year)=[Enter Starting Year]+1) AND ((Sales_2.Year)=[Enter Starting Year]+2) AND ((Sales_3.Year)=[Enter Starting Year]+3) AND ((Sales_4.Year)=[Enter Starting Year]+4) AND ((Sales_5.Year)=[Enter Starting Year]+5));

Hope this helps

 

[MichaelRed]

Well, I 'heard' you say you didn't want to use the old standby (CrossTab), but "He" is Sooooooooo good at this stuff, I just couldn't resist letting him parade for you. It does take 'two to tango', but NOT six, after all "I" need a little help with the tricky steps.

PARAMETERS EnterYr Short;
TRANSFORM Sum(tblSales.Sales) AS SumOfSales
SELECT tblSales.Year
FROM tblSales
WHERE (((CInt([Year])) Between [EnterYr] And [EnterYr]+"6"))
GROUP BY tblSales.Year
PIVOT tblSales.Year;

SELECT Sum(qryXTabSalesYrs.[1994]) AS SumOf1994, Sum(qryXTabSalesYrs.[1995]) AS SumOf1995, Sum(qryXTabSalesYrs.[1996]) AS SumOf1996, Sum(qryXTabSalesYrs.[1997]) AS SumOf1997, Sum(qryXTabSalesYrs.[1998]) AS SumOf1998, Sum(qryXTabSalesYrs.[1999]) AS SumOf1999, Sum(qryXTabSalesYrs.[2000]) AS SumOf2000
FROM qryXTabSalesYrs;

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over