Combining Records From Two Different Tables

[Nitin]

Here I need a help. I have two tables 1) Mis_A 2) Mis_B

Mis_A Mis_B
Location Revenue Location Sale
Mumbai 10 Mumbai 5
Mumbai 10 Chennai 10
Goa 5 Madras 10
Goa 5 Goa 5
I need an output in the Format shown below with sql query
Location Total(Revnue+Sale)
Mumbai 25
Goa 15
Chennai 10
Madras 10
Thanks in advance

[Allen Browne]

1. Create a new query, and type this into SQL View:
SELECT Location, Revenue FROM Mis_A
UNION ALL
SELECT Location, Revenue FROM Mis_B;
Save. Close.

2. Create another query using the first one as your input "table."
Depress the Total button on the toolbar.
Access adds a Total row to the grid.
You can now Group By the Location, and Sum the Revenue.

If practical, the best solution would be to combine the data from Mis_A and
Mis_B into one table, probably with an extra field to indicate whatever the
difference is.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nitin" <ni********@gmail.comwrote in message
news:11**********************@f1g2000cwa.googlegro ups.com...

Here I need a help. I have two tables 1) Mis_A 2) Mis_B

Mis_A Mis_B
Location Revenue Location Sale
Mumbai 10 Mumbai 5
Mumbai 10 Chennai 10
Goa 5 Madras 10
Goa 5 Goa 5
I need an output in the Format shown below with sql query
Location Total(Revnue+Sale)
Mumbai 25
Goa 15
Chennai 10
Madras 10

Thanks in advance

[Nitin]

Allen Browne wrote:

1. Create a new query, and type this into SQL View:
SELECT Location, Revenue FROM Mis_A
UNION ALL
SELECT Location, Revenue FROM Mis_B;
Save. Close.

2. Create another query using the first one as your input "table."
Depress the Total button on the toolbar.
Access adds a Total row to the grid.
You can now Group By the Location, and Sum the Revenue.

If practical, the best solution would be to combine the data from Mis_A and
Mis_B into one table, probably with an extra field to indicate whatever the
difference is.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nitin" <ni********@gmail.comwrote in message
news:11**********************@f1g2000cwa.googlegro ups.com...

Here I need a help. I have two tables 1) Mis_A 2) Mis_B

Mis_A Mis_B
Location Revenue Location Sale
Mumbai 10 Mumbai 5
Mumbai 10 Chennai 10
Goa 5 Madras 10
Goa 5 Goa 5
I need an output in the Format shown below with sql query
Location Total(Revnue+Sale)
Mumbai 25
Goa 15
Chennai 10
Madras 10

Thanks in advance

Thanks Allen
I implemented your logic, I used 1st option it is working fine.