How to create temporary/table table and insert test data in Access VBA

[chieh]

The following is a code is in FoxPro, it creates temporary table and insert test data, How can I do this is Access 2003?

Thank you in advance.
Ch.

*
* Create test table for February Month, and populate test data
*
Create Cursor C_FebData ( Month C(3), company C(10), Client C(15), Account C(6), AccType C(4), Amount N(12,0) )

Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "ABCInc", "Sam", "ACC001", "Cash", 9000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "ABCInc", "Sam", "ACC002", "Cash", 5000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "ABCInc", "Sam", "ACC003", "GIC", 8000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "ABCInc", "Peter", "ACC004", "Cash", 2000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "ABCInc", "Peter", "ACC005", "Cash", 3000)

Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "DEFInc", "Jill", "ACC010", "Cash", 10000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "DEFInc", "Jill", "ACC011", "Cash", 5000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "DEFInc", "Jill", "ACC012", "GIC", 7000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "DEFInc", "Jill", "ACC013", "GIC", 6000)

Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "XYZInc", "Smith", "ACC020", "Cash", 7000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "XYZInc", "Smith", "ACC021", "GIC", 2000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "XYZInc", "John", "ACC022", "Cash", 5000)

 

[PHV]

Use a Recordset.

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

 

[chieh]

Hi PH:

I am new to Access, could you explain this with an example.

Thanks
Ch

 

[PHV]

What have you tried so far and where in your code are you stuck ?
Tip: Google is your friend

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

 

[Andrzejek]

Hey PHV,
Would it also work if chieh would have all the INSERT statements in some text file (MyInsertStmts.txt) and read the file and run the statements like this? :

Dim strTextLine As String

docmd.runsql "DELETE from C_FebData"

Open "C:\SomeFolder\MyInsertStmts.txt" For Input As #1
Do While Not EOF(1)      
   Line Input #1, strTextLine 
   docmd.runsql strTextLine 
Loop
Close #1

Have fun.

---- Andy

 

[PHV]

Andy,
Sure, but with a lot of overhead.
A AddNew method will be my preferred way.

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

 

[chieh]

HI PH:

Basically, I have two set of data, same structure, (Feb and Mar), some account in Feb may closed in Mar or the amount may be different, or there is a new account open for the client but not present in Feb. The key of my program (which I have done, in Visual FoxPro) is to create report showing month over month difference, ie.

Feb
ABCINc, John, Acc001, Cash, $9000
ABCINc, John, Acc002, Cash, $6000
XYZInc, John, Acc003, GIC, $5000

Mar
ABCINc, John, Acc001, Cash, $5000
XYZInc, Alan, Acc004, GIC, $2000
XYZInc, Alan, Acc005, Cash, $3000

As you can see from this example, Acc002 is present in Feb. but missing in March and Acc004 is present in March but not in Feb. Acc005 is in March but not in Feb.

So the report (which need to be group by Account Type) will look like this:

From_to Company, Client, Amount, Company, Client, Amount, Difference
====================================================================
Feb=>Mar Cash
ABCInc, John, $15000, ABCInc, John, $5000, $-10000
n/a, n/a, $0, XYZinc, Alan, $3000, $3000

Feb=>Mar GIC
ABCInc, John, $5000, n/a, n/a, $0, $-6000
n/a, n/a, $0, XYZinc, Alan, $2000, $-2000


Is there an option where I can attach a screen shot of jpg file to describe what I typed, the jpg screen shot are line-up properly...

thanks
ch.

 

[chieh]

HI PH and Andy:

Here's my code in Visual FoxPro and the Access code I did so far....:

===========
FoxPro Code
============

* CompTest.prg
* Compare data between two period, calculate total, etc. and then export the result to Excel
*
* The challange is to create a report THAT show SIDE-BY-SIDE month difference in Amount group by AccType+Client+Company,
* and at the same time show the details the amount breakdown by company and client.
* e.g.
* If the client is available in both the months, the row will show both month's info side-by-side,
* If the client is not available in Feb month, but in March, the row will show N/A in Feb and March info on March side
* If the client is available in Feb, but not in March, the row will show Feb data and N/A on the March side
*
******************************************************************************************************************************

