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]
