Union Query Add Field To It

[burrina]

I have a Union Query I made but now can't seem to remember how to add to it! I need to add the field Bank which is common i both tables.



Here is the code:

SELECT Employees.[EmployeeID], [FirstName] & " " & [LastName] AS ExpName
FROM Employees

UNION SELECT [OtherID]+[Oth] AS [EmployeeID], [FirstName] & " " & [LastName] AS ExpName
FROM TOthers;

[June7]

I looked at the images in your other thread https://www.accessforums.net/access/...lem-30984.html. There is not a field Bank in either of these tables.

Only need to define the fieldnames in the first SELECT statement.

SELECT Employees.[EmployeeID], [FirstName] & " " & [LastName] AS ExpName
FROM Employees
UNION SELECT [OtherID]+[Oth], [FirstName] & " " & [LastName]
FROM TOthers;

[burrina]

NO,there isnt, the field is in BOTH tables that the union query is based on.

[June7]

I am confused. The UNION query shows Employees and TOthers tables. Neither of those tables have Bank field.

TBank and TReg have Bank fields.

I don't see any relationship between Employees and TOther table to TBank and TReg.

Without a relationship, how can the Bank fields be in the UNION query?

[burrina]

Those fields were added after the fact. They are indeed in BOTH of the corresponding tables. Table TBank is just the Bank Account to be associated with the Check Register.
Table TOther is used for NonPayroll transactions. Table TReg is the main table the check register is based off. Main query for check register is QRegB
So, to be clear as mud, there are 3 tables, Employees table, TReg and TOther. (4 tables counting TBank) 3 queries as well.. QRegA QRegB and QPayee which is a union query that uses
QRegA and QRegB

You probably hate me by now. Hard to comprehend this without seeing it. Something I wrote in 2002, when my brain kinda sorta worked. I have got a lot dumber since then.

[June7]

Okay, if the fields are now in the tables then simply include them in UNION same as the other fields.

SELECT [EmployeeID], [FirstName] & " " & [LastName] AS ExpName, Bank
FROM Employees
UNION SELECT [OtherID]+[Oth], [FirstName] & " " & [LastName], Bank
FROM TOthers;

[burrina]

That actually worked, simple huh? Now I just have concentrate a bit more. Since every Employee can have their own Bank and Non-Employees their own bank, hmmm??
Even though I have a separate Bank table although I link to via the Bank field in both the TReg and Employees and TOthers table(s) as a combo!
In my check register I now a different bank account for whatever the Payee has chosen, Bad Idea I think! I just need ONE Ban Account Per Check register, is this not correct?

[June7]

Table relationships don't make sense to me. TBank and TReg are linked on TBank.BankID and TReg.RegID yet there is a RegID field in TBank. I don't understand this data structure.

[burrina]

I want of course to be able to use multiple banks for the check register, just not at the same time. Obviously the RegID field is not needed but in the design sometimes things get overlooked. Simple fix and not a problem. I am now down to ONE Bank account per open register, Yeah! I am still not sure how to link the TOthers table if at all. It i used for NON Employee pay transactions.
Tables Queries Modules

CounterTable QRegA ModCur
Employees QRegB ModCustomCounter
TBanks QPayee
TEmpOrCon
TFrequency
TOthers
TReg
TTransactions
__________________________________________________ ________________________________________
Explanations:

CounterTable Used to increment check numbers
Employees Used to pay employees wages
TBanks Used to store bank account(s)
TEmpOrCon Used to assign a numerical value to type of payee
TFrequency Used to set frequency of recurring transactions
TOthers Used for Non Employee transactions
TReg Used for check register for checks and deposits
TTransactions Used for different types of transactions
__________________________________________________ _________________________________________
QRegA Used for 1/2 of check register criteria
QRegB Used for 1/2 of check register criteria
QPayee Used as Union Query to combine QRegA and QRegB
__________________________________________________ _________________________________________
ModCur Used to convert a numerical value to english
ModCustomCounter Used to increment counter by one
__________________________________________________ _________________________________________
Purpose of Database:

To create checks,deposits,reconcile statements
To track register by individual Bank Account(s)