Floating 24 Months

[Deuce Sapp]

I have a query where I am trying to show customers who have spent money in
the month specified by a form, but not in the previous 24 months. Right now
I am doing it manually by evaluating the following:

[2001]![June]+[2001]![July]+[2001]![August]+[2001]![September]+[2001]![Octob
er]+[2001]![November]+[2001]![December]+[2002]![January]+[2002]![February]+[
2002]![March]+[2002]![April]+[2002]![May]+[2002]![June]+[2002]![July]+[2002]
![August]+[2002]![September]+[2002]![October]+[2002]![November]+[2002]![Dece
mber]+[2003]![January]+[2003]![February]+[2003]![March]+[2003]![April]+[2003
]![May]

with the criteria:
Is Null Or <=0

Is there a way to do this based on the variable in the form?


Deuce

 

[Deuce Sapp]

John,

Thanks for your reply. Actually, my table structure is not by design. My
problem is I have to get my data from an ODBC link to an AS/400 and the only
way I can get it is with the fields, CustomerNumber, Year, Jan, Feb, etc.
with dollar amounts in each of the month fields. Obviously each customer
number is duplicated for each year.

I have a series of queries that generate a distinct list of customer numbers
and another series of queries that populate the year tables...and then I
query the combination of all of those huge ugly queries. I'm sure it isn't
the BEST way to do it, even with my constraints, but it is the only way I
thought of. (Of course, I STOPPED thinking once I saw that I got the results
I was after.) Thus my current dilemma.

I have a query where I am trying to show customers who have spent money in
the month specified by a form, but not in the previous 24 months. Right now
I am doing it manually by evaluating the following:

[2001]![June]+[2001]![July]+[2001]![August]+[2001]![September]+[2001]![Octo

b

er]+[2001]![November]+[2001]![December]+[2002]![January]+[2002]![February]+

[

2002]![March]+[2002]![April]+[2002]![May]+[2002]![June]+[2002]![July]+[2002

]

![August]+[2002]![September]+[2002]![October]+[2002]![November]+[2002]![Dec

e

mber]+[2003]![January]+[2003]![February]+[2003]![March]+[2003]![April]+[200

3

]![May]

If you have a Table named 2001, and your Fields are named June, July
and August...

your are way, way far down the wrong track. Access is a relational
database, not a spreadsheet! Data should be stored IN FIELDS, not in
fieldnames.

There is NO good way to do what you describe with your current table
structure. The only way would be to dynamically build a (snarky, ugly)
SQL string in code based on the form entry.

With a proper relational design, this query becomes much easier (maybe
not trivial, you'll need a NOT EXISTS clause or an outer join) but you
will have MUCH more flexibility. Consider a table with three fields -
CustomerID, SaleDate, and SaleAmount. If they have 80 purchases over
time this table would have 80 records. You can then use criteria to
select sales in a particular month, exclude sales in a particular
month or range of dates, etc. etc.