Limtited Query by Field

[Gil Lopes via AccessMonster.com]

Hi all!

I have a problem. I have a query that selects data from tables and show it
like this:

Product 1 Product 2 Product 3
30-05-2005
29-05-2005
....
31-12-2002

Well...Both Product 1 and 2 have values back to 31-12-2002. But Product 3
only started in the middle of 2004. This fact is preventing my query to show
values from Prod 1 and 2 back to 2002, only showing the values where Prod 3
allready has values available.
I would like my query to show Prod 1 and 2 since 2002, and to show Prod 3
only when it starts, leaving previous dates in blank.

Is this possible?

Many thanks.

 

[Michel Walsh]

Hi,


You probably use a criteria on Product3.

You have to take into account the fact that Product3 can be NULL. If you
get the SQL view of your query, should be like:


... WHERE (Product3= parameter OR Product3 IS NULL) AND ...




Hoping it may help,
Vanderghast, Access MVP

 

[Gil Lopes via AccessMonster.com]

Thanks Michel!

Here's my SQL:

SELECT DISTINCT CartazRendPrudente.Data, CartazRendAcções.Cotação,
CartazRendEquilibrado.Cotação, CartazRendGarantido.Cotação,
CartazRendPrudente.Cotação
FROM ((CartazRendAcções INNER JOIN CartazRendEquilibrado ON CartazRendAcções.
Data = CartazRendEquilibrado.Data) INNER JOIN CartazRendGarantido ON
(CartazRendGarantido.Data = CartazRendEquilibrado.Data) AND
(CartazRendEquilibrado.Data = CartazRendGarantido.Data)) INNER JOIN
CartazRendPrudente ON (CartazRendPrudente.Data = CartazRendGarantido.Data)
AND (CartazRendEquilibrado.Data = CartazRendPrudente.Data) AND
(CartazRendEquilibrado.Data = CartazRendPrudente.Data) AND
(CartazRendGarantido.Data = CartazRendPrudente.Data)
ORDER BY CartazRendPrudente.Data DESC;

Where should I insert that statement?
I have to relate the dates so I get consistent results.

Once again, thanks for your help.

Hi,

You probably use a criteria on Product3.

You have to take into account the fact that Product3 can be NULL. If you
get the SQL view of your query, should be like:

... WHERE (Product3= parameter OR Product3 IS NULL) AND ...

Hoping it may help,
Vanderghast, Access MVP

Hi all!

[quoted text clipped - 19 lines]

Many thanks.

 

[Gil Lopes via AccessMonster.com]

Forgot:

My Product 3 is the CartazRendAcções.Cotação.

Thanks Michel!

Here's my SQL:

SELECT DISTINCT CartazRendPrudente.Data, CartazRendAcções.Cotação,
CartazRendEquilibrado.Cotação, CartazRendGarantido.Cotação,
CartazRendPrudente.Cotação
FROM ((CartazRendAcções INNER JOIN CartazRendEquilibrado ON CartazRendAcções.
Data = CartazRendEquilibrado.Data) INNER JOIN CartazRendGarantido ON
(CartazRendGarantido.Data = CartazRendEquilibrado.Data) AND
(CartazRendEquilibrado.Data = CartazRendGarantido.Data)) INNER JOIN
CartazRendPrudente ON (CartazRendPrudente.Data = CartazRendGarantido.Data)
AND (CartazRendEquilibrado.Data = CartazRendPrudente.Data) AND
(CartazRendEquilibrado.Data = CartazRendPrudente.Data) AND
(CartazRendGarantido.Data = CartazRendPrudente.Data)
ORDER BY CartazRendPrudente.Data DESC;

Where should I insert that statement?
I have to relate the dates so I get consistent results.

Once again, thanks for your help.

Hi,

[quoted text clipped - 13 lines]

 

[Michel Walsh]

Hi,


I see. The general technique, in that case, is to use outer join, rather
than inner join. Do you have a table with all the possible "Data" value
implied in the ON statements? If so, bring the other tables in the query and
use LEFT JOIN from the table of all Data values to the other tables you use.
You can define outer left join in the query designer by clicking on the line
representation of the join.


Hoping it may help,
Vanderghast, Access MVP

Forgot:

My Product 3 is the CartazRendAcções.Cotação.

Thanks Michel!

Here's my SQL:

SELECT DISTINCT CartazRendPrudente.Data, CartazRendAcções.Cotação,
CartazRendEquilibrado.Cotação, CartazRendGarantido.Cotação,
CartazRendPrudente.Cotação
FROM ((CartazRendAcções INNER JOIN CartazRendEquilibrado ON
CartazRendAcções.
Data = CartazRendEquilibrado.Data) INNER JOIN CartazRendGarantido ON
(CartazRendGarantido.Data = CartazRendEquilibrado.Data) AND
(CartazRendEquilibrado.Data = CartazRendGarantido.Data)) INNER JOIN
CartazRendPrudente ON (CartazRendPrudente.Data = CartazRendGarantido.Data)
AND (CartazRendEquilibrado.Data = CartazRendPrudente.Data) AND
(CartazRendEquilibrado.Data = CartazRendPrudente.Data) AND
(CartazRendGarantido.Data = CartazRendPrudente.Data)
ORDER BY CartazRendPrudente.Data DESC;

Where should I insert that statement?
I have to relate the dates so I get consistent results.

Once again, thanks for your help.

Hi,

[quoted text clipped - 13 lines]

Many thanks.

 

[Gil Lopes via AccessMonster.com]

Many thanks, Michel.

I solved the problem just by editing the relations beetween field, and
leaving them all connected to the oldest Product.

Many, many thanks mate! ; )

Hi,

I see. The general technique, in that case, is to use outer join, rather
than inner join. Do you have a table with all the possible "Data" value
implied in the ON statements? If so, bring the other tables in the query and
use LEFT JOIN from the table of all Data values to the other tables you use.
You can define outer left join in the query designer by clicking on the line
representation of the join.

Hoping it may help,
Vanderghast, Access MVP

Forgot:

[quoted text clipped - 28 lines]