Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2002
    Location
    Guernsey
    Posts
    13

    Unanswered: MS Access UNION query problem

    Hi

    I have a database where I need to have a subform as part of my main form. Within this subform, the data needs to be retrieved from a union query, similar to this:

    [code]
    SELECT [name], [id_gained], [address_gained] FROM [tbl_People] WHERE [related_party_1] = 3756 AND [status] = 'Active'
    UNION
    SELECT [name], [id_gained], [address_gained] FROM [tbl_People] WHERE [related_party_2] = 3756 AND [status] = 'Active'
    [code]

    However, because the related party bit needs to change, I am struggling to find a way to do this. There can be up to 5 related parties (I shortened it to make easier reading, as all unions will be the same, just with the related_party number changing)

    Is there a way to get this query information into my subform without having to write the same query for each possible party and then linking that in (still doesn't really work)

    Thanks

  2. #2
    Join Date
    Dec 2002
    Posts
    60

    Re: MS Access UNION query problem

    If the [related_party_x] number (3756 in your example) is contained in a control on your main form (assuming it is a number, not a text field):

    "SELECT [name], [id_gained], [address_gained] FROM [tbl_People] WHERE [related_party_1] =" & Forms![frmName]![ControlName] & " AND [status] = 'Active'
    UNION...

    Originally posted by adamcox
    Hi

    I have a database where I need to have a subform as part of my main form. Within this subform, the data needs to be retrieved from a union query, similar to this:

    [code]
    SELECT [name], [id_gained], [address_gained] FROM [tbl_People] WHERE [related_party_1] = 3756 AND [status] = 'Active'
    UNION
    SELECT [name], [id_gained], [address_gained] FROM [tbl_People] WHERE [related_party_2] = 3756 AND [status] = 'Active'
    [code]

    However, because the related party bit needs to change, I am struggling to find a way to do this. There can be up to 5 related parties (I shortened it to make easier reading, as all unions will be the same, just with the related_party number changing)

    Is there a way to get this query information into my subform without having to write the same query for each possible party and then linking that in (still doesn't really work)

    Thanks

  3. #3
    Join Date
    Mar 2002
    Location
    Guernsey
    Posts
    13

    Re: MS Access UNION query problem

    Thanks, that was excellent bc301, worked first time! My troubles are over!!

    Thanks again,

    Adam

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A better question is why are you using a UNION query? All your data is pulled from a single table with simple criteria. This syntax will be faster:

    SELECT [name], [id_gained], [address_gained]
    FROM [tbl_People]
    WHERE [status] = 'Active'
    and ([related_party_1] = " & Forms![frmName]![ControlName1]
    or [related_party_2] = " & Forms![frmName]![ControlName2]
    or [related_party_3] = " & Forms![frmName]![ControlName3]
    or [related_party_4] = " & Forms![frmName]![ControlName4]
    or [related_party_5] = " & Forms![frmName]![ControlName5])

    You may need to use SELECT DISTINCT.

    I also have to wonder whether further normalizing your table design might make your coding a lot easier.

    blindman

  5. #5
    Join Date
    Mar 2002
    Location
    Guernsey
    Posts
    13
    Thanks blindman, you are right, that is a lot faster, and it shows checkboxes for Yes/No fields rathe than -1/0 like the previous query. Just one thing to point out though, when you wrote
    [related_party_2] = " & Forms![frmName]![Cont
    You didn't need the " & part of things, they could just be left out.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your welcome, and "oops" about the ampersand. I didn't have a copy of Access at hand, so I was drafting the SQL off the top of my head and I forgot to remove the ampersand I copied from bc301's code.

    blindman

Posting Permissions

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