Select Columns from Table

  • Thread starter Thread starter Two Thousand 2000
  • Start date Start date
T

Two Thousand 2000

I'm having trouble getting the necessary columns out of a linked table into a
query. Every day I have a linked table showing data by month (with numeric
column headers 1,2,3....) starting in the current month (with current month
column header always listed as 1). The problem is that I need my query to
always look at the last month in each quarter. So, in January (1), I need to
look at the third month (3 aka March), in February (1), I need to look at the
second month (2 aka March), and so on. I can't change how the linked table
comes into Access and I'd like to limit any user entry if possible. Is there
any way I can use today's date to help pull the correct month, or at most
have the user enter current month to pull the correct columns into a query or
table. Thanks for your help.
 
I'm having trouble getting the necessary columns out of a linked table into a
query. Every day I have a linked table showing data by month (with numeric
column headers 1,2,3....) starting in the current month (with current month
column header always listed as 1). The problem is that I need my query to
always look at the last month in each quarter. So, in January (1), I need to
look at the third month (3 aka March), in February (1), I need to look at the
second month (2 aka March), and so on. I can't change how the linked table
comes into Access and I'd like to limit any user entry if possible. Is there
any way I can use today's date to help pull the correct month, or at most
have the user enter current month to pull the correct columns into a query or
table. Thanks for your help.
Click to expand...

It's a pity you have to deal with this very non-normalized spreadsheet
pretending to be a table!!

You can indeed get today's month number: Month(Date()) for example. To get the
last month in the current quarter you could use the Mod function:

4 - (Month(Date()) - 1) MOD 3

will return - I think, test it!! - the desired column number. You will have to
construct a SQL query to retrieve that field's data, since you can't pass
fieldnames as query parameters.
 
Back
Top