Problem Calculating Year-To-Date, Sales

B

Brian

Hi Everyone,

I've created a report based on a query

Tables
MonthlyFigures-- ID, Date(mm/yyyy), Figures, Manufacturer, Store
Stores -- ID, Name, Post Code
Manufacturer -- ID Name
Manufacturer ID and Store ID are Related to MonthlyFigures Manufacturer
and store

So, I have a sales report that displays the Store Name and Postcode and a
subreport in its detail section, the subreport is called Monthly and is
based on a query called Report that has all the figures and details
required for the report (Which is linked by Store ID).

Report Query --
SELECT Monthlys.Date,
LastYears.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
MonthlyFigures.Figures,
(
Select Sum(MonthlyFigures.Figures)
FROM MonthlyFigures
WHERE Monthlys.Manufacturer = MonthlyFigures.Manufacturer AND
MonthlyFigures.Store = Monthlys.Store AND
MonthlyFigures.Date Between Monthlys.Date AND LastYears.Date;
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);

This query makes a table with all the values I need, the problem comes
when I create the Report from this, it states that Multi-level group by
clause are not allowed in a subquery.

I searched the net and found that maybe I should use a Dsum() instead
of the subquery, this is my attempt

SELECT Monthlys.Date AS rDate,
Monthlys.Manufacturer AS rManufacturer,
Monthlys.Store AS rStore,
Monthlys.Figures AS rFigures,
LastYears.Figures AS rLastFigures,
DSum(MonthlyFigures.Figures, "MonthlyFigures",
([MonthlyFigures].[Date]
Between [Monthlys].[Date] AND [LastYears].[Date]) AND
([MonthlyFigures].[Manufacturer] = [Monthlys].[Manufacturer]) AND
([MonthlyFigures].[Store] = [Monthlys].[Store])
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);

The YTD figures are way off (not even to total of everything that I was
getting)

This post if getting a little too long, so please let me know if I left
anything out.
 
B

Brian

Hi Everyone,

I've created a report based on a query

Tables
MonthlyFigures-- ID, Date(mm/yyyy), Figures, Manufacturer, Store
Stores -- ID, Name, Post Code
Manufacturer -- ID Name
Manufacturer ID and Store ID are Related to MonthlyFigures Manufacturer
and store

So, I have a sales report that displays the Store Name and Postcode and a
subreport in its detail section, the subreport is called Monthly and is
based on a query called Report that has all the figures and details
required for the report (Which is linked by Store ID).

Report Query --
SELECT Monthlys.Date,
LastYears.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
MonthlyFigures.Figures,
(
Select Sum(MonthlyFigures.Figures)
FROM MonthlyFigures
WHERE Monthlys.Manufacturer = MonthlyFigures.Manufacturer AND
MonthlyFigures.Store = Monthlys.Store AND
MonthlyFigures.Date Between Monthlys.Date AND LastYears.Date;
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);

This query makes a table with all the values I need, the problem comes
when I create the Report from this, it states that Multi-level group by
clause are not allowed in a subquery.

I searched the net and found that maybe I should use a Dsum() instead
of the subquery, this is my attempt
Click to expand...

-----
My mistake this is what the query should look like

SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum(MonthlyFigures.Figures, "MonthlyFigures",
([MonthlyFigures].[Date]
Between [Monthlys].[Date] AND [LastYears].[Date]) AND
([MonthlyFigures].[Manufacturer] = [Monthlys].[Manufacturer]) AND
([MonthlyFigures].[Store] = [Monthlys].[Store])
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
 
D

Duane Hookom

You might try something like this. If Manufacturer and Store are text, you
need to add quotes in the same manner that I added "#" around the dates.
SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum("Figures", "MonthlyFigures",
"[Date] Between #" & Monthlys.[Date] &
"# AND #" & [LastYears].[Date] &
"# AND [Manufacturer] = " & [Monthlys].[Manufacturer] &
" AND [Store] = " & [Monthlys].[Store])
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);


--
Duane Hookom
MS Access MVP


Brian said:
Hi Everyone,

I've created a report based on a query

Tables
MonthlyFigures-- ID, Date(mm/yyyy), Figures, Manufacturer, Store
Stores -- ID, Name, Post Code
Manufacturer -- ID Name
Manufacturer ID and Store ID are Related to MonthlyFigures Manufacturer
and store

