ACCESS FEED: [MS_AccessPros] query

Rabu, 31 Agustus 2011

[MS_AccessPros] query

Hi all!

I think this is simple BUT can't get my head around it.

I have two tables--Listings and ListingsMiscCat which joins ListID with MiscCatID. So one ListID can have several MiscCatID. The code I have below also joins the data with other tables.

I'd like to create a query where if there are no MiscCatID for a ListID or one of the MiscCatID's for a ListID is 4, that ListID is not in the result.

I have created one which works for null or if there's one MiscCatID for a ListID and that MiscCat=4. But if there are two MiscCatID's for a ListID, the one with the MiscCat<>4 shows up in the results.

Thanks!
Connie

SELECT Listings.ListID, Pending.PendingID, Listings.PropertyName, ConcatRelated("FullName1","qZ1ListingContactsCurrent","ListID = " & [Listings].[ListID]) & " L# "+[Listings].[LockboxNbr] AS KeyContacts, Listings.TelCombo, IIf([Listings].[ZoningID]=5," L",Left([Occupancy_List].[Occupancy],1)) AS Oc, qAddressCity.AddressCity AS Address, qCurrentPriceAll.CurrentPrice, Listings.ExpDate, ConcatRelated("Email","qZ2ListingContactsEmailNoDupes","ListID = " & [Listings].[ListID])+" " & [Listings].[XtraInfo] AS Email, Listings.ListingAgency, Listings.CurrentListing
FROM ((((qAddressCity INNER JOIN Listings ON qAddressCity.HomeInfoID = Listings.HomeInfoID) LEFT JOIN Occupancy_List ON Listings.OccupancyID = Occupancy_List.OccupancyID) INNER JOIN qCurrentPriceAll ON Listings.ListID = qCurrentPriceAll.ListID) LEFT JOIN ListingsMiscCat ON Listings.ListID = ListingsMiscCat.ListID) LEFT JOIN Pending ON Listings.ListID = Pending.ListID
WHERE (((Listings.ListingAgency)=1) AND ((Listings.CurrentListing)=-1) AND ((ListingsMiscCat.ListID) Is Null)) OR (((Listings.ListingAgency)=1) AND ((Listings.CurrentListing)=-1) AND ((ListingsMiscCat.ListID)<>4));