day before

[Guest]

hi,

I have the following query:
SELECT tbl_account_count_history.status,
tbl_account_count_history.popenterdt,
tbl_account_count_history.countofstatus, Count(tbl_masterpop.[Loan Acct #])
AS [CountOfLoan Acct #]
FROM tbl_account_count_history INNER JOIN tbl_masterpop ON
(tbl_account_count_history.popenterdt = tbl_masterpop.PopEnterDt) AND
(tbl_account_count_history.status = tbl_masterpop.Status)
GROUP BY tbl_account_count_history.status,
tbl_account_count_history.popenterdt, tbl_account_count_history.countofstatus
ORDER BY tbl_account_count_history.status;

here are some sample results:
status popenterdt countofstatus CountOfLoan Acct #
BK_CURR 6/1/2006 789 2339
BK_CURR 6/3/2006 94 610
BK_PDB1-30 6/1/2006 5666 3613
BK_PDB1-30 6/3/2006 921 395
BK_PDB1-30 6/5/2006 251 87
BK_PDB1-30 6/9/2006 1166 688

I want to be sure that the [countofloan acct #] is from the day before, or
tbl_account_count_history.[popenterdt] - 1 day. As you can see, the query
joins [popenterdt] on equal days. How can I "offset" the results to where
the [countofloan acct #] reflects the table value of
[tbl_account_count_history] minus one day?

Thanks in advance,
geebee

 

[Guest]

Create a select query and create an additional output field like --
Yesterday: tbl_account_count_history.[popenterdt]-1

Use the query and join on Yesterday.