Comparison query not returning more than one fld change - why?

J

jason

I am having a bit of problem with my comparison query which flags/trackes
changes to a particular field - tblListings.Status_ID - based on the
comparison btw the primary table [tblListings] and the shadow table
[audListings].

Currently the query IS picking up the change to a particular boat listing
[ListingID=203]and the Status_ID for the date 9/8/2003 but not for today
9/9/2003 and the different boat listings [ListingsID = 202,203,205] .

You can view the actual changes in SCREENSHOT for the shadow table
[AudListings] right here:

http://69.2.200.70/catamaranco/images/screenshot/audListings.jpg

....to see what I mean.

But Here is what is actually returned my query:

http://69.2.200.70/catamaranco/images/screenshot/qrytrackStatus_id.jpg

Thus, showing that the comparison query is only picking up ListingsID=203
for the date 9/8/2003 and not 202, 205 for 9/9/2003.

Could someone help explain why this is. Here is what it currently looks
like:

SELECT [tblListings].[ListingsID], "Market Status has changed" AS Flag,
[tblListings].[Name], [tblListings].[Status_ID],
[tblMarketStatus].[Market_Status], [audListings].[Status_ID],
[audListings].[AudDate]
FROM tblMarketStatus INNER JOIN (tblListings INNER JOIN audListings ON
[tblListings].[ListingsID]=[audListings].[ListingsID]) ON
[tblMarketStatus].[MarketStatusID]=[tblListings].[Status_ID]
WHERE ((([tblListings].[Status_ID])<>[audListings].[Status_ID]));
 
J

John Verhagen

I don't see a record in audListings for ListingsID=202 and AudDate=9/9/2003
so that explains why you don't see it in the query.
Do you have a record in tblMarketStatus where MarketStatusID=1 and another
record with MarketStatusID=5?
Do you have a record in tblListings where ListingsID=205 and Status_ID<>5?
 
J

jason

John - I'm confused all those values are in there. This is really
confusing as my query seems to work based on the date prior to the change
but ignores the change made today.....

- Jason
John Verhagen said:
I don't see a record in audListings for ListingsID=202 and AudDate=9/9/2003
so that explains why you don't see it in the query.
Do you have a record in tblMarketStatus where MarketStatusID=1 and another
record with MarketStatusID=5?
Do you have a record in tblListings where ListingsID=205 and Status_ID<>5?


jason said:
I am having a bit of problem with my comparison query which flags/trackes
changes to a particular field - tblListings.Status_ID - based on the
comparison btw the primary table [tblListings] and the shadow table
[audListings].

Currently the query IS picking up the change to a particular boat listing
[ListingID=203]and the Status_ID for the date 9/8/2003 but not for today
9/9/2003 and the different boat listings [ListingsID = 202,203,205] .

You can view the actual changes in SCREENSHOT for the shadow table
[AudListings] right here:

http://69.2.200.70/catamaranco/images/screenshot/audListings.jpg

...to see what I mean.

But Here is what is actually returned my query:

http://69.2.200.70/catamaranco/images/screenshot/qrytrackStatus_id.jpg

Thus, showing that the comparison query is only picking up ListingsID=203
for the date 9/8/2003 and not 202, 205 for 9/9/2003.

Could someone help explain why this is. Here is what it currently looks
like:

SELECT [tblListings].[ListingsID], "Market Status has changed" AS Flag,
[tblListings].[Name], [tblListings].[Status_ID],
[tblMarketStatus].[Market_Status], [audListings].[Status_ID],
[audListings].[AudDate]
FROM tblMarketStatus INNER JOIN (tblListings INNER JOIN audListings ON
[tblListings].[ListingsID]=[audListings].[ListingsID]) ON
[tblMarketStatus].[MarketStatusID]=[tblListings].[Status_ID]
WHERE ((([tblListings].[Status_ID])<>[audListings].[Status_ID]));
Click to expand...
Click to expand...
 
J

John Verhagen

I cannot see what is in your other tables, but for sure on the audListings
URL that you gave below, the date for ListingsID=202, AudiID=44 is 9/8/2003,
not 9/9/2003. Do you have a URL for the other 2 tables?

jason said:
John - I'm confused all those values are in there. This is really
confusing as my query seems to work based on the date prior to the change
but ignores the change made today.....

