Query with Months as fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have table with fields Acct, Dept, Description, jan,feb,mar,......etc

My question is How can I compare current month to prior month without
changing the fields name in the query everytime?

Please help .. Thank you
Sal
 
You might want to normalize your data so you don't have this issue...

However you might try an expression like:

CurrentMinusPrior: Choose(Month(Date()), [Jan]-[Dec], [Feb]-[Jan],
[Mar]-[Feb], ....[Dec]-[Nov])
 
Unfortunately, I can't change the Network databse. I used your formula, I got
the result. Any other ideas?

ACCOUNT May Apr CurrentMinusPrior
428015 252,165 -1457.551 -252165

Duane Hookom said:
You might want to normalize your data so you don't have this issue...

However you might try an expression like:

CurrentMinusPrior: Choose(Month(Date()), [Jan]-[Dec], [Feb]-[Jan],
[Mar]-[Feb], ....[Dec]-[Nov])

--
Duane Hookom
MS Access MVP

SAL said:
I have table with fields Acct, Dept, Description, jan,feb,mar,......etc

My question is How can I compare current month to prior month without
changing the fields name in the query everytime?

Please help .. Thank you
Sal
Click to expand...
Click to expand...
 
Since the current month is June, I am not sure if your result is correct or
not. Can you tell me?
Do you really want the current month minus the previous month or do you need
something more complex?
--
Duane Hookom
MS Access MVP

SAL said:
Unfortunately, I can't change the Network databse. I used your formula, I
got
the result. Any other ideas?

ACCOUNT May Apr CurrentMinusPrior
428015 252,165 -1457.551 -252165

Duane Hookom said:
You might want to normalize your data so you don't have this issue...

However you might try an expression like:

CurrentMinusPrior: Choose(Month(Date()), [Jan]-[Dec], [Feb]-[Jan],
[Mar]-[Feb], ....[Dec]-[Nov])

--
Duane Hookom
MS Access MVP

SAL said:
I have table with fields Acct, Dept, Description, jan,feb,mar,......etc

My question is How can I compare current month to prior month without
changing the fields name in the query everytime?

Please help .. Thank you
Sal
Click to expand...
Click to expand...
Click to expand...
 
I want the user to be able to compare any month to prior month. Is it
possible? How about vb code? or variable?

Duane Hookom said:
Since the current month is June, I am not sure if your result is correct or
not. Can you tell me?
Do you really want the current month minus the previous month or do you need
something more complex?
--
Duane Hookom
MS Access MVP

SAL said:
Unfortunately, I can't change the Network databse. I used your formula, I
got
the result. Any other ideas?

ACCOUNT May Apr CurrentMinusPrior
428015 252,165 -1457.551 -252165

Duane Hookom said:
You might want to normalize your data so you don't have this issue...

However you might try an expression like:

CurrentMinusPrior: Choose(Month(Date()), [Jan]-[Dec], [Feb]-[Jan],
[Mar]-[Feb], ....[Dec]-[Nov])

--
Duane Hookom
MS Access MVP

I have table with fields Acct, Dept, Description, jan,feb,mar,......etc

My question is How can I compare current month to prior month without
changing the fields name in the query everytime?

Please help .. Thank you
Sal
Click to expand...
Click to expand...
Click to expand...
 
Try:

CurrentMinusPrior: Choose([Enter Month Number], [Jan]-[Dec], [Feb]-[Jan],
[Mar]-[Feb], ....[Dec]-[Nov])

--
Duane Hookom
MS Access MVP


SAL said:
I want the user to be able to compare any month to prior month. Is it
possible? How about vb code? or variable?

Duane Hookom said:
Since the current month is June, I am not sure if your result is correct
or
not. Can you tell me?
Do you really want the current month minus the previous month or do you
need
something more complex?
--
Duane Hookom
MS Access MVP

SAL said:
Unfortunately, I can't change the Network databse. I used your formula,
I
got
the result. Any other ideas?

ACCOUNT May Apr CurrentMinusPrior
428015 252,165 -1457.551 -252165

:

You might want to normalize your data so you don't have this issue...

However you might try an expression like:

CurrentMinusPrior: Choose(Month(Date()), [Jan]-[Dec], [Feb]-[Jan],
[Mar]-[Feb], ....[Dec]-[Nov])

--
Duane Hookom
MS Access MVP

I have table with fields Acct, Dept, Description,
jan,feb,mar,......etc

My question is How can I compare current month to prior month
without
changing the fields name in the query everytime?

Please help .. Thank you
Sal
Click to expand...
Click to expand...
Click to expand...
 
Back
Top