Link to home
Start Free TrialLog in
Avatar of Nagender Reddy
Nagender Reddy

asked on

MA Access - vba to report horizontal data to vertical data.

MA Access - vba to report horizontal data to vertical data.

please do need full
MA-Access---vba-to-report-horizonta.xlsx
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Hi Narender,

EE is typically a place where we help you with problems you're having in your project. Posting an Excel sheet and asking the volunteer Experts to do the needful is basically asking us to do your work for you. Some of the Experts on EE will do that, but you generally normally have to open a Gigs project (which is a paid service).

To understand your project better: You have data in Excel (like the data on your "Form to Report" sheet, in the top 10 rows) and you want that to be moved into Access, and then a report created that looks like the "report .xlsx file"? And you want that report in Access?
Avatar of Nagender Reddy
Nagender Reddy

ASKER

No... i will explain.I have a query its output is in "form to report" sheet 1st table. ineed help to turn its appearance as 2 nd table

At the moment output of report
CID      Tbl_TradeNameC.Name      Tbl_SupplierChemical.Name      tbl_Category.Name      Tbl_PhysicalAppearance.Name      Tbl_ActiveIngredient.Name      Tbl_RegionalAvailabilityChemical.Name      EPA#      SDS      Comment
1      Mikrozid sensitive wipes      Schulke      Quat      Wipe      Quat      EU, Japan, M-East      EPA1245142451            Yes it does the job
2      Incidin active      Ecolab      Peroxide      solution      Peracetic acid      EU      EPA65478595            Smells like acid
3      Surfa'safe      Anios      Quat      Con. Solution      Quat      EU      EPA132546798            Number
4      Rapid Multi Enzyme      3M      Alcohol      solution      Ethanol      India      EPA465987213            Only apply on PET
5      Super Sanicloth      PDI      Alchol-Quat      solution      IPA-Quat      EU, US      EPA798465321            
6      Universal Sanitising wipes      Gama/Clinell      Quat-biguanides      Wipe      Quat-biguanides      EU, Asia, S-Africa      EPA195847263            
7      Oxivier TB wipes      Diversey      Alcohol-Peroxide      Wipe      IPA-Peroxide      N-America      EPA123456789            
8      Caviwipes      Mertex      Alchol-Quat      Wipe      IPA-Quat      N/S-America, Asia                  
9      Descosept pur      Dr Schumacher      Alcohol      solution      IPA      DACH      EPA654789321            
10      Descogen liquid rfu      Antiseptica      Peroxides      solution      Peroxides      DACH, EU      EPA142753954      3


i need report output as

CID      1      2      3      4      5      6      7      8      9      10
Tbl_TradeNameC.Name      Mikrozid sensitive wipes      Incidin active      Surfa'safe      Rapid Multi Enzyme      Super Sanicloth      Universal Sanitising wipes      Oxivier TB wipes      Caviwipes      Descosept pur      Descogen liquid rfu
Tbl_SupplierChemical.Name      Schulke      Ecolab      Anios      3M      PDI      Gama/Clinell      Diversey      Mertex      Dr Schumacher      Antiseptica
tbl_Category.Name      Quat      Peroxide      Quat      Alcohol      Alchol-Quat      Quat-biguanides      Alcohol-Peroxide      Alchol-Quat      Alcohol      Peroxides
Tbl_PhysicalAppearance.Name      Wipe      solution      Con. Solution      solution      solution      Wipe      Wipe      Wipe      solution      solution
Tbl_ActiveIngredient.Name      Quat      Peracetic acid      Quat      Ethanol      IPA-Quat      Quat-biguanides      IPA-Peroxide      IPA-Quat      IPA      Peroxides
Tbl_RegionalAvailabilityChemical.Name      EU, Japan, M-East      EU      EU      India      EU, US      EU, Asia, S-Africa      N-America      N/S-America, Asia      DACH      DACH, EU
EPA#      EPA1245142451      EPA65478595      EPA132546798      EPA465987213      EPA798465321      EPA195847263      EPA123456789            EPA654789321      EPA142753954
SDS                                                            
Comment      Yes it does the job      Smells like acid      Number      Only apply on PET
I'm a bit confused.  Are you trying to convert the data that is in rows 1-11 of the "Form to Report" spreadsheet into a report formatted as in lines 18-27 of that same worksheet?  If so, you would need to create a union query that looks something like:
SELECT CID, "TradeName" as RowHeader, tbl_TradeName.Name as CellValue
FROM yourQuery
UNION ALL
SELECT CID, "SupplierName" as RowHeader, tbl_SupplierChemical.Name as CellValue
FROM yourQuery
...
SELECT CID, "Comment" as RowHeader, [Comment] as CellValue
FROM yourQuery

