VBA coding

[boboleft]

I have a MS access table that contain data which I manually compared to find out the variance between the current week balance vs. balance at beginning of the year at parent acct# level. I am looking to automate this process as much as possible. Not sure if Visual Basic or other coding may help me automate this process. I am just starting out in VBA and need a lot of help with this coding.

Big thanks in advance for helping me out...

MS access table

ReportDate Parent# Child# Balance
1/1/2010 5525 6801 500
1/1/2010 5525 6802 500
1/1/2010 5525 6803 500
1/1/2010 5526 6804 1000
1/1/2010 5527 6805 700
1/1/2010 5527 6806 850
2/5/2010 5525 6801 300
2/5/2010 5525 6802 700
2/5/2010 5525 6803 500
2/5/2010 5526 6804 1000
2/5/2010 5527 6805 700
2/5/2010 5527 6806 850
3/18/2010 5525 6801 1000
3/18/2010 5525 6802 500
3/18/2010 5525 6803 500
3/18/2010 5526 6804 1700
3/18/2010 5527 6805 900
3/18/2010 5527 6806 750

Output

Parent# Beginning balance Ending Balance Variance
5525 1500 2000 500
5526 1000 1700 700
5527 1550 1650 100

 

[MajP]

This can be done in SQL without code. Someone smart can show you how to do it in one big query. I broke it into a few parts to keep it simple

qryStartBalance

SELECT 
 tblData.ReportDate AS StartDate, 
 tblData.[Parent#], 
 Sum(tblData.Balance) AS StartBalance
FROM 
 tblData
GROUP BY 
 tblData.ReportDate, 
 tblData.[Parent#]
HAVING 
 (((tblData.ReportDate) In (select Top 1 reportDate from tblData as A where tblData.[Parent#] = A.[parent#])));

qryEndBalance

SELECT 
 tblData.ReportDate AS EndDate, 
 tblData.[Parent#], 
 Sum(tblData.Balance) AS EndBalance
FROM 
 tblData
GROUP BY 
 tblData.ReportDate, 
 tblData.[Parent#]
HAVING (((tblData.ReportDate) In (select Top 1 reportDate from tblData as A where tblData.[Parent#] = A.[parent#] order by ReportDate DESC)));

use those in
qryStartEndDeltas

SELECT 
 qryEndBalance.[Parent#], 
 qryStartBalance.StartDate, 
 qryStartBalance.StartBalance, 
 qryEndBalance.EndDate, 
 qryEndBalance.EndBalance, 
 [EndBalance]-[StartBalance] AS Difference
FROM 
 qryEndBalance 
INNER JOIN 
 qryStartBalance 
ON 
 qryEndBalance.[Parent#] = qryStartBalance.[Parent#];

 

[boboleft]

Thanks for the input!! How can I combine these 3 queries into one query and just generate one output so I can copy them into a excel file?

thanks

Kristi

 

[MajP]

The last query does that. Here is the result

Parent#	StartDate StartBalance EndDate   EndBalance Difference
5525    1/1/2010  $1,500.00   3/18/2010  $2,000.00  $500.00
5526    1/1/2010  $1,000.00   3/18/2010  $1,700.00  $700.00
5527    1/1/2010  $1,550.00   3/18/2010  $1,650.00  $100.00

I just use the first two queries as input for the third.
Here is the demo

http://www.4shared.com/file/258314369/984aa5a7/balance.html

 

[jrbarnett]

The last of MajP's queries does exactly that. What you do is then copy the results and paste into excel, or use the Access "Export to Excel" functionality.

John

 

[PHV]

A single query without TOP:

SELECT A.[Parent#]
, Sum(IIf(ReportDate=B.BegDate,Balance,0)) AS [Beginning balance]
, Sum(IIf(ReportDate=E.EndDate,Balance,0)) AS [Ending balance]
, Sum(IIf(ReportDate=E.EndDate,Balance,0))-Sum(IIf(ReportDate=B.BegDate,Balance,0)) AS Variance
FROM ([MS access table] A INNER JOIN (
SELECT [Parent#], Min(ReportDate) As BegDate FROM [MS access table] GROUP BY [Parent#]
) B ON A.[Parent#] = B.[Parent#])
INNER JOIN (
SELECT [Parent#], Max(ReportDate) As EndDate FROM [MS access table] GROUP BY [Parent#]
) E ON A.[Parent#] = E.[Parent#]
GROUP BY A.[Parent#]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[boboleft]

Thanks for everyone’s help. The query works perfect. Just one more question, How can I change the query to output the status changes as listed below??

ReportDate Child# status
1/1/2010 6801 active
2/1/2010 6801 active
3/1/2010 6801 active
4/1/2010 6801 inactive


Output

Child# Old status New Status
6801 active Inactive

 

[PHV]

The query works perfect
Which one ?
output the status changes
I didn't notice any mention of a status field in your previous posts, so it seems to be a new requirement and thus, please, ask in a new thread.

 

[boboleft]

Sorry, this another report I need help with. Let me post it as a new one.

 

[boboleft]

Question, I have tried to place the following query into the macro "RunSQL" so I can schedule the macro to run automatically. It appeared the query is too long, it won't even let me paste it. Are there any other ways I can use to schedule this query to run automatically??

thanks

 

[jrbarnett]

Access does not have any sort of scheduling mechanism to run something on a regular basis.

You would need to set code to run at database startup, then run the database through the windows scheduler.

John

 

[boboleft]

Hi,

Are there any ways to put this in a ms macro so it will output the data to an excel file?? I have been manually copy and paste the query every week to run the query and copy and paste the data to an excle. Thanks in advance for the help.

SELECT A.[Parent#], Sum(IIf(ReportDate=B.BegDate,Balance,0)) AS [Beginning balance], Sum(IIf(ReportDate=E.EndDate,Balance,0)) AS [Ending balance], Sum(IIf(ReportDate=E.EndDate,Balance,0))-Sum(IIf(ReportDate=B.BegDate,Balance,0)) AS VarianceFROM ([MS access table] A INNER JOIN (SELECT [Parent#], Min(ReportDate) As BegDate FROM [MS access table] GROUP BY [Parent#]) B ON A.[Parent#] = B.[Parent#])INNER JOIN (SELECT [Parent#], Max(ReportDate) As EndDate FROM [MS access table] GROUP BY [Parent#]) E ON A.[Parent#] = E.[Parent#]GROUP BY A.[Parent#]