I have a group of tables that filter information to each other in an Excel spreadsheet and I’m trying to set up an Access database that will allow this feed to continue. One spreadsheet has ‘permanent’ information in a three-dimensional format (shown below). Another table reads values out of certain columns on the main spreadsheet (tblAccounts), refers to the ‘permanent’ information spreadsheet and finds the value that it falls in line with. That table then presents that information in columns on the tblAccounts spreadsheet. My main table, tblAccounts, has the following columns: Company, AccountNumber, Description, Price, Volume, Symbol, BenefitLevel, Percent. I manually enter the information for the first six columns, but the last two column values are pulled from the ‘permanent’ table.
Here is an example of my ‘permanent’ table spreadsheet:
‘Company’ (there are several of these tables, one for each company)
ABC DEF GHI JKL MNO PQR
0 1 0% 10% 0% 20% 70% 100%
5.99 2 25% 10% 75% 20% 80% 100%
10 3 70% 10% 100% 20% 90% 100%
The values for each ‘permanent’ table vary, but my query should pick out the table to use by the Company associated with each request. To explain the above table a little better, the first column represents a price range (example, if the price on the table falls between $0 and $5.98, then the table extracts the value ‘1’ from column 2, and associates it with the corresponding BenefitLevel column on the tblAccounts table). The tblAccounts table should then takes the number it just extracted from column 2, compares it to the Symbol. (found in tblAccounts) to the Symbol in row 1 and extract the corresponding percentage rate to put in the ‘Percent’ column in the tblAccounts table (which is later used to perform a calculation).
My problem is that I don’t know how to make this work in Access. Where do I start? Do I need multiple tables to represent the ‘permanent’ one above? Should I keep corresponding ‘lookup’ columns in my tblAccounts table to hold the values that are extracted from the ‘permanent’ table? How do I make these columns populate with the correct Percent and BenefitLevel value?
Any help is greatly appreciated! Thanks!
Here is an example of my ‘permanent’ table spreadsheet:
‘Company’ (there are several of these tables, one for each company)
ABC DEF GHI JKL MNO PQR
0 1 0% 10% 0% 20% 70% 100%
5.99 2 25% 10% 75% 20% 80% 100%
10 3 70% 10% 100% 20% 90% 100%
The values for each ‘permanent’ table vary, but my query should pick out the table to use by the Company associated with each request. To explain the above table a little better, the first column represents a price range (example, if the price on the table falls between $0 and $5.98, then the table extracts the value ‘1’ from column 2, and associates it with the corresponding BenefitLevel column on the tblAccounts table). The tblAccounts table should then takes the number it just extracted from column 2, compares it to the Symbol. (found in tblAccounts) to the Symbol in row 1 and extract the corresponding percentage rate to put in the ‘Percent’ column in the tblAccounts table (which is later used to perform a calculation).
My problem is that I don’t know how to make this work in Access. Where do I start? Do I need multiple tables to represent the ‘permanent’ one above? Should I keep corresponding ‘lookup’ columns in my tblAccounts table to hold the values that are extracted from the ‘permanent’ table? How do I make these columns populate with the correct Percent and BenefitLevel value?
Any help is greatly appreciated! Thanks!
