how to combine two tables in access?

Question

i have two tables sales and purchase in my db.

there are four transactions in sales i.e. on 4th ,5th,6,7 Oct
and there are Five Transaction is purchase on 3rd,4,5,8,9 Oct
i wanna now how much amount of sales and purhcase (on that particular day) have been made form 3rd to 9th oct

Answer 1

to display the records from Sales table and Purchase table, do like Monalisa said, but he/she missing 'WHERE' query.

assume :
Create Table Sales (Sales_Date date, total integer);
Create Table Purchase (Purchase_Date, total integer);

Select Sales_Date, Total from SALES where
Sales_Date>='10/03/2006' and Sales_Date<='10/09/2006'
union all
Select Purchase_Date, Total from PURCHASE where
Purchase_Date>='10/03/2006' and Purchase_Date<='10/09/2006'

Notes :
'UNION' will eliminate the same records from PURCHASE table.
to get all records we must use 'UNION ALL' .

To sum sales and purchase in 3rd to 9th oct :

Select sum(A.Total) as TotalSales, sum(B.Total) as TotalPurchase from SALES as A, PURCHASE as B where A.Sales_Date>='10/03/2006' and A.Sales_Date<='10/09/2006'
and B.Purchase_Date>='10/03/2006' and B.Purchase_Date<='10/09/2006'

ps :
my query format maybe have a little different with ms Access query format, so if my query can't run in ms Access, try to change it to ms Access query format.

Answer 2

In get get entry to to, there's a development database pronounced as Northwind traders (or the like). Pull it up & attempt how the varieties paintings. you will see there must be countless tables utilized by skill of way of a sort. you will possibly have a table of shoppers on the subject of a table containing their orders, working occasion. The "link" is the shopper call or quantity that seems in the two tables. you come back to a sort on for a hassle-free field as a thank you to link the tables on an identical time. in case you placed up your tables suited, (and if I comprehend the question suited) you will possibly have the skill to do what you describe. the kind itself, is secondary to setting up the tables and the links between tables. want this facilitates!

Answer 3

the two tables must be of the same structure but of different data.
to combine them,you have to create a union query.
example.
suppose the tables are table1 and table2.
the fields are field1,field2 and field3.
in the sql view of a query write the following statement.

select field1,field,field3
from table1;
union select field1,field,field3
from table2;

Answer 4

aashish Try this link it might help you. I am not sure but might.

http://www.thescripts.com/forum/thread205100.html