Combining two Excel files in Access to create a query

[adasko2asy]

Hello,
I’m working with Microsoft Access and I have two files that I would like to combine into a single query.
File 1: MasterData – this file contains all previously produced product codes with the following columns:

1. Product Id Description
   
2. Material number
   
3. Material - Mag
   
4. Wee category
   
5. WeeWeight
   
6. Battery Weight
   
7. Battery Qty
   
8. Battery type

File 2: Sales – this file contains information about products sold in a given quarter, with these columns:

1. Product ID
   
2. id_product id description
   
3. id_material number
   
4. id_material - Mag
   
5. invoiced qty

The column id_product id description in Sales contains product descriptions that can also be found in the MasterData file (Product Id Description). Similarly, id_material number in Sales contains product codes that are also present in MasterData.
My goal is to create a query in Access that displays the following columns combining data from both files:

1. Product ID
   
   
   
2. Product ID Description
   
3. Material number
   
4. Material - MAG
   
5. Invoiced Qty
   
6. Weee category
   
7. Wee weight
   
8. Total Wee

Is it possible to create such a query in Access that merges these two tables and displays the requested information?
Thank you in advance for your guidance!

[Welshgasman]

Link to both Excel Files and then use whatever join you have to bring in the data.

Work from there. I would be using ProductID to link NOT descriptions.

[adasko2asy]

Wow, i don't know what to say but this answer not help me. I contacted both file. I found codes wchich are missing in masterdata file but i would like to add this codes to masterdata. How to do it ?

[Welshgasman]

If you have those missing codes, then you can use that as the source for an append query.

[xps35]

Creating the desired query is certainly possible. That's precisely what queries are intended for.

The question, however, is to what extent the two files are consistent. In a well-designed database, you would only include the product ID in the sales table of the master data.
So, what if the product descriptions in both files differ with the same ID?

[adasko2asy]

Hi,

so i will maybe ilustrate. I have two table as you can see below. Sales file is the file where i sell goods in month and masterdata where there are all codes but when product is new and selled there is possible that it will be not visible in masterdata so i would like to create a button where i will have possibility to add this codes from sales to masterdata next with all information where are avaiable in masterdata table create a raport for codes selled in this month. Maybe it will help.

[Welshgasman]

It should be added to the master table BEFORE you ever try and sell any of it.

[adasko2asy]

It is possible to add by button ?

[Welshgasman]

You could use NotInIst if using a combo

[xps35]

It would really help if you post a copy of your database.

[ArviLaanemets]

... I have two table as you can see below. Sales file is the file where i sell goods in month ...

How will Access know, to which month data in tbl_sales belong? There must be a field for sale dates, or at least for sale months (in format YYYYMM). And a field for client id too, unless you have a single client (or invoice number which will be an id-field of invoices table which will be linked with clients and client order tables).

[Welshgasman]

My view is that the data should be in the MasterData table before even trying to use it in any other table.

So use the NotInList even to add new products as you try and sell them. It is only a nice to have. The old way was to add it to the MasterData table first in one form, then use it for Sales in another form. You are doing the same thing but pretty much automated.

https://www.youtube.com/results?sear...ess+notinlists

[mike60smart]

Hi
Can you upload a copy of the database?