query filtering

[eyalco]

I have a database of kindergarten in which there are parents and children of course. There are cases where a parent has few kids there.
I built a query which colects the names and e-mails where I send them pictures etc'.

The problem : parents with few kids are getting few of the same e-mails.
Question : how do I filter the query that the same parent of few kids will get only 1 e-mail please?

Thanks.

[NeoPa]

Easily done.
Much easier if we know the table layout though.

You can probably simply use the DISTINCT predicate of the SELECT clause. Post your info and we can tidy it up a bit for you.

[eyalco]

I don't know SQL but here's the sql basis of my query :

SELECT TBL_NEW_CHILDRE N.FAMILY_ID, [FAMILY] & "," & " " & [MOTHER_name] AS name, TBL_NEW_FAMILY.[E-MAIL], TBL_NEW_CHILDRE N.FIRST_NAME, TBL_NEW_CHILDRE N.STATUS
FROM TBL_NEW_FAMILY INNER JOIN TBL_NEW_CHILDRE N ON TBL_NEW_FAMILY. ID_FAMILY = TBL_NEW_CHILDRE N.FAMILY_ID
WHERE (((TBL_NEW_FAMI LY.[E-MAIL]) Is Not Null) AND ((TBL_NEW_CHILD REN.STATUS)="ac tive"));

Thanks for your help.

[NeoPa]

First, let's tidy up the SQL so it's not a complete mess. SQL works fine in any form, but working WITH SQL can be very messy unless you impose some structure on it (Sharing it with others is certainly not to be recommended in that form).

SELECT NC.FAMILY_ID,
       [FAMILY] & ", " & [MOTHER_name] AS Name,
       NF.[E-MAIL],
       NC.FIRST_NAME,
       NC.STATUS
FROM TBL_NEW_FAMILY AS NF INNER JOIN TBL_NEW_CHILDREN AS NC
  ON NF.ID_FAMILY = NC.FAMILY_ID
WHERE ((NF.E-MAIL Is Not Null) AND (NC.STATUS = 'active'))

You will notice from the equivalent tidy SQL, that it doesn't really answer my question. It gives very little information as to the structure of the tables that it uses. It doesn't even specify where [Family] & [Mother_Name] are to be found.
What is probably more important is that it implies that you want the First_Name data from the Tbl_New_Childre n table, which makes little sense if you're asking for the data to be restricted to one item only from the Tbl_New_Family table.
Perhaps you could give some thought to exactly what you DO want and we can help to resolve your issue for you.

[eyalco]

Thanks again.
I have 2 relevant tables : tbl_new_family & tbl_new_childre n, connected by id_family field.
All the parents' details are concentrated in the t_n_f, including the e-mail so the filter should focus on the parents tbl (t_n_f) so only 1 family will appear each time on the query.

That's about what I think - if you need anymore info please direct me to the specific details you might need.

Again, thanks for your help.

BTW, it's the best forum i've ever stumbled upon in access and all.

[NeoPa]

Well, my main problem, expressed as clearly as I can, is that your query wants to display fields from the (t_n_c) table ([First_Name] & [Status]). How can it possibly do that if we restrict the output to one record only from the (t_n_f) table?

There are possibilities. We can look at any one random record from (t_n_c) if you like (how much use would that be?) We could select it on some other specified criteria (No criteria have been specified to date). We could even drop it from the results completely (makes most sense to me as displaying it seems to make little sense at all.)

I can only know what you intend to do if you tell me though. When I know, I'm sure I'll be able to guide you in the right direction (assuming what you want makes logical sense).

[NeoPa]

To illustrate the question, imagine your (t_n_c) table (or that part that refers to the Brooks family at least) contains the data below.

Family_ID    Family    Mother_Name   Status   First_Name
     1       Brooks    Emily         Active   Andrew
     1       Brooks    Emily         Active   Betty
     1       Brooks    Emily         Active   Charles

and the (t_n_f) table has :

ID_Family    Family    E-Mail
     1       Brooks    eb@TheBrooks.com

Clearly then, you would have :
Family_ID = 1
Name = "Brooks, Emily"
E-Mail = "eb@TheBrooks.c om"
Status = "Active"
But what about First_Name? What should go in there? We don't have an obvious fit. Something needs to be specified to determine what information to display.

BTW Welcome to theScripts :)

[eyalco]

Thanks again.

Your 2nd post is what I'm trying to achieve : I want to send e-mails to each family with photos of their children in the kindergarten.

So what you illustrated in your 2nd post is my goal - to have 1 family, 1 e-mail for me to send these photos so if I have a family with 3 children there, they won't receive 3 emails ....

How do I filter my query so the end result is what you showed ?

[NeoPa]

How about you read my post and answer the question therein.
I went to great length to explain that what you're asking for doesn't make sense as you've asked it. It's logically contradictory.
However, if you were actually to answer the question in my post (#7), I'm sure we could proceed.

[eyalco]

Ok, saw it.
The first_name is the child's name that I put there only for internal identification, as I wanted to relate the parent's name to the child's (I can do without it).

Sorry for not noticing it...

[NeoPa]

If the First_Name is not required, it all becomes a lot more straightforward . We still need the (t_n_c) table as the status is held there (that we need to check), but otherwise it's mostly coming from the (t_n_f) table.

SELECT DISTINCT T_N_F.ID_FAMILY,
                T_N_F.FAMILY & ', ' & T_N_C.MOTHER_name AS Name,
                T_N_F.[E-MAIL],
                T_N_C.STATUS
FROM TBL_NEW_FAMILY AS T_N_F INNER JOIN TBL_NEW_CHILDREN AS T_N_C
  ON T_N_F.ID_FAMILY = T_N_C.FAMILY_ID
WHERE ((T_N_F.E-MAIL Is Not Null) AND (T_N_C.STATUS = 'active'))

As I still don't know which table the [FAMILY] & [MOTHER_name] fields come from I've guessed. You will need to correct the tables there if I've mis-guessed but logically, they should be where I've put them in the SQL.

BTW If the [STATUS] field is entered (rather than set by code) you should set T_N_C.STATUS to lower case explicitly (WHERE ... (LCase(T_N_C.ST ATUS) = 'active') ...).

[NeoPa]

NB. If I'm right and the MOTHER_name field is actually in the (t_n_c) table then children of the same family coud possibly have different data in this field.
This would result in multiple lines coming out for any such family using the SQL provided. We can do it differently if that's required, but I can only provide a solution to a problem as it's defined.
Let me know if this suits your requirements.

[eyalco]

You are the greatest !!!
Took me a while to play with but finally I managed (with smal adjustments)!!!

I appreciate your patience and your help.

[NeoPa]

No problems :)
Glad to hear you're sorted.