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.
