Others

How to Use Access Query IF Logic for Multiple Vendors and Delivery Status

Maira Mehtab Sep 22, 2026 871 views

Question details

The user needs to evaluate whether products associated with multiple vendors (Vendor 1 and Vendor 2) have been ordered and delivered for inspection within an MS Access report.

Product: Microsoft Access
Device & OS: not provided
Scenario: Creating a consolidated report for projects that use multiple vendors, which requires checking both the 'Ordered' and 'Delivered' statuses for each vendor.
Observed behavior: The current database design separates Vendor 1 and Vendor 2 into different fields or queries, requiring complex IF logic or query adjustments to evaluate and merge their delivery statuses onto a single report.

Before you start

Review your current database table structure to see if vendors are listed as separate columns (e.g., Vendor1, Vendor2); normalizing your tables first can save significant query-building time.

Solution 1Recommended

Normalize the Database Tables

Redesigning the table to avoid repeating groups like Vendor1 and Vendor2 is the most robust and scalable solution for Access queries.

Having repeating fields such as Vendor1 and Vendor2 is considered poor database design because it violates the First Normal Form (1NF). Consolidating these into a related table makes querying straightforward.

Create a related Vendor Orders table

Go to the 'Create' tab and select 'Table Design'. Create a new table that stores one vendor per record (e.g., fields for ProjectID, VendorID, OrderedStatus, DeliveredStatus).

Establish table relationships

Navigate to 'Database Tools' > 'Relationships'. Link your main Project table to the new Vendor Orders table using the ProjectID field.

Filter the query

Open your query in Design View. Add the new Vendor Orders table and set the Criteria for the 'OrderedStatus' and 'DeliveredStatus' fields to 'True'. This automatically filters all vendors without needing complex IF logic.

Best Practice: Database normalization prevents the need to constantly update your queries and reports if a third or fourth vendor is added to a project in the future.

Solution 2

Create a Custom Boolean VBA Function

If you cannot change the existing table design, use a custom VBA function to evaluate the complex IF logic for multiple vendors.

Solution 3

Combine Vendor Conditions Using an IIf Expression

Use the built-in IIf (Immediate If) function directly in the Access query builder to evaluate multiple fields without writing VBA code.

Free Microsoft Office alternative

Manage Data and Reports Effortlessly with WPS Office

While Microsoft Access is utilized for complex relational database management, you can often handle flat-file databases, evaluate multiple vendor conditions using advanced IF logic, and generate insightful reports using WPS Spreadsheet. WPS Office provides a complete, lightweight suite tailored for high productivity.

1. Download and Install: Get WPS Office from the official website and install it on your device in minutes.
2. Open WPS Spreadsheet: Launch the application and import your existing tabular data or exported database CSV files.
3. Apply Logic Functions: Use built-in functions like IF, AND, and OR to quickly evaluate vendor delivery statuses and build comprehensive reports.

Completely free, lightweight, and fast installationHighly compatible with Microsoft Excel (.xlsx) formats and nested IF functionsFamiliar user interface ensuring seamless migration without a steep learning curveCross-platform support for Windows, Mac, Linux, iOS, and Android

microsoft office alternative - wps office

Frequently Asked Questions

Why is having Vendor1 and Vendor2 fields considered bad database design?

Repeating fields like Vendor1 and Vendor2 violate database normalization rules (specifically First Normal Form). It makes querying overly complex, requires redesigning tables/queries if a third vendor is added, and often results in empty data fields.

Can I combine two separate Access queries into one report?

Yes, you can use a UNION query in SQL View to append the results of a Vendor 1 query and a Vendor 2 query into a single dataset. This combined dataset can then be set as the Record Source for your final report.

How do I write a multiple-condition IF statement in an Access query?

In Access queries, you use the IIf (Immediate If) function rather than standard IF statements. You can combine multiple conditions using the AND/OR operators within the condition argument, such as: IIf([Field1]=True AND [Field2]=True, 'ResultIfTrue', 'ResultIfFalse').

Office Quick Solutions