Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Can we do an applymap to an Access database?
I've got the following code in Excel but i cant seem to convert it into access
(i'm using
ODBC
CONNECT32 TO [MS Access Database;DBQ=C:\Users\Desktop\QV\New Microsoft Access Database.accdb];
)
Map:
mapping load
if(FieldID = 6,DataID,) as DataID,
Data
FROM
(
Customer_Name:
mapping load
if(FieldID = 5,DataID,) as DataID,
Data
FROM
(
Seller:
mapping load
if(FieldID = 7,DataID,) as DataID,
Data
FROM
(
LOAD
DataID,
ApplyMap ('Map', DataID ) as Customer_Number,
ApplyMap('Customer_Name',DataID) as Customer_Name,
ApplyMap('Seller',DataID) as Seller
FROM
(
LOAD
ApplyMap ('Map', DataID ) as Customer_Number,
ApplyMap('Customer_Name',DataID) as Customer_Name,
DataID
FROM
(
You need to use the Select button to select the table from the Access Database
When i try that though it complains i have a circular reference
ODBC
CONNECT32 TO [MS Access Database;DBQ=C:\Users\u22471\Desktop\QV\New Microsoft Access Database.accdb];
Map:
mapping select
if(FieldID = 6,DataID,) as DataID,
Data
FROM
[Sheet1];
This is what i have
ODBC
CONNECT32 TO [MS Access Database;DBQ=C:\Users\Desktop\QV\New Microsoft Access Database.accdb];
Map:
mapping sql select
if(FieldID = 6,DataID,) as DataID,
Data
FROM
[Sheet1];
Customer_Name:
mapping sql select
if(FieldID = 5,DataID,) as DataID,
Data
FROM
(ooxml, embedded labels, table is Sheet1);
Seller:
if(FieldID = 7,DataID,) as DataID,
Data
FROM
(ooxml, embedded labels, table is Sheet1);
DataID,
ApplyMap ('Map', DataID ) as Customer_Number,
ApplyMap('Customer_Name',DataID) as Customer_Name,
ApplyMap('Seller',DataID) as Seller
FROM
(ooxml, embedded labels, table is Sheet1);
ApplyMap ('Map', DataID ) as Customer_Number,
ApplyMap('Customer_Name',DataID) as Customer_Name,
DataID
FROM
(ooxml, embedded labels, table is Sheet1);
Hi Paul,
I am not sure what you are trying to do here.
You have a connect statement to an access database, but yet you are not selecting any tables from the the DB
If you want to create a Mapping Load from the Access Database you need to Connect and then select the table you require, ensure you choose the fields, must be only two fields for a Mapping Load.
Below is an example where I have selected the Product Table and created a Mapping Table
You can then load the excel tables if you need them and add the ApplyMap part.
I hope that makes sense
So i cant applymap from an access database?
Yes you can.
You need to select a table from the Database and make sure you tick the box for Preceding load
Paul,
You can applymap from an access database, you just have to use the proper syntax.
Map:
mapping
Load
if(FieldID = 6,DataID,) as DataID,
Data
;
sql select *
FROM
[Sheet1];
Customer_Name:
mapping
Load
if(FieldID = 5,DataID,) as DataID,
Data
;
sql select *
FROM
(ooxml, embedded labels, table is Sheet1);
Seller:
Load
f(FieldID = 7,DataID,) as DataID,
Data
;
sql select *
FROM
(ooxml, embedded labels, table is Sheet1);
FirstTable:
Load
DataID,
ApplyMap ('Map', DataID ) as Customer_Number,
ApplyMap('Customer_Name',DataID) as Customer_Name,
ApplyMap('Seller',DataID) as Seller
;
sql select *
FROM
(ooxml, embedded labels, table is Sheet1);
SecondTable:
Load
ApplyMap ('Map', DataID ) as Customer_Number,
ApplyMap('Customer_Name',DataID) as Customer_Name,
DataID
;
sql select *
FROM
(ooxml, embedded labels, table is Sheet1);
Good luck
Oscar
Thanks. I'm trying to remove excel out of the equation all together. I designed the original in excel to get the general idea and wanted to convert it to access. I wont be using excel at all, all the information is in access.