Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    2 Fields into 1 (2000/SR1)

    I have a database that keeps track of attendance at our school. I have a Family table and a student table. The students are tied to the family table by a FamilyID number. In the family table, I have a field for Mother and one for Father.

    We have different things that parents can volunteer for. What I want to do is create a form where I can pull down the Mother's name or the Father's name into one field and then I will have the other information on the form, such as what and when, which will come from the VolunteerJob table.

    My question is, how do I pull either the mother or the father into just one field? I don't know whether I need to create a new table or if I can do this in a query. If both Mother and Father volunteer, can I pull both names into one field or would I set up 2 different records for each of the volunteers. I would also like to be able to add a name "on the fly" if I need to, for instance, sometimes the teenage children will volunteer to help but I don't have them in the database, and they would come from a field anyway if I did. The other way I thought about doing this is to only have the Last Name of the family, which would be connected by the FamilyID, on my form and then type in the first name but it seems like I should use the information that has already been filled in.

    Thanks for any help you can give me.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2 Fields into 1 (2000/SR1)

    You might create a union query to get a list of the fathers and mothers. You *must* write a union query in SQL, the query design view can't handle it. It could look like this (of course, with appropriate table and field name substituted):

    SELECT LastName, Father FROM tblFamilies WHERE Father Is Not Null
    UNION
    SELECT LastName, Mother FROM tblFamilies WHERE Mother Is Not Null

    If you want other volunteers to appear in the list too, you could create a separate table tblOtherVolunteers and add this to the query:

    SELECT LastName, Father FROM tblFamilies WHERE Father Is Not Null
    UNION
    SELECT LastName, Mother FROM tblFamilies WHERE Mother Is Not Null
    UNION
    SELECT LastName, FirstName FROM tblOtherVolunteers

    You can use this query as Row Source for a Volunteer combo box. Set the ListOnly property to Yes, and write code in the NotOnList event to handle the case that a new name is entered in the combo box, and add it to tblOtherVolunteers if required. If you search this Forum for NotInList, you will find examples of NotInList event procedures.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2 Fields into 1 (2000/SR1)

    Maybe you build a small form with just the ID, Parets names and last name. In the form, the parents first names will be in separate fields. You can also program some check boxes, one for each name. So, you open a record and select one or both names, then click on a button that uses that information to open a full form with the volunteer info. It will use the names based on the check boxes you progammed.

    This is just brainstorming. If you're a tinkerer... this could be an afternoon project.

    What happens if the parents are split up. Do you have First and Last for one Parent and a different LAST for another (remarried, etc?)

  4. #4
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: 2 Fields into 1 (2000/SR1)

    Thanks Han,

    It took me a few days to get to it and work it out but once I did, it's exactly what I need. Thanks again for the assist.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •