Sum with two conditions (ACCESS Query) - equivalent to Excel sumif

[donrafy]

Hi

I've always worked with Excel to do everything, but with the increase of my data, the program started to get heavy and decreased very information query predicate.
Since I decided to start using access, which is serving my needs parts.


The intention is to create a query table that will look up information from my database and perform some operations. After the completion of this Article, the Commission has sent a report.


I need to add all information of my database that contains two information for my query data.
What would be like "sumifs" Excel function
Could someone give me a light on this?
Thank you very much already.

[ranman256]

You don't sumIf, you put the criteria in the where clause,
then sum the fields needed.

[donrafy]

Could you give me an example?
I have two database table let say database1 and database2, ok?
If I understand it right I should create a query design and then I can put All variables of my database1.
Example:
Data base1
|City name | product | need
|Salvador | Car 1 | 5
|São Paulo | Car 2 | 3
.......
In my database2 I have the same information of my Data base 1 and some others information like this:
|City name | product | offer
|Salvador | Car 1 | 1
|São Paulo | Car 2 | 10
|Salvador | Car 1 | 1

In my query design I like that comes this information:
|City name | product | need | total in stock
|Salvador | Car 1 | 5 | 2
|São Paulo | Car 2 | 3 | 10

How I can code in access to make this sum from one database with the other when I have two conditionals (City name and product).

[ArviLaanemets]

Hi

It looks like you put your Excel dable design directly into Access. Maybe you start over with database instead worksheet :-)

A probable database structure:

a) Registries to avoid typing errors on entry. When count of ID's in registry will be less than max long integer, then you can use autonumeric fields as ID's.
tblCities: CityID, CityName, ...
tblProducts: ProductID, ProductName, ...

b) Data tables. Based on data in your example.
tblMovements: MoveID, CityID, ProductID, MoveTime, MoveType, Qty, ... (where MoveType can have values p.e. 1 - incoming, 2 - outcoming)
NB! Offer for given time moment will be sum of all incoming quantities earlier than this time moment minus all outcoming and quantities earlier than this time moment. To avoid calculating through a very big table at start, you can have an inventory table (tblInventory: InvID, InvDate, CityID, ProductID, Qty), where you periodically store the available quantity - then you can use this as base for offer calculation. The best way to get an offer for given time moment will be to use an User Defined Function, which you can call in any query or on any form whenever needed.

tblOrders: OrderID, OrderNo, Client, OrderDate, ProductID, DeliveryDate, Qty, OrderStatus, ... (where OrderStatus must have values, p.e. 8 and 9, which indicate dropped or fulfilled order respectively)

So your example data will look like:
tblCities:
CityID, CityName
1 Salvador
2 São Paulo

tblProducts:
ProductID, ProductName
1 Car 1
2 Car 2

tblStock:
StockID, CityID, ProductID, MoveTime, MoveType, Qty
1 1 1 01.01.2017 1 2
2 2 2 01.01.2017 1 10 // 1st 2 rows are database setup values for case the Firm did exist before
3 1 1 01.04.2017 2 1
4 1 1 01.05.2017 1 1

Let's look how to calculate InStock for all cities and products for given date, p.e. 15.05.2017:
SELECT
CityID, ProductID, SUM(Iif(MoveType = 1, 1, -1)*Qty) As InStock
FROM
tblStock
WHERE
Year(MoveTime) <= DateSerial(2017,5,15)
GROUP BY
CityID, ProductID
(there are simpler ways to compare dates, but this one is foolproof one, as doesn't depend on regional settings)

The query is working, but Access limitations make it often difficult to use as source for other queries. The one solution is to make it a parametrized query and save it. The other solution is to write an User Defined Function, p.e. GetInStock(CityID, ProductID, DateTime)

tblOrders:
OrderID, CityID, ProductID, OrderDate, DeliveryDate, Qty, OrderStatus
1 1 1 01.05.2017 15.05.2017 5 1
2 2 2 01.05.2017 15.05.2017 3 1

And to get comparision of Need and InStock
SELECT
ord.CityID, ord.ProductID, SUM(ord.Qty) As Need, GetInStock(ord.CityID, ord.ProductID, DateSerial(2017,5,15))
FROM
tbOrders ord
WHERE
ord.DeliveryDate <= DateSerial(Year(2017,5,15) And ord.OrderStatus < 8
GROUP BY
ord.CityID, ord.ProductID