Max of two joined queries

[instereo911 via AccessMonster.com]

Hi all,

I have two joined queries with the same field names for both the queries. one
of the fields is called "MaxofAged Days". I want to make one query that max
the two "MaxofAged Days" fields on both the queries.

Here is the code

SELECT R_Coates_FlashReport_LrgInventory.Date,
R_Coates_FlashReport_LrgInventory.Type, Sum(
[R_Coates_FlashReport_LrgInventory]![Total0and1]+
[R_Coates_FlashReport_SmallGrpInventory]![Total0and1]) AS Between0and1,

Max(([R_Coates_FlashReport_LrgInventory]![MaxOfAged Days]) Or (
[R_Coates_FlashReport_SmallGRPInventory]![MaxOfAged Days])) AS
MAXOFTWOQUERIES

FROM R_Coates_FlashReport_LrgInventory INNER JOIN
R_Coates_FlashReport_SmallGRPInventory ON
(R_Coates_FlashReport_SmallGRPInventory.Type =
R_Coates_FlashReport_LrgInventory.Type) AND
(R_Coates_FlashReport_LrgInventory.Date =
R_Coates_FlashReport_SmallGRPInventory.Date)
GROUP BY R_Coates_FlashReport_LrgInventory.Date,
R_Coates_FlashReport_LrgInventory.Type;


Thanks everyone

 

[KARL DEWEY]

Max(([R_Coates_FlashReport_LrgInventory]![MaxOfAged Days]) Or (
[R_Coates_FlashReport_SmallGRPInventory]![MaxOfAged Days])) AS
MAXOFTWOQUERIES
WRONG - Maximum produces the largest of record in a field, not of multiple
fields. Vertical, not horizontal.

Use this --
IIF([R_Coates_FlashReport_LrgInventory]![MaxOfAged Days] >
[R_Coates_FlashReport_SmallGRPInventory]![MaxOfAged Days],
[R_Coates_FlashReport_LrgInventory]![MaxOfAged Days],
[R_Coates_FlashReport_SmallGRPInventory]![MaxOfAged Days],) AS
MAXOFTWOQUERIES

 

[MGFoster]

Hi all,

I have two joined queries with the same field names for both the queries. one
of the fields is called "MaxofAged Days". I want to make one query that max
the two "MaxofAged Days" fields on both the queries.

Here is the code

SELECT R_Coates_FlashReport_LrgInventory.Date,
R_Coates_FlashReport_LrgInventory.Type, Sum(
[R_Coates_FlashReport_LrgInventory]![Total0and1]+
[R_Coates_FlashReport_SmallGrpInventory]![Total0and1]) AS Between0and1,

Max(([R_Coates_FlashReport_LrgInventory]![MaxOfAged Days]) Or (
[R_Coates_FlashReport_SmallGRPInventory]![MaxOfAged Days])) AS
MAXOFTWOQUERIES

FROM R_Coates_FlashReport_LrgInventory INNER JOIN
R_Coates_FlashReport_SmallGRPInventory ON
(R_Coates_FlashReport_SmallGRPInventory.Type =
R_Coates_FlashReport_LrgInventory.Type) AND
(R_Coates_FlashReport_LrgInventory.Date =
R_Coates_FlashReport_SmallGRPInventory.Date)
GROUP BY R_Coates_FlashReport_LrgInventory.Date,
R_Coates_FlashReport_LrgInventory.Type;


Thanks everyone

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

At first glance it would seem that a UNION query would be more
appropriate, with a wrapping aggregate query:

SELECT [Date], [Type],
SUM(Total0and1) AS Between0and1,
MAX([MaxOfAged Days]) AS MaxOfTwoQueries
FROM (
SELECT [Date], [Type], [Total0and1], [MaxOfAged Days]
FROM R_Coates_FlashReport_LrgInventory

UNION ALL

SELECT [Date], [Type], [Total0and1]), [MaxOfAged Days])
FROM R_Coates_FlashReport_SmallGRPInventory
) AS A

GROUP BY [Date], [Type];

BTW, Date and Type are both keywords, one in VBA and the other in SQL
(that's why I put them in square brackets). It would be a good idea to
rename your columns to more descriptive names (like inventory_date or
report_type, etc.).

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSlfSWYechKqOuFEgEQLg0ACgq1Ir/6+iYXp4FEkb9Ah77xnaCyYAoMFn
JxtPai8B+tlTKVFrL1AKjUDJ
=by7c
-----END PGP SIGNATURE-----