qry-SalesAnalysysCollectData?

  • Thread starter Thread starter Maureen
  • Start date Start date
M

Maureen

tblInventory + tblInvoiceDetail

I would like to collect sales history for analysys. I'd like sales for
various periods (based on tbleInvoiceDetail.date) for each SKU in inventory

M-last 30 days
Q-last 90 days
X-last 180 days
Y-last 366 days
L-Between day 366 and 732
Z-Life to date

I would also like to do the same for tblCustomer + tblInvoiceDetail
collecting the sales history for each customer

I am somewhat new to access and could use some pointers as to the simplest
way to do this. Any suggestions would be apreciated!
M
 
"Maureen" wrote
tblInventory + tblInvoiceDetail

I would like to collect sales history for analysys. I'd like sales for
various periods (based on tbleInvoiceDetail.date) for each SKU in inventory

M-last 30 days
Q-last 90 days
X-last 180 days
Y-last 366 days
L-Between day 366 and 732
Z-Life to date

I would also like to do the same for tblCustomer + tblInvoiceDetail
collecting the sales history for each customer
Click to expand...
Hi Maureen,

Will start with "simple case" where SKU
is also a field in tblInvoiceDetail.

--------------
Z-Life to date
--------------
Click on "Create Query in Design View"

In the Show Table dialog box,
click on tblInvoiceDetail,
click Add,
and then click Close.

Right-mouse click on your table
and choose Properties.
In the Alias row, type in
t1
then close the Properties dialog box.

Drag and drop "SKU" from your table
down to a field row in the lower grid.
Select "Ascending" in the "Sort" row
for this column.

Drag and drop "Qty" from your table
down to a field row in the next column
of the grid.

Change your SELECT query to a groupby
by clicking on the sigma icon in top menu.

In "Qty" column of grid, change "Group By"
to "Sum"

Save query (say "qrySalesLifeToDateBySKU").

In SQL View, your query might look like:

SELECT
t1.SKU,
SUM(t1.Qty) AS [Sum of Qty]
FROM
tblInvoiceDetail AS t1
GROUP BY t1.SKU
ORDER BY t1.SKU;

If your tblInvoiceDetail had a field that gave
total cost in currency (like "ExtCost"), you
could add this field and sum over it as well.

SELECT
t1.SKU,
SUM(t1.Qty) AS [Sum of Qty],
SUM(t1.ExtCost) AS [Sum of ExtCost]
FROM
tblInvoiceDetail AS t1
GROUP BY t1.SKU
ORDER BY t1.SKU;

If you only get a price for the SKU
(say "Cost"), then you could sum over
the expression (Qty*Cost). In the field
row of a column of your grid, type in
something like:

SumExtCost: t1.Qty*t1.Cost

and set its Total row to Sum.

SELECT
t1.SKU,
SUM(t1.Qty) AS [Sum of Qty],
SUM(t1.Qty*t1.Cost) AS SumExtCost
FROM
tblInvoiceDetail AS t1
GROUP BY t1.SKU
ORDER BY t1.SKU;

You don't have to settle for alias that
Access autogenerates for your "Sum"..
in SQL View, you can change to something
like:

SELECT
t1.SKU,
SUM(t1.Qty) AS TotalSKUQty,
SUM(t1.Qty*t1.Cost) AS SumExtCost
FROM
tblInvoiceDetail AS t1
GROUP BY t1.SKU
ORDER BY t1.SKU;

--------------
M-last 30 days
--------------

All the other "by SKU" queries will start
the same as above, but you need to bring
"tblInvoiceDetail.date" down into a field row
of another column of your grid.

"Date" is an Access reserved word, so it would
be best if you can change its name in the table to
something like "InvDate"...if you cannot, always
remember to enclose it within brackets in queries.
But it is a "gotcha" waiting to happen. In the following
I am going to refer to it as "InvDate"....

In Total row for InvDate, change "Group By" to
"Where"

Then in its "Criteria" row, type in
=Date()-30
Click to expand...

So your SQL might look like:

SELECT
t1.SKU,
SUM(t1.Qty) AS TotalSKUQty,
SUM(t1.Qty*t1.Cost) AS SumExtCost
FROM
tblInvoiceDetail AS t1
WHERE ((tbl1.InvDate)>=Date()-30)
GROUP BY t1.SKU
ORDER BY t1.SKU;

