Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Union Queries (XP / 2000)

    Hi, I have a couple of questions...

    1. I have got two tables, one of adults and one of children, they are connected through the adult ID as a one to many relationship as you might imagine. I want to make a query that will give all people, both adults and children with birthdays on a certain month (which is an input parametere on running the query).
    Is the only way to do theis with a SQL UNION query? I have tried with a normal query, using the 'DatePart' function and OR fields on the criteria, but get some rather strange results. I have tried changing the join type on the relationship between the tables in the query, and even removing it, but cannot get the results i desire.

    Whilst the SQL UNION i did works fine, i was wondering if there is any way of retrieving info from two tables at the same time form a normal query? EG, all adults and children with birthdays in december...

    2. How do i make a YES/NO field to show 'Male' and 'Female' in a dropdown box and enter the respective number (0,-1) in the field?
    I have done the "Male";"Female" thing in Format, and then gone to Lookup tab and selected 'Combobox'. I have found if in the row source i enter "Male";"Female" this is what shows up in the combobox, but how do i get this dropdown to link to a -1 and 0 as the data to store in the yes/no field?

    Forgive the obtuse questions! <img src=/S/bif.gif border=0 alt=bif width=70 height=28>
    Thanks,

    pmatz

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

    Re: Union Queries (XP / 2000)

    1. I guess that you want to create one list, mixing names from both tables. That is typically what you do with a union query.

    2. You should use two columns in the combo box: the first one (hidden) contains the boolean value, the second (displayed) contains the text. So set Column Count to 2 and Column Widths to 0", 1". There are two ways to specify the Row Source:
    - Set Row Source Type to Value List, and set Row Source to 0,"Male",1,"Female"
    - Create a small lookup table with two fields ID (Yes/No, primary key) and Gender (Text).

    <table border=1><td>ID</td><td>Gender</td><td align=right>0</td><td>Male</td><td align=right>-1</td><td>Female</td></table>
    Set Row Source Type of the combo box to Table/Query and Row Source to the name of the table.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Queries (XP / 2000)

    Cheers Hans, thats cool. I think i will use the first option regards the boolean dropdown.

    Regards
    Thanks,

    pmatz

Posting Permissions

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