Matching a field with another table

[Malc_B]

Hi there - relatively new user to Access (well, new returner after a LOT of years away). Have a little problem.

I have one table with a number of fields (about 70) which are named using a code (for instance ABC - which is not arbitrary but actually means something). Then each record relates to a year. So each field may have a date, integer, text, but it is consistent. What i would like to be able to do is have another table with 2 fields - one using the field names ie ABC from the main table - and another with a more full description of what it is. I'd link to link the two so that I can run a query to give me all the details from the first table but with an added line showing the full description. Easy done if the original table is set up with the years as fields but that's not really how it should be. Can anyone help a new idiot out?

[CJ_London]

mixing horizontal and vertical tables is always going to be complicated. The description of your first table sounds a lot like you are using Excel principles (horizontal) which are the opposite of database principles (vertical)

One of the issues you will have is trying to mix numeric and text datatypes in the same column - can do it in excel, but not a database

Only way I can think you could do it is in a form or report load event is to loop through all the controls and where the controlsource is the name of one of your ABC fields, use a dlookup to find the description and populate the control controltiptext property.

As an alternative to a second table, you might want to consider populating the description column for each field in the table design - the description will then appear in the bottom bar of the access window

[Malc_B]

Thanks for the follow up. NI terms of table 1 I had presumed that a record per year (with each of the 70 variables - now split into secondary tables for ease of use) was the way to go. That way each year you add a single record not a field to each record. Or am I wrong? Similarly it meant that the types fo field would not need to be mixed - each abbreviation being a field after all.
I like the idea of adding the description to have it show up (I didn't think it would). My other option of course is to sort it in Excel once Excel has grabbed the data mind you (the idea is that I can access the info in the access database over the web but having a nightmare doing it straight from excel)

Thanks again.

[CJ_London]

That way each year you add a single record not a field to each record. Or am I wrong?

without seeing your data, difficult to advise. I assumed it was something like

Year...Sales....COS...Direct Costs...etc
2019..100.....50......25....

in which case would be better to use

Account..........Year.....Amount
Sales.............2019...100
COS..............2019....50
Direct Costs...2019....25
etc

then you can link on your other table

Account.........AcctDesc
Sales.............Sales for the year excluding VAT
COS..............Cost of sales for the year excluding VAT
Direct Costs...Cost of shipping/freight
etc

Note these should have PK/FK to link on so actually your tables would look like

tblAnalysis
AnalPK....AccountFK..........AnalYear.....Amount
1............22.....................2019.........1 00
2............24.....................2019.........5 0
3............33.....................2019.........2 5

tblAccounts
AccountPK...AcctName.........AcctDesc
22..............Sales...............Sales for the year excluding VAT
24..............COS................Cost of sales for the year excluding VAT
33..............Direct Costs.....Cost of shipping/freight

the link between AccountFK and AccountPK enables you to bring through the account name and description in a single query and you can use a crosstab to display as per your current table (populating the controltiptext as previously advised). You would probably also need a sort column in tblAccounts so you can set an order so columns are displaying in the correct order - or perhaps you have an account number which can be sorted as required. Or you can set the crosstab to have the years across the top in which case you can include your description down the side (which is probably how I would do it)

[Malc_B]

You've pretty much knocked it on the head in terms of layout of table 1. Thanks for the advice. I'l chew it over and see what happens next. Thanks again.