Connect two different queries for these outcomes

[jason]

Need guidance - I am really struggling here. I have two queries which track
two criteria for a particular yacht:

- Price Changes
- Market Changes

I need to somehow combine them or link them to allow me to work out what
exactly has changed for a particular boat(s) and then formulate
an email to the user.

These are the possible outcomes for a particular boat:

A. A boat has a price reduction and a change in market status
B. A boat has a price reduction but NO market status change
C. A boat has NO price reduction but has a market status change
D. A boat has NO price reduction and NO market Status Change.

Here are my two queries that are currently seperate from each other:


1. -------------------------------
MARKET STATUS QUERY (Tracks Market Status Changes for a boat)
-------------------------------
PARAMETERS LID Long, pDate DateTime;
SELECT [tblListings].[Name], [tblModel].[Model] AS Model,
[tblListings].[Size_ID] AS Size_ID, Max([audMarketStatus].[AudDate]) AS
AudDate, [audMarketStatus].[AudType], [audMarketStatus].[AudUser],
[audMarketStatus].[AudIP], [audMarketStatus].[ListingsID],
[audMarketStatus].[Old_Status_ID], tblMarketStatus_2.Market_Status AS
OLDSTATUS, [audMarketStatus].[New_Status_ID],
tblMarketStatus_3.Market_Status AS NEWSTATUS
FROM tblMarketStatus AS tblMarketStatus_3 INNER JOIN (tblMarketStatus AS
tblMarketStatus_2 INNER JOIN (tblModel INNER JOIN (tblMarketStatus INNER
JOIN (audMarketStatus INNER JOIN tblListings ON
[audMarketStatus].[ListingsID]=[tblListings].[ListingsID]) ON
[tblMarketStatus].[MarketStatusID]=[tblListings].[Status_ID]) ON
[tblModel].[ModelID]=[tblListings].[Model_ID]) ON
tblMarketStatus_2.MarketStatusID=[audMarketStatus].[Old_Status_ID]) ON
tblMarketStatus_3.MarketStatusID=[audMarketStatus].[New_Status_ID]
GROUP BY [tblListings].[Name], [tblModel].[Model], [tblListings].[Size_ID],
[audMarketStatus].[AudType], [audMarketStatus].[AudUser],
[audMarketStatus].[AudIP], [audMarketStatus].[ListingsID],
[audMarketStatus].[Old_Status_ID], tblMarketStatus_2.Market_Status,
[audMarketStatus].[New_Status_ID], tblMarketStatus_3.Market_Status, [LID],
[pDate]
HAVING ((([LID]) Is Null) AND (([pDate]) Is Null)) OR
(((audMarketStatus.ListingsID)=[LID]) AND (([pDate]) Is Null)) OR
(((Max(audMarketStatus.AudDate))=[pDate]) AND (([LID]) Is Null)) OR
(((Max(audMarketStatus.AudDate))=[pDate]) AND
((audMarketStatus.ListingsID)=[LID]));

1. -------------------------------
PRICE CHANGES(Tracks Market Status Changes for a boat)
-------------------------------
PARAMETERS LID Long;
SELECT tblListingsPriceChanges.*, [tblListings].[ListingsID],
[tblListingsPriceChanges].[PriceChangeInsertionDate] AS
PriceChangeInsertionDate
FROM tblListings INNER JOIN tblListingsPriceChanges ON
[tblListings].[ListingsID]=[tblListingsPriceChanges].[ListingsID]
WHERE ((([LID]) Is Null)) Or ((([tblListings].[ListingsID])=[LID]))
ORDER BY [ChangeDate] DESC;

Many thanks for any help you can offer me.

Regards
Jason

 

[[MVP] S. Clark]

You may not need to combine the SQL of both queries together, but you can
simply link them together into a third, or another permutation into a fourth
or fifth.

Qry1 = price reduction
Qry2 = Market Change