So, I have a sales report that displays the Store Name and Postcode and a
subreport in its detail section, the subreport is called Monthly and is
based on a query called Report that has all the figures and details
required for the report (Which is linked by Store ID).

Report Query --
SELECT Monthlys.Date,
LastYears.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
MonthlyFigures.Figures,
(
Select Sum(MonthlyFigures.Figures)
FROM MonthlyFigures
WHERE Monthlys.Manufacturer = MonthlyFigures.Manufacturer AND
MonthlyFigures.Store = Monthlys.Store AND
MonthlyFigures.Date Between Monthlys.Date AND LastYears.Date;
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);

This query makes a table with all the values I need, the problem comes
when I create the Report from this, it states that Multi-level group by
clause are not allowed in a subquery.

I searched the net and found that maybe I should use a Dsum() instead
of the subquery, this is my attempt
Click to expand...

-----
My mistake this is what the query should look like

SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum(MonthlyFigures.Figures, "MonthlyFigures",
([MonthlyFigures].[Date]
Between [Monthlys].[Date] AND [LastYears].[Date]) AND
([MonthlyFigures].[Manufacturer] = [Monthlys].[Manufacturer]) AND
([MonthlyFigures].[Store] = [Monthlys].[Store])
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
The YTD figures are way off (not even to total of everything that I was
getting)

This post if getting a little too long, so please let me know if I left
anything out.
Click to expand...
Click to expand...
 
B

Brian

You might try something like this. If Manufacturer and Store are text, you
need to add quotes in the same manner that I added "#" around the dates.
SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum("Figures", "MonthlyFigures",
"[Date] Between #" & Monthlys.[Date] &
"# AND #" & [LastYears].[Date] &
"# AND [Manufacturer] = " & [Monthlys].[Manufacturer] &
" AND [Store] = " & [Monthlys].[Store])
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
Click to expand...


Hi Duane, Thanks for the reply,

The Manufacturer and Stores are linked fields to other tables (I'm not
sure of the exact terminology), So the Field contains a numerical
identifier, also I tried adding the hashes around the dates and got the
same results,
So now I'm not sure what I'm doing wrong, I'm trying to turn this
query with subquery
----------
SELECT Monthlys.Date, LastYears.Date, Monthlys.Manufacturer,
Monthlys.Store, Monthlys.Figures, LastYears.Figures,
MonthlyFigures.Figures, (
Select Sum(MonthlyFigures.Figures)
FROM MonthlyFigures
WHERE Monthlys.Manufacturer = MonthlyFigures.Manufacturer AND
MonthlyFigures.Store = Monthlys.Store AND MonthlyFigures.Date Between
Monthlys.Date AND LastYears.Date;
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
----------
Into a different query that I can generate a report from, which is why I
was trying the following.
----------
SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum(MonthlyFigures.Figures, "MonthlyFigures",
([MonthlyFigures].[Date] Between [Monthlys].[Date] AND
[LastYears].[Date]) AND ([MonthlyFigures].[Manufacturer] =
[Monthlys].[Manufacturer]) AND ([MonthlyFigures].[Store] =
[Monthlys].[Store]) ) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
 
D

Duane Hookom

Try this:
SELECT Monthlys.Date As MthDate, LastYears.Date As LYDate,
Monthlys.Manufacturer, Monthlys.Store,
Monthlys.Figures as MthFigures, LastYears.Figures as LYFigures,
Dsum ("Figures","MonthlyFigures",
"Manufacturer=" & Monthlys.Manufacturer &
" AND Store = " & Monthlys.Store & " AND [Date] Between #" &
Monthlys.Date & "# AND #" & LastYears.Date & "#") AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);


--
Duane Hookom
MS Access MVP


Brian said:
You might try something like this. If Manufacturer and Store are text,
you
need to add quotes in the same manner that I added "#" around the dates.
SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum("Figures", "MonthlyFigures",
"[Date] Between #" & Monthlys.[Date] &
"# AND #" & [LastYears].[Date] &
"# AND [Manufacturer] = " & [Monthlys].[Manufacturer] &
" AND [Store] = " & [Monthlys].[Store])
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
Click to expand...


Hi Duane, Thanks for the reply,

The Manufacturer and Stores are linked fields to other tables (I'm not
sure of the exact terminology), So the Field contains a numerical
identifier, also I tried adding the hashes around the dates and got the
same results,
So now I'm not sure what I'm doing wrong, I'm trying to turn this
query with subquery
----------
SELECT Monthlys.Date, LastYears.Date, Monthlys.Manufacturer,
Monthlys.Store, Monthlys.Figures, LastYears.Figures,
MonthlyFigures.Figures, (
Select Sum(MonthlyFigures.Figures)
FROM MonthlyFigures
WHERE Monthlys.Manufacturer = MonthlyFigures.Manufacturer AND
MonthlyFigures.Store = Monthlys.Store AND MonthlyFigures.Date Between
Monthlys.Date AND LastYears.Date;
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
----------
Into a different query that I can generate a report from, which is why I
was trying the following.
----------
SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum(MonthlyFigures.Figures, "MonthlyFigures",
([MonthlyFigures].[Date] Between [Monthlys].[Date] AND
[LastYears].[Date]) AND ([MonthlyFigures].[Manufacturer] =
[Monthlys].[Manufacturer]) AND ([MonthlyFigures].[Store] =
[Monthlys].[Store]) ) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
Click to expand...
 
R

rundyz

All the YTD figures were Errors ( data type mismatch) I should get some
more time today to look closer, and I really appreciate the help...

(Brian - using "google groups"!)

Duane said:
Try this:
SELECT Monthlys.Date As MthDate, LastYears.Date As LYDate,
Monthlys.Manufacturer, Monthlys.Store,
Monthlys.Figures as MthFigures, LastYears.Figures as LYFigures,
Dsum ("Figures","MonthlyFigures",
"Manufacturer=" & Monthlys.Manufacturer &
" AND Store = " & Monthlys.Store & " AND [Date] Between #" &
Monthlys.Date & "# AND #" & LastYears.Date & "#") AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);


--
Duane Hookom
MS Access MVP


Brian said:
You might try something like this. If Manufacturer and Store are text,
you
need to add quotes in the same manner that I added "#" around the dates.
SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum("Figures", "MonthlyFigures",
"[Date] Between #" & Monthlys.[Date] &
"# AND #" & [LastYears].[Date] &
"# AND [Manufacturer] = " & [Monthlys].[Manufacturer] &
" AND [Store] = " & [Monthlys].[Store])
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
Click to expand...


Hi Duane, Thanks for the reply,

The Manufacturer and Stores are linked fields to other tables (I'm not
sure of the exact terminology), So the Field contains a numerical
identifier, also I tried adding the hashes around the dates and got the
same results,
So now I'm not sure what I'm doing wrong, I'm trying to turn this
query with subquery
----------
SELECT Monthlys.Date, LastYears.Date, Monthlys.Manufacturer,
Monthlys.Store, Monthlys.Figures, LastYears.Figures,
MonthlyFigures.Figures, (
Select Sum(MonthlyFigures.Figures)
FROM MonthlyFigures
WHERE Monthlys.Manufacturer = MonthlyFigures.Manufacturer AND
MonthlyFigures.Store = Monthlys.Store AND MonthlyFigures.Date Between
Monthlys.Date AND LastYears.Date;
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
----------
Into a different query that I can generate a report from, which is why I
was trying the following.
----------
SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum(MonthlyFigures.Figures, "MonthlyFigures",
([MonthlyFigures].[Date] Between [Monthlys].[Date] AND
[LastYears].[Date]) AND ([MonthlyFigures].[Manufacturer] =
[Monthlys].[Manufacturer]) AND ([MonthlyFigures].[Store] =
[Monthlys].[Store]) ) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
Click to expand...
Click to expand...
 
D

Duane Hookom

Just to confirm:
Figures is numeric
Manufacturer is numeric
Store is numeric
Date is date
GetReportDate() returns a date

All numeric fields align to the right when viewed in datasheet.
--
Duane Hookom
MS Access MVP


All the YTD figures were Errors ( data type mismatch) I should get some
more time today to look closer, and I really appreciate the help...

(Brian - using "google groups"!)

Duane said:
Try this:
SELECT Monthlys.Date As MthDate, LastYears.Date As LYDate,
Monthlys.Manufacturer, Monthlys.Store,
Monthlys.Figures as MthFigures, LastYears.Figures as LYFigures,
Dsum ("Figures","MonthlyFigures",
"Manufacturer=" & Monthlys.Manufacturer &
" AND Store = " & Monthlys.Store & " AND [Date] Between #" &
Monthlys.Date & "# AND #" & LastYears.Date & "#") AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);


--
Duane Hookom
MS Access MVP


Brian said:
9 Dec 2005 07:50:06 -0600, Duane Hookom wrote:

You might try something like this. If Manufacturer and Store are text,
you
need to add quotes in the same manner that I added "#" around the
dates.
SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum("Figures", "MonthlyFigures",
"[Date] Between #" & Monthlys.[Date] &
"# AND #" & [LastYears].[Date] &
"# AND [Manufacturer] = " & [Monthlys].[Manufacturer] &
" AND [Store] = " & [Monthlys].[Store])
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);


