Table vs. Query

[RobSchultz]

Is there a difference to Access/Jet that would keep me from using a query in the same manner as a table?

Here is a simplistic representation of what I am trying to accomplish...

tblAccounts
accountID (autonumber)
isInactive (yes/no)
1 0
2 -1

tblBalanceHistory
balanceID (autonumber)
accountID (FK to tblAccounts)
balanceDate (date/time)
balance (currency)
1 1 6/24/2004 $5.00
2 1 5/31/2004 $25.00
3 2 6/24/2004 $37.00
4 2 5/31/2004 $0.00

qryPreviouslyInactiveAccounts
gives me a list of all accountIDs where the account is currently inactive, received a balance this month, but didn't have one last month. Outputs the following...
accountID isPreviouslyInactive (always -1)
2 -1

I have populated a tblTest to mimic the results of qryPreviouslyInactiveAccounts...
accountID (have tried making this a prikey or not)
isPreviouslyInactive = -1 (have made this both a number and a yes/no).
I have populated it with the same info as the query...
2 -1


I need a query to list all of the accounts and whether or not they are previously inactive but when I use the following query...

SELECT acc.accountID, pia.isPreviouslyInactive, test.isPreviouslyInactive
FROM (tblAccounts AS acc LEFT JOIN tblTEST AS test ON acc.accountID = test.accountID) LEFT JOIN qryPreviouslyInactiveAccounts AS pia ON acc.accountID = pia.accountID;

I get back the following

accountID qryPIA tblTest
1 -1
2 -1 -1

IOW, the tblTest join returns back accurate results while the qryPreviouslyInactiveAccounts join always returns back -1.

I know I can pre-populate the "tblTEST" with data from the query and use it or change the qryPreviouslyInactiveAccounts to include all accounts but they both seem very inelegant especially in light of the fact that tblTEST works properly.

Please advise,

Rob

-Focus on the solution to the problem, not the obstacles in the way.-

 

[RobSchultz]

Nevermind,

It all had to do with the fact that I was pre-seeding the qryPreviouslyInactiveAccounts.isPreviouslyInactive with -1 rather than tblAccounts.isInactive.

Regards,

Rob

-Focus on the solution to the problem, not the obstacles in the way.-