Query is REALLY stumping me

[Abraxus]

I have a BIG list of vendors with their daily spend and the account number it was spent against.

I've been asked to identify the vendors that have spent anything since 1/1/2009 where ALL of their spend was against one of 7 account numbers.

I'm lost and frustrated.

Any suggestions?

Thanks!

[HansV]

Just to make sure - the "ALL of their spend" applies to spendings since 1/1/2009?

[Abraxus]

Correct. Sorry I was not more clear.

[HansV]

I'd create two queries.

First create a query based on the table with the expenditures.
Add the Vendor, Spending Date and Account Number fields to the query grid.
Clear the Show check box for the last two columns.
In the Spending Date column, enter the following in the Criteria row:

>=#1/1/2009#

In the Account Number column, enter the following in the Criteria row:

Not In (11, 23, 58, 132, 134, 558, 914)

substituting the 7 account numbers you want to concentrate on.
In the above line, I have assumed that the Account Number is numeric; if it is a text field, enclose the values in quotes:

Not In ("11", "23", "58", "132", "134", "558", "914")

Save this query as qryWrongAccount.

Next, create another query based on the expenditures table.
Set the Unique Values property of this query to Yes.
Add the Vendor and the Spending Date fields to the query grid.
Clear the Show check box for the second column.
In the Vendor column, enter the following in the Criteria row:

Not In (SELECT VendorID FROM qryWrongAccount)

substituting the actual name of the Vendor field.
In the Spending date column, enter the following in the Criteria Row:

>=#1/1/2009#

This query should return the vendors you want.

[Abraxus]

As cool as this sounds to try, whenever I run the query, it never completes. I've had it going for over 24 hours this last time with no luck.

I took out all the date criteria as I realized my data already met that criteria and I have an index on the vendor id field.

Any suggestions?

My table has 1.4 million records, if that helps...

[HansV]

Try the following:

1) Create a table tblInclude with a single field Account of the same type as the Account field in the expenditures table; make this field the primary key.
Add a record for each of the Accounts that you want to focus on (so the table will have 7 records).

2) Create a query based on the expenditures table and on tblInclude.
Join them on the Account field.
Double click the join line and select the option to include ALL records from the expenditures table, then click OK.
Add the Vendor and Spending Date from the expenditures table and the Account field from tblInclude to the query grid.
In the Spending Date column, enter the following in the Criteria row:

>=#1/1/2009#

In the Account column, enter the following in the Criteria row:

Is Null

Save this query as qryWrongAccount.

Note: if this query is slow to open, or if the next step is slow, consider changing qryWrongAccount into a make-table query and running it to create a table with the accounts that you want to avoid.

3) Next, create a new query based on the expenditures table and on qryWrongAccount.
Join them on the Vendor field.
Double click the join line and select the option to include ALL records from the expenditures table, then click OK.
Set the Unique Values property of this query to Yes.
Add the Vendor and the Spending Date fields from the expenditures table and the Vendor field from the query to the query grid.
Clear the Show check box for the last two columns.
In the Spending date column, enter the following in the Criteria Row:

>=#1/1/2009#

In the Vendor column from the query (NOT that from the table!), enter the following in the Criteria row:

Is Null

If this is still too slow, replace qryWrongAccount in this last query with the table resulting from changing it into a make-table query and running it.

If that is too slow too, it's time to move to SQL Server...