Maintaining 60 months of data.

[Tom F via AccessMonster.com]

I am looking for the best way to keep 60 months of inventory demand
history. For example, beginning at field position 100, I have the oldest
month(60), at field 101, I have month (59),... at field position 160, I
have month (1). On a monthly basis, I need to pull the current activity
and place it into field position 160 after moving the contents of all the
fields one position to the left until the 60th month is moved. I tried
doing this with a update query, but all the fields contained the 1st month
(field 160) value. Does someone know of a quick way to accomplish this?
Appreciate any help..

 

[John Vinson]

I am looking for the best way to keep 60 months of inventory demand
history. For example, beginning at field position 100, I have the oldest
month(60), at field 101, I have month (59),... at field position 160, I
have month (1). On a monthly basis, I need to pull the current activity
and place it into field position 160 after moving the contents of all the
fields one position to the left until the 60th month is moved. I tried
doing this with a update query, but all the fields contained the 1st month
(field 160) value. Does someone know of a quick way to accomplish this?
Appreciate any help..

WHOA. *Stop right there*.

You are applying spreadsheet logic to a relational database. Storing
data (months) in fieldnames is SIMPLY WRONG, and will get you in no
end of trouble.

If you have a one (item) to many (months) relationship, the correct
table structure is to model it as a one to many relationship betwen
two tables. Consider a structure like:

Items
ItemID
<information about the item that you're tracking>

InventoryDemandHistory
ItemID
InventoryDate Date/Time
Demand

Rather than 60 *FIELDS* for inventory demand you would have 60 (or
more, as many as you can ever want; you can use queries to filter
them) *RECORDS*.

Your table design will *simply not work* in any reliable manner in
Access. And with 100 fields before this, I strongly suspect you have
at least one more such embedded one to many relationship. If you're
going to use Access, use it relationally - you'll find that it's very
powerful and very logical, but that it is NOT A SPREADSHEET.

John W. Vinson[MVP]