Field Selection Criteria

[Rob]

Is there a way to use a combo box or some sort of coding to select specific
fields from a query?
I have a query that combines data from 2 tables. Each field represents
actual or budget data for several different cost centers. The query gives me
all the data I need, however I would like to set it up so a user can select 1
specfic cost center to view. Is this possible?

I have attached the SQL below:
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];

 

[Klatuu]

I can't tell from your code which field represents a cost center, but this is
not that difficult to do.

Creata a combo box on your form that will give you a list of all the cost
centers. Avoid using a value list, it will cause you problems in the future
(I will address that later). Use a query on a table or tables that will make
your combo a list of all cost centers. In the After Update event of the
combo, requery the form.

You will also have to modify the query to filter on the value of the combo
box. It will also need to include in the filtering the logic to present all
cost centers.

To help with that, here is a site that will explain how to add "(All)" to a
combo

http://www.mvps.org/access/forms/frm0043.htm

Once you have that down, then you need to filter the query using something
like:

[CostCenter] Like IIf([Forms]![MyForm]![cboCostCenter] = "(All)", "*",
[Forms]![MyForm]![cboCostCenter])

Is there a way to use a combo box or some sort of coding to select specific
fields from a query?
I have a query that combines data from 2 tables. Each field represents
actual or budget data for several different cost centers. The query gives me
all the data I need, however I would like to set it up so a user can select 1
specfic cost center to view. Is this possible?

I have attached the SQL below:
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];