Connect two different queries for these outcomes

  • Thread starter Thread starter jason
  • Start date Start date
J

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
 
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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

jason said:
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
Click to expand...
 
Back
Top