Dynamic column names in multiple tables

[jcuoco78]

Good day,

I am trying to create a database where I need to have multiple tables with the same column headers. The data in each table will be different. The first table is linked to an excel sheet, which will have week numbers as column headers. The second table will have the same column headers and will use values from the first tables columns to do math on them and enter the new value into the second tables corresponding row/column.

My issue currently is figuring out how to make the columns in the second table have the same headers as the first table the way I do in Excel with (='Sheet1!A1) for example. The linked table will update every week with the current week number being the first column.

I hope I explained this well enough for someone to help!

 

[ranman256]

You shouldn't.
Every XL file should have the exact same column names. But an extra field for the date.
every file is appended to 1 single table. Then queries can analyse all the data from 1 source.

 

[xenou]

I was thinking on the same lines. Not sure if this is good for Access. Perhaps just continue to work in Excel? A database table does not change column names, so if the spreadsheet is updating the first row on a weekly basis, (column headers) you won't find it a good fit for Access and in any case as pointed out above, in a database normally the week numbers would be a row field under a column named WeekNumber, not column names.

 

[jcuoco78]

Thanks for the feedback. I will try to rethink my approach!