Selecting All Records

[wem3rd]

I am graphing growth over time and need to have the graph show all time
periods even though the "Data" may not exist past say the first few
intervals.

There are two tables, one with a complete standard set of data (weight over
time) and the other has weight only over the first third or so of the sample
time period. I have tried Select All ..... in the Query Criteria in the
standard time field and expected it to pull in all Standard data, but it only
selects data up through what exists for the Sample data.

Thanks

 

[wem3rd]

Use an outer join so that all rows from the standard data's table are
returned as well as the matching rows from the sample data, e.g.

SELECT Standard.SomeField, Sample.SomeField
FROM Standard LEFT JOIN Sample
ON Standard.TimePeriod = Sample.TimePeriod;

In query design view you can do this by right clicking on the line joining
the tables and then selecting Join Properties. Then select the appropriate
option in the lower part of the dialogue.

Ken Sheridan
Stafford, England

Ken,

Thanks for the geat response. I had itset up just as you suggested - except
the "On standard,timperiod was On Sample.time period. It didn't work so I
changed my sql to the following and receive an Error "Join expression not
supported."
If I return the code back to "ON [T Standards].Age = [T Standards].Age", the
quey runs but the missing ages remain elusive.

Here is the code that reeturns te error:

SELECT [T Standards].Age, [T FlockData].FLKID, [T FlockData].[Eggs/Wk], [T
FlockData].[F Inv]
FROM [T Standards] LEFT JOIN [T FlockData] ON [T Standards].Age = [T
Standards].Age
WHERE ((([T FlockData].FLKID)="3030530"));

Thanks for helping!

 

[Ken Sheridan]

Your problem here is that you are trying to restrict the query's result set
on the basis of a column in the T FlockData table, which is on the right side
of the join. This won't work and effectively turns an outer join into an
inner join; you can only restrict on a column on the left side of a LEFT JOIN
or the right side of a RIGHT JOIN.

The error, however, results from the fact that the join clause uses the same
column from one table, [T Standards].Age, not separate columns from each.

I'm not quite clear as to what results you want. You originally said you
wanted all the rows from T Standards but you are restricting the query by a
column in T FlockData. These are mutually incompatible requirements as I've
said above. I think you might need to use two separate queries, one on the T
Standards table to return all rows and one on the T FlockData to return the
rows WHERE FLKID = "3030530". Then join the first query to the second one
using a LEFT JOIN on the relevant columns from each. See if that gives you
the desired result. If not you'll need to spell out your requirements in
more detail.

Ken Sheridan
Stafford, England

 

[wem3rd]

Your problem here is that you are trying to restrict the query's result set
on the basis of a column in the T FlockData table, which is on the right side
of the join. This won't work and effectively turns an outer join into an
inner join; you can only restrict on a column on the left side of a LEFT JOIN
or the right side of a RIGHT JOIN.

The error, however, results from the fact that the join clause uses the same
column from one table, [T Standards].Age, not separate columns from each.

I'm not quite clear as to what results you want. You originally said you
wanted all the rows from T Standards but you are restricting the query by a
column in T FlockData. These are mutually incompatible requirements as I've
said above. I think you might need to use two separate queries, one on the T
Standards table to return all rows and one on the T FlockData to return the
rows WHERE FLKID = "3030530". Then join the first query to the second one
using a LEFT JOIN on the relevant columns from each. See if that gives you
the desired result. If not you'll need to spell out your requirements in
more detail.

Ken Sheridan
Stafford, England

Ken,

Thanks for the geat response. I had itset up just as you suggested - except
the "On standard,timperiod was On Sample.time period. It didn't work so I
changed my sql to the following and receive an Error "Join expression not
supported."
If I return the code back to "ON [T Standards].Age = [T Standards].Age", the
quey runs but the missing ages remain elusive.

Here is the code that reeturns te error:

SELECT [T Standards].Age, [T FlockData].FLKID, [T FlockData].[Eggs/Wk], [T
FlockData].[F Inv]
FROM [T Standards] LEFT JOIN [T FlockData] ON [T Standards].Age = [T
Standards].Age
WHERE ((([T FlockData].FLKID)="3030530"));

Thanks for helping!

Eurika!

Ken, you hit the nail on the head. I finally grasp what's going on and I
have succeeded in making the datasety I need for graphing. It is rather
cumbersome to have to run several maketable queries to achieve it, but I can
do that easily in a macro.

It occurred to me that perhaps I might nest a subquery if on the proper side
of the join, and I can scope that possibility out myself.

I do have an ongoing problem making any subquery work in the criteria
control in the quey grid. I cannot seem to get the sytax correct and have
failed to get a good example in my reference books. Could you show me how a
truly simple and straightforward subquery would look on the grid?

Thanks for helping once again

wem3rd

 

[Ken Sheridan]

It depends on the type of subquery. Here's a couple of examples which do the
same thing, one using the IN operator, the other the EXISTS predicate to find
all customers who've made orders in 2006. Normally this would be done with a
join, but for the sake of these examples we'll use subqueries.

The first query would be:

SELECT *
FROM Customers
WHERE CustomerID IN
(SELECT CustomerID
FROM Orders
WHERE YEAR(OrderDate) = 2006);

In query design view you'd create a query based on the Customers table and
put the following in the 'criteria' row of the CustomerID column:

IN(SELECT CustomerID FROM Orders WHERE YEAR(OrderDate) = 2006)

Doing the same with the EXISTS predicate the query would be:

SELECT *
FROM Customers
WHERE EXISTS
(SELECT *
FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID
AND YEAR(OrderDate) = 2006);

In query design view you'd again you'd create a query based on the Customers
table and enter the following in the 'field' row of a blank column

EXISTS(SELECT * FROM Orders WHERE Orders.CustomerID = Customers.CustomerID
AND YEAR(OrderDate) = 2006)

In the 'criteria' row of the same column you enter TRUE and uncheck the
'show' checkbox for the column.

Ken Sheridan
Stafford, England

 

[wem3rd]

It depends on the type of subquery. Here's a couple of examples which do the
same thing, one using the IN operator, the other the EXISTS predicate to find
all customers who've made orders in 2006. Normally this would be done with a
join, but for the sake of these examples we'll use subqueries.

The first query would be:

SELECT *
FROM Customers
WHERE CustomerID IN
(SELECT CustomerID
FROM Orders
WHERE YEAR(OrderDate) = 2006);

In query design view you'd create a query based on the Customers table and
put the following in the 'criteria' row of the CustomerID column:

IN(SELECT CustomerID FROM Orders WHERE YEAR(OrderDate) = 2006)

Doing the same with the EXISTS predicate the query would be:

SELECT *
FROM Customers
WHERE EXISTS
(SELECT *
FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID
AND YEAR(OrderDate) = 2006);

In query design view you'd again you'd create a query based on the Customers
table and enter the following in the 'field' row of a blank column

EXISTS(SELECT * FROM Orders WHERE Orders.CustomerID = Customers.CustomerID
AND YEAR(OrderDate) = 2006)

In the 'criteria' row of the same column you enter TRUE and uncheck the
'show' checkbox for the column.

Ken Sheridan
Stafford, England

Ken,

I have succeeded in this effort now thanks to your help. The Syntax errors
that stopped me in my tracks were simply caused by my including a semicolon
at the end of the statement in the crteria control box. I also was not
familiar with the "IN" operator.

Onward, and thanks.

wem3rd