Thanks. I am familiar with the process you explained, but not sure it works
in this case. The cost areas are all separate field names so I would have to
have a criteria in each field, right? I basically need to give the user the
ability to select a specific field of the query(actually 2 fields, bgt &
act), not data within the field. I have posted the SQL below for a better
understanding.
Thank you.
SELECT [FY06 Current Table].[P&L], Sum([tbl BUDGET].[US$$]) AS [Bgt US],
Sum([FY06 Current Table].[TY w Risk P&L US]) AS [Act US], Sum([tbl
BUDGET].[Canada$$]) AS [Bgt CAN], Sum([FY06 Current Table].[TY w Risk P&L
CAN]) AS [Act CAN], Sum([tbl BUDGET].[SCItotal$$]) AS [Bgt SCI], Sum([FY06
Current Table].[TY w Risk P&L SCI]) AS [Act SCI], Sum([tbl BUDGET].[AP$$]) AS
[Bgt AP], Sum([FY06 Current Table].[TY w Risk AP]) AS [Act AP], Sum([tbl
BUDGET].[SBJ$$]) AS [Bgt SBJ], Sum([FY06 Current Table].[TY w Risk SBJ]) AS
[Act SBJ], Sum([tbl BUDGET].[EMEA$$]) AS [Bgt EMEA], Sum([FY06 Current
Table].[TY w Risk EMEA]) AS [Act EMEA], Sum([tbl BUDGET].[UK$$]) AS [Bgt UK],
Sum([FY06 Current Table].[TY w Risk UK]) AS [Act UK], Sum([tbl
BUDGET].[LA$$]) AS [Bgt LA], Sum([FY06 Current Table].[TY w Risk LA]) AS [Act
LA], Sum([FY06 Current Table].[TY w Risk SCI SSC]) AS [Act SCI SSC],
Sum([FY06 Current Table].[TY w Risk P&L Total SBUX]) AS [Act Total SBUX],
Sum([tbl BUDGET].[Other$$]) AS [Bgt Other], Sum([FY06 Current Table].[TY w
Risk Other]) AS [Act Other], Sum([tbl BUDGET].[LS$$]) AS [Bgt LS], Sum([FY06
Current Table].[TY w Risk LS]) AS [Act LS], Sum([tbl BUDGET].[FS$$]) AS [Bgt
FS], Sum([FY06 Current Table].[TY w Risk FS]) AS [Act FS], Sum([tbl
BUDGET].[FICE$$]) AS [Bgt FICE], Sum([FY06 Current Table].[TY w Risk FICE])
AS [Act FICE], Sum([tbl BUDGET].[US G&A$$]) AS [Bgt US G&A], Sum([FY06
Current Table].[TY w Risk US G&A]) AS [Act US G&A], Sum([tbl BUDGET].[Intl
G&A$$]) AS [Bgt Intl G&A], Sum([FY06 Current Table].[TY w Risk Intl G&A]) AS
[Act Intl G&A], Sum([tbl BUDGET].[SCO$$]) AS [Bgt SCO], Sum([FY06 Current
Table].[TY w Risk SCO]) AS [Act SCO], Sum([tbl BUDGET].[SBC$$]) AS [Bgt SBC],
Sum([FY06 Current Table].[TY w Risk SBC]) AS [Act SBC], Sum([tbl
BUDGET].[Corp$$]) AS [Bgt Corp], Sum([FY06 Current Table].[TY w Risk Corp])
AS [Act Corp]
FROM [tbl BUDGET] RIGHT JOIN [FY06 Current Table] ON [tbl BUDGET].[P&L Line]
= [FY06 Current Table].[P&L]
GROUP BY [FY06 Current Table].[P&L];
John Vinson said:
I am trying to setup a query or queries to pull data from a drop down list.
Here is the problem: I have 2 tables that store the data. One table contains
budget data and the other actual data. Both tables have 5 different cost
areas (US, Canada, Asia, Japan, Mexico). The common field in both tables is
P&L line.
I would like to setup a query where the user can select a cost area and
display budget vs. actual results by P&L line.
I started with a simple query including both tables and joining on the P&L
line. I am displaying every field and naming them bgt or actual (for example,
Bgt US, Act US, Bgt Canada, Act Canada, etc). This query gives me the results
I need, but I want to set it up so the user can select a particular cost
area.
Can this be done?
I know I can setup individual queries for each cost area and then use a
command button, but this would be to busy. (There are actually 15 cost areas,
not just 5).
Very simple. Use a Parameter Query - a standard tool which you'll use
over and over in Access.
In this case create a small Form, let's call it frmCrit, with a combo
box cboCostArea. The combo should be based on a query selecting all
the cost areas.
In the Criteria line of your query, instead of typing "Mexico", you
would put
=[Forms]![frmCrit]![cboCostArea]
The query will then look to that control for the desired value.
Put a command button on frmCrit to open the Report or Form which will
display the results of this query; generally it's not necessary nor a
good idea to open query datasheets directly.
John W. Vinson[MVP]
