Using variables in Access Queries

[dauphijd]

Is it possible to use variable in an access querry. I wish to have the the year field (ex: d2002) to change automatically. The database I am using has about 50 different queries and changing the year on an annual basis, one by one, is a terrible waste of time.

If I'm barking up the wrong tree and someone has a much easier idea..... let me know

********************************************
SELECT [Leaders List].name, [Leaders List].last, [Leaders List].employer, [Leaders List].d2002, [Leaders List].wpdiv
FROM [Leaders List]
GROUP BY [Leaders List].name, [Leaders List].last, [Leaders List].employer, [Leaders List].d2002, [Leaders List].wpdiv, [Leaders List].w_or_b
HAVING ((([Leaders List].d2002)>999) AND (([Leaders List].wpdiv)="44") AND (([Leaders List].w_or_b)="W"))
ORDER BY [Leaders List].employer;

*********************************************

 

[Donna Tunstall]

Greetings from sunny Mississauga:

I am sure there is a way to pull a variable or something in, but with my minimalist skills I never found out how. I got around the problem by creating a v. small table with just one field in it - the year number, and then dropped that into all queries which required that data. When the year changes I just change it in that table.

Bit cumbersome but works for me.

HTH

 

[The_Doc_Man]

I'm not sure what you are asking.

You can certainly define queries that contain parameters (that's a name you can look up in the Help text), one of which could surely be your year.

You can also define the current year using the DatePart function (that's another thing you can look up in Help). There is a way to use the Now function as an argument of the DatePart function to get the current year.

Yet somehow your question doesn't "feel" like either of those is the answer. Perhaps you should try to explain what you are doing, not in SQL but in English. That might help us to better understand your question.

 

[dauphijd]

Each column represents the amount of donations during a given year. So currently there is a column named d2002, d2001, ect. What I am trying to do is find a method so that I don't need to recreate/modify queries each year as ther are almost 50 different queries.

I was simply looking for a methos that would automate the process and at Jan 1, 2003 change the year column to d2003 and on Jan 2004, change to d2004, and so on

 

[Rich]

You do not need nor should have seperate columns for each year, a single date field which records the donation date will enable you to query data via parameter queries etc

 

[dauphijd]

the data that is being queried is coming from one table. This table is auto exported to access from our developer software that contains the full database. I don't have the power to change the setup of the exported table.

 

[Rich]

Yes I see, you've taken a classic spreadsheet approach which is causing the problems you now have.
Your structure needs quite a lot of re-working, but having done that you'll be able to select any of the years from a combo box say and filter the results accordingly. Do you want to take this approach?

 

[The_Doc_Man]

I think I know how to explain the final goal.

You need to look into crosstab queries, which would give you the format you want, I think.

You would have to convert the tables a little bit, perhaps, but once converted you would have little or no problem building many crosstabs for each of your categorizations.

I know you said you can't convert your tables. OK, I'll buy that. You can't make the folks who give you the table do anything other than what they do. But there is nothing to stop you from importing this ugly spreadsheet to a table, then building the final table with one or more Append queries based on the imported table. Then when you are done you can trash the imported (raw) table.

You want that donation record to have a name, an amount, and a year as data fields in a single table.

I.e.

Field: DonorName (text)
Field: DonorAmt (currency)
Field: DonationDate (Date)
other fields as required

Then you can crosstab on donorname by donationdate (grouped by year, the crosstab wizard will do this for you), showing total donor amount as the intersection of the donorname row and donationdate column. You can also get totals rows or total columns if you wish.