By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,155 Members | 2,518 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,155 IT Pros & Developers. It's quick & easy.

query filtering

P: 38
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.
Dec 10 '07 #1
Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,616
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.
Dec 10 '07 #2

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

SELECT TBL_NEW_CHILDREN.FAMILY_ID, [FAMILY] & "," & " " & [MOTHER_name] AS name, TBL_NEW_FAMILY.[E-MAIL], TBL_NEW_CHILDREN.FIRST_NAME, TBL_NEW_CHILDREN.STATUS
FROM TBL_NEW_FAMILY INNER JOIN TBL_NEW_CHILDREN ON TBL_NEW_FAMILY.ID_FAMILY = TBL_NEW_CHILDREN.FAMILY_ID
WHERE (((TBL_NEW_FAMILY.[E-MAIL]) Is Not Null) AND ((TBL_NEW_CHILDREN.STATUS)="active"));
Thanks for your help.
Dec 11 '07 #3

NeoPa
Expert Mod 15k+
P: 31,616
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).
Expand|Select|Wrap|Line Numbers
  1. SELECT NC.FAMILY_ID,
  2.        [FAMILY] & ", " & [MOTHER_name] AS Name,
  3.        NF.[E-MAIL],
  4.        NC.FIRST_NAME,
  5.        NC.STATUS
  6. FROM TBL_NEW_FAMILY AS NF INNER JOIN TBL_NEW_CHILDREN AS NC
  7.   ON NF.ID_FAMILY = NC.FAMILY_ID
  8. 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_Children 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.
Dec 11 '07 #4

P: 38
Thanks again.
I have 2 relevant tables : tbl_new_family & tbl_new_children, 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.
Dec 11 '07 #5

NeoPa
Expert Mod 15k+
P: 31,616
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).
Dec 11 '07 #6

NeoPa
Expert Mod 15k+
P: 31,616
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.
Expand|Select|Wrap|Line Numbers
  1. Family_ID    Family    Mother_Name   Status   First_Name
  2.      1       Brooks    Emily         Active   Andrew
  3.      1       Brooks    Emily         Active   Betty
  4.      1       Brooks    Emily         Active   Charles
and the (t_n_f) table has :
Expand|Select|Wrap|Line Numbers
  1. ID_Family    Family    E-Mail
  2.      1       Brooks    eb@TheBrooks.com
Clearly then, you would have :
Family_ID = 1
Name = "Brooks, Emily"
E-Mail = "eb@TheBrooks.com"
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 :)
Dec 11 '07 #7

P: 38
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 ?
Dec 12 '07 #8

NeoPa
Expert Mod 15k+
P: 31,616
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.
Dec 12 '07 #9

P: 38
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...
Dec 13 '07 #10

NeoPa
Expert Mod 15k+
P: 31,616
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT T_N_F.ID_FAMILY,
  2.                 T_N_F.FAMILY & ', ' & T_N_C.MOTHER_name AS Name,
  3.                 T_N_F.[E-MAIL],
  4.                 T_N_C.STATUS
  5. FROM TBL_NEW_FAMILY AS T_N_F INNER JOIN TBL_NEW_CHILDREN AS T_N_C
  6.   ON T_N_F.ID_FAMILY = T_N_C.FAMILY_ID
  7. 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.STATUS) = 'active') ...).
Dec 13 '07 #11

NeoPa
Expert Mod 15k+
P: 31,616
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.
Dec 13 '07 #12

P: 38
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.
Dec 14 '07 #13

NeoPa
Expert Mod 15k+
P: 31,616
No problems :)
Glad to hear you're sorted.
Dec 14 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.