Dynamic query

M

Mike

I have a table with data for different months of the year, eg.:

JanDebit, JanCredit, JanAdvance, JanTotal,
FebDebit, FebCredit, FebAdvance, FebTotal,
MarDebit, MarCredit, MarAdvance, MarTotal, and so on...

I would like to create a query which could retrieve neccessary fields
depending on the input parameters.

For example:
On my form I type "2" (means "February") and I get the following query:

SELECT MyTable.FebDebit As Debit, MyTable.FebCredit As Credit,
MyTable.FebAdvance As Advance, MyTable.FebTotal As Total
FROM MyTable

If on my form I type "3" (means "March") I should get the following:

SELECT MyTable.MarDebit As Debit, MyTable.MarCredit As Credit,
MyTable.MarAdvance As Advance, MyTable.MarTotal As Total
FROM MyTable

(the first three letters of the field names are replaced)

Is it possible?

Thank you in advance for any tips!
 
D

Douglas J. Steele

Do yourself a HUGE favour, and redesign your tables so that they're
normalized (see what Jeff Conrad has at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
for more details on normalization)

You should definitely NOT have fields named Jan..., Feb..., etc: you should
have the month as a separate field.

As well, you shouldn't be storing a computed total in the table. As fellow
Access MVP John Vinson likes to say "Storing calculated data generally
accomplishes only three things: it wastes disk space, it wastes time (a disk
fetch is much slower than almost any reasonable calculation), and it risks
data validity, since once it's stored in a table either the Total or one of
the fields that goes into the total may be changed, making the value WRONG."

Your query would then look like:

SELECT Debit, Credit, Advance,
SELECT Nz([Debit],0) + Nz([Credit],0) + Nz([Advance], 0) As Total
FROM MyTable
WHERE WhatMonth = [Enter Month Number]
 
V

Vincent Johns

Mike said:
I have a table with data for different months of the year, eg.:

JanDebit, JanCredit, JanAdvance, JanTotal,
FebDebit, FebCredit, FebAdvance, FebTotal,
MarDebit, MarCredit, MarAdvance, MarTotal, and so on...

Oog. Let me guess that, in this Table, [JanAdvance] is very similar to
[FebAdvance] except that its datum applies to January instead of to
February. Otherwise, the data type is the same, description is the
same, etc. Am I correct?

I suggest that you first modify this Table so that it contains only 5
fields:

Month
Debit
Credit
Advance
Total

Also, if it's possible to calculate the [JanTotal] field value based on
some other fields in that record in the Table, then delete the entire
[Total] field. (You will be able to get the value formerly stored there
by using a Query.)

The [Month] field you might want to define as a Date/Time field, so it
would include the year as well. But if you never include months from
more than one year in the same Table, that would not be necessary.
I would like to create a query which could retrieve neccessary fields
depending on the input parameters.

For example:
On my form I type "2" (means "February") and I get the following query:

SELECT MyTable.FebDebit As Debit, MyTable.FebCredit As Credit,
MyTable.FebAdvance As Advance, MyTable.FebTotal As Total
FROM MyTable

Having re-structured your Table, that will be pretty easy; all you'll
need to do will be to include a WHERE clause in the SQL.
If on my form I type "3" (means "March") I should get the following:

SELECT MyTable.MarDebit As Debit, MyTable.MarCredit As Credit,
MyTable.MarAdvance As Advance, MyTable.MarTotal As Total
FROM MyTable

(the first three letters of the field names are replaced)

Is it possible?

What you suggest is possible, but that doesn't mean you should try to do
it. One purpose of Access (or any other RDBMS) is to make it easy to
organize and analyze your information. What you suggest here (involving
calculating field names) is not an easy way to proceed.
Thank you in advance for any tips!

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
R

Randy Harris

Vincent Johns said:
Mike said:
I have a table with data for different months of the year, eg.:

JanDebit, JanCredit, JanAdvance, JanTotal,
FebDebit, FebCredit, FebAdvance, FebTotal,
MarDebit, MarCredit, MarAdvance, MarTotal, and so on...

Oog. Let me guess that, in this Table, [JanAdvance] is very similar to
[FebAdvance] except that its datum applies to January instead of to
February. Otherwise, the data type is the same, description is the
same, etc. Am I correct?

I suggest that you first modify this Table so that it contains only 5
fields:

Month
Debit
Credit
Advance
Total

Except, don't use Month (reserved word). Use FiscalMonth or DataMonth or
whatever else makes sense.
Also, if it's possible to calculate the [JanTotal] field value based on
some other fields in that record in the Table, then delete the entire
[Total] field. (You will be able to get the value formerly stored there
by using a Query.)

The [Month] field you might want to define as a Date/Time field, so it
would include the year as well. But if you never include months from
more than one year in the same Table, that would not be necessary.
I would like to create a query which could retrieve neccessary fields
depending on the input parameters.

For example:
On my form I type "2" (means "February") and I get the following query:

SELECT MyTable.FebDebit As Debit, MyTable.FebCredit As Credit,
MyTable.FebAdvance As Advance, MyTable.FebTotal As Total
FROM MyTable

Having re-structured your Table, that will be pretty easy; all you'll
need to do will be to include a WHERE clause in the SQL.
If on my form I type "3" (means "March") I should get the following:

SELECT MyTable.MarDebit As Debit, MyTable.MarCredit As Credit,
MyTable.MarAdvance As Advance, MyTable.MarTotal As Total
FROM MyTable

(the first three letters of the field names are replaced)

Is it possible?

What you suggest is possible, but that doesn't mean you should try to do
it. One purpose of Access (or any other RDBMS) is to make it easy to
organize and analyze your information. What you suggest here (involving
calculating field names) is not an easy way to proceed.
Thank you in advance for any tips!

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Top