QryA = Qry1 AND Qry2
QryB = Qry1 AND NOT Qry2
QryC = NOT Qry1 AND Qry2
QryD = NOT Qry1 AND NOT Qry2

So, in code, you could submit a BoatID against QryA. If a record is
returned, then email, else try QryB, etc.

The NOT could be implemented using outer joins.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Need guidance - I am really struggling here. I have two queries which track
two criteria for a particular yacht:

- Price Changes
- Market Changes

I need to somehow combine them or link them to allow me to work out what
exactly has changed for a particular boat(s) and then formulate
an email to the user.

These are the possible outcomes for a particular boat:

A. A boat has a price reduction and a change in market status
B. A boat has a price reduction but NO market status change
C. A boat has NO price reduction but has a market status change
D. A boat has NO price reduction and NO market Status Change.

Here are my two queries that are currently seperate from each other:


1. -------------------------------
MARKET STATUS QUERY (Tracks Market Status Changes for a boat)
-------------------------------
PARAMETERS LID Long, pDate DateTime;
SELECT [tblListings].[Name], [tblModel].[Model] AS Model,
[tblListings].[Size_ID] AS Size_ID, Max([audMarketStatus].[AudDate]) AS
AudDate, [audMarketStatus].[AudType], [audMarketStatus].[AudUser],
[audMarketStatus].[AudIP], [audMarketStatus].[ListingsID],
[audMarketStatus].[Old_Status_ID], tblMarketStatus_2.Market_Status AS
OLDSTATUS, [audMarketStatus].[New_Status_ID],
tblMarketStatus_3.Market_Status AS NEWSTATUS
FROM tblMarketStatus AS tblMarketStatus_3 INNER JOIN (tblMarketStatus AS
tblMarketStatus_2 INNER JOIN (tblModel INNER JOIN (tblMarketStatus INNER
JOIN (audMarketStatus INNER JOIN tblListings ON
[audMarketStatus].[ListingsID]=[tblListings].[ListingsID]) ON
[tblMarketStatus].[MarketStatusID]=[tblListings].[Status_ID]) ON
[tblModel].[ModelID]=[tblListings].[Model_ID]) ON
tblMarketStatus_2.MarketStatusID=[audMarketStatus].[Old_Status_ID]) ON
tblMarketStatus_3.MarketStatusID=[audMarketStatus].[New_Status_ID]
GROUP BY [tblListings].[Name], [tblModel].[Model], [tblListings].[Size_ID],
[audMarketStatus].[AudType], [audMarketStatus].[AudUser],
[audMarketStatus].[AudIP], [audMarketStatus].[ListingsID],
[audMarketStatus].[Old_Status_ID], tblMarketStatus_2.Market_Status,
[audMarketStatus].[New_Status_ID], tblMarketStatus_3.Market_Status, [LID],
[pDate]
HAVING ((([LID]) Is Null) AND (([pDate]) Is Null)) OR
(((audMarketStatus.ListingsID)=[LID]) AND (([pDate]) Is Null)) OR
(((Max(audMarketStatus.AudDate))=[pDate]) AND (([LID]) Is Null)) OR
(((Max(audMarketStatus.AudDate))=[pDate]) AND
((audMarketStatus.ListingsID)=[LID]));

1. -------------------------------
PRICE CHANGES(Tracks Market Status Changes for a boat)
-------------------------------
PARAMETERS LID Long;
SELECT tblListingsPriceChanges.*, [tblListings].[ListingsID],
[tblListingsPriceChanges].[PriceChangeInsertionDate] AS
PriceChangeInsertionDate
FROM tblListings INNER JOIN tblListingsPriceChanges ON
[tblListings].[ListingsID]=[tblListingsPriceChanges].[ListingsID]
WHERE ((([LID]) Is Null)) Or ((([tblListings].[ListingsID])=[LID]))
ORDER BY [ChangeDate] DESC;

Many thanks for any help you can offer me.

Regards
Jason