Running sums

G

grantschneider

What I am looking to do is create a running sum of the [Share Total]
for each [SVP] by Date.


Disregard the many commission columns, they will be irrelevant to the
running sum.


Eventually, I am trying to find out when each SVP hits a certain
amount of shares sold so that they then go into an 'accelerated'
commission. For every month thereafter they receive a higher
commission percentage.




Here is my SQL code:

PARAMETERS [Forms]![SVP]![SVP] Text ( 255 ), [Forms]![SVP]![Month]
Text ( 255 );
SELECT [Master Sales Forecast].Account, [Master Sales
Forecast].Aircraft, [Master Sales Forecast].[Share Total], [Master
Sales Forecast].SVP, IIf([master sales forecast].account="Renewal
+ve",
[Agg]*0.5*Forms!SVP![Renewal Multiple],0) AS Renewal, IIf([master
sales forecast].account="Referral",[Agg]*Forms!SVP![Referral
Multiple],
0) AS Referral, IIf([master sales forecast].account="add-on",
[Agg]*Forms!SVP![Add-on Multiple],0) AS [Add-on], IIf([master sales
forecast].account="Graduate",[Agg]*Forms!SVP![Graduate Multiple],0)
AS
Graduate, IIf([master sales forecast].account="New Sale",[Agg]*Forms!
SVP![New Sale Multiple],0) AS [New Sale], [Master Sales Forecast].VP,
[Share Total]*Forms!SVP![Agg Multiple]*[Points] AS Agg, [graduate]+
[Renewal]+[Referral]+[new sale]+[add-on] AS Total, [Master Sales
Forecast].Month, MonthConversion.Date
FROM ([Master Sales Forecast] INNER JOIN Points ON [Master Sales
Forecast].Aircraft = Points.Aircraft) LEFT JOIN MonthConversion ON
[Master Sales Forecast].Month = MonthConversion.Month
WHERE ((([Master Sales Forecast].Account)<>"card adjustment" And
([Master Sales Forecast].Account)<>"renewal -ve" And ([Master Sales
Forecast].Account)<>"churn" And ([Master Sales
Forecast].Account)<>"upgrade/downgrade" And ([Master Sales
Forecast].Account)<>"share reduction") AND (([Master Sales
Forecast].SVP) Like ([Forms]![SVP]![SVP]) & "*") AND (([Master Sales
Forecast].Month) Like ([Forms]![SVP]![Month] & "*")));
 
J

Jamie Collins

What I am looking to do is create a running sum of the [Share Total]
for each [SVP] by Date.

Disregard the many commission columns, they will be irrelevant to the
running sum.

Eventually, I am trying to find out when each SVP hits a certain
amount of shares sold so that they then go into an 'accelerated'
commission. For every month thereafter they receive a higher
commission percentage.

Here is my SQL code:

PARAMETERS [Forms]![SVP]![SVP] Text ( 255 ), [Forms]![SVP]![Month]
Text ( 255 );
SELECT [Master Sales Forecast].Account, [Master Sales
Forecast].Aircraft, [Master Sales Forecast].[Share Total], [Master
Sales Forecast].SVP, IIf([master sales forecast].account="Renewal
+ve",
[Agg]*0.5*Forms!SVP![Renewal Multiple],0) AS Renewal, IIf([master
sales forecast].account="Referral",[Agg]*Forms!SVP![Referral
Multiple],
0) AS Referral, IIf([master sales forecast].account="add-on",
[Agg]*Forms!SVP![Add-on Multiple],0) AS [Add-on], IIf([master sales
forecast].account="Graduate",[Agg]*Forms!SVP![Graduate Multiple],0)
AS
Graduate, IIf([master sales forecast].account="New Sale",[Agg]*Forms!
SVP![New Sale Multiple],0) AS [New Sale], [Master Sales Forecast].VP,
[Share Total]*Forms!SVP![Agg Multiple]*[Points] AS Agg, [graduate]+
[Renewal]+[Referral]+[new sale]+[add-on] AS Total, [Master Sales
Forecast].Month, MonthConversion.Date
FROM ([Master Sales Forecast] INNER JOIN Points ON [Master Sales
Forecast].Aircraft = Points.Aircraft) LEFT JOIN MonthConversion ON
[Master Sales Forecast].Month = MonthConversion.Month
WHERE ((([Master Sales Forecast].Account)<>"card adjustment" And
([Master Sales Forecast].Account)<>"renewal -ve" And ([Master Sales
Forecast].Account)<>"churn" And ([Master Sales
Forecast].Account)<>"upgrade/downgrade" And ([Master Sales
Forecast].Account)<>"share reduction") AND (([Master Sales
Forecast].SVP) Like ([Forms]![SVP]![SVP]) & "*") AND (([Master Sales
Forecast].Month) Like ([Forms]![SVP]![Month] & "*")));
Click to expand...

I find your SQL a little impenetrable <g>. Have a go with mine :)

Consider the standard trick of using a Calendar table, one row for
each day's date (dt) with columns for year (Y) and month number (M)
respectively (see
http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html):

The following is ANSI-92 Query Mode SQL syntax (http://
office.microsoft.com/en-gb/access/HP030704831033.aspx):