-----------
Q-last 90 days
-----------
same as above, but Criteria row might look like:
=Date()-90
Click to expand...

"X" and "Y" above would be similar except
use 180 and 366 respectfully, instead of 90.

---------------
L-Between day 366 and 732
---------------

same as above, but Criteria row might look like:
=Date()-732 And <Date() - 365
Click to expand...

or

BETWEEN Date()-732 AND Date()-366

////////////////tblCustomer + tblInvoiceDetail////////////////

Click on "Create Query in Design View"

In the Show Table dialog box,
click on tblCustomer,
click Add,
click on tblInvoiceDetail,
click on Add again,
and then click Close.

You should now show your 2 tables
in the query designer.

Right-mouse click on tblCustomer
and choose Properties.
In the Alias row, type in
c1
then close the Properties dialog box.

Right-mouse click on the tblInvoiceDetail table
and choose Properties.
In the Alias row, type in
t1
then close the Properties dialog box.

If you do not get a "correct" join line
between the 2 tables (its possible for
Access to assume wrongly, so you
may first have to right-mouse click
on a join line and choose delete).

Then click and hold down on c1 table's
join field (possibly "CustomerID")
and "drag and drop"
over on t1 table's join field
(possibly also "CustomerID").

If, in your query, you want to show
*all* Customers even if they do not
show up in the invoice detail table,
right-mouse click on the join line,
choose properties, and select the
option to show all Customers.

Bring "CustomerName" (or however
you identify a distinct Customer) down
into field row.

Change to Group By query.

The rest is similar to above depending
how you want to define a group and
what you want to sum, and over what
dates. For example:

SELECT
c1.CustomerName,
SUM(t1.Qty) AS TotalCustQty,
SUM(t1.Qty*t1.Cost) AS SumExtCost
FROM
tblCustomer AS c1
INNER JOIN
tblInvoiceDetail AS t1
ON c1.CustomerID = t1.CustomerID
GROUP BY c1.CustomerName
ORDER BY c1.CustomerName;

or all Customers:

SELECT
c1.CustomerName,
SUM(NZ(t1.Qty)) AS TotalCustQty,
SUM(NZ(t1.Qty)*NZ(t1.Cost)) AS SumExtCost
FROM
tblCustomer AS c1
LEFT JOIN
tblInvoiceDetail AS t1
ON c1.CustomerID = t1.CustomerID
GROUP BY c1.CustomerName
ORDER BY c1.CustomerName;

or all Customers last 30 days:

SELECT
c1.CustomerName,
SUM(NZ(t1.Qty)) AS TotalCustQty,
SUM(NZ(t1.Qty)*NZ(t1.Cost)) AS SumExtCost
FROM
tblCustomer AS c1
LEFT JOIN
tblInvoiceDetail AS t1
ON c1.CustomerID = t1.CustomerID
WHERE ((tbl1.InvDate)>=Date()-30)
GROUP BY c1.CustomerName
ORDER BY c1.CustomerName;

////////// tblInventory + tblInvoiceDetail////////////
Getting *all* SKU's from tblInventory
would be similar to above.

SELECT
t2.SKU,
SUM(NZ(t1.Qty)) AS TotalSKUQty,
SUM(NZ(t1.Qty)*NZ(t1.Cost)) AS SumExtCost
FROM
tblInventory AS t2
LEFT JOIN
tblInvoiceDetail AS t1
ON t2.SKU = t1.SKU
GROUP BY t2.SKU
ORDER BY t2.SKU;

or all SKU's last 30 days:

SELECT
t2.SKU,
SUM(NZ(t1.Qty)) AS TotalSKUQty,
SUM(NZ(t1.Qty)*NZ(t1.Cost)) AS SumExtCost
FROM
tblInventory AS t2
LEFT JOIN
tblInvoiceDetail AS t1
ON t2.SKU = t1.SKU
WHERE ((tbl1.InvDate)>=Date()-30)
GROUP BY t2.SKU
ORDER BY t2.SKU;

Well...I've assumed alot which may be
wrong, but I think you might get the general
gist of this.

Please respond back if I have misunderstood
or was not clear about something.
(I will be "away" for a few days though)

Good luck,

Gary Walter
 
Sorry for typos

"tbl1.InvDate" should be "t1.InvDate"

of course I copy-and-pasted so all
were muffed!
 
Gary,

