Data from multiple tables
-
I have two tables like this Sales table Purchases table --------------------- ------------------------------ SaleDateTime | Cost PurchaseDateTime | Cost --------------------- ------------------------------ 02-09-11 | 35 02-09-11 | 48 03-09-11 | 35 02-09-11 | 48 06-09-11 | 35 04-09-11 | 48 07-09-11 | 35 05-09-11 | 48 08-09-11 | 35 08-09-11 | 48 I want to produce result like this Date | Sale_total | purchase_total -------------------------------------------- 02-09-11 | 35 | 96 03-09-11 | 35 | 0 04-09-11 | 0 | 48 05-09-11 | 0 | 48 06-09-11 | 35 | 0 07-09-11 | 35 | 0 08-09-11 | 35 | 48 how can I do this ? Additional info:- I am using MS Access.
--------------------------------------------- _" Future Lies in Present "_Manmohan Bishnoi
-
I have two tables like this Sales table Purchases table --------------------- ------------------------------ SaleDateTime | Cost PurchaseDateTime | Cost --------------------- ------------------------------ 02-09-11 | 35 02-09-11 | 48 03-09-11 | 35 02-09-11 | 48 06-09-11 | 35 04-09-11 | 48 07-09-11 | 35 05-09-11 | 48 08-09-11 | 35 08-09-11 | 48 I want to produce result like this Date | Sale_total | purchase_total -------------------------------------------- 02-09-11 | 35 | 96 03-09-11 | 35 | 0 04-09-11 | 0 | 48 05-09-11 | 0 | 48 06-09-11 | 35 | 0 07-09-11 | 35 | 0 08-09-11 | 35 | 48 how can I do this ? Additional info:- I am using MS Access.
--------------------------------------------- _" Future Lies in Present "_Manmohan Bishnoi
You can divide this task into three parts. The first part uses inner join to find the records from both tables with common date/time. The second part deals with the case that date/time only appears in the first table. The third part deals with the case that date/time only appears in the second table. The result is a union of the above three queries. To show you what I mean in a cleaner example, let's suppose the first table is called "Table1" with the following two fields: a1 and a2 (corresponding to your "Sales" table's SaleDateTime and Cost columns), and the second table is called "Table2" with the following two fields: a1 and a2 (corresponding to your "Purchase" table's PurchaseDateTime and Cost columns). The following query should give you what you wanted.
SELECT a.a1, sum(a.aa2), sum(b.aa2)
FROM (SELECT a1, sum(a2) as aa2 FROM Table1 GROUP BY a1) as a,
(SELECT a1, sum(a2) as aa2 FROM Table2 GROUP BY a1) as b
WHERE (a.a1=b.a1)
GROUP BY a.a1;UNION
SELECT a1, sum(a2), 0
FROM Table1
WHERE a1 not in (SELECT a1 FROM Table2)
GROUP BY a1UNION
SELECT a1, 0, sum(a2)
FROM Table2
WHERE a1 not in (SELECT a1 FROM Table1)
GROUP BY a1 -
You can divide this task into three parts. The first part uses inner join to find the records from both tables with common date/time. The second part deals with the case that date/time only appears in the first table. The third part deals with the case that date/time only appears in the second table. The result is a union of the above three queries. To show you what I mean in a cleaner example, let's suppose the first table is called "Table1" with the following two fields: a1 and a2 (corresponding to your "Sales" table's SaleDateTime and Cost columns), and the second table is called "Table2" with the following two fields: a1 and a2 (corresponding to your "Purchase" table's PurchaseDateTime and Cost columns). The following query should give you what you wanted.
SELECT a.a1, sum(a.aa2), sum(b.aa2)
FROM (SELECT a1, sum(a2) as aa2 FROM Table1 GROUP BY a1) as a,
(SELECT a1, sum(a2) as aa2 FROM Table2 GROUP BY a1) as b
WHERE (a.a1=b.a1)
GROUP BY a.a1;UNION
SELECT a1, sum(a2), 0
FROM Table1
WHERE a1 not in (SELECT a1 FROM Table2)
GROUP BY a1UNION
SELECT a1, 0, sum(a2)
FROM Table2
WHERE a1 not in (SELECT a1 FROM Table1)
GROUP BY a1It is difficult to join datetime fields because of the time portion. What appears as 09-10-11 may actually be stored as 09-10-11:09:30.00.00 in one field and 09-10-11:12:49.46.50 in another. So when you look at the date portion you would think they're the same value but they're not because of the time portion.
-
It is difficult to join datetime fields because of the time portion. What appears as 09-10-11 may actually be stored as 09-10-11:09:30.00.00 in one field and 09-10-11:12:49.46.50 in another. So when you look at the date portion you would think they're the same value but they're not because of the time portion.
Actually I modified the fields SaleDateTime and PurchaseDateTime. These fields contain only date values like 09-11-2011.
--------------------------------------------- _" Future Lies in Present "_Manmohan Bishnoi
-
Actually I modified the fields SaleDateTime and PurchaseDateTime. These fields contain only date values like 09-11-2011.
--------------------------------------------- _" Future Lies in Present "_Manmohan Bishnoi
You may have changed the display, that doesn't change the actual date value which still includes the time portion. So the date may display as 09-11-2011 but the real value may be 09/11/2011 08:30:20:15. Anyway, try joining using those fields and see what you get.
-
You may have changed the display, that doesn't change the actual date value which still includes the time portion. So the date may display as 09-11-2011 but the real value may be 09/11/2011 08:30:20:15. Anyway, try joining using those fields and see what you get.
yes you were right. I had to change actual data of those fields. thanks
--------------------------------------------- _" Future Lies in Present "_Manmohan Bishnoi