MS Access 2003 Query

[easg]

I need to create a DB that controls the inventory, so I created two tables one for the entries and one for the issues, but now I can`t create a query that join the two tables as one and make the rest because if an Item that has an entry in the table of entries but has not movements in the table of issues, the query doesn`t include the item for example if I have 20 item of x product but I haven`t sale no one, the item is not included in the query and if I sale a product that has no entry in the table of entries the item is not included in the query, is there a way to qet a query to work in this escenario?

[sdkfz]

Not exactly sure of what you need here, but to get the wheels going....

In the query when you have it in desgin view the two tables should be linked by a common denominator - say 'item inventory control number'. Now when the two are linked the default connection is 'show me records in both tables where there is a common item in both tables', but you can change this by right clicking on the connector line, the other two options are give me all of table X and related records in Y and vice versa. In each of these cases you get the entire table and anything found in the other, so in the inventory table as 'all of' you would get all the rows of data for the fields selected from that table and if there were any sales - then the data for the fields you select from the sales table.

or am I way off base here?

[easg]

It didn`t work, now it duplicates the values, if there is one entry of 10 for x item and there are 2 exits of 3 it duplicates the entrie of 10 and sums 20

[faulkner132]

I have never used the Access query wizard, but you can accomplish what you need to by using LEFT JOIN.

When you create your "link" between tables, instead of using JOIN or INNER JOIN, use LEFT JOIN instead. This will bring over everything from the FROM table and only matching items in the JOIN table.

It's a bit hard to explain, but a simple Google search will produce better explainations than mine. This should get you started though.

[sdkfz]

faulkner - the left join is what I gave easg which they claim is not what they are looking for....

easg - since we (I) are/am not getting it - would you mind trying to restate the question? also could you post the SQL statement of the query that you have now? To do this in design view - go to the menu bar and select view and choose SQL view, then you can copy and paste the text out- this does not harm the query and you can go back to the mnue bar and change the view back

[rcvalencia9]

Are you sure you created a Foreign Key this will be important when you use the JOIN clause. A good foreign key is [Item Category] if you're selling products.

Important Note: When you connect two tables using query, Table 1.Foreign key has at least a match in the 2nd Table. For Example in your first table you have 20 Asus P5GD1 MOBOS and the the [Item Category] of each item is "Motherboards". The data "Motherboards" under the [Item Category] should also exist in the 2nd Table to create a query.

[easg]

SELECT [Detalle Entradas Almacen].ID_Articulo, Sum([Detalle Entradas Almacen].Cantidad) AS SumaDeCantidad, Sum([Detalle equipos reparados].Und) AS SumaDeUnd
FROM ([Catalogo de Articulos] INNER JOIN [Detalle Entradas Almacen] ON [Catalogo de Articulos].Id_Articulos = [Detalle Entradas Almacen].ID_Articulo) INNER JOIN [Detalle equipos reparados] ON [Catalogo de Articulos].Id_Articulos = [Detalle equipos reparados].Id_Articulo
GROUP BY [Detalle Entradas Almacen].ID_Articulo;

This is the sql view of the query, and there is an attach with a general view of the relationships of the db, what I want is a tipical inventory report etries exits and difference, but it only takes in count the movements that has registries in the two tables, if I change the Join config it duplicates the values of the item to fill empty spaces, thus creating duplicated movements.

[DougL]

I found this particular website to be an excellent information area for access problems etc.

http://www.utteraccess.com/

Doug