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

R

Ron

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
 
A

Allen Browne

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))
 
G

Gary Walter

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;

Allen Browne said:
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))

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Ron said:
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
Click to expand...
Click to expand...
 
R

Ron

{;ease See Notes In Previous answers
--
Ron


Gary Walter said:
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;
Click to expand...

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

Allen Browne said:
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
Click to expand...
Click to expand...

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))
Click to expand...
Click to expand...

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.

???????
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Ron said:
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
Click to expand...
Click to expand...
Click to expand...
 
M

Michael Gramelspacher

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.
Click to expand...

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;
 
R

Ron

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)

--
Ron


Michael Gramelspacher said:
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.
Click to expand...

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;
Click to expand...
 
M

Michael Gramelspacher

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)
Click to expand...

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;
 
R

Ron

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.
--
Ron


Michael Gramelspacher said:
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)
Click to expand...

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;
Click to expand...
 
M

Michael Gramelspacher

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;
Click to expand...

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.
 
M

Michael Gramelspacher

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)
Click to expand...

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.
 
Top