*
* Create test table for February Month, and populate test data
*
Create Cursor C_FebData ( Month C(3), company C(10), Client C(15), Account C(6), AccType C(4), Amount N(12,0) )

Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "ABCInc", "Sam", "ACC001", "Cash", 9000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "ABCInc", "Sam", "ACC002", "Cash", 5000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "ABCInc", "Sam", "ACC003", "GIC", 8000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "ABCInc", "Peter", "ACC004", "Cash", 2000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "ABCInc", "Peter", "ACC005", "Cash", 3000)

Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "DEFInc", "Jill", "ACC010", "Cash", 10000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "DEFInc", "Jill", "ACC011", "Cash", 5000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "DEFInc", "Jill", "ACC012", "GIC", 7000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "DEFInc", "Jill", "ACC013", "GIC", 6000)

Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "XYZInc", "Smith", "ACC020", "Cash", 7000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "XYZInc", "Smith", "ACC021", "GIC", 2000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "XYZInc", "John", "ACC022", "Cash", 5000)

Select C_FebData
Index on Acctype + Company + Client to Temp1


*
* Create test table for March Month, and populate test data
*
Create Cursor C_MarData ( Month C(3), company C(10), Client C(15), Account C(6), AccType C(4), Amount N(12,0) )

Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "ABCInc", "Sam", "ACC001", "Cash", 6000)
Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "ABCInc", "Sam", "ACC002", "Cash", 7000)
Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "ABCInc", "Sam", "ACC003", "GIC", 9000)
Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "ABCInc", "Peter", "ACC006", "GIC", 2000)
Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "ABCInc", "Peter", "ACC007", "GIC", 3000)

Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "DEFInc", "Jill", "ACC010", "Cash", 12000)
Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "DEFInc", "Jill", "ACC011", "Cash", 8000)
Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "DEFInc", "Jill", "ACC013", "GIC", 3000)

Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "XYZInc", "Smith", "ACC020", "Cash", 6000)
Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "XYZInc", "Smith", "ACC022", "Cash", 10000)
Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "XYZInc", "John", "ACC023", "GIC", 5000)
Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "XYZInc", "John", "ACC024", "Loan", 7000)

Select C_MarData
Index on Acctype + Company + Client to Temp2


* Create temp cursor for storing the side-by-side comparison report.
Create Cursor C_Report (From_To C(8), Company1 C(10), Client1 C(15), Amount1 N(12,0), Filler C(2), Company2 C(10), Client2 C(15), Amount2 N(12,0), DiffAmount N(12,0))

* Create temp cursor same as C_Report for temporary storing and sorting the data before append them to C_Report
Create Cursor C_ReportTmp (From_To C(8), Company1 C(10), Client1 C(15), Amount1 N(12,0), Filler C(2), Company2 C(10), Client2 C(15), Amount2 N(12,0), DiffAmount N(12,0))


*
* Find the Unique Account Type across two comparing month's data,
* e.g. In this example, Feb data only have "Cash" and "GIC" account type,
* where as in March, it had "Cash", "GIC", and "Loan"
*
Select distinct(AccType) as AccType from C_FebData ;
Union ;
Select distinct(AccType) as AccType from C_MarData ;
Into Cursor C_UniqueAccType

*
* Group the amount by AccType, Client and Company, e.g. If Peter in Company ABCInc has two cash Account Type,
* (ACC004 = $2000, & ACC005 = $3000), the group amount will be $5000 for Peter of ABCInc.
*
Select C_FebData
brow nowait
select Month, Company, Client, AccType, sum(amount) as clnAmount ;
from C_FebData group by AccType, Client, Company ;
order by AccType, Company, Client ;
Into Cursor C_FebGroup

Select C_FebGroup
Go Top

*
* Group the amount by AccType, Client and Company, e.g. If Peter in Company ABCInc has two cash Account Type,
* (ACC004 = $2000, & ACC005 = $3000), the group amount will be $5000 for Peter of ABCInc.
*
Select C_MarData
brow nowait
select Month, Company, Client, AccType, sum(amount) as clnAmount ;
from C_MarData group by AccType, Client, Company ;
order by AccType, Company, Client ;
Into Cursor C_MarGroup

Select C_MarGroup
Go Top


Select C_Report
Select C_ReportTmp
Index on Client1 + Company1 to Temp3

*
* Create the report
*
Select C_UniqueAccType
Scan All
Select C_ReportTmp
Zap

