How do you split the data from an SQL Union All clause into another column?

Question

I am trying to do an SQL Union All clause in Microsoft Access 2007 with data from two different tables. One table has three columns of data in it and the other has only one. For the table that has only one column, I list that column and two nulls.

When I run this query, it shows the data from the three columns of the first table and for some reason that baffles me, the data from the second table is placed underneath the data in the first column of the first table. I am not sure as to what is causing this or what can be done to fix it.

To be more simply put:


Here is the first table:

ITEM PRICE DATE_PURCHASED
Coffee ($1.95) 8/22/2010
Church Contribution ($25.00) 8/22/2010
Groceries ($54.98) 8/22/2010
Bedspread ($215.00) 8/22/2010
Spa ($155.00) 8/22/2010
Dinner W/Gail ($60.00) 8/22/2010
Coffee ($1.95) 8/23/2010
Lunch ($8.50) 8/23/2010
Babysitter ($30.00) 8/23/2010
Coffee ($1.95) 8/24/2010
Lunch ($7.00) 8/24/2010
Drug Store ($34.95) 8/24/2010
Groceries ($102.83) 8/24/2010
Coffee ($1.95) 8/25/2010
Auto Repair ($582.63) 8/25/2010
Sold Painting $2,500.00 8/26/2010
Coffee ($1.95) 8/26/2010
Dinner Party ($187.00) 8/26/2010
Coffee ($1.95) 8/27/2010
Paycheck $4,000.00 8/27/2010
Dinner Dress ($1,300.00) 8/27/2010
Babysitter ($45.00) 8/27/2010
Coffee ($1.95) 8/23/2010


here is the data from the second table:

COST
($53.26)
($148.59)
($350.11)
$1,500.00
($1.95)
($8.50)
($2.43)
($700.00)
($45.00)
($206.14)
($35.00)
($6.25)
$800.00
($1.95)
($1.95)


Here's my query:

select a.item,
a.price,
a.date_purchased
from ex1501_sue a
union all
select b.cost,
null,
null
from ex1501_bob b;

and here's the result:

item price date_purchased
Coffee ($1.95) 8/22/2010
Church Contribution ($25.00) 8/22/2010
Groceries ($54.98) 8/22/2010
Bedspread ($215.00) 8/22/2010
Spa ($155.00) 8/22/2010
Dinner W/Gail ($60.00) 8/22/2010
Coffee ($1.95) 8/23/2010
Lunch ($8.50) 8/23/2010
Babysitter ($30.00) 8/23/2010
Coffee ($1.95) 8/24/2010
Lunch ($7.00) 8/24/2010
Drug Store ($34.95) 8/24/2010
Groceries ($102.83) 8/24/2010
Coffee ($1.95) 8/25/2010
Auto Repair ($582.63) 8/25/2010
Sold Painting $2,500.00 8/26/2010
Coffee ($1.95) 8/26/2010
Dinner Party ($187.00) 8/26/2010
Coffee ($1.95) 8/27/2010
Paycheck $4,000.00 8/27/2010
Dinner Dress ($1,300.00) 8/27/2010
Babysitter ($45.00) 8/27/2010
Coffee ($1.95) 8/23/2010
-53.26
-148.59
-350.11
1500
-1.95
-8.5
-2.43
-700
-45
-206.14
-35
-6.25
800
-1.95
-1.95


the numbers in the item column are from the cost column in the second table. Could somebody out there show me how to fix this problem?

Answer 1

Exactly what are you trying to accomplish here? What is the relation between the two tables and how is it defined? A union basically just combines two or more subtables into one result, which is just what you got - your set of rows from your first table, followed by your set of rows from the second (padded by nulls, since the number of columns of a union must match in number and type.) If the two tables are supposed to be related just by physical order (row 1 of table A relates to row 1 of table B, etc.), be aware that this will not necessarily work in every DBMS - physical order of data rows withing tables does not necessarily relate to the order of rows in a result set, especially if database objects like indexes exist (and almost certainly not if you do any ordering in your query explicitly or implicitly!)

Answer 2

Change the column order:



select a.item,

a.price,

a.date_purchased

from ex1501_sue a

union all

select null,

b.cost,

null

from ex1501_bob b;

Answer 3

you want to setup each and each and every of the field values for the hot record before you Insert it, so so that you may use the present record to get regardless of values you want to be an same, and set the UserID to regardless of fee you want the hot record to be.