I don't know why this query only finds three items

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ron - 13 Feb 2008 05:08 GMT

The following SQL Statement will only show 3 items no matter how many items
meet the criteria and I cannot understand why:

SELECT [Meals Defined].MealDate, [Meals Defined].Meal, [Meals
Defined].Quantity, FoodItems.ServSize, FoodItems.ServUnits,
[FoodItems]![TotCarbs]*([Meals Defined]![Quantity]/[FoodItems]![ServSize]) AS
CarbsUsd, ([Protein]*[Meals Defined]![Quantity]/[FoodItems]![ServSize]) AS
ProtUsd, [Calories]*([FoodItems]![ServSize]/[Meals Defined]![Quantity]) AS
CalUsd, [Sugars]*([FoodItems]![ServSize]/[Meals Defined]![Quantity]) AS
SugUsd, FoodItems.Potassium, [TotFat]*([FoodItems]![ServSize]/[Meals
Defined]![Quantity]) AS FatUsd, FoodItems.ItemName
FROM [Meals Defined] LEFT JOIN FoodItems ON [Meals Defined].[Food Name] =
FoodItems.ItemName
WHERE ((([Meals Defined].MealDate)=[Enter the Date of the meal]));

As you can see there are two tables which are joined and in the one table
(Meals Defined) there are several MealDate which have many rows of items
which match the selection criteria. This query will only pick 3 rows to show
all of the fields. It may show the remaining rows, but only some of the
fields.

Can you please show me what's wrong?

TIA

Signature

Ron

Reply to this Message

Allen Browne - 13 Feb 2008 12:41 GMT

One possibility is that JET is not understanding the data types correctly.

If you open Meals Defined table in design view, what is the data type of
MealDate?

Assuming it is a Date/Time field, declare your paramater so JET knows its
type. Open the query in design view. Choose Parameters on the Query menu. In
the dialog enter:
[Enter the Date of the meal] Date/Time

If that does not solve the problem, perhaps the MealDate field contains a
time component as well as a date. If so, only the dates that have no time
component will match the date you type as your parameter. To solve this,
try:
WHERE (([Meals Defined].MealDate >= [Enter the Date of the meal])
AND ([Meals Defined].MealDate < [Enter the Date of the meal] + 1))

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> The following SQL Statement will only show 3 items no matter how many
> items
[quoted text clipped - 23 lines]
>
> TIA

Reply to this Message

Gary Walter - 13 Feb 2008 13:02 GMT

Hi Ron,

Allen has probably solved your problem,
but I had already applied aliases to your
query (so I could better follow) and "have
to ask" why Quantity/ServSize get reversed
in some of your calcs?

PARAMETERS [Enter the Date of the meal] DATETIME;
SELECT
M.MealDate,
M.Meal,
M.Quantity,
F.ServSize,
F.ServUnits,
F.TotCarbs*(M.Quantity/F.ServSize) AS CarbsUsd,
F.Protein*(M.Quantity/F.ServSize) AS ProtUsd,
F.Calories*(F.ServSize/M.Quantity) AS CalUsd,
F.Sugars*(F.ServSize/M.Quantity) AS SugUsd,
F.Potassium,
F.TotFat*(F.ServSize/M.Quantity) AS FatUsd,
F.ItemName
FROM
[Meals Defined] AS M
LEFT JOIN
FoodItems AS F
ON
M.Food Name = F.ItemName
WHERE
M.MealDate >= [Enter the Date of the meal]
AND
M.MealDate < [Enter the Date of the meal] + 1;

> One possibility is that JET is not understanding the data types correctly.
>
[quoted text clipped - 42 lines]
>>
>> TIA

Reply to this Message

Ron - 13 Feb 2008 18:13 GMT

