SumIF in access

[sharkantipav]

Hello All,


I am a newbie, in Access programming.
I have created a database with 10 tables - 5 tables reporting sales of 5 differents product and 5 statics tables (customers, salesman, billing...)
In 5 first table I have some fields that are the same names Broker1 Broker2 Date CMSN1 CMSN2
The field Broker1-2 can take 5 different value A B C D E
I want to create a form where I can select a date or a range of date
Then calculate for each broker Sumif Broker = A, CMSN1+CMSN2 etccc and display as an array
Thank you very much

[June7]

Each product has its own table? Are the fields the same for all? Should be one table.

Multiple similar name fields indicates a non-normalized data stucture and usually makes what you want difficult to accomplish. You have only 2 fields for brokers? You have 5 brokers? You want to sum by broker name/ID? If you hard code this then what if the brokers change (people come and go)? Will have to modify code.

A UNION query can rearrange the broker data to a normalized structure then use that query to summarize by broker. There is no wizard for UNION, must type or copy/paste into SQL View of query builder.

SELECT ID, Broker1 As Broker FROM table
UNION SELECT ID, Broker2 FROM table;