SQL doubts - two consolidated tables (excel sheets)

Asked

Viewed 498 times

0

Hello. I have a question, which I believe is simple for forum veterans. I don’t have much notion of SQL, but I need to integrate two excel files One of stock and one of sales

They are two different xls. The two have a BD (tab) called "General Base".

I have columns of brand, product, month, year and values... where:

In the stock sheet, my value field is called STOCK and sales, SALE

I need to create a third spreadsheet that consolidates the two via Query, where I will take the columns of (BRAND / PRODUCT / YEAR / MONTH) of the two and I will take the STOCK and SALES also, besides creating a third column.. In this third column, called "STOCK EXIT", the formula is as follows::

SAIDA ESTOQUE = Estoque do mês anterior* + venda - estoque do mês atual

This way, in the stock of the previous month, you would need to make a preview or summation, pulling the value of the previous month... for example. if I bring a line that has month of February, should bring (JANUARY STOCK + February sales - February Stock)

If someone helps me solve this, I really appreciate it. PS: I’ve used Union in sql, but I’ve paired columns with different names

[![image 1 - stock base][1][1]

[![image 2 - sales base][2][2]

imagem 3 - esta seria a planilha final.. onde eu conseguiria agrupar os dados da base de Estoque e de vendas

unfortunately I cannot insert all images

sql

  • it would be useful to put some code so that we can check only words it is difficult to see your real problem if you have read https://answall.com/help/mcve

  • You are importing the xls for some table and want to join the data with SQL? That’s it?

  • actually not Marcelo... I am throwing the two excel files directly to another excel file... in (Data > from other sources >Microsoft Query > excel files).... what I wanted was to unite this query automatically.. this way, every time the two "bases" were updated.. when I updated this query, I was able to replicate what I want to the table

1 answer

1

You can do it this way:

Step 1: Create query for file 1 (Image 1)

Step 2: Create query for file 2 (Image 1)

Step 3: Create a new query Through the "Combine Queries" > "Start Query Editor" menu (Image 2)

Step 4: Keep the desired columns of each query if necessary, you can right-click to rename the columns.

Imagem 1

Imagem 2

  • good afternoon Alexandre.. actually, I didn’t want to upload the data as table. i have two excel files, relatively heavy... what I would like to do is have a new excel, where I can update the data table and be able to make the information available automatically... I have never uploaded query data, using two tables... generally, I do it using a single file.. and just for the purposes of consultation.... this time, I wanted to integrate the two files and include some operations in the file that will consolidate the two tables... I don’t know if this is really possible

  • Understood, this could also be done in SQL and then carry to Excel. But I edited the answer to use the queries directly in Excel.

  • Thanks for the help.... the problem is that I do not know if this is available in Excel 2010. I even managed to do the integration using right Outer Join... and Inner Join.... the problem is that I would need a complete query that has all the values.. but it’s giving error when I try to apply Full Outer Join.. I even posted the example on another forum where I can attach files... that’s temporary. I needed to consolidate a base here.. I’ll try to enroll in a database course as soon as possible

  • Stack Overflow is not a forum.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.