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.

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

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
>
>
>

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 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!!

"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

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