Open in new window


This would give you a query with a structure like:
CID          RowHeader                CellValue
1                TradeName                    Mikrozid sensitive wipes
1                SupplierName               Shulke
1                Category                        Quat
1                PhysicalAppearance     Wipe    

Then, use that query to create a cross-tab query using the new [RowHeader] column as a RowHeader, CID as the ColumnHeader, and CellValue as the Value.  When you do this, select FIRST instead of GroupBy for the CellValue in the query grid.

Keep in mind that this will truncate any of the cells to 255 characters, so your [Comments] column, if it is a memo field, would not contain more than 255 columns.
hi,

attache dis the report output in ms access . i want to export that data thru EXPORT button on top highlighted.

when i click on export button that data needs to be expoeted to a xlsx file with format horizontal records to vertical records.

as second table in [form to report] sheet in excel sheet

Regards
Nag
Hi,

You could simply export you data as you do now, then transpose it. Excel have a build in function for that:
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim data() As Variant

Set wb = ThisWorkbook
Set ws = wb.Worksheets(1)
data = ws.Range("A1:C2").value  '// read data
data = wb.Application.WorksheetFunction.Transpose(data)
ws.range("A4:B6").value = data '// write data transposed
Set ws = Nothing
Set wb = Nothing

Open in new window

it is solved
Which solution did you use?
Private Sub cmdchemicalsearchexport_Click()
   
    Dim x1 As Excel.Application
    Set x1 = New Excel.Application
    x1.Visible = True
    x1.workbooks.Open ("D:\ReportChemicalSearch.xlsx")
    x1.sheets("sheet1").Select
    Dim db As Database
    Set db = CurrentDb
    Dim rs As Recordset
   Set rs = db.OpenRecordset(Me.RecordSource)
        x1.sheets("sheet1").Cells(1, 1).Value = "Trade Name"
        x1.sheets("sheet1").Cells(2, 1).Value = "Supplier"
        x1.sheets("sheet1").Cells(3, 1).Value = "Physical Appearance"
        x1.sheets("sheet1").Cells(4, 1).Value = "Active Ingredient"
        x1.sheets("sheet1").Cells(5, 1).Value = "Regional Availability"
        x1.sheets("sheet1").Cells(6, 1).Value = "Category"
        x1.sheets("sheet1").Cells(7, 1).Value = "EPA#"
        x1.sheets("sheet1").Cells(8, 1).Value = "Comment"
       
    Dim rownum As Long, colnum As Long
    Dim i As Long
    i = 0
    rownum = 1
    colnum = 2
    rs.MoveFirst
    For Index = 1 To rs.Fields.Count
   
    If rs.Fields(i).Name <> "SDS" And rs.Fields(i).Name <> "CID" Then
        Do While Not rs.EOF
        x1.sheets("sheet1").Cells(rownum, colnum).Value = rs.Fields(i).Value
        rs.MoveNext
        colnum = colnum + 1
         Loop
        rownum = rownum + 1
        colnum = 2
    End If
    i = i + 1
   
    rs.MoveFirst
    Next
    End Sub
But i am facing issue of setting . i want set borders to the exported data. please any one help me on it.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.