Multiple Columns into single Colum

[haroon.mansha]

I want to create the date from multiple colums to single colums. Just for example

table 1 (local purchase)
Itemname Date
Apple 12/01/2013
Mango 13/01/2013

Table 2 (Import purchase)
Item Name DAte
Apple 12/04/2013
Mango 08/06/2013


Now i want to make one query, which can you the date as follows when we give criteria = apple

Item Name Date purchase mode
Apple 12/01/2013 local
Apple 12/04/2013 Import


Means two dates from different table into one query colum... One way in my mind to make one table for both tables.

 

[EternalMyrtle]

You could use a union query.

Create a query and go to sql view. Put something like this in (changing table and field names for your tables and fields):

SELECT ItemName
FROM Table1
UNION SELECT ItemName
FROM Table2

Hope this helps.

 

[haroon.mansha]

I will try but I am confuse how single column can show column data in two rows. Is there any other way

 

[EternalMyrtle]

Try it and see if it achieves the desired result. If not, post your specific problem and someone will help you.

As far as I know, a union is the only way to intellectually combine the data into a single column of your table without physically putting it into one table. But I certainly don't know everything

 

[pr2-eugin]

I sense some design issue here.. Why is the similar structure and similar data residing in two separate tables? The only factor that differs is the PurchaseOrderOrigin.. IMHO, they should be in one table.. This is why you are having trouble.. What should have been a simple SELECT is making you go over and beyond unwanted coding..

Also, UNION is Distinctive and returns only unique values.. To get the information that you want, the Code should be..

SELECT ItemName, DateOrdered, "Local" As PurchasedFrom FROM Table1
[B]UNION[/B]
SELECT ItemName, DateOrdered, "Import" As PurchasedFrom FROM Table2

 

[EternalMyrtle]

You will also need to add the additional fields so all rows show up in the Union query.

So the SQL will look something like this:

SELECT ItemName, DateShipped, ImportType
FROM Table1
UNION SELECT ItemName, DateShipped, ImportType
FROM Table2

 

[EternalMyrtle]

All of what pr2-eugin says is true including the part about the tables.

Why not just have a field for import type rather than keeping the info in two separate tables. Then you don't need to worry about any of this...

 

[EternalMyrtle]

Or "purchase mode", whatever you are calling it...

 

[haroon.mansha]

Yes I think should make a single table.as if I need sales query should also include fields in same table to see report of inputs and outputs in one page

 

[haroon.mansha]

Actually purpose of two tables was due to fact if itenname is foreign purchased master tables need some different data of supplier and if it is local purchased the master table need different data in master field

Its simple just like local person need to show ID while foreign person needs to show passport