Parameter in Qry

[Ananth]

I have a Spend_Table in a database that has 3 year data (2007,2008,2009).
Fields are as under

Country
Supplier
Year
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

Data appearing under months are in Euros

I have to extract data from this table for a 12 Month period, for Feb-09, I
have to extract data from this table from Mar-08 to Feb-09 and then do some
anlaysis.

I presently achieve this by 2 queries. Using Spend_table In the first query,
choosing criteria for Year as “2008â€, extract data for Mar-08 to Dec-08. Then
I run a append query choosing criteria for Year as “2009â€, extract data for
Jan & Feb-09. Every month I need to modify the query by excluding one month
in Qry 1 and adding 1 month in Qry2,

Can this be achieved by a single query and by providing some parameters for
start month & end month

 

[Allen Browne]

The problem here is with the way the table is designed. It looks pretty good
for a spreadsheet, but is not the right way to store the data in a
relational database.

Try a table with fields like this:
CountryID relates to the primary key of a table of countries.
SupplierID relates to the primary key of your table of suppliers
TheMonth date/time
Amount Currency

Each month, you add a new record for each combination of country + supplier.
For the date field, I suggest you enter the first of the month (e.g.
1/1/2009). If you wish, you can set the Format property of the date field
to:
mmmm yyyy

Now that you have a *record* for each month, instead of a *field* for each
month, you can query it any way you like. For example, to show the data for
the last 12 completed months, the Criteria under TheMonth would be like this
(on one line):
Between DateSerial(Year(Date())-1, Month(Date()) -1, 1)
And DateSerial(Year(Date()), Month(Date()), 0)

Ultimately if you are trying to do year-to-date values as well as current
values, or last-year's figures beside this years figures, you will need a
subquery. Here's a starting point about that:
http://allenbrowne.com/subquery-01.html

 

[Ananth]

I bow to yr opinion/observation. The table is a web extract and it was
designed like that. I have taken your hint of modifying the table to the
structure you have suggested. The results are amazing. Thanks for yr input.

Regards & Best Wishes