- Jason
John Verhagen said:
I don't see a record in audListings for ListingsID=202 and AudDate=9/9/2003
so that explains why you don't see it in the query.
Do you have a record in tblMarketStatus where MarketStatusID=1 and another
record with MarketStatusID=5?
Do you have a record in tblListings where ListingsID=205 and
Click to expand...
Status_ID said:
jason said:
I am having a bit of problem with my comparison query which flags/trackes
changes to a particular field - tblListings.Status_ID - based on the
comparison btw the primary table [tblListings] and the shadow table
[audListings].

Currently the query IS picking up the change to a particular boat listing
[ListingID=203]and the Status_ID for the date 9/8/2003 but not for today
9/9/2003 and the different boat listings [ListingsID = 202,203,205] .

You can view the actual changes in SCREENSHOT for the shadow table
[AudListings] right here:

http://69.2.200.70/catamaranco/images/screenshot/audListings.jpg

...to see what I mean.

But Here is what is actually returned my query:

http://69.2.200.70/catamaranco/images/screenshot/qrytrackStatus_id.jpg

Thus, showing that the comparison query is only picking up ListingsID=203
for the date 9/8/2003 and not 202, 205 for 9/9/2003.

Could someone help explain why this is. Here is what it currently looks
like:

SELECT [tblListings].[ListingsID], "Market Status has changed" AS Flag,
[tblListings].[Name], [tblListings].[Status_ID],
[tblMarketStatus].[Market_Status], [audListings].[Status_ID],
[audListings].[AudDate]
FROM tblMarketStatus INNER JOIN (tblListings INNER JOIN audListings ON
[tblListings].[ListingsID]=[audListings].[ListingsID]) ON
[tblMarketStatus].[MarketStatusID]=[tblListings].[Status_ID]
WHERE ((([tblListings].[Status_ID])<>[audListings].[Status_ID]));
Click to expand...
Click to expand...
Click to expand...
 
J

jason

Hi John,

I have decided to change tack and store changes to Status in a MarketStatus
table and record both the
OLD status and the NEW status for each new record inserted +
CreationDate...this way I can easily determine what has changed and when it
was changed.

I have outlined the solution to the problem in the thread called Audit Trail
:: compare two records and then compile....I received a lot of help from D
Steele...

I think my solution works....

It is now just question of first cycling the market status query to
determine changes and then cycle my price reduction query to determine
changes or somehoew combine the two in a union query watching out for data
types....

Cheeers
Jason
John Verhagen said:
I cannot see what is in your other tables, but for sure on the audListings
URL that you gave below, the date for ListingsID=202, AudiID=44 is 9/8/2003,
not 9/9/2003. Do you have a URL for the other 2 tables?

jason said:
John - I'm confused all those values are in there. This is really
confusing as my query seems to work based on the date prior to the change
but ignores the change made today.....

- Jason
John Verhagen said:
I don't see a record in audListings for ListingsID=202 and AudDate=9/9/2003
so that explains why you don't see it in the query.
Do you have a record in tblMarketStatus where MarketStatusID=1 and another
record with MarketStatusID=5?
Do you have a record in tblListings where ListingsID=205 and
Click to expand...
Status_ID said:
I am having a bit of problem with my comparison query which flags/trackes
changes to a particular field - tblListings.Status_ID - based on the
comparison btw the primary table [tblListings] and the shadow table
[audListings].

Currently the query IS picking up the change to a particular boat listing
[ListingID=203]and the Status_ID for the date 9/8/2003 but not for today
9/9/2003 and the different boat listings [ListingsID = 202,203,205] ..

You can view the actual changes in SCREENSHOT for the shadow table
[AudListings] right here:

http://69.2.200.70/catamaranco/images/screenshot/audListings.jpg

...to see what I mean.

But Here is what is actually returned my query:

http://69.2.200.70/catamaranco/images/screenshot/qrytrackStatus_id.jpg

Thus, showing that the comparison query is only picking up ListingsID=203
for the date 9/8/2003 and not 202, 205 for 9/9/2003.

Could someone help explain why this is. Here is what it currently looks
like:

SELECT [tblListings].[ListingsID], "Market Status has changed" AS Flag,
[tblListings].[Name], [tblListings].[Status_ID],
[tblMarketStatus].[Market_Status], [audListings].[Status_ID],
[audListings].[AudDate]
FROM tblMarketStatus INNER JOIN (tblListings INNER JOIN audListings ON
[tblListings].[ListingsID]=[audListings].[ListingsID]) ON
[tblMarketStatus].[MarketStatusID]=[tblListings].[Status_ID]
WHERE ((([tblListings].[Status_ID])<>[audListings].[Status_ID]));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sub select to get name from id in another table 4
Connect two different queries for these outcomes 1

Top