Help with a Query

[Bob Vance]

My query has 4 fields

All Records "Active"
HorseID (ID Number)
Invoicing1 (Yes/No)
Invoicing2 (True/False, Check Box)

What I am trying to achieve is to show records Only thats are
Active,HorseID that all have the same ID number, No, True

This would Query
Active| 12 | No | True
Active| 12 | No | True

This Would not Query
Active |23| No| True
Active |23| No| False

This would Query
Active|34| No|True

This Would not Query
Active|40|No|False

Thanks for any Help.....Bob

 

[Tom Wickerath]

Hi Bob,

Perhaps you can use a subquery to solve this request. Something like this,
where "TableName" is the name of your table (make the appropriate
substitutions):

SELECT [All Records], HorseID, Invoicing1, Invoicing2
FROM TableName
WHERE [All Records]="active"
AND HorseID Not In (
SELECT HorseID
FROM TableName
WHERE Invoicing2=0)
ORDER BY HorseID;


For more information on learning to use subqueries, please see Access MVP
Allen Browne's page on this topic:

Subquery basics
http://allenbrowne.com/subquery-01.html

Surviving Subqueries
http://allenbrowne.com/subquery-02.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[gllincoln]

Hi Bob,

You could try it this way...

SELECT MyTable.HorseID, MyTable.[All Records], MyTable.Invoicing1,
MyTable.Invoicing2
FROM MyTable
WHERE (((MyTable.HorseID) Not In (Select HorseID as tmp FROM MyTable WHERE
Invoicing2=0))
AND ((MyTable.[All Records])="Active")
AND ((MyTable.Invoicing1)=No)
AND ((MyTable.Invoicing2)=-1));

Hope This Helps,
Gordon

 

[Bob Vance]

Thanks Guys, I've tried to make it simpler
If All the same [HorseID] numbers have a tick in [Invoicing], they are to
show
So No horse is meant to have all its [Invoicing] ticked
Thanks for all your help Bob

SELECT qryHorseNameAll.Name, tblHorseDetails.HorseID,
tblHorseDetails.OwnerID, tblHorseDetails.Invoicing, tblHorseInfo.Status
FROM (tblHorseDetails INNER JOIN tblHorseInfo ON tblHorseDetails.HorseID =
tblHorseInfo.HorseID) INNER JOIN qryHorseNameAll ON tblHorseInfo.HorseID =
qryHorseNameAll.HorseID
WHERE (((tblHorseInfo.Status) Like "Active"));

Hi Bob,

You could try it this way...

SELECT MyTable.HorseID, MyTable.[All Records], MyTable.Invoicing1,
MyTable.Invoicing2
FROM MyTable
WHERE (((MyTable.HorseID) Not In (Select HorseID as tmp FROM MyTable WHERE
Invoicing2=0))
AND ((MyTable.[All Records])="Active")
AND ((MyTable.Invoicing1)=No)
AND ((MyTable.Invoicing2)=-1));

Hope This Helps,
Gordon

My query has 4 fields

All Records "Active"
HorseID (ID Number)
Invoicing1 (Yes/No)
Invoicing2 (True/False, Check Box)

What I am trying to achieve is to show records Only thats are
Active,HorseID that all have the same ID number, No, True

This would Query
Active| 12 | No | True
Active| 12 | No | True

This Would not Query
Active |23| No| True
Active |23| No| False

This would Query
Active|34| No|True

This Would not Query
Active|40|No|False

Thanks for any Help.....Bob

 

[Tom Wickerath]

Hi Bob,

So you've got what you need, or do you still need help?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________