Join Data Between Two Tables

[moustafa]

I have two tables
first table : customer name,QUANTITY ,delivery date,product type
second table : customer name,QUANTITY ,delivery date,product type
i want to make query to sum the QUANTITY from the two tables and group by customer name and delivery date

can any one help me to do this ...
:banghead::banghead::banghead::banghead::banghead:

 

[Galaxiom]

Re: Very ARGENT

Why do you have two tables?

If you can't change that then you will need to UNION them first. I have done it with a subquery but you can do the union first as a separate query and then build another query on it.

SELECT [Customer Name], Sum([Quantity]), [Delivery Date]
FROM 
(
SELECT [Customer Name], [Quantity], [Delivery Date]
FROM Table1
UNION ALL
SELECT [Customer Name], [Quantity], [Delivery Date]
FROM Table2
) AS U
GROUP BY [Customer Name], [Delivery Date]
ORDER BY [Customer Name], [Delivery Date]

BTW Normally you would do this on CustomerID then join the name, otherwise two customers with the same name will have their records combined.

 

[moustafa]

Re: Very ARGENT

Excuse me where i will type that code

 

[moustafa]

About query

Hi every body
i have two queries with no primary key in the original tables because the original tables in ecxel sheet and i linked this sheet to my db

?? The two table have the same columns names
cutomer name, qty, date

?? How can i group the two queries in on query that show me
customer name , date, (sum of qty )


thanks

 

[Galaxiom]

Re: Very ARGENT

Excuse me where i will type that code

Make sure the names I used are exactly correct for your tables and fields and edit my code if necessary.

Then create a query in the designer and change to SQL View. Paste the code into there, replacing anything that is already there.

 

[moustafa]

HOW CAN I MERGE THE DATA BETWEEN THESE TWO QUERIES AND VIEW IT IN
ONE QUERY WITHOUT ANY DUPLICATED IN CUSTOMERS

THANKS

 

[Galaxiom]

You have not said anything at all about trying the code I already provided and now you are shouting.

That SQL code does exactly what you asked.

 

[moustafa]

the code not work correctly it;s give me message ( error in from clause ) note: i al ready add the true names for the tables

.... thanks for you interesting...

 

[Galaxiom]

Just did a quick test. It is working perfectly for me.

Note that I did omit a closing bracket originally but I fixed that when I posted Post #5.

 

[moustafa]

thanks it's work after i removed the bracket but when i add the group by and order by it;s not work again >>> may be because the ; i did not know where i should type it

 

[Galaxiom]

My test attached. Can you see what is different in your database?

 

[moustafa]

SELECT Customer_Name, Quantity, Delivery_Date
FROM WINDMILL
UNION ALL
SELECT Customer Name, Quantity, Delivery_Date
FROM CRUSHER
GROUP BY Customer_Name, Delivery_Date;

 

[Galaxiom]

SELECT [Customer Name], Sum([Quantity]), [Delivery Date]
FROM 
(
SELECT Customer_Name, Quantity, Delivery_Date
FROM WINDMILL
UNION ALL
SELECT Customer Name, Quantity, Delivery_Date
FROM CRUSHER
) AS U 
GROUP BY Customer_Name, Delivery_Date;

 

[moustafa]

thanks thanks thanks

it's work correctly

 

[moustafa]

but i have one more question
how can i group and sum the quantity for every customer and group the delivery date by monthe

( i want the total quantity for every customer in the month ) thanks again

 

[moustafa]

I'm waiting for you feedback

 

[moustafa]

i want the total quantity for every customer in the month ) thanks again

 

[CazB]

Try this?

SELECT [Customer Name], Sum([Quantity]), [DeliveryMonth]
FROM 
(
SELECT Customer_Name, Quantity, Delivery_Date, Month(Delivery_Date) AS DeliveryMonth
FROM WINDMILL
UNION ALL
SELECT Customer Name, Quantity, Delivery_Date, Month(Delivery_Date) AS DeliveryMonth
FROM CRUSHER
) AS U 
GROUP BY Customer_Name, DeliveryMonth;

 

[moustafa]

thanks very much for your support

 

[moustafa]

Hi every one
i want to group by week , month , customer_name, type
so what;s the wrong in my code :

SELECT U.[Customer_Name], Sum(U.[Quantity]) AS SumOfQuantity, U.[DeliveryWeek], U.[DeliveryMonth], U.[TYPE]
FROM (SELECT Customer_Name, Quantity, Delivery_Date, ,Datepart("ww",Delivery_Date) AS DeliveryWeek ,Month(Delivery_Date) AS DeliveryMonth,TYPE
FROM WINDMILL
UNION ALL
SELECT Customer_Name, Quantity, Delivery_Date,Datepart("ww",Delivery_Date) AS DeliveryWeek , Month(Delivery_Date) AS DeliveryMonth, TYPE
FROM CRUSHER
) AS U
GROUP BY U.TYPE, U.DeliveryMonth, U.DeliveryWeek, U.Customer_Name;