Querying a little bit more than an year...

[Guest]

Hi all,

I'm having a problem and maybe someone has an answer to it: I have a table
that has records with 12 values each one representing the total number of
visitors of that month of the year. Of course, the year is an important index
for that table. For field grouping purposes, I need to make one or several
queries that returns a record with the 12 fields for that year and 2 other
(the november and december values) from the record of the previous year.

For example, I have a table Visitors with the following fields and values:
year,v01,v02,v03,v04,v05,v06,v07,v08,v09,v10,v11,v12
2004,23,34,45,43,32,21,12,23,45,55,44,33
2003,99,98,97,96,95,89,88,87,86,85,79,78
2002,55,56,57,58,59,65,66,67,68,69,75,76

And I want the output
year,v-1,v-0,v01,v02,v03,v04,v05,v06,v07,v08,v09,v10,v11,v12
2004,79,78,23,34,45,43,32,21,12,23,45,55,44,33
2003,75,76,99,98,97,96,95,89,88,87,86,85,79,78

Anyone knows a way to do this using only queries withou development code?
Thanks.
Pedro L.

 

[John Vinson]

Hi all,

I'm having a problem and maybe someone has an answer to it: I have a table
that has records with 12 values each one representing the total number of
visitors of that month of the year. Of course, the year is an important index
for that table. For field grouping purposes, I need to make one or several
queries that returns a record with the 12 fields for that year and 2 other
(the november and december values) from the record of the previous year.

For example, I have a table Visitors with the following fields and values:
year,v01,v02,v03,v04,v05,v06,v07,v08,v09,v10,v11,v12
2004,23,34,45,43,32,21,12,23,45,55,44,33
2003,99,98,97,96,95,89,88,87,86,85,79,78
2002,55,56,57,58,59,65,66,67,68,69,75,76

And I want the output
year,v-1,v-0,v01,v02,v03,v04,v05,v06,v07,v08,v09,v10,v11,v12
2004,79,78,23,34,45,43,32,21,12,23,45,55,44,33
2003,75,76,99,98,97,96,95,89,88,87,86,85,79,78

Anyone knows a way to do this using only queries withou development code?

You're having trouble because your tables are incorrectly normalized.
You're storing data - a month - in fieldnames. This is NEVER a good
idea, and it's a particularly difficult problem when you want to do
this kind of comparison!

A better structure would be a "tall-thin" table with fields Year,
MonthNo, and Visitors, with values like

2004; 1; 23
2004; 2; 34
2004; 3; 45
....
2002; 12; 76

etc.

You can migrate your data into this structure using a Normalizing
Union Query:

SELECT [Year], (1) AS MonthNo, V01 AS Visitors FROM yourtable
UNION ALL
SELECT [Year], (2), V02 FROM yourtable
UNION ALL
SELECT [Year], (3), V03 FROM yourtable
.... <etc. for all 12 months>

Build this query in the SQL window and then base an Append query upon
it to populate your tall-thin table.

Your final Report can be generated using a Crosstab query.

John W. Vinson[MVP]

 

[John Spencer (MVP)]

If your Year field (bad name - there is a Year function is numeric.

SELECT X.[Year], x1.v11, x1.v12, x.v01,...xV12
FROM Visitors as X
LEFT JOIN Visitors as X1
On X.[Year] = X1.[Year] - 1

IF [Year] is a text field then use
... on Val(X.[Year]) = Val(X.[Year]) -1

But John Vinson's solution is MUCH better in most cases. Redesign your tables
to normalize the data. If you don't understand that post back here or in the
design/table groups.

 

[Guest]

John,

Thank you, worked fine. I will not discuss what is the normalization level
that data should be normalized because that is a theme for several master and
doctoral thesis. ;-)

Best regards.
Pedro L.

If your Year field (bad name - there is a Year function is numeric.

SELECT X.[Year], x1.v11, x1.v12, x.v01,...xV12
FROM Visitors as X
LEFT JOIN Visitors as X1
On X.[Year] = X1.[Year] - 1

IF [Year] is a text field then use
... on Val(X.[Year]) = Val(X.[Year]) -1

But John Vinson's solution is MUCH better in most cases. Redesign your tables
to normalize the data. If you don't understand that post back here or in the
design/table groups.

Hi all,

I'm having a problem and maybe someone has an answer to it: I have a table
that has records with 12 values each one representing the total number of
visitors of that month of the year. Of course, the year is an important index
for that table. For field grouping purposes, I need to make one or several
queries that returns a record with the 12 fields for that year and 2 other
(the november and december values) from the record of the previous year.

For example, I have a table Visitors with the following fields and values:
year,v01,v02,v03,v04,v05,v06,v07,v08,v09,v10,v11,v12
2004,23,34,45,43,32,21,12,23,45,55,44,33
2003,99,98,97,96,95,89,88,87,86,85,79,78
2002,55,56,57,58,59,65,66,67,68,69,75,76

And I want the output
year,v-1,v-0,v01,v02,v03,v04,v05,v06,v07,v08,v09,v10,v11,v12
2004,79,78,23,34,45,43,32,21,12,23,45,55,44,33
2003,75,76,99,98,97,96,95,89,88,87,86,85,79,78

Anyone knows a way to do this using only queries withou development code?
Thanks.
Pedro L.