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
please do need full
MA-Access---vba-to-report-horizonta.xlsx
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.Nam e Tbl_ActiveIngredient.Name Tbl_RegionalAvailabilityCh emical.Nam e 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.Nam e 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_RegionalAvailabilityCh emical.Nam e 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
At the moment output of report
CID Tbl_TradeNameC.Name Tbl_SupplierChemical.Name tbl_Category.Name Tbl_PhysicalAppearance.Nam
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.Nam
Tbl_ActiveIngredient.Name Quat Peracetic acid Quat Ethanol IPA-Quat Quat-biguanides IPA-Peroxide IPA-Quat IPA Peroxides
Tbl_RegionalAvailabilityCh
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:
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.
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
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.
ASKER
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
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:
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
ASKER
it is solved
Which solution did you use?
ASKER
Private Sub cmdchemicalsearchexport_Cl ick()
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.Record Source)
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
Dim x1 As Excel.Application
Set x1 = New Excel.Application
x1.Visible = True
x1.workbooks.Open ("D:\ReportChemicalSearch.
x1.sheets("sheet1").Select
Dim db As Database
Set db = CurrentDb
Dim rs As Recordset
Set rs = db.OpenRecordset(Me.Record
x1.sheets("sheet1").Cells(
x1.sheets("sheet1").Cells(
x1.sheets("sheet1").Cells(
x1.sheets("sheet1").Cells(
x1.sheets("sheet1").Cells(
x1.sheets("sheet1").Cells(
x1.sheets("sheet1").Cells(
x1.sheets("sheet1").Cells(
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(
rs.MoveNext
colnum = colnum + 1
Loop
rownum = rownum + 1
colnum = 2
End If
i = i + 1
rs.MoveFirst
Next
End Sub
ASKER
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 TRIALMembers 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.
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?