Year-to-Date Query

I would like to build a query that compares our monthly
plan to the Year -to-date actual. I have table 1 and
table 2 built. I need help with query1.

Table1: Monthly plan

State Jan Feb Mar Apr May Jun

NY 400 600 800 900 500 300
RI 500 700 800 300 400 500
MA 800 800 500 300 400 700


Table2: Actual

State Amount Date
NY 500 Jan
RI 500 Jan
MA 400 Jan
NY 300 Feb
RI 800 Feb
MA 900 Feb

Query1: YTD Comparisons

State YTD Actual YTD Plan Variance
NY 800 1000 -200
RI 1,300 1,200 100
MA 1,300 1,600 -300

Thanks in Advance

On Tue, 8 Mar 2005 07:40:58 -0800, "Ramone"
wrote:

I would like to build a query that compares our monthly
plan to the Year -to-date actual. I have table 1 and
table 2 built. I need help with query1.

Table1: Monthly plan

State Jan Feb Mar Apr May Jun

NY 400 600 800 900 500 300
RI 500 700 800 300 400 500
MA 800 800 500 300 400 700

Sorry... but that's not a table. That's a spreadsheet. Storing data
(months) in a field name is *extremely bad design* in a relational
database - for one thing, it makes it far more difficult to do what
you ask.

Secondly, storing a three-letter text string "May" may be understood
as a date by a human, but will be understood as a three letter text
string (sorting after the text strings Jul and Aug), not as a date.

I'd suggest that your Plan table should be stored differently: three
fields, State (Text 2), PlanDate (Date/Time), and Amount (with the
first two fields defined as the Primary Key). The data might look like

NY 1/1/2005 400
NY 2/1/2005 600
....
RI 1/1/2005 500
RI 2/1/2005 800

You can get from where you are to this structure with a "Normalizing
Union Query". In the SQL window put

SELECT [State], #1/1/2005# AS PlanDate, [Jan] AS Amount
FROM [Monthly Plan]
WHERE [Jan] IS NOT NULL
UNION ALL
SELECT [State], #2/1/2005#, [Feb]
FROM [Monthly Plan]
WHERE [Feb] IS NOT NULL
UNION ALL
etc etc through all 12 months

Save this query and then base a MakeTable query upon it; let's call
the new table Planned.

Table2: Actual

State Amount Date
NY 500 Jan
RI 500 Jan
MA 400 Jan
NY 300 Feb
RI 800 Feb
MA 900 Feb

This is a better structure - but do change the three-letter code to a
real date/time field. Don't use Date as the fieldname though - it's a
reserved word.

Query1: YTD Comparisons

State YTD Actual YTD Plan Variance
NY 800 1000 -200
RI 1,300 1,200 100
MA 1,300 1,600 -300

Thanks in Advance

Create a Query joining [Planned] to [Actual], joining by [State] and
by the date field. Put a criterion on the date field of

= Date()

Make it a Totals query by clicking the Greek Sigma icon. Group By
State, and type

Variance: Sum([Actual].[Amount]) - Sum([Planned].[Amount])


With your current table structure, you'll still need to do the UNION
query and use it in place of the new [Planned] table... and it's going
to be slow.

John W. Vinson[MVP]