|
|
|
date: Thu, 28 Aug 2008 14:44:01 -0700,
group: microsoft.public.access.queries
back
RE: Show Zeros in Query
Thanks for looking at my post Karl. Well, I guess the best way to explain,
is that sometimes a region, such as East or West, will not have any data, and
in the query results, these items will not be displayed. I was hoping to
display an item, with a zero in the query field, even if there is no data
there, kind of like Excel would do.
Thanks,
Ryan---
--
RyGuy
"KARL DEWEY" wrote:
> >>How can I display all records, even those that are zero?
> I do not follow - your query will show those that sum to zero IF they are a
> region you specified AND inventoryClass="Class2".
>
> Post sample data with results and expected results.
>
> --
> KARL DEWEY
> Build a little - Test a little
>
>
> "ryguy7272" wrote:
>
> > I did a google search and didn't get an answer to my question. Also, did a
> > search in the Queries group of the Access DG, still didn't find a SOLUTION.
> > My question is as follows... How can I display all records, even those that
> > are zero?
> >
> > My SQL is here:
> > SELECT tblRegion.SalesRegion, Sum(tblRegion.Q308) AS SumOfQ308,
> > Sum(tblRegion.Q408) AS SumOfQ408
> > FROM tblRegion
> > GROUP BY tblRegion.SalesRegion, tblRegion.inventoryClass
> > HAVING (((tblRegion.SalesRegion)="All" Or (tblRegion.SalesRegion)="Alliance"
> > Or (tblRegion.SalesRegion)="East" Or (tblRegion.SalesRegion)="Inside Sales"
> > Or (tblRegion.SalesRegion)="Unassigned" Or (tblRegion.SalesRegion)="West")
> > AND ((tblRegion.inventoryClass)="Class2"));
> >
> >
> >
> > Thanks so much,
> > Ryan---
> >
> > --
> > RyGuy
date: Thu, 28 Aug 2008 15:11:02 -0700
author: ryguy7272
RE: Show Zeros in Query
The easiest way due to your large number of stated regions is to build a
table of regions with a Yes/No field to check is it is to be included in the
report.
Then LEFT JOIN it in your query as below --
SELECT tblRegion.SalesRegion, Sum(tblRegion.Q308) AS SumOfQ308,
Sum(tblRegion.Q408) AS SumOfQ408
FROM RegionTable LEFT JOIN tblRegion ON RegionTable.Region =
tblRegion.SalesRegion
WHERE tblRegion.inventoryClass)="Class2" AND RegionTable.Include = -1
GROUP BY tblRegion.SalesRegion, tblRegion.inventoryClass;
--
KARL DEWEY
Build a little - Test a little
"ryguy7272" wrote:
> Thanks for looking at my post Karl. Well, I guess the best way to explain,
> is that sometimes a region, such as East or West, will not have any data, and
> in the query results, these items will not be displayed. I was hoping to
> display an item, with a zero in the query field, even if there is no data
> there, kind of like Excel would do.
>
> Thanks,
> Ryan---
> --
> RyGuy
>
>
> "KARL DEWEY" wrote:
>
> > >>How can I display all records, even those that are zero?
> > I do not follow - your query will show those that sum to zero IF they are a
> > region you specified AND inventoryClass="Class2".
> >
> > Post sample data with results and expected results.
> >
> > --
> > KARL DEWEY
> > Build a little - Test a little
> >
> >
> > "ryguy7272" wrote:
> >
> > > I did a google search and didn't get an answer to my question. Also, did a
> > > search in the Queries group of the Access DG, still didn't find a SOLUTION.
> > > My question is as follows... How can I display all records, even those that
> > > are zero?
> > >
> > > My SQL is here:
> > > SELECT tblRegion.SalesRegion, Sum(tblRegion.Q308) AS SumOfQ308,
> > > Sum(tblRegion.Q408) AS SumOfQ408
> > > FROM tblRegion
> > > GROUP BY tblRegion.SalesRegion, tblRegion.inventoryClass
> > > HAVING (((tblRegion.SalesRegion)="All" Or (tblRegion.SalesRegion)="Alliance"
> > > Or (tblRegion.SalesRegion)="East" Or (tblRegion.SalesRegion)="Inside Sales"
> > > Or (tblRegion.SalesRegion)="Unassigned" Or (tblRegion.SalesRegion)="West")
> > > AND ((tblRegion.inventoryClass)="Class2"));
> > >
> > >
> > >
> > > Thanks so much,
> > > Ryan---
> > >
> > > --
> > > RyGuy
date: Thu, 28 Aug 2008 16:07:02 -0700
author: KARL DEWEY
RE: Show Zeros in Query
I'm back to square one Karl. Can you give me detailed instructions about
creating the table and doing a left-join from the original table to the new
table? I tried a bunch of things, including adding the new table and using
Yes/No fields, but nothing worked. Alternatively, I was trying to use the
Expression Builder to do something like this:
Sum(NZ(tblRegion!inventoryClass,0))
I didn't get very far with that method either.
Can you assist with one of these options?
Thanks so much!
Ryan---
--
RyGuy
"KARL DEWEY" wrote:
> The easiest way due to your large number of stated regions is to build a
> table of regions with a Yes/No field to check is it is to be included in the
> report.
>
> Then LEFT JOIN it in your query as below --
> SELECT tblRegion.SalesRegion, Sum(tblRegion.Q308) AS SumOfQ308,
> Sum(tblRegion.Q408) AS SumOfQ408
> FROM RegionTable LEFT JOIN tblRegion ON RegionTable.Region =
> tblRegion.SalesRegion
> WHERE tblRegion.inventoryClass)="Class2" AND RegionTable.Include = -1
> GROUP BY tblRegion.SalesRegion, tblRegion.inventoryClass;
>
> --
> KARL DEWEY
> Build a little - Test a little
>
>
> "ryguy7272" wrote:
>
> > Thanks for looking at my post Karl. Well, I guess the best way to explain,
> > is that sometimes a region, such as East or West, will not have any data, and
> > in the query results, these items will not be displayed. I was hoping to
> > display an item, with a zero in the query field, even if there is no data
> > there, kind of like Excel would do.
> >
> > Thanks,
> > Ryan---
> > --
> > RyGuy
> >
> >
> > "KARL DEWEY" wrote:
> >
> > > >>How can I display all records, even those that are zero?
> > > I do not follow - your query will show those that sum to zero IF they are a
> > > region you specified AND inventoryClass="Class2".
> > >
> > > Post sample data with results and expected results.
> > >
> > > --
> > > KARL DEWEY
> > > Build a little - Test a little
> > >
> > >
> > > "ryguy7272" wrote:
> > >
> > > > I did a google search and didn't get an answer to my question. Also, did a
> > > > search in the Queries group of the Access DG, still didn't find a SOLUTION.
> > > > My question is as follows... How can I display all records, even those that
> > > > are zero?
> > > >
> > > > My SQL is here:
> > > > SELECT tblRegion.SalesRegion, Sum(tblRegion.Q308) AS SumOfQ308,
> > > > Sum(tblRegion.Q408) AS SumOfQ408
> > > > FROM tblRegion
> > > > GROUP BY tblRegion.SalesRegion, tblRegion.inventoryClass
> > > > HAVING (((tblRegion.SalesRegion)="All" Or (tblRegion.SalesRegion)="Alliance"
> > > > Or (tblRegion.SalesRegion)="East" Or (tblRegion.SalesRegion)="Inside Sales"
> > > > Or (tblRegion.SalesRegion)="Unassigned" Or (tblRegion.SalesRegion)="West")
> > > > AND ((tblRegion.inventoryClass)="Class2"));
> > > >
> > > >
> > > >
> > > > Thanks so much,
> > > > Ryan---
> > > >
> > > > --
> > > > RyGuy
date: Fri, 29 Aug 2008 08:36:03 -0700
author: ryguy7272
RE: Show Zeros in Query
Thought about it more over past few days, and I got a new perspective on
things with some help from others on this DG. Solution is here:
http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.access.queries&tid=a862b44e-6023-4668-8a03-1caee796cf70&cat=&lang=en&cr=US&sloc=&p=1
--
RyGuy
"ryguy7272" wrote:
> I'm back to square one Karl. Can you give me detailed instructions about
> creating the table and doing a left-join from the original table to the new
> table? I tried a bunch of things, including adding the new table and using
> Yes/No fields, but nothing worked. Alternatively, I was trying to use the
> Expression Builder to do something like this:
> Sum(NZ(tblRegion!inventoryClass,0))
>
> I didn't get very far with that method either.
>
> Can you assist with one of these options?
>
> Thanks so much!
> Ryan---
>
> --
> RyGuy
>
>
> "KARL DEWEY" wrote:
>
> > The easiest way due to your large number of stated regions is to build a
> > table of regions with a Yes/No field to check is it is to be included in the
> > report.
> >
> > Then LEFT JOIN it in your query as below --
> > SELECT tblRegion.SalesRegion, Sum(tblRegion.Q308) AS SumOfQ308,
> > Sum(tblRegion.Q408) AS SumOfQ408
> > FROM RegionTable LEFT JOIN tblRegion ON RegionTable.Region =
> > tblRegion.SalesRegion
> > WHERE tblRegion.inventoryClass)="Class2" AND RegionTable.Include = -1
> > GROUP BY tblRegion.SalesRegion, tblRegion.inventoryClass;
> >
> > --
> > KARL DEWEY
> > Build a little - Test a little
> >
> >
> > "ryguy7272" wrote:
> >
> > > Thanks for looking at my post Karl. Well, I guess the best way to explain,
> > > is that sometimes a region, such as East or West, will not have any data, and
> > > in the query results, these items will not be displayed. I was hoping to
> > > display an item, with a zero in the query field, even if there is no data
> > > there, kind of like Excel would do.
> > >
> > > Thanks,
> > > Ryan---
> > > --
> > > RyGuy
> > >
> > >
> > > "KARL DEWEY" wrote:
> > >
> > > > >>How can I display all records, even those that are zero?
> > > > I do not follow - your query will show those that sum to zero IF they are a
> > > > region you specified AND inventoryClass="Class2".
> > > >
> > > > Post sample data with results and expected results.
> > > >
> > > > --
> > > > KARL DEWEY
> > > > Build a little - Test a little
> > > >
> > > >
> > > > "ryguy7272" wrote:
> > > >
> > > > > I did a google search and didn't get an answer to my question. Also, did a
> > > > > search in the Queries group of the Access DG, still didn't find a SOLUTION.
> > > > > My question is as follows... How can I display all records, even those that
> > > > > are zero?
> > > > >
> > > > > My SQL is here:
> > > > > SELECT tblRegion.SalesRegion, Sum(tblRegion.Q308) AS SumOfQ308,
> > > > > Sum(tblRegion.Q408) AS SumOfQ408
> > > > > FROM tblRegion
> > > > > GROUP BY tblRegion.SalesRegion, tblRegion.inventoryClass
> > > > > HAVING (((tblRegion.SalesRegion)="All" Or (tblRegion.SalesRegion)="Alliance"
> > > > > Or (tblRegion.SalesRegion)="East" Or (tblRegion.SalesRegion)="Inside Sales"
> > > > > Or (tblRegion.SalesRegion)="Unassigned" Or (tblRegion.SalesRegion)="West")
> > > > > AND ((tblRegion.inventoryClass)="Class2"));
> > > > >
> > > > >
> > > > >
> > > > > Thanks so much,
> > > > > Ryan---
> > > > >
> > > > > --
> > > > > RyGuy
date: Tue, 2 Sep 2008 08:11:05 -0700
author: ryguy7272
|
|
