Hello Everyone,
I am new to the board and fairly new to Access as well. I have been assigned a task at work to import an Excel spreadsheet into Access to utilize reporting functions/features. I am aware it would be best for it all to be done in Access however the business is not willing to move from Excel. On the spreadsheet it lists information related to enrollment, specifically the person who is working on the enrollment. Because the same person may work on several groups there are duplicate names. What I am having trouble wraping my head around and completing is they want a report that shows the level of completion of these groups. I was originally thinking I could count the fields however that doesnt seem to be best practice and it also has scewed the numbers and I was thinking I could divide by the number of fields with data in them for that given person. My table structure is not normalized so I have 1 table with 63 different field names. I have gotten this far with some assistance:
Option Compare Database
Dim i As Integer
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT [Group Number],[Group Name],[PARNT CODE],[Market Segment],[N= New Group R= Renewal O= Offcycle AR= Accumulator Renewal],[Exception Group(Y or N)],[Target # of subs from NB71 or UW],[Client Manager/Account Executive],[ebn, Eexchange, eSales, Paper] , [Spreadsheet Election Option if BF],[2013 OE Period],[Comments:],[eExchange/ebn Full or Changes File],[Membership Professional], [Team Code],[Group Level Paperwork Received Date],[Signed Alere Form Received(Y or N)],[Group level Completed Date],[Membership Mapped Date],[Membership Completed Date],[Coverage Anaylsis/ Mock Bill Run Date],[Date sent to Peer Audit],[Date Returned from Peer Audit],[FO Indicator Set Date],[Projected OE Period],[2013 Actual File Receipt Date],[2013 OE File Completion Date],[eExchange Intolerable Report Date],[Custom ID Card (Y or N)],[Sample ID Card Generated Date],[ID Card Approved Sample Date],[ID Card Target Date],[ID Card Trigger Date] _
[ID Card Released Date],[Number of ID Cards released],[Ebooklets or Paper],[Custom Contract(Y or N)],[Custom Member Guide(Y or N)],[Guide Approved Date],[Guide Triggered Date],[Electronic Invoice, Self bill, Standard Paper, No Bill],[Bill Trigger Date],[FSD Specialist],[Alpha Prefix],[Alpha Prefix Verification Date],[Special PORG],[LU to ASO],[New Groups],[Actual Number of New Groups],[New Group Estimated # of Cards Renewals],[Actual Number of Renewal Groups PG's(Y or N)],[AIT Rep Name],[EBN Employer Services Contact],[Group Level Paperwork Target Received Date],[HR Intouch for ebn groups only(Y or N)],[Payroll Connect],[Special E Links(Y or N)],[Projected Option if BF],[Membership File Target Received Date],[Stop Loss - ASO Only (Y or N)]FROM tblGroupTracking WHERE ID=" & intID)]
For i = 0 To 62 'index begins with 0
total = total + IIf(IsNull(rs.Fields(i)), 0, 1) 'this is use of Fields collection, do not use your field name
Next
End Sub
I apologize if I was unclear and really appreciate any assistance/guidance that can be provided.
I am new to the board and fairly new to Access as well. I have been assigned a task at work to import an Excel spreadsheet into Access to utilize reporting functions/features. I am aware it would be best for it all to be done in Access however the business is not willing to move from Excel. On the spreadsheet it lists information related to enrollment, specifically the person who is working on the enrollment. Because the same person may work on several groups there are duplicate names. What I am having trouble wraping my head around and completing is they want a report that shows the level of completion of these groups. I was originally thinking I could count the fields however that doesnt seem to be best practice and it also has scewed the numbers and I was thinking I could divide by the number of fields with data in them for that given person. My table structure is not normalized so I have 1 table with 63 different field names. I have gotten this far with some assistance:
Option Compare Database
Dim i As Integer
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT [Group Number],[Group Name],[PARNT CODE],[Market Segment],[N= New Group R= Renewal O= Offcycle AR= Accumulator Renewal],[Exception Group(Y or N)],[Target # of subs from NB71 or UW],[Client Manager/Account Executive],[ebn, Eexchange, eSales, Paper] , [Spreadsheet Election Option if BF],[2013 OE Period],[Comments:],[eExchange/ebn Full or Changes File],[Membership Professional], [Team Code],[Group Level Paperwork Received Date],[Signed Alere Form Received(Y or N)],[Group level Completed Date],[Membership Mapped Date],[Membership Completed Date],[Coverage Anaylsis/ Mock Bill Run Date],[Date sent to Peer Audit],[Date Returned from Peer Audit],[FO Indicator Set Date],[Projected OE Period],[2013 Actual File Receipt Date],[2013 OE File Completion Date],[eExchange Intolerable Report Date],[Custom ID Card (Y or N)],[Sample ID Card Generated Date],[ID Card Approved Sample Date],[ID Card Target Date],[ID Card Trigger Date] _
[ID Card Released Date],[Number of ID Cards released],[Ebooklets or Paper],[Custom Contract(Y or N)],[Custom Member Guide(Y or N)],[Guide Approved Date],[Guide Triggered Date],[Electronic Invoice, Self bill, Standard Paper, No Bill],[Bill Trigger Date],[FSD Specialist],[Alpha Prefix],[Alpha Prefix Verification Date],[Special PORG],[LU to ASO],[New Groups],[Actual Number of New Groups],[New Group Estimated # of Cards Renewals],[Actual Number of Renewal Groups PG's(Y or N)],[AIT Rep Name],[EBN Employer Services Contact],[Group Level Paperwork Target Received Date],[HR Intouch for ebn groups only(Y or N)],[Payroll Connect],[Special E Links(Y or N)],[Projected Option if BF],[Membership File Target Received Date],[Stop Loss - ASO Only (Y or N)]FROM tblGroupTracking WHERE ID=" & intID)]
For i = 0 To 62 'index begins with 0
total = total + IIf(IsNull(rs.Fields(i)), 0, 1) 'this is use of Fields collection, do not use your field name
Next
End Sub
I apologize if I was unclear and really appreciate any assistance/guidance that can be provided.