Thank for your post. I understand the basics and have the six queries done
for inventory and customer with all of them collected into a master to drive
my report. I thought that perhaps there was a simpler way... I guess not.

M

Gary Walter said:
"Maureen" wrote
tblInventory + tblInvoiceDetail

I would like to collect sales history for analysys. I'd like sales for
various periods (based on tbleInvoiceDetail.date) for each SKU in inventory

M-last 30 days
Q-last 90 days
X-last 180 days
Y-last 366 days
L-Between day 366 and 732
Z-Life to date

I would also like to do the same for tblCustomer + tblInvoiceDetail
collecting the sales history for each customer
Click to expand...
Hi Maureen,

Will start with "simple case" where SKU
is also a field in tblInvoiceDetail.

--------------
Z-Life to date
--------------
Click on "Create Query in Design View"

In the Show Table dialog box,
click on tblInvoiceDetail,
click Add,
and then click Close.

Right-mouse click on your table
and choose Properties.
In the Alias row, type in
t1
then close the Properties dialog box.

Drag and drop "SKU" from your table
down to a field row in the lower grid.
Select "Ascending" in the "Sort" row
for this column.

Drag and drop "Qty" from your table
down to a field row in the next column
of the grid.

Change your SELECT query to a groupby
by clicking on the sigma icon in top menu.

In "Qty" column of grid, change "Group By"
to "Sum"

Save query (say "qrySalesLifeToDateBySKU").

In SQL View, your query might look like:

SELECT
t1.SKU,
SUM(t1.Qty) AS [Sum of Qty]
FROM
tblInvoiceDetail AS t1
GROUP BY t1.SKU
ORDER BY t1.SKU;

If your tblInvoiceDetail had a field that gave
total cost in currency (like "ExtCost"), you
could add this field and sum over it as well.

SELECT
t1.SKU,
SUM(t1.Qty) AS [Sum of Qty],
SUM(t1.ExtCost) AS [Sum of ExtCost]
FROM
tblInvoiceDetail AS t1
GROUP BY t1.SKU
ORDER BY t1.SKU;

If you only get a price for the SKU
(say "Cost"), then you could sum over
the expression (Qty*Cost). In the field
row of a column of your grid, type in
something like:

SumExtCost: t1.Qty*t1.Cost

and set its Total row to Sum.

SELECT
t1.SKU,
SUM(t1.Qty) AS [Sum of Qty],
SUM(t1.Qty*t1.Cost) AS SumExtCost
FROM
tblInvoiceDetail AS t1
GROUP BY t1.SKU
ORDER BY t1.SKU;

You don't have to settle for alias that
Access autogenerates for your "Sum"..
in SQL View, you can change to something
like:

SELECT
t1.SKU,
SUM(t1.Qty) AS TotalSKUQty,
SUM(t1.Qty*t1.Cost) AS SumExtCost
FROM
tblInvoiceDetail AS t1
GROUP BY t1.SKU
ORDER BY t1.SKU;

--------------
M-last 30 days
--------------

All the other "by SKU" queries will start
the same as above, but you need to bring
"tblInvoiceDetail.date" down into a field row
of another column of your grid.

"Date" is an Access reserved word, so it would
be best if you can change its name in the table to
something like "InvDate"...if you cannot, always
remember to enclose it within brackets in queries.
But it is a "gotcha" waiting to happen. In the following
I am going to refer to it as "InvDate"....

In Total row for InvDate, change "Group By" to
"Where"

Then in its "Criteria" row, type in
=Date()-30
Click to expand...

So your SQL might look like:

SELECT
t1.SKU,
SUM(t1.Qty) AS TotalSKUQty,
SUM(t1.Qty*t1.Cost) AS SumExtCost
FROM
tblInvoiceDetail AS t1
WHERE ((tbl1.InvDate)>=Date()-30)
GROUP BY t1.SKU
ORDER BY t1.SKU;

-----------
Q-last 90 days
-----------
same as above, but Criteria row might look like:
=Date()-90
Click to expand...

"X" and "Y" above would be similar except
use 180 and 366 respectfully, instead of 90.

---------------
L-Between day 366 and 732
---------------

same as above, but Criteria row might look like:
=Date()-732 And <Date() - 365
Click to expand...

or

BETWEEN Date()-732 AND Date()-366

////////////////tblCustomer + tblInvoiceDetail////////////////

Click on "Create Query in Design View"

