Using a list box in a query

R

Rob

I am trying to create a list box to give the user the ability to select a
specific data point to view actual and budget detail. See my SQL below. This
query provides the data needed, but I would to give the user the ability to
select a specific country to view instead of all at once.
I have been unable to perform this based on how the tables are set up. Any
ideas?
Thanks.

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];
 
D

Duane Hookom

No offense but that query is a mess caused by line wrapping and spaces in
field and table names. Consider formatting it with line breaks so that
potential helpers don't have to. For instance:
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],

I didn't see a field named "country" or something similar. If your countries
are embedded in field names consider normalizing your tables or using a
union query.

--
Duane Hookom
MS Access MVP

Rob said:
I am trying to create a list box to give the user the ability to select a
specific data point to view actual and budget detail. See my SQL below.
This
query provides the data needed, but I would to give the user the ability
to
select a specific country to view instead of all at once.
I have been unable to perform this based on how the tables are set up. Any
ideas?
Thanks.

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];
Click to expand...
 
R

Rob

Thanks. What do you mean by "normalizing" my tables?

Duane Hookom said:
No offense but that query is a mess caused by line wrapping and spaces in
field and table names. Consider formatting it with line breaks so that
potential helpers don't have to. For instance:
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],

I didn't see a field named "country" or something similar. If your countries
are embedded in field names consider normalizing your tables or using a
union query.

--
Duane Hookom
MS Access MVP

Rob said:
I am trying to create a list box to give the user the ability to select a
specific data point to view actual and budget detail. See my SQL below.
This
query provides the data needed, but I would to give the user the ability
to
select a specific country to view instead of all at once.
I have been unable to perform this based on how the tables are set up. Any
ideas?
Thanks.

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];
Click to expand...
Click to expand...
 
D

Duane Hookom

Rather than have fields with country names, you would normally create tables
with a field for Country. Consider reading up on table structures at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101.

--
Duane Hookom
MS Access MVP

Rob said:
Thanks. What do you mean by "normalizing" my tables?

Duane Hookom said:
No offense but that query is a mess caused by line wrapping and spaces in
field and table names. Consider formatting it with line breaks so that
potential helpers don't have to. For instance:
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],

I didn't see a field named "country" or something similar. If your
countries
are embedded in field names consider normalizing your tables or using a
union query.

--
Duane Hookom
MS Access MVP

Rob said:
I am trying to create a list box to give the user the ability to select
a
specific data point to view actual and budget detail. See my SQL below.
This
query provides the data needed, but I would to give the user the
ability
to
select a specific country to view instead of all at once.
I have been unable to perform this based on how the tables are set up.
Any
ideas?
Thanks.

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];
Click to expand...
Click to expand...
Click to expand...
 
R

Rob

Thanks for the reference. Unfortunately, the data I am working with is from
an existing excel template that I am linking to. Is there any way to
re-structure the tables other than manually?

Duane Hookom said:
Rather than have fields with country names, you would normally create tables
with a field for Country. Consider reading up on table structures at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101.

--
Duane Hookom
MS Access MVP

Rob said:
Thanks. What do you mean by "normalizing" my tables?

Duane Hookom said:
No offense but that query is a mess caused by line wrapping and spaces in
field and table names. Consider formatting it with line breaks so that
potential helpers don't have to. For instance:
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],

I didn't see a field named "country" or something similar. If your
countries
are embedded in field names consider normalizing your tables or using a
union query.

--
Duane Hookom
MS Access MVP

I am trying to create a list box to give the user the ability to select
a
specific data point to view actual and budget detail. See my SQL below.
This
query provides the data needed, but I would to give the user the
ability
to
select a specific country to view instead of all at once.
I have been unable to perform this based on how the tables are set up.
Any
ideas?
Thanks.

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];
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

If you want your users to be able to select which fields/columns to return
in a query, you may need to write some code that builds a SQL string.

--
Duane Hookom
MS Access MVP

Rob said:
Thanks for the reference. Unfortunately, the data I am working with is
from
an existing excel template that I am linking to. Is there any way to
re-structure the tables other than manually?

Duane Hookom said:
Rather than have fields with country names, you would normally create
tables
with a field for Country. Consider reading up on table structures at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101.

--
Duane Hookom
MS Access MVP

Rob said:
Thanks. What do you mean by "normalizing" my tables?

:

No offense but that query is a mess caused by line wrapping and spaces
in
field and table names. Consider formatting it with line breaks so that
potential helpers don't have to. For instance:
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],

I didn't see a field named "country" or something similar. If your
countries
are embedded in field names consider normalizing your tables or using
a
union query.

--
Duane Hookom
MS Access MVP

I am trying to create a list box to give the user the ability to
select
a
specific data point to view actual and budget detail. See my SQL
below.
This
query provides the data needed, but I would to give the user the
ability
to
select a specific country to view instead of all at once.
I have been unable to perform this based on how the tables are set
up.
Any
ideas?
Thanks.

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];
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Field Selection Criteria 1
Using a parameter in a union query 2
How to use update query to update multiple fields in a table 2
Moving Formulas when Sorting a table 0
Creating a Lookup Table Using a Do...Until Loop in VBA 0
Error with Equations in Tables Spanning Page Breaks 0
run a make table query using parameters in multi-select list box 7
Assigning cell values with VB 3

Top