Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Combining fields from the same table (Possibly with Union?)

Discussion in 'Information Technology' started by Shaun Rucker, Jul 28, 2006.

  1. Shaun Rucker

    Shaun Rucker
    Expand Collapse
    Guest

    I have one large table from which many different forms spawn. Each form has
    its own unique fields, but most all of them contain the same data. For
    instance, I have three different fields that contain company codes, but they
    are named "Write Off Co Code", "Vendor Co Code" & "Check/Wire Co Code". I
    can't say why they used three different fields for the same data when this db
    was created, I wasn't here then.

    Anyway, I need to create a query on the three different sections of data:
    write offs, vendors and checks. How do I create a query (or make-table
    query) that will combine the identical fields? I figure a Union is what I
    need, but I don't know SQL to save my life and am not sure how to make it
    work since they are all in the same table in the first place.

    I'm assuming something like this:

    Select[Write Off Co Code] from [Adjustment Table]
    Union
    Select[Vendor Co Code] from [Adjustment Table]
    Union
    Select[Check/Wire Co Code] from [Adjustment Table]

    Then I don't know what to do next. I think I need an "As" statement after
    that, but not sure what it should look like. Maybe:

    As[Co Code] in [Adjustment Table]

    Any help is much welcome and appreciated.
     
  2. Loading...

    Similar Threads Forum Date
    US Defense Department Hiring Resident Aliens in Medical and Language Fields Business, Lifestyle & Leisure Dec 6, 2008
    Sikh News Women more vulnerable to cancer in Punjab's cotton fields (New Kerala) Breaking News Apr 1, 2008
    Sikh News Women more vulnerable to cancer in Punjab's cotton fields (Calcutta News) Breaking News Apr 1, 2008
    Sikh News Punjab's killing fields (The Times of India) Breaking News Feb 24, 2008
    Sikh News MQM fields 218 candidates in Punjab (The Pakistan Link) Breaking News Feb 1, 2008

  3. Wolfgang Kais

    Wolfgang Kais
    Expand Collapse
    Guest

    Hello Shaun.

    "Shaun Rucker" wrote
    > I have one large table from which many different forms spawn. Each
    > form has its own unique fields, but most all of them contain the same
    > data. For instance, I have three different fields that contain company
    > codes, but they are named "Write Off Co Code", "Vendor Co Code"
    > & "Check/Wire Co Code". I can't say why they used three different
    > fields for the same data when this db was created, I wasn't here then.
    >
    > Anyway, I need to create a query on the three different sections of
    > data: write offs, vendors and checks. How do I create a query
    > (or make-table query) that will combine the identical fields?
    > I figure a Union is what I need, but I don't know SQL to save my
    > life and am not sure how to make it work since they are all in the
    > same table in the first place.
    >
    > I'm assuming something like this:
    >
    > Select[Write Off Co Code] from [Adjustment Table]
    > Union
    > Select[Vendor Co Code] from [Adjustment Table]
    > Union
    > Select[Check/Wire Co Code] from [Adjustment Table]
    >
    > Then I don't know what to do next. I think I need an "As" statement after
    > that, but not sure what it should look like. Maybe:
    >
    > As[Co Code] in [Adjustment Table]


    Select [Write Off Co Code] As [Co Code] from [Adjustment Table]
    Union
    Select [Vendor Co Code] from [Adjustment Table]
    Union
    Select [Check/Wire Co Code] from [Adjustment Table]

    --
    Regards,
    Wolfgang
     
  4. Shaun Rucker

    Shaun Rucker
    Expand Collapse
    Guest

    Thanks Wolfgang, that works, mostly. I got that particular one to work. Now
    I need it to get much more complicated. I need to query my database and
    collect 11 different fields:
    1) Policy # (No Union statement needed for this field)
    2) Co Code
    3) Date Processed
    4) Assigned to (No Union statement needed for this field)
    5) Total
    6) Main/Sub
    7) Account Number
    8) SAP Doc #
    9) Authorized By
    10) Department
    11) Reason

    I need to add text boxes using a Between...And statement for the user to
    enter the date range needed. The other issue I am not so sure about is that
    some of the values for "Authorized By" and "Department" are calculated values
    from another query. I attempted to use the same naming logic for the query,
    but I don't think it's correct. Here is what I came up with for my query.

    SELECT [Policy Number] From [Adjustment Table]

    Select [Write Off Company Codes] As [Co Code] from [Adjustment Table] Union
    Select [Vendor SAP Company Codes] from [Adjustment Table] Union Select
    [Check/Wire Company Codes] from [Adjustment Table]

    Select [Write Off Date Processed] as [Date Processed] from [Adjustment
    Table] Union Select [Vendor Date Processed] from [Adjustment Table] Union
    Select [Check/Wire Date Processed] from [Adjustment Table] Where ([Date
    Processed] Between [Enter Start Date] And [Enter End Date])

    Select [Assigned To] From [Adjustment Table]

    Select [Write Off Amount Total] As [Amount] from [Adjustment Table] Union
    Select [Vendor Amount of Refund] from [Adjustment Table] Union Select
    [Check/Wire Amount Total] from [Adjustment Table]

    Select [Write Off Main/Subs] As [Main/Sub] from [Adjustment Table] Union
    Select [Vendor Main/Subs] from [Adjustment Table] Union Select [Check/Wire
    Main/Subs] from [Adjustment Table]

    Select [Write Off Account Numbers] As [Account Number] from [Adjustment
    Table] Union Select [Vendor Account Numbers] from [Adjustment Table] Union
    Select [Check/Wire Main/Subs2] from [Adjustment Table]

    Select [Write Off SAP Doc Number] As [Doc Number] From [Adjustment Table]
    Union Select [Vendor Document Number] From [Adjustment Table] Union Select
    [Check/Wire SAP Document Number] from [Adjustment Table]

    Select [Write Off Authorized By] As [Authorized] From [Adjustment Table]
    Union Select [Vendor Authorized By] From [Adjustment Table] Union Select
    [Expr1] From [qry @Deb_Monthly_W/O_Rpt_Legal/SL_Checks]

    Select [Department Name] As [Dept] From [Adjustment Table] Union Select
    [Vendor Authorized Departments] From [Adjustment Table] Union Select [Exp2]
    From [qry @Deb_Monthly_W/O_Rpt_Legal/SL_Checks]

    Select [Write Off Claims Reason] As [Reason] From [Adjustment Table] Union
    Select [Vendor Reason] From [Adjustment Table] Union Select [Check/Wire
    Request Reason] From [Adjustment Table]

    If I tell it to Run, I get an error "Syntax Error in FROM Clause". I'm
    assuming that is in my very first statement.

    Thanks again for your help!
    Shaun

    "Wolfgang Kais" wrote:

    > Hello Shaun.
    >
    > "Shaun Rucker" wrote
    > > I have one large table from which many different forms spawn. Each
    > > form has its own unique fields, but most all of them contain the same
    > > data. For instance, I have three different fields that contain company
    > > codes, but they are named "Write Off Co Code", "Vendor Co Code"
    > > & "Check/Wire Co Code". I can't say why they used three different
    > > fields for the same data when this db was created, I wasn't here then.
    > >
    > > Anyway, I need to create a query on the three different sections of
    > > data: write offs, vendors and checks. How do I create a query
    > > (or make-table query) that will combine the identical fields?
    > > I figure a Union is what I need, but I don't know SQL to save my
    > > life and am not sure how to make it work since they are all in the
    > > same table in the first place.
    > >
    > > I'm assuming something like this:
    > >
    > > Select[Write Off Co Code] from [Adjustment Table]
    > > Union
    > > Select[Vendor Co Code] from [Adjustment Table]
    > > Union
    > > Select[Check/Wire Co Code] from [Adjustment Table]
    > >
    > > Then I don't know what to do next. I think I need an "As" statement after
    > > that, but not sure what it should look like. Maybe:
    > >
    > > As[Co Code] in [Adjustment Table]

    >
    > Select [Write Off Co Code] As [Co Code] from [Adjustment Table]
    > Union
    > Select [Vendor Co Code] from [Adjustment Table]
    > Union
    > Select [Check/Wire Co Code] from [Adjustment Table]
    >
    > --
    > Regards,
    > Wolfgang
    >
    >
    >
     
  5. Wolfgang Kais

    Wolfgang Kais
    Expand Collapse
    Guest

    Shaun Rucker wrote:
    >>> I have one large table from which many different forms spawn.
    >>> Each form has its own unique fields, but most all of them contain
    >>> the same data. For instance, I have three different fields that
    >>> contain company codes, but they are named "Write Off Co Code",
    >>> "Vendor Co Code" & "Check/Wire Co Code". I can't say why they used
    >>> three different fields for the same data when this db was created,
    >>> I wasn't here then.
    >>> Anyway, I need to create a query on the three different sections
    >>> of data: write offs, vendors and checks. How do I create a query
    >>> (or make-table query) that will combine the identical fields?
    >>> I figure a Union is what I need, but I don't know SQL to save my
    >>> life and am not sure how to make it work since they are all in the
    >>> same table in the first place.
    >>>
    >>> I'm assuming something like this:
    >>>
    >>> Select[Write Off Co Code] from [Adjustment Table]
    >>> Union
    >>> Select[Vendor Co Code] from [Adjustment Table]
    >>> Union
    >>> Select[Check/Wire Co Code] from [Adjustment Table]
    >>>
    >>> Then I don't know what to do next. I think I need an "As"
    >>> statement after that, but not sure what it should look like.
    >>> Maybe: As[Co Code] in [Adjustment Table]


    >> Select [Write Off Co Code] As [Co Code] from [Adjustment Table]
    >> Union
    >> Select [Vendor Co Code] from [Adjustment Table]
    >> Union
    >> Select [Check/Wire Co Code] from [Adjustment Table]


    > Thanks Wolfgang, that works, mostly. I got that particular one to
    > work. Now I need it to get much more complicated. I need to query
    > my database and collect 11 different fields:
    > 1) Policy # (No Union statement needed for this field)
    > 2) Co Code
    > 3) Date Processed
    > 4) Assigned to (No Union statement needed for this field)
    > 5) Total
    > 6) Main/Sub
    > 7) Account Number
    > 8) SAP Doc #
    > 9) Authorized By
    > 10) Department
    > 11) Reason
    >
    > I need to add text boxes using a Between...And statement for the
    > user to enter the date range needed. The other issue I am not so
    > sure about is that some of the values for "Authorized By" and
    > "Department" are calculated values from another query.
    > I attempted to use the same naming logic for the query, but I don't
    > think it's correct. Here is what I came up with for my query.
    >
    > SELECT [Policy Number] From [Adjustment Table]
    >
    > Select [Write Off Company Codes] As [Co Code] from [Adjustment Table]
    > Union Select [Vendor SAP Company Codes] from [Adjustment Table]
    > Union Select [Check/Wire Company Codes] from [Adjustment Table]
    >
    > Select [Write Off Date Processed] as [Date Processed] from
    > [Adjustment Table]
    > Union Select [Vendor Date Processed] from [Adjustment Table]
    > Union Select [Check/Wire Date Processed] from [Adjustment Table] Where
    > ([Date Processed] Between [Enter Start Date] And [Enter End Date])
    >
    > Select [Assigned To] From [Adjustment Table]
    >
    > Select [Write Off Amount Total] As [Amount] from [Adjustment Table]
    > Union Select [Vendor Amount of Refund] from [Adjustment Table]
    > Union Select [Check/Wire Amount Total] from [Adjustment Table]
    >
    > Select [Write Off Main/Subs] As [Main/Sub] from [Adjustment Table]
    > Union Select [Vendor Main/Subs] from [Adjustment Table]
    > Union Select [Check/Wire Main/Subs] from [Adjustment Table]
    >
    > Select [Write Off Account Numbers] As [Account Number] from
    > [Adjustment Table]
    > Union Select [Vendor Account Numbers] from [Adjustment Table]
    > Union Select [Check/Wire Main/Subs2] from [Adjustment Table]
    >
    > Select [Write Off SAP Doc Number] As [Doc Number] From
    > [Adjustment Table]
    > Union Select [Vendor Document Number] From [Adjustment Table]
    > Union Select [Check/Wire SAP Document Number] from [Adjustment Table]
    >
    > Select [Write Off Authorized By] As [Authorized] From
    > [Adjustment Table]
    > Union Select [Vendor Authorized By] From [Adjustment Table]
    > Union Select [Expr1] From [qry @Deb_Monthly_W/O_Rpt_Legal/SL_Checks]
    >
    > Select [Department Name] As [Dept] From [Adjustment Table]
    > Union Select [Vendor Authorized Departments] From [Adjustment Table]
    > Union Select [Exp2] From [qry @Deb_Monthly_W/O_Rpt_Legal/SL_Checks]
    >
    > Select [Write Off Claims Reason] As [Reason] From [Adjustment Table]
    > Union Select [Vendor Reason] From [Adjustment Table]
    > Union Select [Check/Wire Request Reason] From [Adjustment Table]
    >
    > If I tell it to Run, I get an error "Syntax Error in FROM Clause".
    > I'm assuming that is in my very first statement.


    Does all this appear in ome query? This will indeed not work.
    What you will need is a union of 3 Select statements, each with
    a where clause for the particular date field.
    First you must create a query that selects (and calculates) the
    required "Check/Wire" values. Name them appropriately, not
    Expr1 or Exp2. If I assume that your query is named [qry_Check/Wire],
    thes this is probably what you need:

    PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
    Select [Policy Number],
    [Write Off Company Codes] As [Co Code],
    [Write Off Date Processed] As [Date Processed],
    [Assigned To],
    [Write Off Amount Total] As [Amount],
    [Write Off Main/Subs] As [Main/Sub],
    [Write Off Account Numbers] As [Account Number],
    [Write Off SAP Doc Number] As [Doc Number],
    [Write Off Authorized By] As [Authorized],
    [Department Name] As [Dept],
    [Write Off Claims Reason] As [Reason]
    From [Adjustment Table] Where ([Write Off Date Processed]
    Between [Enter Start Date] And [Enter End Date])
    Union
    Select [Policy Number], [Vendor SAP Company Codes],
    [Vendor Date Processed], [Assigned To],
    [Vendor Amount of Refund], [Vendor Main/Subs],
    [Vendor Account Numbers], [Vendor Document Number],
    [Vendor Authorized By], [Vendor Authorized Departments],
    [Vendor Reason]
    From [Adjustment Table] Where ([Vendor Date Processed]
    Between [Enter Start Date] And [Enter End Date])
    Union
    Select [Policy Number], [Check/Wire Company Codes],
    [Check/Wire Date Processed], [Assigned To],
    [Check/Wire Amount Total], [Check/Wire Main/Subs],
    [Check/Wire Main/Subs2], [Check/Wire SAP Document Number],
    [Check/Wire Authorized By], [Check/Wire Department Name],
    [Check/Wire Request Reason]
    From [qry_Check/Wire] Where ([Check/Wire Date Processed]
    Between [Enter Start Date] And [Enter End Date]);

    As siyd before, [qry_Check/Wire] must be created first and it's
    field names and the ones in the third Select statement of the Union
    query must be the same.
    I hope this helps.

    --
    Regards,
    Wolfgang
     
  6. Shaun Rucker

    Shaun Rucker
    Expand Collapse
    Guest

    "Wolfgang Kais" wrote:

    > Shaun Rucker wrote:
    > >>> I have one large table from which many different forms spawn.
    > >>> Each form has its own unique fields, but most all of them contain
    > >>> the same data. For instance, I have three different fields that
    > >>> contain company codes, but they are named "Write Off Co Code",
    > >>> "Vendor Co Code" & "Check/Wire Co Code". I can't say why they used
    > >>> three different fields for the same data when this db was created,
    > >>> I wasn't here then.
    > >>> Anyway, I need to create a query on the three different sections
    > >>> of data: write offs, vendors and checks. How do I create a query
    > >>> (or make-table query) that will combine the identical fields?
    > >>> I figure a Union is what I need, but I don't know SQL to save my
    > >>> life and am not sure how to make it work since they are all in the
    > >>> same table in the first place.
    > >>>
    > >>> I'm assuming something like this:
    > >>>
    > >>> Select[Write Off Co Code] from [Adjustment Table]
    > >>> Union
    > >>> Select[Vendor Co Code] from [Adjustment Table]
    > >>> Union
    > >>> Select[Check/Wire Co Code] from [Adjustment Table]
    > >>>
    > >>> Then I don't know what to do next. I think I need an "As"
    > >>> statement after that, but not sure what it should look like.
    > >>> Maybe: As[Co Code] in [Adjustment Table]

    >
    > >> Select [Write Off Co Code] As [Co Code] from [Adjustment Table]
    > >> Union
    > >> Select [Vendor Co Code] from [Adjustment Table]
    > >> Union
    > >> Select [Check/Wire Co Code] from [Adjustment Table]

    >
    > > Thanks Wolfgang, that works, mostly. I got that particular one to
    > > work. Now I need it to get much more complicated. I need to query
    > > my database and collect 11 different fields:
    > > 1) Policy # (No Union statement needed for this field)
    > > 2) Co Code
    > > 3) Date Processed
    > > 4) Assigned to (No Union statement needed for this field)
    > > 5) Total
    > > 6) Main/Sub
    > > 7) Account Number
    > > 8) SAP Doc #
    > > 9) Authorized By
    > > 10) Department
    > > 11) Reason
    > >
    > > I need to add text boxes using a Between...And statement for the
    > > user to enter the date range needed. The other issue I am not so
    > > sure about is that some of the values for "Authorized By" and
    > > "Department" are calculated values from another query.
    > > I attempted to use the same naming logic for the query, but I don't
    > > think it's correct. Here is what I came up with for my query.
    > >
    > > SELECT [Policy Number] From [Adjustment Table]
    > >
    > > Select [Write Off Company Codes] As [Co Code] from [Adjustment Table]
    > > Union Select [Vendor SAP Company Codes] from [Adjustment Table]
    > > Union Select [Check/Wire Company Codes] from [Adjustment Table]
    > >
    > > Select [Write Off Date Processed] as [Date Processed] from
    > > [Adjustment Table]
    > > Union Select [Vendor Date Processed] from [Adjustment Table]
    > > Union Select [Check/Wire Date Processed] from [Adjustment Table] Where
    > > ([Date Processed] Between [Enter Start Date] And [Enter End Date])
    > >
    > > Select [Assigned To] From [Adjustment Table]
    > >
    > > Select [Write Off Amount Total] As [Amount] from [Adjustment Table]
    > > Union Select [Vendor Amount of Refund] from [Adjustment Table]
    > > Union Select [Check/Wire Amount Total] from [Adjustment Table]
    > >
    > > Select [Write Off Main/Subs] As [Main/Sub] from [Adjustment Table]
    > > Union Select [Vendor Main/Subs] from [Adjustment Table]
    > > Union Select [Check/Wire Main/Subs] from [Adjustment Table]
    > >
    > > Select [Write Off Account Numbers] As [Account Number] from
    > > [Adjustment Table]
    > > Union Select [Vendor Account Numbers] from [Adjustment Table]
    > > Union Select [Check/Wire Main/Subs2] from [Adjustment Table]
    > >
    > > Select [Write Off SAP Doc Number] As [Doc Number] From
    > > [Adjustment Table]
    > > Union Select [Vendor Document Number] From [Adjustment Table]
    > > Union Select [Check/Wire SAP Document Number] from [Adjustment Table]
    > >
    > > Select [Write Off Authorized By] As [Authorized] From
    > > [Adjustment Table]
    > > Union Select [Vendor Authorized By] From [Adjustment Table]
    > > Union Select [Expr1] From [qry @Deb_Monthly_W/O_Rpt_Legal/SL_Checks]
    > >
    > > Select [Department Name] As [Dept] From [Adjustment Table]
    > > Union Select [Vendor Authorized Departments] From [Adjustment Table]
    > > Union Select [Exp2] From [qry @Deb_Monthly_W/O_Rpt_Legal/SL_Checks]
    > >
    > > Select [Write Off Claims Reason] As [Reason] From [Adjustment Table]
    > > Union Select [Vendor Reason] From [Adjustment Table]
    > > Union Select [Check/Wire Request Reason] From [Adjustment Table]
    > >
    > > If I tell it to Run, I get an error "Syntax Error in FROM Clause".
    > > I'm assuming that is in my very first statement.

    >
    > Does all this appear in ome query? This will indeed not work.
    > What you will need is a union of 3 Select statements, each with
    > a where clause for the particular date field.
    > First you must create a query that selects (and calculates) the
    > required "Check/Wire" values. Name them appropriately, not
    > Expr1 or Exp2. If I assume that your query is named [qry_Check/Wire],
    > thes this is probably what you need:
    >
    > PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
    > Select [Policy Number],
    > [Write Off Company Codes] As [Co Code],
    > [Write Off Date Processed] As [Date Processed],
    > [Assigned To],
    > [Write Off Amount Total] As [Amount],
    > [Write Off Main/Subs] As [Main/Sub],
    > [Write Off Account Numbers] As [Account Number],
    > [Write Off SAP Doc Number] As [Doc Number],
    > [Write Off Authorized By] As [Authorized],
    > [Department Name] As [Dept],
    > [Write Off Claims Reason] As [Reason]
    > From [Adjustment Table] Where ([Write Off Date Processed]
    > Between [Enter Start Date] And [Enter End Date])
    > Union
    > Select [Policy Number], [Vendor SAP Company Codes],
    > [Vendor Date Processed], [Assigned To],
    > [Vendor Amount of Refund], [Vendor Main/Subs],
    > [Vendor Account Numbers], [Vendor Document Number],
    > [Vendor Authorized By], [Vendor Authorized Departments],
    > [Vendor Reason]
    > From [Adjustment Table] Where ([Vendor Date Processed]
    > Between [Enter Start Date] And [Enter End Date])
    > Union
    > Select [Policy Number], [Check/Wire Company Codes],
    > [Check/Wire Date Processed], [Assigned To],
    > [Check/Wire Amount Total], [Check/Wire Main/Subs],
    > [Check/Wire Main/Subs2], [Check/Wire SAP Document Number],
    > [Check/Wire Authorized By], [Check/Wire Department Name],
    > [Check/Wire Request Reason]
    > From [qry_Check/Wire] Where ([Check/Wire Date Processed]
    > Between [Enter Start Date] And [Enter End Date]);
    >
    > As siyd before, [qry_Check/Wire] must be created first and it's
    > field names and the ones in the third Select statement of the Union
    > query must be the same.
    > I hope this helps.
    >
    > --
    > Regards,
    > Wolfgang
    >
    >

    This looks like it will work for what I need. Unfortunately, because of
    vacations (for co-workers, not me), I won't be able to get to this for at
    least a week I think. I'll give it a shot as soon as I can and make sure to
    let you know the results!!
     
  7. Shaun Rucker

    Shaun Rucker
    Expand Collapse
    Guest

    "Wolfgang Kais" wrote:

    > Shaun Rucker wrote:
    > >>> I have one large table from which many different forms spawn.
    > >>> Each form has its own unique fields, but most all of them contain
    > >>> the same data. For instance, I have three different fields that
    > >>> contain company codes, but they are named "Write Off Co Code",
    > >>> "Vendor Co Code" & "Check/Wire Co Code". I can't say why they used
    > >>> three different fields for the same data when this db was created,
    > >>> I wasn't here then.
    > >>> Anyway, I need to create a query on the three different sections
    > >>> of data: write offs, vendors and checks. How do I create a query
    > >>> (or make-table query) that will combine the identical fields?
    > >>> I figure a Union is what I need, but I don't know SQL to save my
    > >>> life and am not sure how to make it work since they are all in the
    > >>> same table in the first place.
    > >>>
    > >>> I'm assuming something like this:
    > >>>
    > >>> Select[Write Off Co Code] from [Adjustment Table]
    > >>> Union
    > >>> Select[Vendor Co Code] from [Adjustment Table]
    > >>> Union
    > >>> Select[Check/Wire Co Code] from [Adjustment Table]
    > >>>
    > >>> Then I don't know what to do next. I think I need an "As"
    > >>> statement after that, but not sure what it should look like.
    > >>> Maybe: As[Co Code] in [Adjustment Table]

    >
    > >> Select [Write Off Co Code] As [Co Code] from [Adjustment Table]
    > >> Union
    > >> Select [Vendor Co Code] from [Adjustment Table]
    > >> Union
    > >> Select [Check/Wire Co Code] from [Adjustment Table]

    >
    > > Thanks Wolfgang, that works, mostly. I got that particular one to
    > > work. Now I need it to get much more complicated. I need to query
    > > my database and collect 11 different fields:
    > > 1) Policy # (No Union statement needed for this field)
    > > 2) Co Code
    > > 3) Date Processed
    > > 4) Assigned to (No Union statement needed for this field)
    > > 5) Total
    > > 6) Main/Sub
    > > 7) Account Number
    > > 8) SAP Doc #
    > > 9) Authorized By
    > > 10) Department
    > > 11) Reason
    > >
    > > I need to add text boxes using a Between...And statement for the
    > > user to enter the date range needed. The other issue I am not so
    > > sure about is that some of the values for "Authorized By" and
    > > "Department" are calculated values from another query.
    > > I attempted to use the same naming logic for the query, but I don't
    > > think it's correct. Here is what I came up with for my query.
    > >
    > > SELECT [Policy Number] From [Adjustment Table]
    > >
    > > Select [Write Off Company Codes] As [Co Code] from [Adjustment Table]
    > > Union Select [Vendor SAP Company Codes] from [Adjustment Table]
    > > Union Select [Check/Wire Company Codes] from [Adjustment Table]
    > >
    > > Select [Write Off Date Processed] as [Date Processed] from
    > > [Adjustment Table]
    > > Union Select [Vendor Date Processed] from [Adjustment Table]
    > > Union Select [Check/Wire Date Processed] from [Adjustment Table] Where
    > > ([Date Processed] Between [Enter Start Date] And [Enter End Date])
    > >
    > > Select [Assigned To] From [Adjustment Table]
    > >
    > > Select [Write Off Amount Total] As [Amount] from [Adjustment Table]
    > > Union Select [Vendor Amount of Refund] from [Adjustment Table]
    > > Union Select [Check/Wire Amount Total] from [Adjustment Table]
    > >
    > > Select [Write Off Main/Subs] As [Main/Sub] from [Adjustment Table]
    > > Union Select [Vendor Main/Subs] from [Adjustment Table]
    > > Union Select [Check/Wire Main/Subs] from [Adjustment Table]
    > >
    > > Select [Write Off Account Numbers] As [Account Number] from
    > > [Adjustment Table]
    > > Union Select [Vendor Account Numbers] from [Adjustment Table]
    > > Union Select [Check/Wire Main/Subs2] from [Adjustment Table]
    > >
    > > Select [Write Off SAP Doc Number] As [Doc Number] From
    > > [Adjustment Table]
    > > Union Select [Vendor Document Number] From [Adjustment Table]
    > > Union Select [Check/Wire SAP Document Number] from [Adjustment Table]
    > >
    > > Select [Write Off Authorized By] As [Authorized] From
    > > [Adjustment Table]
    > > Union Select [Vendor Authorized By] From [Adjustment Table]
    > > Union Select [Expr1] From [qry @Deb_Monthly_W/O_Rpt_Legal/SL_Checks]
    > >
    > > Select [Department Name] As [Dept] From [Adjustment Table]
    > > Union Select [Vendor Authorized Departments] From [Adjustment Table]
    > > Union Select [Exp2] From [qry @Deb_Monthly_W/O_Rpt_Legal/SL_Checks]
    > >
    > > Select [Write Off Claims Reason] As [Reason] From [Adjustment Table]
    > > Union Select [Vendor Reason] From [Adjustment Table]
    > > Union Select [Check/Wire Request Reason] From [Adjustment Table]
    > >
    > > If I tell it to Run, I get an error "Syntax Error in FROM Clause".
    > > I'm assuming that is in my very first statement.

    >
    > Does all this appear in ome query? This will indeed not work.
    > What you will need is a union of 3 Select statements, each with
    > a where clause for the particular date field.
    > First you must create a query that selects (and calculates) the
    > required "Check/Wire" values. Name them appropriately, not
    > Expr1 or Exp2. If I assume that your query is named [qry_Check/Wire],
    > thes this is probably what you need:
    >
    > PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
    > Select [Policy Number],
    > [Write Off Company Codes] As [Co Code],
    > [Write Off Date Processed] As [Date Processed],
    > [Assigned To],
    > [Write Off Amount Total] As [Amount],
    > [Write Off Main/Subs] As [Main/Sub],
    > [Write Off Account Numbers] As [Account Number],
    > [Write Off SAP Doc Number] As [Doc Number],
    > [Write Off Authorized By] As [Authorized],
    > [Department Name] As [Dept],
    > [Write Off Claims Reason] As [Reason]
    > From [Adjustment Table] Where ([Write Off Date Processed]
    > Between [Enter Start Date] And [Enter End Date])
    > Union
    > Select [Policy Number], [Vendor SAP Company Codes],
    > [Vendor Date Processed], [Assigned To],
    > [Vendor Amount of Refund], [Vendor Main/Subs],
    > [Vendor Account Numbers], [Vendor Document Number],
    > [Vendor Authorized By], [Vendor Authorized Departments],
    > [Vendor Reason]
    > From [Adjustment Table] Where ([Vendor Date Processed]
    > Between [Enter Start Date] And [Enter End Date])
    > Union
    > Select [Policy Number], [Check/Wire Company Codes],
    > [Check/Wire Date Processed], [Assigned To],
    > [Check/Wire Amount Total], [Check/Wire Main/Subs],
    > [Check/Wire Main/Subs2], [Check/Wire SAP Document Number],
    > [Check/Wire Authorized By], [Check/Wire Department Name],
    > [Check/Wire Request Reason]
    > From [qry_Check/Wire] Where ([Check/Wire Date Processed]
    > Between [Enter Start Date] And [Enter End Date]);
    >
    > As siyd before, [qry_Check/Wire] must be created first and it's
    > field names and the ones in the third Select statement of the Union
    > query must be the same.
    > I hope this helps.
    >
    > --
    > Regards,
    > Wolfgang
    >

    Wolfgang,

    You're awesome!! That works perfectly!! I already had my other query
    created, so I didn't have to do that step again. My only very small question
    remaining is, it asks me to enter the start and end dates twice. I made sure
    to copy and paste in the statement so that all of the [Enter Start Date]'s
    and [Enter End Date]'s are exact, but it still asks. This only takes about
    two extra seconds to run, so I'm not too worried about it though.

    Thanks again, I'm glad guys like you are out here to help us SQL-impaired
    people!!
    Shaun Rucker
     
  8. Wolfgang Kais

    Wolfgang Kais
    Expand Collapse
    Guest

    Hello Shaun.

    Shaun Rucker wrote:
    >>>>> I have one large table from which many different forms spawn.
    >>>>> Each form has its own unique fields, but most all of them contain
    >>>>> the same data. For instance, I have three different fields that
    >>>>> contain company codes, but they are named "Write Off Co Code",
    >>>>> "Vendor Co Code" & "Check/Wire Co Code". I can't say why they used
    >>>>> three different fields for the same data when this db was created,
    >>>>> I wasn't here then.
    >>>>> Anyway, I need to create a query on the three different sections
    >>>>> of data: write offs, vendors and checks. How do I create a query
    >>>>> (or make-table query) that will combine the identical fields?
    >>>>> I figure a Union is what I need, but I don't know SQL to save my
    >>>>> life and am not sure how to make it work since they are all in the
    >>>>> same table in the first place.
    >>>>>
    >>>>> I'm assuming something like this:
    >>>>>
    >>>>> Select[Write Off Co Code] from [Adjustment Table]
    >>>>> Union
    >>>>> Select[Vendor Co Code] from [Adjustment Table]
    >>>>> Union
    >>>>> Select[Check/Wire Co Code] from [Adjustment Table]
    >>>>>
    >>>>> Then I don't know what to do next. I think I need an "As"
    >>>>> statement after that, but not sure what it should look like.
    >>>>> Maybe: As[Co Code] in [Adjustment Table]


    >>>> Select [Write Off Co Code] As [Co Code] from [Adjustment Table]
    >>>> Union
    >>>> Select [Vendor Co Code] from [Adjustment Table]
    >>>> Union
    >>>> Select [Check/Wire Co Code] from [Adjustment Table]


    >>> Thanks Wolfgang, that works, mostly. I got that particular one to
    >>> work. Now I need it to get much more complicated. I need to query
    >>> my database and collect 11 different fields:
    >>> 1) Policy # (No Union statement needed for this field)
    >>> 2) Co Code
    >>> 3) Date Processed
    >>> 4) Assigned to (No Union statement needed for this field)
    >>> 5) Total
    >>> 6) Main/Sub
    >>> 7) Account Number
    >>> 8) SAP Doc #
    >>> 9) Authorized By
    >>> 10) Department
    >>> 11) Reason
    >>>
    >>> I need to add text boxes using a Between...And statement for the
    >>> user to enter the date range needed. The other issue I am not so
    >>> sure about is that some of the values for "Authorized By" and
    >>> "Department" are calculated values from another query.
    >>> I attempted to use the same naming logic for the query, but I don't
    >>> think it's correct. Here is what I came up with for my query.
    >>>
    >>> SELECT [Policy Number] From [Adjustment Table]
    >>>
    >>> Select [Write Off Company Codes] As [Co Code] from [Adjustment Table]
    >>> Union Select [Vendor SAP Company Codes] from [Adjustment Table]
    >>> Union Select [Check/Wire Company Codes] from [Adjustment Table]
    >>>
    >>> Select [Write Off Date Processed] as [Date Processed] from
    >>> [Adjustment Table]
    >>> Union Select [Vendor Date Processed] from [Adjustment Table]
    >>> Union Select [Check/Wire Date Processed] from [Adjustment Table] Where
    >>> ([Date Processed] Between [Enter Start Date] And [Enter End Date])
    >>>
    >>> Select [Assigned To] From [Adjustment Table]
    >>>
    >>> Select [Write Off Amount Total] As [Amount] from [Adjustment Table]
    >>> Union Select [Vendor Amount of Refund] from [Adjustment Table]
    >>> Union Select [Check/Wire Amount Total] from [Adjustment Table]
    >>>
    >>> Select [Write Off Main/Subs] As [Main/Sub] from [Adjustment Table]
    >>> Union Select [Vendor Main/Subs] from [Adjustment Table]
    >>> Union Select [Check/Wire Main/Subs] from [Adjustment Table]
    >>>
    >>> Select [Write Off Account Numbers] As [Account Number] from
    >>> [Adjustment Table]
    >>> Union Select [Vendor Account Numbers] from [Adjustment Table]
    >>> Union Select [Check/Wire Main/Subs2] from [Adjustment Table]
    >>>
    >>> Select [Write Off SAP Doc Number] As [Doc Number] From
    >>> [Adjustment Table]
    >>> Union Select [Vendor Document Number] From [Adjustment Table]
    >>> Union Select [Check/Wire SAP Document Number] from [Adjustment Table]
    >>>
    >>> Select [Write Off Authorized By] As [Authorized] From
    >>> [Adjustment Table]
    >>> Union Select [Vendor Authorized By] From [Adjustment Table]
    >>> Union Select [Expr1] From [qry @Deb_Monthly_W/O_Rpt_Legal/SL_Checks]
    >>>
    >>> Select [Department Name] As [Dept] From [Adjustment Table]
    >>> Union Select [Vendor Authorized Departments] From [Adjustment Table]
    >>> Union Select [Exp2] From [qry @Deb_Monthly_W/O_Rpt_Legal/SL_Checks]
    >>>
    >>> Select [Write Off Claims Reason] As [Reason] From [Adjustment Table]
    >>> Union Select [Vendor Reason] From [Adjustment Table]
    >>> Union Select [Check/Wire Request Reason] From [Adjustment Table]
    >>>
    >>> If I tell it to Run, I get an error "Syntax Error in FROM Clause".
    >>> I'm assuming that is in my very first statement.


    >> Does all this appear in ome query? This will indeed not work.
    >> What you will need is a union of 3 Select statements, each with
    >> a where clause for the particular date field.
    >> First you must create a query that selects (and calculates) the
    >> required "Check/Wire" values. Name them appropriately, not
    >> Expr1 or Exp2. If I assume that your query is named [qry_Check/Wire],
    >> thes this is probably what you need:
    >>
    >> PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
    >> Select [Policy Number],
    >> [Write Off Company Codes] As [Co Code],
    >> [Write Off Date Processed] As [Date Processed],
    >> [Assigned To],
    >> [Write Off Amount Total] As [Amount],
    >> [Write Off Main/Subs] As [Main/Sub],
    >> [Write Off Account Numbers] As [Account Number],
    >> [Write Off SAP Doc Number] As [Doc Number],
    >> [Write Off Authorized By] As [Authorized],
    >> [Department Name] As [Dept],
    >> [Write Off Claims Reason] As [Reason]
    >> From [Adjustment Table] Where ([Write Off Date Processed]
    >> Between [Enter Start Date] And [Enter End Date])
    >> Union
    >> Select [Policy Number], [Vendor SAP Company Codes],
    >> [Vendor Date Processed], [Assigned To],
    >> [Vendor Amount of Refund], [Vendor Main/Subs],
    >> [Vendor Account Numbers], [Vendor Document Number],
    >> [Vendor Authorized By], [Vendor Authorized Departments],
    >> [Vendor Reason]
    >> From [Adjustment Table] Where ([Vendor Date Processed]
    >> Between [Enter Start Date] And [Enter End Date])
    >> Union
    >> Select [Policy Number], [Check/Wire Company Codes],
    >> [Check/Wire Date Processed], [Assigned To],
    >> [Check/Wire Amount Total], [Check/Wire Main/Subs],
    >> [Check/Wire Main/Subs2], [Check/Wire SAP Document Number],
    >> [Check/Wire Authorized By], [Check/Wire Department Name],
    >> [Check/Wire Request Reason]
    >> From [qry_Check/Wire] Where ([Check/Wire Date Processed]
    >> Between [Enter Start Date] And [Enter End Date]);
    >>
    >> As sayd before, [qry_Check/Wire] must be created first and it's
    >> field names and the ones in the third Select statement of the
    >> Union query must be the same.
    >> I hope this helps.


    > You're awesome!! That works perfectly!! I already had my other
    > query created, so I didn't have to do that step again. My only
    > very small question remaining is, it asks me to enter the start
    > and end dates twice. I made sure to copy and paste in the statement
    > so that all of the [Enter Start Date]'s and [Enter End Date]'s are
    > exact, but it still asks. This only takes about two extra seconds
    > to run, so I'm not too worried about it though.


    You probably have also used parameters in you other query...

    > Thanks again, I'm glad guys like you are out here to help us
    > SQL-impaired people!!


    You're welcome.

    --
    Regards,
    Wolfgang
     

Share This Page