Query Where Clause

  • Thread starter Thread starter Charles G via AccessMonster.com
  • Start date Start date
C

Charles G via AccessMonster.com

I have a report based on a single select query. When I run my report I get
all the records, however, some of my records share account numbers and
service codes. I would like to consolidate all the same account numbers with
the same service code into one record and add up the totals.

My report has:

[Client Name] [Account Number] [Service Code[ [Volume]
[Month of Activity]

Test 1234567899 385
101,000 September
Quiz 7265497282 390
35 September
Test 1234567899 385
55,000 September
Test 1234567899 390
25,650 September

When I run my report I want to see:

[Client Name] [Account Number] [Service Code[ [Volume]
[Month of Activity]

Test 1234567899 385
156,000 September
Quiz 7265497282 390
35 September
Test 1234567899 390
25,650 September

Can I create a query from a table with existing data and run a report
consolidating similar account numbers with matching service codes?

-Charles-
 
Yes, you can, and this is exactly the purpose of the GROUP BY clause. Your
existing query probably looks like:
SELECT [Client Name], [Account Number], [Service Code], [Volume], [Month of
Activity]
FROM Table1
WHERE etc etc

If you go into Query Designer, right click on the fields and choose "Totals",
you can then change [Volume] to be a SUM instead of a GROUP BY. The resulting
SQL will look like:
SELECT [Client Name], [Account Number], [Service Code], SUM([Volume]) as
TotalVolume, [Month of Activity]
FROM Table1
WHERE etc etc
GROUP BY [Client Name], [Account Number], [Service Code], [Month of Activity]
 
Everytime I put in my [Month of Activity] WHERE clause, the query won't keep
it. It won't let me check the box because you can't have a WHERE clause in a
Totals row. So, I added [Month of Activity] GROUP BY clause beside my
[Month of Activity] WHERE clause hoping to keep my [Month of Activity] WHERE
clause. Didn't work.

The first time I ran my query and report everything worked great. However,
when I shut my database down, re-entered more data and ran a report later, it
didn't work. It's wiping out my WHERE clause.

Below is my SQL view. I have many calculations for my coin denominations and
my volume in my report.
PARAMETERS [Month of Activity] Text;
SELECT [Account Analysis Input Table].[Client Name], [AA Client List Table].
[Client Account #], [Account Analysis Input Table].[Service Code], Sum(
[Account Analysis Input Table].[Currency Volume]) AS [SumOfCurrency Volume],
[Account Analysis Input Table].Pennies, ([Pennies]/0.5) AS Pennyrolls,
[Account Analysis Input Table].Nickels, ([Nickels]/2) AS Nickelrolls,
[Account Analysis Input Table].Dimes, ([Dimes]/5) AS Dimerolls, [Account
Analysis Input Table].Quarters, ([Quarters]/10) AS Quarterrolls, [Account
Analysis Input Table].Halves, ([Halves]/25) AS Halverolls, [Account Analysis
Input Table].Dollars, ([Dollars]/25) AS Dollarrolls, [Account Analysis Input
Table].[Boxed Coin], ([Boxed Coin]*50) AS Boxx50, Sum(((((((([Pennyrolls]+
[Nickelrolls]+[Dimerolls]+[Quarterrolls]+[Halverolls]+[Dollarrolls]+[Boxx50]+
[Currency Volume])))))))) AS Volume, [Account Analysis Input Table].[Month of
Activity]
FROM [Account Analysis Input Table] INNER JOIN [AA Client List Table] ON
[Account Analysis Input Table].[Client Name] = [AA Client List Table].[Client
Name]
GROUP BY [Account Analysis Input Table].[Client Name], [AA Client List Table].
[Client Account #], [Account Analysis Input Table].[Service Code], [Account
Analysis Input Table].Pennies, ([Pennies]/0.5), [Account Analysis Input Table]
..Nickels, ([Nickels]/2), [Account Analysis Input Table].Dimes, ([Dimes]/5),
[Account Analysis Input Table].Quarters, ([Quarters]/10), [Account Analysis
Input Table].Halves, ([Halves]/25), [Account Analysis Input Table].Dollars, (
[Dollars]/25), [Account Analysis Input Table].[Boxed Coin], ([Boxed Coin]*50),
[Account Analysis Input Table].[Month of Activity];

Thanks,

-Charles-
 
I've changed a few things around and have had a degree of success. I can now
group all the same account numbers who have a service code of 390. Service
Code 390 is for currency and in order to achive the currency volume I do not
need to perform any query calculations.

My query, however, is not grouping the same account numbers with service
code 385. Service Code 385 is for rolled coin and has calculations for each
coin denomination in order to come up with a rolled coin amount. In my report
the rolled coin amount + currency amount will equal [Volume].

I'm currently using this expression in my query for [Volume] in my report:

Volume: Sum(((((((((([Pennyrolls]+[Nickelrolls]+[Dimerolls]+[Quarterrolls]+
[Halverolls]+[Dollarrolls]+[Boxx50]+[Currency Volume])))))))))).

I wish I could explain this more clearly, so here is my current SQL:

PARAMETERS [Month of Activity] Text;
SELECT [Account Analysis Input Table].[Client Name], [AA Client List Table].
[Client Account #], [Account Analysis Input Table].[Service Code], Sum(
[Account Analysis Input Table].[Currency Volume]) AS [SumOfCurrency Volume],
[Account Analysis Input Table].Pennies, ([Pennies]/0.5) AS Pennyrolls,
[Account Analysis Input Table].Nickels, ([Nickels]/2) AS Nickelrolls,
[Account Analysis Input Table].Dimes, ([Dimes]/5) AS Dimerolls, [Account
Analysis Input Table].Quarters, ([Quarters]/10) AS Quarterrolls, [Account
Analysis Input Table].Halves, ([Halves]/25) AS Halverolls, [Account Analysis
Input Table].Dollars, ([Dollars]/25) AS Dollarrolls, [Account Analysis Input
Table].[Boxed Coin], ([Boxed Coin]*50) AS Boxx50, Sum(((((((((([Pennyrolls]+
[Nickelrolls]+[Dimerolls]+[Quarterrolls]+[Halverolls]+[Dollarrolls]+[Boxx50]+
[Currency Volume])))))))))) AS Volume, [Account Analysis Input Table].[Month
of Activity]
FROM [Account Analysis Input Table] INNER JOIN [AA Client List Table] ON
[Account Analysis Input Table].[Client Name] = [AA Client List Table].[Client
Name]
WHERE ((([Account Analysis Input Table].[Month of Activity])="January" Or (
[Account Analysis Input Table].[Month of Activity])="February" Or ([Account
Analysis Input Table].[Month of Activity])="March" Or ([Account Analysis
Input Table].[Month of Activity])="April" Or ([Account Analysis Input Table].
[Month of Activity])="May" Or ([Account Analysis Input Table].[Month of
Activity])="June" Or ([Account Analysis Input Table].[Month of Activity])
="July" Or ([Account Analysis Input Table].[Month of Activity])="August" Or (
[Account Analysis Input Table].[Month of Activity])="September" Or ([Account
Analysis Input Table].[Month of Activity])="October" Or ([Account Analysis
Input Table].[Month of Activity])="November" Or ([Account Analysis Input
Table].[Month of Activity])="December"))
GROUP BY [Account Analysis Input Table].[Client Name], [AA Client List Table].
[Client Account #], [Account Analysis Input Table].[Service Code], [Account
Analysis Input Table].Pennies, [Account Analysis Input Table].Nickels,
[Account Analysis Input Table].Dimes, [Account Analysis Input Table].Quarters,
[Account Analysis Input Table].Halves, [Account Analysis Input Table].Dollars,
[Account Analysis Input Table].[Boxed Coin], [Account Analysis Input Table].
[Month of Activity]
HAVING ((([Account Analysis Input Table].[Month of Activity])=[Month of
Activity]));

-Charles-
 
Ouch! Frankyl, I think you're trying to do too much in the one query - it
makes it a lot harder to find the isolated bug when you're trying to do
everything at once. I like to break things up into subqueries - although you
can end up with a lot of queries, I find that this makes debugging the entire
thing a lot easier.

So, reverse engineering your tables, it looks like you have:
[Account Analysis Input Table] with fields [Client Name], [Month of Activity],
[Service Code], [Currency Volume], Pennies, Nickels, Dimes, Quarters, Halves,
Dollars, [Boxed Coin];
[AA Client List Table] with fields [Client Name], [Client Account #]

First, let's calculate all your rolls with the query [Account Roll Query]:
SELECT [Account Analysis Input Table].[Client Name], [Account Analysis Input
Table].[Month of Activity], [Account Analysis Input Table].[Service Code],
[Account Analysis Input Table].[Currency Volume], [Account Analysis Input
Table].Pennies, ([Pennies]/0.5) AS Pennyrolls, [Account Analysis Input Table].
Nickels, ([Nickels]/2) AS Nickelrolls, [Account Analysis Input Table].Dimes,
([Dimes]/5) AS Dimerolls, [Account Analysis Input Table].Quarters, ([Quarters]
/10) AS Quarterrolls, [Account Analysis Input Table].Halves, ([Halves]/25) AS
Halverolls, [Account Analysis Input Table].Dollars, ([Dollars]/25) AS
Dollarrolls, [Account Analysis Input Table].[Boxed Coin], ([Boxed Coin]*50)
AS Boxx50
FROM [Account Analysis Input Table];

Now, we can summarise all this into the query [Currency Volume]:
SELECT [Account Roll Query].[Client Name], [Account Roll Query].[Month of
Activity], [Account Roll Query].[Service Code], Sum([Pennyrolls]+[Nickelrolls]
+[Dimerolls]+[Quarterrolls]+[Halverolls]+[Dollarrolls]+[Boxx50]+[Currency
Volume]) AS Volume
FROM [Account Roll Query]
GROUP BY [Account Roll Query].[Client Name], [Account Roll Query].[Month of
Activity], [Account Roll Query].[Service Code];

You can then use as the basis of your enquiry query (note: I have called the
parameter [Enter Month] rather than [Month of Acitvity], again to reduce
confusion from duplicate names meaning different things):
SELECT [AA Client List Table].[Client Name], [AA Client List Table].[Client
Account #], [Currency Volume Query].[Month of Activity], [Currency Volume
Query].[Service Code], [Currency Volume Query].Volume
FROM [Currency Volume Query] INNER JOIN [AA Client List Table] ON [Currency
Volume Query].[Client Name] = [AA Client List Table].[Client Name]
WHERE ((([Currency Volume Query].[Month of Activity])=[Enter Month of
Activity]));
 
David, you were right on. Before I left work I created another query and it
worked like a charm.

-Charles-
 
Back
Top