Pull Specific Data by Previous Month's Date (Date Entered via prom

[Vylent Fyre]

Below is the table that houses the information (I'm just showing November and
December 2007 for simplicity's sake.)


Month Company Curr Month BV Amount Due ST Bal Correcting JE
11/1/2007 010 $211,532.48 $8,503.28 $37,697.89
$70,078.97
12/1/2007 010 $236,614.35 $9,023.38 $37,697.89 $70,078.97

I'd like to be able to have a query that will calculate the Curr Month BV
from the Prev Month BV by specifying the date and will show you the Prev
Month's BV from the date you specified in the prompt. For example: You run
the query, it asks you for the Month, you type in 12/1/2007, I want it to
take 12/1/2007's BV and subtract it from 11/1/2007 BV's (previous month) and
also to show both the Curr Month BV (12/1/2007) and Prev Month BV (11/1/2007)
from the date specified in the prompt.

is this possible? I had no luck with a crosstab query, either.

 

[Conan Kelly]

Vylent,

See if this will work:

SELECT MainTable.Month, MainTable.Company, MainTable.[Curr Month BV],
(SELECT [Curr Month BV] FROM Table2 AS PrevMonthSub1 WHERE MainTable.Company
= PrevMonthSub1.Company AND PrevMonthSub1.Month = (SELECT MAX(Month) FROM
Table2 AS PrevMonthSub2 WHERE MainTable.Company = PrevMonthSub2.Company AND
MainTable.Month > PrevMonthSub2.Month)) AS [Prev Month BV],
MainTable.[Amount Due], MainTable.[ST Bal], MainTable.[Correcting JE]
FROM Table2 AS MainTable where MainTable.Month = [Please enter a month];

I would copy & paste the SQL above into notepad, do a find & replace, find
"Table2", replace with the name of your table (make sure to include square
brackets if your table name has spaces or other special characters), then
copy from notepad into AC's SQL View of the query.

I've tested this with the small sample you provided...seems to work.

I did not do the calculation with "Curr Month BV" and "Prev Month BV". I
don't think AC will let you do that in the same query...you might have to
expierment with that. If it does not, save this query and then create a new
query based on this one, and you can do your calculation in the new query.

HTH,

Conan