In the Show Table dialog box,
click on tblCustomer,
click Add,
click on tblInvoiceDetail,
click on Add again,
and then click Close.

You should now show your 2 tables
in the query designer.

Right-mouse click on tblCustomer
and choose Properties.
In the Alias row, type in
c1
then close the Properties dialog box.

Right-mouse click on the tblInvoiceDetail table
and choose Properties.
In the Alias row, type in
t1
then close the Properties dialog box.

If you do not get a "correct" join line
between the 2 tables (its possible for
Access to assume wrongly, so you
may first have to right-mouse click
on a join line and choose delete).

Then click and hold down on c1 table's
join field (possibly "CustomerID")
and "drag and drop"
over on t1 table's join field
(possibly also "CustomerID").

If, in your query, you want to show
*all* Customers even if they do not
show up in the invoice detail table,
right-mouse click on the join line,
choose properties, and select the
option to show all Customers.

Bring "CustomerName" (or however
you identify a distinct Customer) down
into field row.

Change to Group By query.

The rest is similar to above depending
how you want to define a group and
what you want to sum, and over what
dates. For example:

SELECT
c1.CustomerName,
SUM(t1.Qty) AS TotalCustQty,
SUM(t1.Qty*t1.Cost) AS SumExtCost
FROM
tblCustomer AS c1
INNER JOIN
tblInvoiceDetail AS t1
ON c1.CustomerID = t1.CustomerID
GROUP BY c1.CustomerName
ORDER BY c1.CustomerName;

or all Customers:

SELECT
c1.CustomerName,
SUM(NZ(t1.Qty)) AS TotalCustQty,
SUM(NZ(t1.Qty)*NZ(t1.Cost)) AS SumExtCost
FROM
tblCustomer AS c1
LEFT JOIN
tblInvoiceDetail AS t1
ON c1.CustomerID = t1.CustomerID
GROUP BY c1.CustomerName
ORDER BY c1.CustomerName;

or all Customers last 30 days:

SELECT
c1.CustomerName,
SUM(NZ(t1.Qty)) AS TotalCustQty,
SUM(NZ(t1.Qty)*NZ(t1.Cost)) AS SumExtCost
FROM
tblCustomer AS c1
LEFT JOIN
tblInvoiceDetail AS t1
ON c1.CustomerID = t1.CustomerID
WHERE ((tbl1.InvDate)>=Date()-30)
GROUP BY c1.CustomerName
ORDER BY c1.CustomerName;

////////// tblInventory + tblInvoiceDetail////////////
Getting *all* SKU's from tblInventory
would be similar to above.

SELECT
t2.SKU,
SUM(NZ(t1.Qty)) AS TotalSKUQty,
SUM(NZ(t1.Qty)*NZ(t1.Cost)) AS SumExtCost
FROM
tblInventory AS t2
LEFT JOIN
tblInvoiceDetail AS t1
ON t2.SKU = t1.SKU
GROUP BY t2.SKU
ORDER BY t2.SKU;

or all SKU's last 30 days:

SELECT
t2.SKU,
SUM(NZ(t1.Qty)) AS TotalSKUQty,
SUM(NZ(t1.Qty)*NZ(t1.Cost)) AS SumExtCost
FROM
tblInventory AS t2
LEFT JOIN
tblInvoiceDetail AS t1
ON t2.SKU = t1.SKU
WHERE ((tbl1.InvDate)>=Date()-30)
GROUP BY t2.SKU
ORDER BY t2.SKU;

Well...I've assumed alot which may be
wrong, but I think you might get the general
gist of this.

Please respond back if I have misunderstood
or was not clear about something.
(I will be "away" for a few days though)

Good luck,

Gary Walter
Click to expand...
 
Gary,

Thank for your post. I understand the basics and have the six queries done
for inventory and customer with all of them collected into a master to drive
my report. I thought that perhaps there was a simpler way... I guess not.
Click to expand...
Hi Maureen,

Did you see Duane's response to
"Current Date, MTD, YTD, Last Year's MTD"?

Possibly you could use the same method for

M-last 30 days ((t1.InvDate)>=Date()-30)
Q-last 90 days ((t1.InvDate)>=Date()-90)
X-last 180 days ((t1.InvDate)>=Date()-180)
Y-last 366 days ((t1.InvDate)>=Date()-366)
L-Between day 366 and 732 ((t1.InvDate)>=Date()-732
And (t1.InvDate)<Date() - 365)
Z-Life to date

