Question exporting each field of my access database to an excel by with different format (1 Viewer)

[driftking]

hello,

need help about an issue.

i actually have an ms access database with the following format(assuming)

title1 title2 title3 title4 title5 title6
1a 1b 1c 1d 1e 1f
2b 2b 2c 2d 2e 2f
3a 3b 3c 3d 3e 3f
4a 4b 4c 4d 4e 4f








for each row in the ms access database above i need to retrieve an excel in the format below

title1 1a
title2 1b
title3 1c
title4 1d
title5 1e
title6 1f


title1 2a
title2 2b
title3 2c
title4 2d
title5 2e
title6 2f


and so on....

Any suggestion from where i can proceed,what tool can i use??

thanks in advance

 

[Pat Hartman]

The situation is usually reversed. Your Access table is normalized and you want to de-normalize it to export to Excel. In this case your Access table is not normalized and you want to normalize it. My suggestion is to keep the normalized table and ditch the unnormalized version.

You will need one make table query and then an append query for each column you need to pivot. So create a query that selects the first column and change it to a make table query. Add a second column with the literal value "title1" that will be saved as the first fields. Then for each subsequent column, make an append query that selects the column you want to append and change the literal value to coorespond to that column's name.

If you need to do this more than once, save the set of queries so you can run them again. If you only need to do it once, you can just keep changing the same query as you append each column. Be careful to remember to change the column name for each subsequent append.

SELECT "Title1" as Title, Title1 as TitleValue INTO NewTable
FROM OldTable;

INSERT INTO NewTable ( Title, TitleValue )
SELECT "Title2", Title2
FROM OldTable;

INSERT INTO NewTable (Title, TitleValue )
SELECT "Title3", Title3
From OldTable;
...

 

[driftking]

Alright,
i reform my question(now that myself have understand the issue clearly ).

i have an access database with a table containing data.
assuming i have 100s of rows in it.
What i want to do is for each row in the table i want to generate an excel sheet with specific fields in the database match specific cell in the excel template(please find attached).

That is if i have 1 hundred rows in my access database....i have to generate 100 excel sheet and rename it automatically with 1 of the field in my database(find an example attached)

i was thinking of making a form in ms access with button in it. on_click (event) it must loop through the database rows and generate excels according to the number of rows.

can you suggest me a solution or how can i realise my idea

ps: i have never work with access form before

attachment
1 ms access(contactDB) : from which i have to extract data
1 excel(<NOM DU CONTACT>the field name from the ms access db): the template