Select C_FebGroup
Scan All For AccType == C_UniqueAccType.AccType
Select C_ReportTmp
Append Blank
Replace Company1 with C_FebGroup.Company, ;
Client1 with C_FebGroup.Client, ;
Amount1 with C_FebGroup.ClnAmount, ;
Company2 with "N/A", ;
Client2 with "N/A", ;
Amount2 with 0
EndScan

Select C_MarGroup
Scan All For AccType == C_UniqueAccType.AccType
Select C_ReportTmp
seek (C_MarGroup.Client + C_MarGroup.Company)
If found()
Replace Company2 with C_MarGroup.Company, ;
Client2 with C_MarGroup.Client, ;
Amount2 with C_MarGroup.ClnAmount
Else
Select C_ReportTmp
Append Blank
Replace Company1 with "N/A", ;
Client1 with "N/A", ;
Amount1 with 0, ;
Company2 with C_MarGroup.Company, ;
Client2 with C_MarGroup.Client, ;
Amount2 with C_MarGroup.ClnAmount
Endif
EndScan

Select C_ReportTmp
Replace all DiffAmount with Amount1 - Amount2
Sum(Amount1) to lnSum1
Sum(Amount2) to lnSum2

Select * From C_ReportTmp order by DiffAmount Descending Into Cursor C_ReportOrd

Select C_Report
Append blank
Replace From_To with [Feb=>Mar], ;
Company1 with C_UniqueAccType.AccType, ;
Client1 with "", ;
Amount1 with lnSum1, ;
Company2 with "", ;
Client2 with "", ;
Amount2 with lnSum2


Append from DBF("C_ReportTmp")
Append Blank
EndScan

Select C_Report
brow nowait
copy to C_Report.txt type Xls


=====================
and here's my Access Code so far, instead of querying the data from the table I created already in access, I wanted to create the table, insert the test data right inside the vba code, so it would be easier for you to test if I just copy and past teh code here, otherwise I hav to send you the test data somehow.
=============
Public Sub test()

Dim db As Database
Dim strAccType As String
Dim strSQL_AccType As String, strSQL_Feb As String, StrSQL_Mar As String
Dim rs_AccType As DAO.Recordset, rs_FebQry As DAO.Recordset, rs_MarQry As DAO.Recordset
Dim qdf As DAO.QueryDef

'Find the unique AccType across both months. i.e. Cash, GIC, Loan
strSQL_AccType = "select AccType From FebData union Select AccType From MarData Group by Acctype"

Set db = CurrentDb
Set rs_AccType = db.OpenRecordset(strSQL_AccType)
Dim strCompany_Feb, strClient_Feb, strCompany_Mar, strClient_Mar As String
Dim intAmount_Feb, intAmount_Mar, intDifference As Integer

Do While Not rs_AccType.EOF

strAccType = rs_AccType!AccType
Debug.Print "AccType: " & strAccType

strSQL_Feb = "select Company, Client, AccType, sum(amount) as clnAmount " & _
" from FebData " & "Where AccType = '" & strAccType & "'" & _
" group by AccType, Company, Client order by AccType, Company, Client "

StrSQL_Mar = "select Company, Client, AccType, sum(amount) as clnAmount " & _
" from MarData " & "Where AccType = '" & strAccType & "'" & _
" group by AccType, Company, Client order by AccType, Company, Client "

Set rs_FebQry = db.OpenRecordset(strSQL_Feb)
Set rs_MarQry = db.OpenRecordset(StrSQL_Mar)

Do While Not rs_FebQry.EOF
If rs_FebQry!AccType = strAccType Then
Debug.Print Space(5) & "FEB Client: " & rs_FebQry!Client & ", " & rs_FebQry!AccType & ", Amount: " & rs_FebQry!ClnAmount
End If
rs_FebQry.MoveNext
Loop

Do While Not rs_MarQry.EOF
If rs_MarQry!AccType = strAccType Then
Debug.Print Space(5) & "Mar Client: " & rs_MarQry!Client & ", " & rs_MarQry!AccType & ", Amount: " & rs_MarQry!ClnAmount
End If
rs_MarQry.MoveNext
Loop
rs_AccType.MoveNext
Loop

rs_AccType.Close
rs_FebQry.Close
rs_MarQry.Close

Set rs_AccType = Nothing
Set rs_FebQry = Nothing
Set rs_MarQry = Nothing

End Sub