He used the Abs value of date test multiplied
by Qty and sums that result.

for example:

SELECT
t2.SKU,
SUM(NZ(t1.Qty)) AS Z
SUM(NZ((t1.Qty)*Abs(((t1.InvDate)>=Date()-30))) AS M
{etc., I did not check parentheses above}

FROM
tblInventory AS t2
LEFT JOIN
tblInvoiceDetail AS t1
ON t2.SKU = t1.SKU
GROUP BY t2.SKU
ORDER BY t2.SKU;
 
Gary,

Thanks for your reply. I located a post from Duanes on the topic. It looks
like his suggestion does YTD... I suspect this is a calendar year. What I
want is a 'rolling' sales history for each SKU and Customer that will
provide a comparison of sales for the last 366 day compared to the 366 days
prior rather than YTD.

M
 
Hi Maureen,

I was referring to the "technique,"
not "YTD".

M-last 30 days ((t1.InvDate)>=Date()-30)
Q-last 90 days ((t1.InvDate)>=Date()-90)
X-last 180 days ((t1.InvDate)>=Date()-180)
Y-last 366 days ((t1.InvDate)>=Date()-366)
L-Between day 366 and 732 ((t1.InvDate)>=Date()-732
And (t1.InvDate)<Date() -
365)
Z-Life to date

Look just at the "M" condition

((t1.InvDate)>=Date()-30)

If I am Access and looking at a record in
the query,
if my InvDate is within the last 30 days,
the "M" condition evaluates to -1 (TRUE)
if not,
the "M" condition evaluates to 0 (FALSE)

So...if we take the absolute value of this condition
and multiply it by the Qty for the record Access is
looking at, it returns Qty if within the last 30 days.

Then if we SUM all those returned Qty's, we get your
rolling sales history for Qty within the last 30 days in
one of the fields in the query.

{following does not worry about null}

SUM(t1.Qty*Abs(t1.InvDate>=Date()-30)) AS M,

Basically we've moved the WHERE clause up into
the SUM, so you could do this in separate fields of
your query for all M, Q, X, Y, and L conditions.....
all within the same query.

SUM(t1.Qty*Abs(t1.InvDate>=Date()-90)) AS Q,

SUM(t1.Qty*Abs(t1.InvDate>=Date()-180)) AS X,

SUM(t1.Qty*Abs(t1.InvDate>=Date()-366)) AS Y,

SUM(t1.Qty*Abs(t1.InvDate>=Date()-732 AND t1.InvDate<Date()-365)) AS L,

SUM(t1.Qty) AS Z

I thought it was pretty clever and I believe it is
applicable to your situation. Of course...I could
be wrong.

Good luck,

Gary Walter
 
SELECT [T1].[SKU], Sum([Quantity]*Abs([Invoice Date]>Date()-30)) AS M

FROM [T1] INNER JOIN [T2] ON [T1].[invoice] = [T2].Invoice

GROUP BY [T1].[SKU]

HAVING ((([T1].[SKU])="sample"));

I am getting an error:*** incorrect syntax near '>'

any suggestions?
M
 
"Maureen" wrote
SELECT [T1].[SKU], Sum([Quantity]*Abs([Invoice Date]>Date()-30)) AS M
FROM [T1] INNER JOIN [T2] ON [T1].[invoice] = [T2].Invoice
GROUP BY [T1].[SKU]
HAVING ((([T1].[SKU])="sample"));
I am getting an error:*** incorrect syntax near '>'
Click to expand...

Hi Maureen,

I guess I do not understand your table structure.

Typically, it is considered good form when asking
a question to provide pertinent tables, their structure,
and some sample data (no offense intended).

Sometimes I can provide an example using guessed-at
structure and it is close enough for some people to
adapt to their situation.

Also...sometimes I will help someone do something
which goes counter to what I would be comfortable
with in my own databases.

For instance, if I had a tblInvoiceDetail that contained
fields

InvoiceNum
InvoiceDate
SKU
Quantity
Cost

and a tblInventory that contained
SKU
(one record for every SKU)

I would be comfortable creating a
separate query for each interval based
solely from tblInvoiceDetail

qry30Days

SELECT
t1.SKU,
SUM(t1.Qty) AS TotalSKUQty,
SUM(t1.Qty*t1.Cost) AS SumExtCost
FROM
tblInvoiceDetail AS t1
WHERE ((t1.InvoiceDate)>=Date()-30)
GROUP BY t1.SKU;

qry90Days

SELECT
t1.SKU,
SUM(t1.Qty) AS TotalSKUQty,
SUM(t1.Qty*t1.Cost) AS SumExtCost
FROM
tblInvoiceDetail AS t1
WHERE ((t1.InvoiceDate)>=Date()-90)
GROUP BY t1.SKU;

etc.

Then in a summary query,
LEFT JOIN tblInventory
with each of these interval queries
on SKU.

SELECT
tblInventory.SKU,
NZ(qry30Days.TotalSKUQty) AS 30DayQty,
NZ(qry30Days.SumExtCost) AS 30DayExtCost,
NZ(qry90Days.TotalSKUQty) AS 90DayQty,
NZ(qry90Days.SumExtCost) AS 90DayExtCost,
....etc.
FROM
((tblInventory LEFT JOIN qry30Days
ON tblInventory.SKU = qry30Days.SKU)
LEFT JOIN qry90Days
ON tblInventory.SKU = qry90Days.SKU)
.....etc.
ORDER By SKU;

If the above were how the tables were structured,
that would be how I would do it...and sleep a
"satisfied, restful sleep."

Sometimes there are "more clever" ways,
and sometimes a poster will ask for help
"doing it all in one query"...and sometimes,
against (what I think is) my better judgement,
I will help.

Again, I mean no offense, I just think I would
be remiss if I did not tell you how I would do
it.

Now for the clever, all-in-one-query solution....

In your query

SELECT [T1].[SKU], Sum([Quantity]*Abs([Invoice Date]>Date()-30)) AS M
FROM [T1] INNER JOIN [T2] ON [T1].[invoice] = [T2].Invoice
GROUP BY [T1].[SKU]
HAVING ((([T1].[SKU])="sample"));

I don't know what T1 nor T2 are.

Do you have a "tblInvoice" that you
have to include in order to get an
Invoice Date?

In the db's I work with, SKU is
a distinct number, so I was wrong
apparently there in my guess.

Please provide your pertinent tables,
their structure, and some sample data.


{guessing}

tblInvoice

Invoice Invoice Date
A 7/4/2002
B 9/1/2003
C 1/5/2004
D 4/10/2004
E 5/15/2004

tblInvoiceDetail

Invoice SKU Qty
A 1 5
A 2 6
B 1 4
C 2 9
D 1 3
D 2 1
E 1 8

for today (6/1/2004)

?Date()-30 (M)
5/2/2004 <-- E (Sumof1=8, Sumof2=0)
?Date()-90 (Q)
3/3/2004 <-- E, D (Sumof1=11,Sumof2=1)
?Date()-180 (X)
12/4/2003 <-- E, D, C (Sumof1=11,Sumof2=10)
?Date()-366 (Y)
6/1/2003 <-- E, D, C, B (Sumof1=15,Sumof2=10)
?Date()-732 ?Date()-365 (L)
5/31/2002 6/2/2003 <-- A (Sumof1=5 ,Sumof2=6)


SELECT t2.SKU,
SUM(t2.Qty*Abs(t1.[Invoice Date]>=Date()-30)) AS M,
SUM(t2.Qty*Abs(t1.[Invoice Date]>=Date()-90)) AS Q,
SUM(t2.Qty*Abs(t1.[Invoice Date]>=Date()-180)) AS X,
SUM(t2.Qty*Abs(t1.[Invoice Date]>=Date()-366)) AS Y,
SUM(t2.Qty*Abs(t1.[Invoice Date]>=Date()-732
AND t1.[Invoice Date]<Date()-365)) AS L,
SUM(t2.Qty) AS Z
FROM tblInvoice AS t1
INNER JOIN
tblInvoiceDetail AS t2
ON t1.Invoice = t2.Invoice
GROUP BY t2.SKU;

SKU M Q X Y L Z
1 8 11 11 15 5 20
2 0 1 10 10 6 16

One more time..I meant no offense
in anything I said above.

Good luck,

Gary Walter
 
forgot an "=" in "L"

SUM(t2.Qty*Abs(t1.[Invoice Date]>=Date()-732
AND t1.[Invoice Date]<=Date()-365)) AS L,
 
Back
Top