{;ease See Notes In Previous answers

Signature

Ron

> Hi Ron,
>
[quoted text clipped - 28 lines]
> AND
> M.MealDate < [Enter the Date of the meal] + 1;

Under normal circumstances I would say what difference does that make???
But in this instance I will try whatever anyone suggest. Will be back

> > One possibility is that JET is not understanding the data types correctly.
> >
[quoted text clipped - 5 lines]
> > In the dialog enter:
> > [Enter the Date of the meal] Date/Time

I did use the parameters window to no effect.

> > If that does not solve the problem, perhaps the MealDate field contains a
> > time component as well as a date. If so, only the dates that have no time
> > component will match the date you type as your parameter. To solve this,
> > try:
> > WHERE (([Meals Defined].MealDate >= [Enter the Date of the meal])
> > AND ([Meals Defined].MealDate < [Enter the Date of the meal] + 1))

Here is the SQL as I modified it:

SELECT [Meals Defined].MealDate, FoodItems.ItemName, [Meals Defined].Meal,
[Meals Defined].Quantity, [Meals Defined].ID, FoodItems.ServSize,
FoodItems.ServUnits, FoodItems.Calories, FoodItems.TotFat,
FoodItems.Cholesterol, FoodItems.Sodium, FoodItems.TotCarbs,
FoodItems.Sugars, FoodItems.Protein
FROM FoodItems LEFT JOIN [Meals Defined] ON FoodItems.ItemName = [Meals
Defined].[Food Name]
WHERE ((([Meals Defined].MealDate)>=[Enter the Date] And ([Meals
Defined].MealDate)<[Enter the Date]+1))
ORDER BY FoodItems.ItemName;

I got this error:

This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables. (Error 3071)

I checked for the parenthesis and I think they are correct. Nothing else
was changed.

???????

> >> The following SQL Statement will only show 3 items no matter how many
> >> items
[quoted text clipped - 25 lines]
> >>
> >> TIA

Reply to this Message

Michael Gramelspacher - 13 Feb 2008 19:40 GMT

>Here is the SQL as I modified it:
>
[quoted text clipped - 18 lines]
>I checked for the parenthesis and I think they are correct. Nothing else
>was changed.

try:

PARAMETERS [Enter the Date] DateTime;
SELECT Q1.MealDate,
FoodItems.ItemName,
Q1.Meal,
Q1.Quantity,
Q1.ID,
FoodItems.ServSize,
FoodItems.ServUnits,
FoodItems.Calories,
FoodItems.TotFat,
FoodItems.Cholesterol,
FoodItems.Sodium,
FoodItems.TotCarbs,
FoodItems.Sugars,
FoodItems.Protein
FROM FoodItems
LEFT JOIN (SELECT *
FROM [Meals Defined]
WHERE [Meals Defined].MealDate >=
[Enter the Date]
AND [Meals Defined].MealDate <
DATEADD('d',1,[Enter the Date])) AS Q1
ON FoodItems.ItemName = Q1.[Food Name]
ORDER BY FoodItems.ItemName;

Reply to this Message

Ron - 13 Feb 2008 20:36 GMT

I wrote the SQL into my App:

Parameters [Enter the Date] DateTime;
SELECT [Meals Defined].MealDate,
FoodItems.ItemName,
[Meals Defined].Meal,
[Meals Defined].Quantity,
[Meals Defined].ID,
FoodItems.ServSize,
FoodItems.ServUnits,
FoodItems.[TotCarbs],
FoodItems.[Protein],
FoodItems.[Calories],
FoodItems.[Sugars],
FoodItems.Potassium,
FoodItems.[TotFat]
FROM Fooditems
LEFT JOIN
(SELECT [Meals Defined].MealDate,
FoodItems.ItemName,
[Meals Defined].Meal,
[Meals Defined].Quantity,
[Meals Defined].ID,
FoodItems.ServSize,
FoodItems.ServUnits,
FoodItems.[TotCarbs],
FoodItems.[Protein],
FoodItems.[Calories],
FoodItems.[Sugars],
FoodItems.Potassium,
FoodItems.[TotFat],
FROM [Meals Defined]
Where [Meals Defined].MealDate>=[Enter the Date of the meal]
AND [Meals Defined].MealDate<DateADD('d',1,[Enter the Date of the meal]))
AS [Meals Defined] ON FoodItems.ItemName = [Meals Defined].[FoodName]
ORDER BY FoodItems.ItemName;

I Get this error:

The SELECT statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect. (Error 3141)

Signature

Ron

> >Here is the SQL as I modified it:
> >
[quoted text clipped - 45 lines]
> ON FoodItems.ItemName = Q1.[Food Name]
> ORDER BY FoodItems.ItemName;

Reply to this Message

Michael Gramelspacher - 13 Feb 2008 21:21 GMT

>I wrote the SQL into my App:
>
[quoted text clipped - 37 lines]
>The SELECT statement includes a reserved word or an argument name that is
>misspelled or missing, or the punctuation is incorrect. (Error 3141)

This is not the query I offered. Did my query not work? Let's break out that
nested table query into a separate query and then do the left join to Query1.

Query1:

PARAMETERS [Enter the Date of the meal] DateTime;
SELECT *
FROM [Meals Defined]
WHERE [Meals Defined].MealDate >=
[Enter the Date of the meal]
AND [Meals Defined].MealDate <
DATEADD('d',1,[Enter the Date of the meal]);

Query2:

SELECT Q1.MealDate,
FoodItems.ItemName,
Q1.Meal,
Q1.Quantity,
Q1.ID,
FoodItems.ServSize,
FoodItems.ServUnits,
FoodItems.Calories,
FoodItems.TotFat,
FoodItems.Cholesterol,
FoodItems.Sodium,
FoodItems.TotCarbs,
FoodItems.Sugars,
FoodItems.Potassium,
FoodItems.Protein
FROM FoodItems
LEFT JOIN Query1 AS Q1
ON FoodItems.ItemName = Q1.[Food Name]
ORDER BY FoodItems.ItemName;

Reply to this Message

Ron - 14 Feb 2008 15:44 GMT

I went back to the Query You wrote as such:

PARAMETERS [Enter the Date] DateTime;
SELECT [Meals Defined].MealDate,
FoodItems.ItemName,
[Meals Defined].Meal,
[Meals Defined].Quantity,
[Meals Defined].ID,
FoodItems.ServSize,
FoodItems.ServUnits,
FoodItems.Calories,
FoodItems.TotFat,
FoodItems.Cholesterol,
FoodItems.Sodium,
FoodItems.TotCarbs,
FoodItems.Sugars,
FoodItems.Protein
FROM FoodItems
LEFT JOIN (SELECT *
FROM [Meals Defined]
WHERE [Meals Defined].MealDate >=
[Enter the Date]
AND [Meals Defined].MealDate <
DATEADD('d',1,[Enter the Date])) AS [Meals Defined]
ON FoodItems.ItemName = [Meals Defined].[Food Name]
ORDER BY FoodItems.ItemName;

It worked except it brought all 415 items on FoodItems. It did not just
bring the 5 items on 2/12/08.

Signature

Ron

> >I wrote the SQL into my App:
> >
[quoted text clipped - 72 lines]
> ON FoodItems.ItemName = Q1.[Food Name]
> ORDER BY FoodItems.ItemName;

Reply to this Message

Michael Gramelspacher - 14 Feb 2008 18:14 GMT

>I went back to the Query You wrote as such:
>
[quoted text clipped - 22 lines]
> ON FoodItems.ItemName = [Meals Defined].[Food Name]
>ORDER BY FoodItems.ItemName;

Change Left Join to Inner Join and try again.

A Left Join preserves the rows from the left table. The right table furnishes
Nulls where there is no match on the joined columns. Result is that you get all
rows from FoodItems.

Reply to this Message

Ron - 14 Feb 2008 18:32 GMT

All I did was change LEFT to INNER and now I get:

This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables. (Error 3071)

Signature

Ron

> >I went back to the Query You wrote as such:
> >
[quoted text clipped - 28 lines]
> Nulls where there is no match on the joined columns. Result is that you get all
> rows from FoodItems.

Reply to this Message

Michael Gramelspacher - 14 Feb 2008 18:52 GMT

>All I did was change LEFT to INNER and now I get:
>
>This expression is typed incorrectly, or it is too complex to be evaluated.
>For example, a numeric expression may contain too many complicated elements.
>Try simplifying the expression by assigning parts of the expression to
>variables. (Error 3071)

It works for me in Access 2003 without any date of course. All I can suggest is
to break out the nested query from the main query into a separate query and then
see it thatquery works by itself. If so, then join to this query instead of
embedding it in your main query.

Reply to this Message