CREATE TABLE Calendar (
dt DATETIME PRIMARY KEY,
Y INTEGER NOT NULL,
M INTEGER NOT NULL
)
;
INSERT INTO Calendar (dt, Y, M)
VALUES (#1990-01-01 00:00:00#, 1990, 1)
;
INSERT INTO Calendar (dt, Y, M)
VALUES (#1990-01-02 00:00:00#, 1990, 1)
;
INSERT INTO Calendar (dt, Y, M)
VALUES (#1990-01-03 00:00:00#, 1990, 1)
;
etc etc until the end of time (say, 2020 <g>).

Here's the bare bones of a sales table:

CREATE TABLE SalesTransactionLog
(
salesman_number INTEGER NOT NULL,
effective_date DATETIME NOT NULL,
CHECK
(
DATEPART('H', effective_date) = 0
AND DATEPART('N', effective_date) = 0
AND DATEPART('S', effective_date) = 0
),
shares_amount INTEGER NOT NULL,
CHECK
(
shares_amount > 0
)
)
;

Some test data:

INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (1, #2007-01-01 00:00:00#, 11)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (1, #2007-01-21 00:00:00#, 22)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (1, #2007-03-03 00:00:00#, 33)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (1, #2007-03-23 00:00:00#, 44)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (1, #2007-05-05 00:00:00#, 55)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (1, #2007-05-25 00:00:00#, 66)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (2, #2007-03-01 00:00:00#, 1)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (2, #2007-03-31 00:00:00#, 2)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (2, #2007-04-01 00:00:00#, 3)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (2, #2007-04-30 00:00:00#, 4)
;

This shows the total sales per salesman per month:

SELECT S1.salesman_number, C1.Y, C1.M,
IIF(SUM(S1.shares_amount) IS NULL, 0, SUM(S1.shares_amount)) AS
shares_amount_this_month
FROM SalesTransactionLog AS S1
INNER JOIN Calendar AS C1
ON S1.effective_date = C1.dt
GROUP BY S1.salesman_number, C1.Y, C1.M

This shows all combinations of months and salesmen (note the BETWEEN
to give reasonable limits to the Calendar table):

SELECT S1.salesman_number, C1.Y, C1.M
FROM SalesTransactionLog AS S1, Calendar AS C1
WHERE C1.dt BETWEEN
(
SELECT MIN(S2.effective_date)
FROM SalesTransactionLog AS S2
) AND
(
SELECT MAX(S2.effective_date)
FROM SalesTransactionLog AS S2
)
GROUP BY S1.salesman_number, C1.Y, C1.M

Put the above two queries together:

CREATE VIEW SalesByMonth (
Y, M, salesman_number, shares_amount_this_month
)
AS
SELECT A1.Y, A1.M, A1.salesman_number, M1.shares_amount_this_month
FROM
(
SELECT S1.salesman_number, C1.Y, C1.M
FROM SalesTransactionLog AS S1, Calendar AS C1
WHERE C1.dt BETWEEN
(
SELECT MIN(S2.effective_date)
FROM SalesTransactionLog AS S2
) AND
(
SELECT MAX(S2.effective_date)
FROM SalesTransactionLog AS S2
)
GROUP BY S1.salesman_number, C1.Y, C1.M
) AS A1
LEFT JOIN
(
SELECT S1.salesman_number, C1.Y, C1.M,
IIF(SUM(S1.shares_amount) IS NULL, 0, SUM(S1.shares_amount)) AS
shares_amount_this_month
FROM SalesTransactionLog AS S1
INNER JOIN Calendar AS C1
ON S1.effective_date = C1.dt
GROUP BY S1.salesman_number, C1.Y, C1.M
) AS M1
ON A1.salesman_number = M1.salesman_number
AND A1.Y = M1.Y
AND A1.M = M1.M
GROUP BY A1.Y, A1.M, A1.salesman_number, M1.shares_amount_this_month;

Now a simple self-join to find the cumulative sales:

SELECT T2.Y, T2.M, T2.salesman_number,
SUM(T1.shares_amount_this_month) AS
shares_amount_cumulative_to_this_month
FROM SalesByMonth AS T1
INNER JOIN SalesByMonth AS T2
ON T1.salesman_number = T2.salesman_number
AND T1.Y <= T2.Y
AND T1.M <= T2.M
GROUP BY T2.Y, T2.M, T2.salesman_number
ORDER BY T2.salesman_number, T2.Y, T2.M;

HTH.

Jamie.

--
 
G

grantschneider

What I am looking to do is create a running sum of the [Share Total]
for each [SVP] by Date.
Click to expand...
Disregard the many commission columns, they will be irrelevant to the
running sum.
Click to expand...
Eventually, I am trying to find out when each SVP hits a certain
amount of shares sold so that they then go into an 'accelerated'
commission. For every month thereafter they receive a higher
commission percentage.
Click to expand...
Here is my SQL code:
Click to expand...
PARAMETERS [Forms]![SVP]![SVP] Text ( 255 ), [Forms]![SVP]![Month]
Text ( 255 );
SELECT [Master Sales Forecast].Account, [Master Sales
Forecast].Aircraft, [Master Sales Forecast].[Share Total], [Master
Sales Forecast].SVP, IIf([master sales forecast].account="Renewal
+ve",
[Agg]*0.5*Forms!SVP![Renewal Multiple],0) AS Renewal, IIf([master
sales forecast].account="Referral",[Agg]*Forms!SVP![Referral
Multiple],
0) AS Referral, IIf([master sales forecast].account="add-on",
[Agg]*Forms!SVP![Add-on Multiple],0) AS [Add-on], IIf([master sales
forecast].account="Graduate",[Agg]*Forms!SVP![Graduate Multiple],0)
AS
Graduate, IIf([master sales forecast].account="New Sale",[Agg]*Forms!
SVP![New Sale Multiple],0) AS [New Sale], [Master Sales Forecast].VP,
[Share Total]*Forms!SVP![Agg Multiple]*[Points] AS Agg, [graduate]+
[Renewal]+[Referral]+[new sale]+[add-on] AS Total, [Master Sales
Forecast].Month, MonthConversion.Date
FROM ([Master Sales Forecast] INNER JOIN Points ON [Master Sales
Forecast].Aircraft = Points.Aircraft) LEFT JOIN MonthConversion ON
[Master Sales Forecast].Month = MonthConversion.Month
WHERE ((([Master Sales Forecast].Account)<>"card adjustment" And
([Master Sales Forecast].Account)<>"renewal -ve" And ([Master Sales
Forecast].Account)<>"churn" And ([Master Sales
Forecast].Account)<>"upgrade/downgrade" And ([Master Sales
Forecast].Account)<>"share reduction") AND (([Master Sales
Forecast].SVP) Like ([Forms]![SVP]![SVP]) & "*") AND (([Master Sales
Forecast].Month) Like ([Forms]![SVP]![Month] & "*")));
Click to expand...

I find your SQL a little impenetrable <g>. Have a go with mine :)

Consider the standard trick of using a Calendar table, one row for
each day's date (dt) with columns for year (Y) and month number (M)
respectively (seehttp://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using...

The following is ANSI-92 Query Mode SQL syntax (http://
office.microsoft.com/en-gb/access/HP030704831033.aspx):

CREATE TABLE Calendar (
dt DATETIME PRIMARY KEY,
Y INTEGER NOT NULL,
M INTEGER NOT NULL
)
;
INSERT INTO Calendar (dt, Y, M)
VALUES (#1990-01-01 00:00:00#, 1990, 1)
;
INSERT INTO Calendar (dt, Y, M)
VALUES (#1990-01-02 00:00:00#, 1990, 1)
;
INSERT INTO Calendar (dt, Y, M)
VALUES (#1990-01-03 00:00:00#, 1990, 1)
;
etc etc until the end of time (say, 2020 <g>).

Here's the bare bones of a sales table:

CREATE TABLE SalesTransactionLog
(
salesman_number INTEGER NOT NULL,
effective_date DATETIME NOT NULL,
CHECK
(
DATEPART('H', effective_date) = 0
AND DATEPART('N', effective_date) = 0
AND DATEPART('S', effective_date) = 0
),
shares_amount INTEGER NOT NULL,
CHECK
(
shares_amount > 0
)
)
;

Some test data:

INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (1, #2007-01-01 00:00:00#, 11)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (1, #2007-01-21 00:00:00#, 22)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (1, #2007-03-03 00:00:00#, 33)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (1, #2007-03-23 00:00:00#, 44)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (1, #2007-05-05 00:00:00#, 55)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (1, #2007-05-25 00:00:00#, 66)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (2, #2007-03-01 00:00:00#, 1)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (2, #2007-03-31 00:00:00#, 2)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (2, #2007-04-01 00:00:00#, 3)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (2, #2007-04-30 00:00:00#, 4)
;

This shows the total sales per salesman per month:

SELECT S1.salesman_number, C1.Y, C1.M,
IIF(SUM(S1.shares_amount) IS NULL, 0, SUM(S1.shares_amount)) AS
shares_amount_this_month
FROM SalesTransactionLog AS S1
INNER JOIN Calendar AS C1
ON S1.effective_date = C1.dt
GROUP BY S1.salesman_number, C1.Y, C1.M

This shows all combinations of months and salesmen (note the BETWEEN
to give reasonable limits to the Calendar table):

SELECT S1.salesman_number, C1.Y, C1.M
FROM SalesTransactionLog AS S1, Calendar AS C1
WHERE C1.dt BETWEEN
(
SELECT MIN(S2.effective_date)
FROM SalesTransactionLog AS S2
) AND
(
SELECT MAX(S2.effective_date)
FROM SalesTransactionLog AS S2
)
GROUP BY S1.salesman_number, C1.Y, C1.M

Put the above two queries together:

CREATE VIEW SalesByMonth (
Y, M, salesman_number, shares_amount_this_month
)
AS
SELECT A1.Y, A1.M, A1.salesman_number, M1.shares_amount_this_month
FROM
(
SELECT S1.salesman_number, C1.Y, C1.M
FROM SalesTransactionLog AS S1, Calendar AS C1
WHERE C1.dt BETWEEN
(
SELECT MIN(S2.effective_date)
FROM SalesTransactionLog AS S2
) AND
(
SELECT MAX(S2.effective_date)
FROM SalesTransactionLog AS S2
)
GROUP BY S1.salesman_number, C1.Y, C1.M
) AS A1
LEFT JOIN
(
SELECT S1.salesman_number, C1.Y, C1.M,
IIF(SUM(S1.shares_amount) IS NULL, 0, SUM(S1.shares_amount)) AS
shares_amount_this_month
FROM SalesTransactionLog AS S1
INNER JOIN Calendar AS C1
ON S1.effective_date = C1.dt
GROUP BY S1.salesman_number, C1.Y, C1.M
) AS M1
ON A1.salesman_number = M1.salesman_number
AND A1.Y = M1.Y
AND A1.M = M1.M
GROUP BY A1.Y, A1.M, A1.salesman_number, M1.shares_amount_this_month;

Now a simple self-join to find the cumulative sales:

SELECT T2.Y, T2.M, T2.salesman_number,
SUM(T1.shares_amount_this_month) AS
shares_amount_cumulative_to_this_month
FROM SalesByMonth AS T1
INNER JOIN SalesByMonth AS T2
ON T1.salesman_number = T2.salesman_number
AND T1.Y <= T2.Y
AND T1.M <= T2.M
GROUP BY T2.Y, T2.M, T2.salesman_number
ORDER BY T2.salesman_number, T2.Y, T2.M;

HTH.

Jamie.

--- Hide quoted text -

- Show quoted text -
Click to expand...

Wow. Thanks.
 
G

grantschneider

What I am looking to do is create a running sum of the [Share Total]
for each [SVP] by Date.
Click to expand...
Disregard the many commission columns, they will be irrelevant to the
running sum.
Click to expand...
Eventually, I am trying to find out when each SVP hits a certain
amount of shares sold so that they then go into an 'accelerated'
commission. For every month thereafter they receive a higher
commission percentage.
Click to expand...
Here is my SQL code:
Click to expand...
PARAMETERS [Forms]![SVP]![SVP] Text ( 255 ), [Forms]![SVP]![Month]
Text ( 255 );
SELECT [Master Sales Forecast].Account, [Master Sales
Forecast].Aircraft, [Master Sales Forecast].[Share Total], [Master
Sales Forecast].SVP, IIf([master sales forecast].account="Renewal
+ve",
[Agg]*0.5*Forms!SVP![Renewal Multiple],0) AS Renewal, IIf([master
sales forecast].account="Referral",[Agg]*Forms!SVP![Referral
Multiple],
0) AS Referral, IIf([master sales forecast].account="add-on",
[Agg]*Forms!SVP![Add-on Multiple],0) AS [Add-on], IIf([master sales
forecast].account="Graduate",[Agg]*Forms!SVP![Graduate Multiple],0)
AS
Graduate, IIf([master sales forecast].account="New Sale",[Agg]*Forms!
SVP![New Sale Multiple],0) AS [New Sale], [Master Sales Forecast].VP,
[Share Total]*Forms!SVP![Agg Multiple]*[Points] AS Agg, [graduate]+
[Renewal]+[Referral]+[new sale]+[add-on] AS Total, [Master Sales
Forecast].Month, MonthConversion.Date
FROM ([Master Sales Forecast] INNER JOIN Points ON [Master Sales
Forecast].Aircraft = Points.Aircraft) LEFT JOIN MonthConversion ON
[Master Sales Forecast].Month = MonthConversion.Month
WHERE ((([Master Sales Forecast].Account)<>"card adjustment" And
([Master Sales Forecast].Account)<>"renewal -ve" And ([Master Sales
Forecast].Account)<>"churn" And ([Master Sales
Forecast].Account)<>"upgrade/downgrade" And ([Master Sales
Forecast].Account)<>"share reduction") AND (([Master Sales
Forecast].SVP) Like ([Forms]![SVP]![SVP]) & "*") AND (([Master Sales
Forecast].Month) Like ([Forms]![SVP]![Month] & "*")));
Click to expand...

I find your SQL a little impenetrable <g>. Have a go with mine :)

Consider the standard trick of using a Calendar table, one row for
each day's date (dt) with columns for year (Y) and month number (M)
respectively (seehttp://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using...

The following is ANSI-92 Query Mode SQL syntax (http://
office.microsoft.com/en-gb/access/HP030704831033.aspx):

CREATE TABLE Calendar (
dt DATETIME PRIMARY KEY,
Y INTEGER NOT NULL,
M INTEGER NOT NULL
)
;
INSERT INTO Calendar (dt, Y, M)
VALUES (#1990-01-01 00:00:00#, 1990, 1)
;
INSERT INTO Calendar (dt, Y, M)
VALUES (#1990-01-02 00:00:00#, 1990, 1)
;
INSERT INTO Calendar (dt, Y, M)
VALUES (#1990-01-03 00:00:00#, 1990, 1)
;
etc etc until the end of time (say, 2020 <g>).

Here's the bare bones of a sales table:

CREATE TABLE SalesTransactionLog
(
salesman_number INTEGER NOT NULL,
effective_date DATETIME NOT NULL,
CHECK
(
DATEPART('H', effective_date) = 0
AND DATEPART('N', effective_date) = 0
AND DATEPART('S', effective_date) = 0
),
shares_amount INTEGER NOT NULL,
CHECK
(
shares_amount > 0
)
)
;

Some test data:

INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (1, #2007-01-01 00:00:00#, 11)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (1, #2007-01-21 00:00:00#, 22)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (1, #2007-03-03 00:00:00#, 33)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (1, #2007-03-23 00:00:00#, 44)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (1, #2007-05-05 00:00:00#, 55)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (1, #2007-05-25 00:00:00#, 66)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (2, #2007-03-01 00:00:00#, 1)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (2, #2007-03-31 00:00:00#, 2)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (2, #2007-04-01 00:00:00#, 3)
;
INSERT INTO SalesTransactionLog (salesman_number, effective_date,
shares_amount)
VALUES (2, #2007-04-30 00:00:00#, 4)
;

This shows the total sales per salesman per month:

SELECT S1.salesman_number, C1.Y, C1.M,
IIF(SUM(S1.shares_amount) IS NULL, 0, SUM(S1.shares_amount)) AS
shares_amount_this_month
FROM SalesTransactionLog AS S1
INNER JOIN Calendar AS C1
ON S1.effective_date = C1.dt
GROUP BY S1.salesman_number, C1.Y, C1.M

This shows all combinations of months and salesmen (note the BETWEEN
to give reasonable limits to the Calendar table):

SELECT S1.salesman_number, C1.Y, C1.M
FROM SalesTransactionLog AS S1, Calendar AS C1
WHERE C1.dt BETWEEN
(
SELECT MIN(S2.effective_date)
FROM SalesTransactionLog AS S2
) AND
(
SELECT MAX(S2.effective_date)
FROM SalesTransactionLog AS S2
)
GROUP BY S1.salesman_number, C1.Y, C1.M

Put the above two queries together:

CREATE VIEW SalesByMonth (
Y, M, salesman_number, shares_amount_this_month
)
AS
SELECT A1.Y, A1.M, A1.salesman_number, M1.shares_amount_this_month
FROM
(
SELECT S1.salesman_number, C1.Y, C1.M
FROM SalesTransactionLog AS S1, Calendar AS C1
WHERE C1.dt BETWEEN
(
SELECT MIN(S2.effective_date)
FROM SalesTransactionLog AS S2
) AND
(
SELECT MAX(S2.effective_date)
FROM SalesTransactionLog AS S2
)
GROUP BY S1.salesman_number, C1.Y, C1.M
) AS A1
LEFT JOIN
(
SELECT S1.salesman_number, C1.Y, C1.M,
IIF(SUM(S1.shares_amount) IS NULL, 0, SUM(S1.shares_amount)) AS
shares_amount_this_month
FROM SalesTransactionLog AS S1
INNER JOIN Calendar AS C1
ON S1.effective_date = C1.dt
GROUP BY S1.salesman_number, C1.Y, C1.M
) AS M1
ON A1.salesman_number = M1.salesman_number
AND A1.Y = M1.Y
AND A1.M = M1.M
GROUP BY A1.Y, A1.M, A1.salesman_number, M1.shares_amount_this_month;

Now a simple self-join to find the cumulative sales:

SELECT T2.Y, T2.M, T2.salesman_number,
SUM(T1.shares_amount_this_month) AS
shares_amount_cumulative_to_this_month
FROM SalesByMonth AS T1
INNER JOIN SalesByMonth AS T2
ON T1.salesman_number = T2.salesman_number
AND T1.Y <= T2.Y
AND T1.M <= T2.M
GROUP BY T2.Y, T2.M, T2.salesman_number
ORDER BY T2.salesman_number, T2.Y, T2.M;

HTH.

Jamie.

--- Hide quoted text -

- Show quoted text -
Click to expand...

I am having serious trouble getting this to work in Access 2003. I
cant figure out where to begin.
 
G

grantschneider

This should make it a lot simpler:

I am trying to create a running sum of [Share Total] per [SVP] by
[DATE].

Here is the Code:

SELECT [Master Sales Forecast].SVP, [Master Sales Forecast].[Share
Total], [Master Sales Forecast].Month, MonthConversion.Date
FROM [Master Sales Forecast] INNER JOIN MonthConversion ON [Master
Sales Forecast].Month = MonthConversion.Month;
 
J

John Spencer

Assumptions:
SVP is a text field
Date is a datetime field (by the way date is a bad choice for a field name,
since Date is a function that returns the current date)

Given that the following might work.
SELECT [Master Sales Forecast].SVP
, [Master Sales Forecast].[Share Total]
, [Master Sales Forecast].Month
, MonthConversion.Date
, DSUM("[Share Total","[Master Sales Forecast]","SVP=""" & SVP & """ AND
[Date] <=#" & [Date] & "#") as RunningSum
FROM [Master Sales Forecast] INNER JOIN MonthConversion ON [Master
Sales Forecast].Month = MonthConversion.Month;

Personally I would use a subquery.
SELECT [Master Sales Forecast].SVP
, [Master Sales Forecast].[Share Total]
, [Master Sales Forecast].Month
, MonthConversion.Date
, (Select Sum([Share Total)
FROM [Master Sales Forecast] as Tmp
WHERE Tmp.SVP=[Master Sales Forecast].SVP
And Tmp.[Date] <=[Master Sales Forecast].[Date]) as RunningSum
FROM [Master Sales Forecast] INNER JOIN
MonthConversion ON [Master Sales Forecast].Month = MonthConversion.Month;
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
B

BruceM

Would the Date (and Month) fields need to be surrounded by square brackets
(since they are reserved words) in all cases, or just in some?

John Spencer said:
Assumptions:
SVP is a text field
Date is a datetime field (by the way date is a bad choice for a field
name, since Date is a function that returns the current date)

Given that the following might work.
SELECT [Master Sales Forecast].SVP
, [Master Sales Forecast].[Share Total]
, [Master Sales Forecast].Month
, MonthConversion.Date
, DSUM("[Share Total","[Master Sales Forecast]","SVP=""" & SVP & """ AND
[Date] <=#" & [Date] & "#") as RunningSum
FROM [Master Sales Forecast] INNER JOIN MonthConversion ON [Master
Sales Forecast].Month = MonthConversion.Month;

Personally I would use a subquery.
SELECT [Master Sales Forecast].SVP
, [Master Sales Forecast].[Share Total]
, [Master Sales Forecast].Month
, MonthConversion.Date
, (Select Sum([Share Total)
FROM [Master Sales Forecast] as Tmp
WHERE Tmp.SVP=[Master Sales Forecast].SVP
And Tmp.[Date] <=[Master Sales Forecast].[Date]) as RunningSum
FROM [Master Sales Forecast] INNER JOIN
MonthConversion ON [Master Sales Forecast].Month = MonthConversion.Month;
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

This should make it a lot simpler:

I am trying to create a running sum of [Share Total] per [SVP] by
[DATE].

Here is the Code:

SELECT [Master Sales Forecast].SVP, [Master Sales Forecast].[Share
Total], [Master Sales Forecast].Month, MonthConversion.Date
FROM [Master Sales Forecast] INNER JOIN MonthConversion ON [Master
Sales Forecast].Month = MonthConversion.Month;
Click to expand...
Click to expand...
 
J

Jamie Collins

The following is ANSI-92 Query Mode SQL syntax (http://
office.microsoft.com/en-gb/access/HP030704831033.aspx):
[snipped]
Click to expand...

I am having serious trouble getting this to work in Access 2003. I
cant figure out where to begin.
Click to expand...

Try running this VBA. It should create a new mdb file in your Temp
folder, with all the tables and data:

Sub Salesmen()
' Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
..Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
..Execute _
"CREATE TABLE Calendar ( dt DATETIME NOT" & _
" NULL CONSTRAINT pk__Calendar PRIMARY KEY," & _
" Y INTEGER, M INTEGER);"
..Execute _
"INSERT INTO Calendar (dt) VALUES" & _
" (#1990-01-01 00:00:00#);"

Dim sql
sql = _
"INSERT INTO Calendar (dt) SELECT CDATE(Units.nbr" & _
" + Tens.nbr + Hundreds.nbr + Thousands.nbr" & _
" + TenThousands.nbr) AS dt FROM (SELECT" & _
" nbr FROM (SELECT 0 AS nbr FROM Calendar" & _
" UNION ALL SELECT 1 FROM Calendar UNION" & _
" ALL SELECT 2 FROM Calendar UNION ALL SELECT" & _
" 3 FROM Calendar UNION ALL SELECT 4 FROM" & _
" Calendar UNION ALL SELECT 5 FROM Calendar" & _
" UNION ALL SELECT 6 FROM Calendar UNION" & _
" ALL SELECT 7 FROM Calendar UNION ALL SELECT" & _
" 8 FROM Calendar UNION ALL SELECT 9 FROM" & _
" Calendar) AS Digits) AS Units, (SELECT" & _
" nbr * 10 AS nbr FROM (SELECT 0 AS nbr" & _
" FROM Calendar UNION ALL SELECT 1 FROM Calendar" & _
" UNION ALL SELECT 2 FROM Calendar UNION" & _
" ALL SELECT 3 FROM Calendar UNION ALL SELECT" & _
" 4 FROM Calendar UNION ALL SELECT 5 FROM" & _
" Calendar UNION ALL SELECT 6 FROM Calendar" & _
" UNION ALL SELECT 7 FROM Calendar UNION" & _
" ALL SELECT 8 FROM Calendar UNION ALL SELECT" & _
" 9 FROM Calendar) AS Digits) AS Tens," & _
" (SELECT nbr * 100 AS nbr FROM (SELECT" & _
" 0 AS nbr FROM Calendar UNION ALL SELECT"
sql = sql & _
" 1 FROM Calendar UNION ALL SELECT 2 FROM" & _
" Calendar UNION ALL SELECT 3 FROM Calendar" & _
" UNION ALL SELECT 4 FROM Calendar UNION" & _
" ALL SELECT 5 FROM Calendar UNION ALL SELECT" & _
" 6 FROM Calendar UNION ALL SELECT 7 FROM" & _
" Calendar UNION ALL SELECT 8 FROM Calendar" & _
" UNION ALL SELECT 9 FROM Calendar) AS Digits" & _
") AS Hundreds, (SELECT nbr * 1000 AS nbr" & _
" FROM (SELECT 0 AS nbr FROM Calendar UNION" & _
" ALL SELECT 1 FROM Calendar UNION ALL SELECT" & _
" 2 FROM Calendar UNION ALL SELECT 3 FROM" & _
" Calendar UNION ALL SELECT 4 FROM Calendar" & _
" UNION ALL SELECT 5 FROM Calendar UNION" & _
" ALL SELECT 6 FROM Calendar UNION ALL SELECT" & _
" 7 FROM Calendar UNION ALL SELECT 8 FROM" & _
" Calendar UNION ALL SELECT 9 FROM Calendar" & _
") AS Digits) AS Thousands, (SELECT nbr" & _
" * 10000 AS nbr FROM (SELECT 0 AS nbr FROM" & _
" Calendar UNION ALL SELECT 1 FROM Calendar" & _
" UNION ALL SELECT 2 FROM Calendar UNION" & _
" ALL SELECT 3 FROM Calendar UNION ALL SELECT" & _
" 4 FROM Calendar UNION ALL SELECT 5 FROM" & _
" Calendar UNION ALL SELECT 6 FROM Calendar" & _
" UNION ALL SELECT 7 FROM Calendar UNION"
sql = sql & _
" ALL SELECT 8 FROM Calendar UNION ALL SELECT" & _
" 9 FROM Calendar) AS Digits) AS TenThousands" & _
" WHERE Units.nbr + Tens.nbr + Hundreds.nbr" & _
" + Thousands.nbr + TenThousands.nbr BETWEEN" & _
" CLNG(DATESERIAL(1990, 1, 2)) AND" & _
" CLNG(DATESERIAL(2020, 12, 31));"
..Execute sql

sql = _
"UPDATE Calendar SET Y = DATEPART('YYYY', dt)," & _
" M = DATEPART('M', dt);"
..Execute sql

sql = _
"CREATE TABLE SalesTransactionLog" & vbCr & "(" & vbCr & "" & _
" salesman_number INTEGER NOT" & _
" NULL," & vbCr & " effective_date DATETIME" & _
" NOT NULL," & vbCr & " CHECK" & vbCr & " (" & vbCr & " " & _
" DATEPART('H', effective_date)" & _
" = 0" & vbCr & " AND DATEPART('N', effective_date)" & _
" = 0" & vbCr & " AND DATEPART('S', effective_date)" & _
" = 0" & vbCr & " )," & vbCr & " shares_amount INTEGER" & _
" NOT NULL," & vbCr & " CHECK" & vbCr & " (" & vbCr & " " & _
" shares_amount > 0" & vbCr & " )" & vbCr & ")" & vbCr & ";"
..Execute sql
sql = _
"INSERT INTO SalesTransactionLog" & _
" (salesman_number, effective_date," & vbCr & "shares_amount)" & vbCr
& "" & _
" VALUES (1, #2007-01-01 00:00:00#," & _
" 11)" & vbCr & ";"
..Execute sql
sql = _
"INSERT INTO SalesTransactionLog" & _
" (salesman_number, effective_date," & vbCr & "shares_amount)" & vbCr
& "" & _
" VALUES (1, #2007-01-21 00:00:00#," & _
" 22)" & vbCr & ";"
..Execute sql
sql = _
"INSERT INTO SalesTransactionLog" & _
" (salesman_number, effective_date," & vbCr & "shares_amount)" & vbCr
& "" & _
" VALUES (1, #2007-03-03 00:00:00#," & _
" 33)" & vbCr & ";"
..Execute sql
sql = _
"INSERT INTO SalesTransactionLog" & _
" (salesman_number, effective_date," & vbCr & "shares_amount)" & vbCr
& "" & _
" VALUES (1, #2007-03-23 00:00:00#," & _
" 44)" & vbCr & ";"
..Execute sql
sql = _
"INSERT INTO SalesTransactionLog" & _
" (salesman_number, effective_date," & vbCr & "shares_amount)" & vbCr
& "" & _
" VALUES (1, #2007-05-05 00:00:00#," & _
" 55)" & vbCr & ";"
..Execute sql
sql = _
"INSERT INTO SalesTransactionLog" & _
" (salesman_number, effective_date," & vbCr & "shares_amount)" & vbCr
& "" & _
" VALUES (1, #2007-05-25 00:00:00#," & _
" 66)" & vbCr & ";"
..Execute sql
sql = _
"INSERT INTO SalesTransactionLog" & _
" (salesman_number, effective_date," & vbCr & "shares_amount)" & vbCr
& "" & _
" VALUES (2, #2007-03-01 00:00:00#," & _
" 1)" & vbCr & ";"
..Execute sql
sql = _
"INSERT INTO SalesTransactionLog" & _
" (salesman_number, effective_date," & vbCr & "shares_amount)" & vbCr
& "" & _
" VALUES (2, #2007-03-31 00:00:00#," & _
" 2)" & vbCr & ";"
..Execute sql
sql = _
"INSERT INTO SalesTransactionLog" & _
" (salesman_number, effective_date," & vbCr & "shares_amount)" & vbCr
& "" & _
" VALUES (2, #2007-04-01 00:00:00#," & _
" 3)" & vbCr & ";"
..Execute sql
sql = _
"INSERT INTO SalesTransactionLog" & _
" (salesman_number, effective_date," & vbCr & "shares_amount)" & vbCr
& "" & _
" VALUES (2, #2007-04-30 00:00:00#," & _
" 4)" & vbCr & ";"
..Execute sql
sql = _
"CREATE VIEW SalesByMonth (" & vbCr & " " & _
" Y, M, salesman_number, shares_amount_this_month" & vbCr & ")" & vbCr
& "AS" & vbCr & "SELECT" & _
" A1.Y, A1.M, A1.salesman_number," & _
" M1.shares_amount_this_month" & vbCr & "FROM" & vbCr & "(" & vbCr &
"" & _
" SELECT S1.salesman_number," & _
" C1.Y, C1.M" & vbCr & " FROM SalesTransactionLog" & _
" AS S1, Calendar AS C1" & vbCr & " WHERE" & _
" C1.dt BETWEEN" & vbCr & " (" & vbCr & " SELECT" & _
" MIN(S2.effective_date)" & vbCr & " " & _
" FROM SalesTransactionLog AS S2" & vbCr & "" & _
" ) AND" & vbCr & " (" & vbCr & " SELECT
MAX(S2.effective_date)" & vbCr & "" & _
" FROM SalesTransactionLog" & _
" AS S2" & vbCr & " )" & vbCr & " GROUP BY S1.salesman_number," &
_
" C1.Y, C1.M" & vbCr & ") AS A1" & vbCr & "LEFT JOIN" & vbCr & "(" &
vbCr & "" & _
" SELECT S1.salesman_number," & _
" C1.Y, C1.M," & vbCr & " IIF(SUM(S1.shares_amount)" & _
" IS NULL, 0, SUM(S1.shares_amount))" & _
" AS" & vbCr & "shares_amount_this_month" & vbCr & "" & _
" FROM SalesTransactionLog AS" & _
" S1" & vbCr & " INNER JOIN Calendar AS"
sql = sql & _
" C1" & vbCr & " ON S1.effective_date =" & _
" C1.dt" & vbCr & " GROUP BY S1.salesman_number," & _
" C1.Y, C1.M" & vbCr & ") AS M1" & vbCr & "ON A1.salesman_number" & _
" = M1.salesman_number" & vbCr & "AND A1.Y" & _
" = M1.Y" & vbCr & "AND A1.M = M1.M" & vbCr & "GROUP" & _
" BY A1.Y, A1.M, A1.salesman_number," & _
" M1.shares_amount_this_month;"
..Execute sql

sql = _
"SELECT DISTINCT ' Y', ' M', ' salesman_number','
shares_amount_this_month'" & _
" FROM SalesByMonth UNION ALL" & _
" SELECT T2.Y, T2.M, T2.salesman_number," & vbCr &
"SUM(T1.shares_amount_this_month)" & _
" AS" & vbCr & "shares_amount_cumulative_to_this_month" & vbCr &
"FROM" & _
" SalesByMonth AS T1" & vbCr & "INNER JOIN" & _
" SalesByMonth AS T2" & vbCr & "ON T1.salesman_number" & _
" = T2.salesman_number" & vbCr & "AND T1.Y" & _
" <= T2.Y" & vbCr & "AND T1.M <= T2.M" & vbCr & "GROUP" & _
" BY T2.Y, T2.M, T2.salesman_number" & vbCr & "ORDER" & _
" BY 3, 1, 2;"

Dim rs
Set rs = .Execute(sql)
MsgBox rs.GetString
End With
Set .ActiveConnection = Nothing
End With
End Sub
 
J

Jamie Collins

Would the Date (and Month) fields need to be surrounded by square brackets
(since they are reserved words) in all cases, or just in some?
Click to expand...

For example,

SELECT Date FROM Test;

works fine but

SELECT Date AS Date FROM Test;

fails with a 'reserved word' error. Non-intuitive, if you ask me, but
that's the way it is. So, rather than discover the cases where not
escaping a reserved word works, why not simply always escape them
(better still, avoid them <g>)?

Jamie.

--
 
G

grantschneider

Assumptions:
SVP is a text field
Date is a datetime field (by the way date is a bad choice for a field name,
since Date is a function that returns the current date)

Given that the following might work.
SELECT [Master Sales Forecast].SVP
, [Master Sales Forecast].[Share Total]
, [Master Sales Forecast].Month
, MonthConversion.Date
, DSUM("[Share Total","[Master Sales Forecast]","SVP=""" & SVP & """ AND
[Date] <=#" & [Date] & "#") as RunningSum
FROM [Master Sales Forecast] INNER JOIN MonthConversion ON [Master
Sales Forecast].Month = MonthConversion.Month;

Personally I would use a subquery.
SELECT [Master Sales Forecast].SVP
, [Master Sales Forecast].[Share Total]
, [Master Sales Forecast].Month
, MonthConversion.Date
, (Select Sum([Share Total)
FROM [Master Sales Forecast] as Tmp
WHERE Tmp.SVP=[Master Sales Forecast].SVP
And Tmp.[Date] <=[Master Sales Forecast].[Date]) as RunningSum
FROM [Master Sales Forecast] INNER JOIN
MonthConversion ON [Master Sales Forecast].Month = MonthConversion.Month;
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.




This should make it a lot simpler:
Click to expand...
I am trying to create a running sum of [Share Total] per [SVP] by
[DATE].
Click to expand...
Here is the Code:
Click to expand...
SELECT [Master Sales Forecast].SVP, [Master Sales Forecast].[Share
Total], [Master Sales Forecast].Month, MonthConversion.Date
FROM [Master Sales Forecast] INNER JOIN MonthConversion ON [Master
Sales Forecast].Month = MonthConversion.Month;- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

I tried the subquery but it just gives me the sum totals per SVP
without a running sum
 
J

John Spencer

Whoops. Your Date field is in MonthConversion and not in Master Sales
Forecast. PERHAPS the following modification will work.

SELECT [Master Sales Forecast].SVP
, [Master Sales Forecast].[Share Total]
, [Master Sales Forecast].Month
, MonthConversion.Date

, (Select Sum([Share Total)
FROM [Master Sales Forecast] as Tmp
INNER JOIN MonthConversion as M
ON Tmp.Month = M.Month
WHERE Tmp.SVP=[Master Sales Forecast].SVP
And M.[Date] <=[MonthConversion].[Date]) as RunningSum

FROM [Master Sales Forecast] INNER JOIN MonthConversion
ON [Master Sales Forecast].Month = MonthConversion.Month;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Assumptions:
SVP is a text field
Date is a datetime field (by the way date is a bad choice for a field
name,
since Date is a function that returns the current date)
Click to expand...
SNIP
Personally I would use a subquery.
SELECT [Master Sales Forecast].SVP
, [Master Sales Forecast].[Share Total]
, [Master Sales Forecast].Month
, MonthConversion.Date
, (Select Sum([Share Total)
FROM [Master Sales Forecast] as Tmp
WHERE Tmp.SVP=[Master Sales Forecast].SVP
And Tmp.[Date] <=[Master Sales Forecast].[Date]) as
RunningSum
FROM [Master Sales Forecast] INNER JOIN
MonthConversion ON [Master Sales Forecast].Month = MonthConversion.Month;
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

This should make it a lot simpler:
Click to expand...
I am trying to create a running sum of [Share Total] per [SVP] by
[DATE].
Click to expand...
Here is the Code:
Click to expand...
SELECT [Master Sales Forecast].SVP, [Master Sales Forecast].[Share
Total], [Master Sales Forecast].Month, MonthConversion.Date
FROM [Master Sales Forecast] INNER JOIN MonthConversion ON [Master
Sales Forecast].Month = MonthConversion.Month;- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

I tried the subquery but it just gives me the sum totals per SVP
without a running sum
Click to expand...
 
G

grantschneider

Whoops. Your Date field is in MonthConversion and not in Master Sales
Forecast. PERHAPS the following modification will work.

SELECT [Master Sales Forecast].SVP
, [Master Sales Forecast].[Share Total]
, [Master Sales Forecast].Month
, MonthConversion.Date

, (Select Sum([Share Total)
FROM [Master Sales Forecast] as Tmp
INNER JOIN MonthConversion as M
ON Tmp.Month = M.Month
WHERE Tmp.SVP=[Master Sales Forecast].SVP
And M.[Date] <=[MonthConversion].[Date]) as RunningSum

FROM [Master Sales Forecast] INNER JOIN MonthConversion
ON [Master Sales Forecast].Month = MonthConversion.Month;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.


Assumptions:
SVP is a text field
Date is a datetime field (by the way date is a bad choice for a field
name,
since Date is a function that returns the current date)
Click to expand...
SNIP


Personally I would use a subquery.
SELECT [Master Sales Forecast].SVP
, [Master Sales Forecast].[Share Total]
, [Master Sales Forecast].Month
, MonthConversion.Date
, (Select Sum([Share Total)
FROM [Master Sales Forecast] as Tmp
WHERE Tmp.SVP=[Master Sales Forecast].SVP
And Tmp.[Date] <=[Master Sales Forecast].[Date]) as
RunningSum
FROM [Master Sales Forecast] INNER JOIN
MonthConversion ON [Master Sales Forecast].Month = MonthConversion.Month;
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.
This should make it a lot simpler:
I am trying to create a running sum of [Share Total] per [SVP] by
[DATE].
Here is the Code:
SELECT [Master Sales Forecast].SVP, [Master Sales Forecast].[Share
Total], [Master Sales Forecast].Month, MonthConversion.Date
FROM [Master Sales Forecast] INNER JOIN MonthConversion ON [Master
Sales Forecast].Month = MonthConversion.Month;- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
I tried the subquery but it just gives me the sum totals per SVP
without a running sum- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

It worked. Thanks again.

Grant
 
B

BruceM

Avoiding them is best, of course. Thanks for the clarification. As you
say, not very intuitive that it works as a field but fails as an alias.
 

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

problem with forms and queries 2
Form not working with query 6
Append Query - Problem 7
Finding matching records on a form 0
form not working with query 0
problem with parameter query 3
Querying multple criteria but allowing them to not be blank 3
Running sums and other help 0

Top