Using "Excel" features in Access (1 Viewer)

[Mitch_____W]

I have a pretty large DB in the design stages and one of the things I am going to need it to do is generate reports with "spreadsheet like" tables in them. These "spreadsheet like" tables will be generated each time the report is run based on the current client record on a form. There are several fields from the current client record, which will be used in many formulas and functions to create this "spreadsheet like" table(s). I have, so far, created Excel sheets which import the data from Access. The problem is that Excel can take the data imported from Access and fill in the sheet accordingly with all formulas and functions and formatting, which I don't believe Access can do. I haven't figured out how to make the "refreshed" excel sheet appear in an Access report. I would think that there has to be a way to do everything in Access without using Excel at all... Is there????

If life is a box of chocolates, mine was left out in the sun....

 

[Pat Hartman]

I'm sure Access can do it but you'll need to tell us what "it" is.

 

[Mitch_____W]

I need a report to generate a table which is "filled" down based on one record's data... First column would be "age", second would be "Salary", third would be "Bi_Wkly_Premium" (which is calculated on "Salary")... The next row would show "Age" incremented by 1 yr, "Salary" increased by 2%, and "Bi_Wkly_Premium" recalculated based on new salary... I would need the rows filled from "Age" to 80....

As it stands, I am doing this table in excel (easy enough to do) but I don't want to do the export/import thing unless it is the only way...

Can I accomplish this with a Crosstab Query?

 

[Mitch_____W]

Anyone???? Any ideas????

Please help!!!

 

[Jerry Stoner]

The only way I know how to do this since access overwrites the spreadsheet when you export the query is to use 2 spreadsheets. A "buffer" opened minimized and a "formatted" maximized, formatted the way you want the data and linked via formulas to the buffer spreadsheet.

Private Sub cmdToCharts_Click()


Dim objExcel As Object
Dim strFile As String
Dim strFile2 As String
strFile = "C:\qcdatabase\chartbuffer.xls"
strFile2 = "C:\qcdatabase\scrapcharts.xls"
Me.Refresh
If DCount("*", "[qryScrapChart]") > 0 Then
DoCmd.OutputTo acOutputQuery, "qryScrapChart", acFormatXLS, strFile, False
Set objExcel = GetObject(strFile)
objExcel.Application.Visible = True
objExcel.Windows(1).Visible = True
objExcel.Windows(1).WindowState = xlMinimized

Set objExcel = GetObject(strFile2)
objExcel.Application.Visible = True
objExcel.Windows(1).Visible = True
objExcel.Windows(1).WindowState = xlMaximized
Else
MsgBox "There is no data to chart."
End If
End Sub

 

[Pat Hartman]

No. A crosstab query simply pivots rows into columns. You'll need to create a new table with 80 rows. One for each age that you want to work with. You can join to this table using a query similar to the following:

SELECT Q.CustID, Q.CustAge, Q.Salary, Q.BiWeekPremium, tblAge.AgeID
FROM Query2 AS Q INNER JOIN tblAge ON Q.CustAge <= tblAge.AgeID;


Notice that the relational operator in the join is <= rather than just = as is normal. You cannot create this join in the QBE grid so once you have the query built and an equi-join defined, switch to SQL view and change the operator to <=.

Once you have this recordset, you'll need to read it in code, do the appropriate calculations, and write it out to a temp table.

This is much more complicated with Access than it is with Excel but you can do it easily enough if you are comfortable with VBA.

You can try doing the calculations with a query rather than storing them to a temp table. You will need to use the same concept as is used when calculating a running sum. Search the archives Jon K recently posted an excellent sample that you can adapt for this purpose.

 

[Jerry Stoner]

Pat is of course right but... I was thinking just get the data into excel as I described and do your formatting and do all this

I need a report to generate a table which is "filled" down based on one record's data... First column would be "age", second would be "Salary", third would be "Bi_Wkly_Premium" (which is calculated on "Salary")... The next row would show "Age" incremented by 1 yr, "Salary" increased by 2%, and "Bi_Wkly_Premium" recalculated based on new salary... I would need the rows filled from "Age" to 80....

In your linked and formatted spreadsheet. Seems aesier to me and I use this alot.

Hm
I didnt see this
"I haven't figured out how to make the "refreshed" excel sheet appear in an Access report. "
Maybe you will have to go Pats way or print the excel spreadsheet and attach to the report as a way of chaeting. You can always print both at the same time.

 

[Mitch_____W]

Pat I thought I was following you but I just wasn't able to make it work.... I have attached a pic of the table I was able to create in Excel... Maybe you can see what I am trying to acheive... This sheet was generated using one client's record and specifically the client's Age (which is used for the first age in the table) Annual_Salary (Which is used for the first Annual Salary in the table). The next 4 columns are calculated as: Biweekly_Premium = (INT(Annual_Salary/1000)*.16) , Monthly_Premium = Biweekly_Premium*2), Annual_Premium = (Monthly_Premium*12), Accumulated_Cost = (Running Sum of Annual_Premium), Basic = ((INT(Annual_Salary/1000)*1000)+1000)...... Then the next row recalcs accordingly.... Geesh! Now I am confused!!!

There are several other IIF's built in all over the sheet like to make Annual_Salary = 0 if Age of that row = the client's retire date....
link won't work so just cut and paste this url:

www .geocities.com/mitch_d_w/untitled.jpg

 

[Pat Hartman]

I was not offering any help on using Excel. I was telling you how to start with a VBA solution.

 

[Mitch_____W]

I am not asking for help with how to do this in Excel.... I already did it in Excel.... I want to get the same result (as shown in the linked page above) with the use of Access only... As you stated in your previous post, I created a Table (in Access) with the AgeId Field populated 1 - 80; created a query (In Access) with the ClientID, ClientAnnual_Salary, and Client_Age; tried to create another query with the SQL you provided, but could not get it to work....

I am not following you.... Please steer me in the right direction...

 

[Pat Hartman]

Now you need to write code to calculate the data. I don't have any sample code for you. Look in help for Do Until to find help on how to loop through a recordset. Look in help for OpenRecordset to find code to open a recordset. Look in help for AddNew to find out how to add records to the output recordset. You'll need two recordsets open. The recordset your code will loop through is the query I suggested. The other recordset will be written to within the code loop and contain the calculated results.

....
Dimension the variables and open the recordsets
.....
rst1.MoveFirst
Do Until rst1.EOF
rst2.AddNew
calculate
rst2.Edit
MoveNext
Loop

rst1.Close
rst2.Close

 

[mission2java_78]

Dont forget to properly set both objects to Nothing when you are done using them:

Set rst1= Nothing
Set rst2 = Nothing
Set db = Nothing

Jon