Re: Using a list box in a query

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" <Rob@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:886E64C0-3846-4555-B93F-3E196556C5DC@xxxxxxxxxxxxxxxx
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" wrote:

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" <Rob@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:32666642-6546-4C0B-B85D-9C25E47C8BE4@xxxxxxxxxxxxxxxx
Thanks. What do you mean by "normalizing" my tables?

"Duane Hookom" wrote:

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" <Rob@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9C411080-6DEC-43BE-8385-625EF0339267@xxxxxxxxxxxxxxxx
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];












.

Relevant Pages
Loading