help writing sql statement

[Silvio]

I need help in writing this statement: In short I have 2 tables with the
following relation - (Address) one-to-may (holds) – Desired outcome: Include
only records from tblAddress where **all** the related hold in tblHolds in
field DispoID is not null. A typical address has 1-5 hold so if any the olds
is still open (DispoID is null) then that address is excluded from the query.
Obviously what I have done is not working.


SELECT tblAddress.AddressID, tblAddress.AssignedToID, tblHolds.DispoID
FROM tblAddress LEFT JOIN tblHolds ON tblAddress.AddressID =
tblHolds.AddressID
GROUP BY tblAddress.AddressID, tblAddress.AssignedToID, tblHolds.DispoID
HAVING (((tblAddress.AssignedToID) Is Null) AND ((tblHolds.DispoID) Is Not
Null));

Thanks folks.

 

[Lord Kelvan]

umm ok your query is just bad first you shouldnt us a left join and
you dont use group by you would use distinct and you will need a
nested query to check if all holds are closed if you are using a
number datatype for the dispoidthen you may need to check for a 0
rather than a null depending on how you are entering data

SELECT tbladdress.addressid, tbladdress.AssignedToID, tblholds.DispoID
FROM tblholds INNER JOIN tbladdress ON tblholds.addressid =
tbladdress.addressid
WHERE (((tbladdress.addressid) Not In (SELECT tblholds.addressid
FROM tblholds
GROUP BY tblholds.DispoID, tblholds.addressid
HAVING (((tblholds.DispoID)=0) or ((tblholds.dispoID) is null)))));

that query will give you all the addresses and all their corrisponding
holds (note it cannot give you an address if there is no hold value

SELECT DISTINCT tbladdress.addressid, tbladdress.AssignedToID
FROM tbladdress
WHERE (((tbladdress.addressid) Not In (SELECT tblholds.addressid
FROM tblholds
GROUP BY tblholds.DispoID, tblholds.addressid
HAVING (((tblholds.DispoID)=0) or ((tblholds.dispoID) is null)))));

that query will give you just the address values (note it will give
you address values without holds)

SELECT tbladdress.addressid, tbladdress.AssignedToID, tblholds.DispoID
FROM tbladdress LEFT JOIN tblholds ON tbladdress.addressid =
tblholds.addressid
WHERE (((tbladdress.addressid) Not In (SELECT tblholds.addressid
FROM tblholds
GROUP BY tblholds.DispoID, tblholds.addressid
HAVING (((tblholds.DispoID)=0) or ((tblholds.dispoID) is null)))));

that query will give you all addressvalues and their corrisponding
holds (note this query will also give you addresses with no holds)

i hope one of these queries helps

Regards
Kelvan

 

[Silvio]

Lord thank you very much. This is what works - I don't have 0 values - Is
Null or a number:

SELECT tblAddress.AddressID
FROM tblAddress INNER JOIN tblHolds ON tblAddress.AddressID =
tblHolds.AddressID
WHERE (((tblAddress.AddressID) Not In (SELECT tblholds.AddressID
FROM tblHolds
GROUP BY tblHolds.DispoID, tblHolds.AddressID
HAVING ((tblHolds.DispoID) is null))))
GROUP BY tblAddress.AddressID;

I need to use the group by otherwise I see the same records as many time as
the number of Holds it has. However, by doing this the record becomes not
editable. So I create a second query to filter the record I want:

SELECT tblAddress.StNumber, tblAddress.StName, tblAddress.AssignedToID,
tblAddress.AddressID
FROM tblAddress
WHERE (((tblAddress.AddressID) In (select addressID from qryReassign)));

qryReassign is the name of the first query on top. Is there a way to achieve
the same result in a single query that is Editable (data from the tblAddress
only)?

Again, that you a million!

 

[Lord Kelvan]

you can avoid group by by using the distinct keyword but if you
directally join two tables you cannot edit the data which is why you
have to use a sub query

SELECT tblAddress.StNumber, tblAddress.StName,
tblAddress.AssignedToID,
tblAddress.AddressID
FROM tbladdress
WHERE (((tbladdress.addressid) Not In (SELECT tblholds.addressid
FROM tblholds
GROUP BY tblholds.DispoID, tblholds.addressid
HAVING (((tblholds.dispoID) is null)))));

that query will let it be editible but it will also select addresses
that have no hold values

SELECT tblAddress.StNumber, tblAddress.StName,
tblAddress.AssignedToID,
tblAddress.AddressID
FROM tbladdress
WHERE (((tbladdress.addressid) In (SELECT tblholds.addressid
FROM tblholds
GROUP BY tblholds.addressid, tblholds.DispoID
HAVING (((tblholds.addressid) Not In (SELECT tblholds.addressid
FROM tblholds
GROUP BY tblholds.DispoID, tblholds.addressid
HAVING (((tblholds.dispoID) is null))))))));

that query will give you the same thing but it wont give you addresses
that dont have hold values

you wont get duplicates because you are not joining tables

hope this helps

Regards
Kelvan

 

[Silvio]

Perfect! Just what I wanted. Thank you 2 millions.