Hi Duane, Thanks for the reply,

The Manufacturer and Stores are linked fields to other tables (I'm not
sure of the exact terminology), So the Field contains a numerical
identifier, also I tried adding the hashes around the dates and got the
same results,
So now I'm not sure what I'm doing wrong, I'm trying to turn this
query with subquery
----------
SELECT Monthlys.Date, LastYears.Date, Monthlys.Manufacturer,
Monthlys.Store, Monthlys.Figures, LastYears.Figures,
MonthlyFigures.Figures, (
Select Sum(MonthlyFigures.Figures)
FROM MonthlyFigures
WHERE Monthlys.Manufacturer = MonthlyFigures.Manufacturer AND
MonthlyFigures.Store = Monthlys.Store AND MonthlyFigures.Date Between
Monthlys.Date AND LastYears.Date;
) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
----------
Into a different query that I can generate a report from, which is why
I
was trying the following.
----------
SELECT Monthlys.Date,
Monthlys.Manufacturer,
Monthlys.Store,
Monthlys.Figures,
LastYears.Figures,
DSum(MonthlyFigures.Figures, "MonthlyFigures",
([MonthlyFigures].[Date] Between [Monthlys].[Date] AND
[LastYears].[Date]) AND ([MonthlyFigures].[Manufacturer] =
[Monthlys].[Manufacturer]) AND ([MonthlyFigures].[Store] =
[Monthlys].[Store]) ) AS YTD
FROM LastYears, Monthlys INNER JOIN MonthlyFigures ON Monthlys.ID =
MonthlyFigures.ID
WHERE (
((Monthlys.Date)=GetReportDate()) AND
((Monthlys.Manufacturer)=[LastYears].[Manufacturer]) AND
((Monthlys.Store)=[LastYears].[Store])
);
Click to expand...
Click to expand...
Click to expand...
 
