Thanks Rob the table has 4 fields, HorseID,OwnerID,Percentage,Invoicing
I dont really mind which owners and how many are getting invoiced the
problem is if you tick the checkbox by there name on the Horse form they
will not get invoiced, Now if a horse does not have an Owner or all owners
are ticked for no Invoice my Db stalls when I distribute the invoices at
the end of the month, I already have an "Alarm bell" if a Horse has no
owner, but now I need another just in case I have ticked all owner in that
horse,even if its just one owner and i tick him , I will be in
trouble......Thanks Bob (Tick Means No Invoice)
Rob Parker said:
And a follow-up: Perhaps ClientID is on both tables. If so, would you
please give a full description of your tables and all (possibly relevant)
fields).
Thanks,
Rob
Rob Parker said:
So, to check that I'm understanding this correctly, for your first
scenario:
"HorseID number 10 has 2 clients one is ticked in Invoicing and the
other not"
The query you are wanting should return HorseID 10 in this case.
Since this will have to involve the ClientID (because you are actually
interested in the combination of HorseID with ClientID), can you please
tell me which table this field is in.
Do you want to also know which ClientID was not invoiced?
Rob
Dales Expression is only giving me a zero when Invoicing is ticked!
e.g.: HorseID number 10 has 2 clients one is ticked in Invoicing and
the other not so im getting a zero and a 1 in expression
What I need to determine is that if there is another HorseID with the
same number and it is not ticked *NO* alarm bells
Only Alarm bells if same HorseID and Invoicing are ticked and there
are no other HorseID with the same Number un-ticked
Thanks Bob
message Hi Bob,
Seems to me that the solution Dale posted a bit later should do what
you want.
Rob
Rob by doing a group by query , all I need is some expression that
will give me
If [HorseID] has a tick in [Invoicing] and there is No other
[HorsesID] with the same number, this is the Horse I want to show !
Thanks for any Help ................Bob
SELECT tblHorseDetails.HorseID, tblHorseDetails.Invoicing,
qryHorseNameAll.Name
FROM tblHorseDetails INNER JOIN qryHorseNameAll ON
tblHorseDetails.HorseID = qryHorseNameAll.HorseID
GROUP BY tblHorseDetails.HorseID, tblHorseDetails.Invoicing,
qryHorseNameAll.Name;
message Hi Bob,
It's becoming a little clearer, but if what you want involves the
ClientID, then that field MUST be included in the query somewhere.
Which table is it in, and what datatype is it?
More example data would help. What you've given here looks like the
output from your original query. What I need is the data in both
tables, and what you want in your output.
Rob
Thanks Rob, I will show you what I mean, because the horse may have
2 or 3 Clients in it, there is a Client ID that I have not enetered
they all come from the same table tblHorseDetails
HorseID (Number) Invoicing (Check Box)
12 yes
12 yes
12
23 yes
34
34
45
56 yes
56 yes
56 yes
67
78 yes
78
So the result shoub be 23 and 56 because they have NO Invoicing
Client
Hope you can understand this...................thanks,Bob
message Hi Bob,
I'm not sure I understand what you mean by (Not Unique) Records.
The query you currently have will give all you horses, and the
invoicing field for those which have any invoicing records. If
you are wanting to exclude horses with no invoicing records,
simply make the join a RIGHT JOIN, rather than a LEFT JOIN.
If you mean something else, could you please explain further,
and/or illustrate with some sample data.
HTH,
Rob
Is it possible to have (Not Unique) Records!
I want to show the Not Unique records of HorseID (Number) and
Invoicing (Yes/No)
Tahnks if you can help...........Bob
SELECT tblHorseInfo.HorseID, tblHorseDetails.Invoicing
FROM tblHorseInfo LEFT JOIN tblHorseDetails ON
tblHorseInfo.HorseID = tblHorseDetails.HorseID
ORDER BY tblHorseInfo.HorseID;
