By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

Combining Records From Two Different Tables

P: n/a
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

Dec 9 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Dec 9 '06 #2

P: n/a

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.

Dec 9 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.