B

Brian

Just to confirm:
Figures is numeric
Manufacturer is numeric
Store is numeric
Date is date
GetReportDate() returns a date

All numeric fields align to the right when viewed in datasheet.
Click to expand...


Yeah, you were right, I hadn't changed the fields back to numbers after
messing around with them earlier, the figures appear now, but the amounts
are still off, I haven't yet figured what they add up to
 
B

Brian

Yeah, you were right, I hadn't changed the fields back to numbers after
messing around with them earlier, the figures appear now, but the amounts
are still off, I haven't yet figured what they add up to
Click to expand...

Still having trouble identifying these figures,

this is a list of the total figures for the manufacturer WITHIN the
desired period


mm/yyyy - Figures
11/2004 - -138
12/2004 - 3895
01/2005 - 2824
02/2005 - -2592
03/2005 - 1744
04/2005 - 102
05/2005 - 9392
06/2005 - 343
08/2005 - -3645
09/2005 - -83
10/2005 - 3045
11/2005 - 3677
TOTAL (YTD) = 18654
The Following aren't in the range I'm testing (11/2004-11/2005)
07/2004 - 21247
08/2004 - 4592
09/2004 - 12632
10/2004 - 568

The Result I'm getting from the query is 45620
The Dsum criteria seems to be ok, so I'm lost!
any thoughts?
 
D

Duane Hookom

It's near impossible to determine what's wonky. Have you tried opening the
debug window and entering something like:

?Dsum ("Figures","MonthlyFigures", "Manufacturer= x AND Store = y AND
[Date] Between #a# AND #b#")
where x and y are numbers and a and b are dates.
 

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

Year to Date subquery 7
Completely Stumped - Calculating Year to Date 7
Month to date and Year to date sales figures 10
Refer to a query in qry of rpt 1
Year to date expression/calculation 2
Year-To-Date Query Help Needed 6
Sales Summary Date Range Filter Question? 4
total sales compared with year to date sales 0

Top