Query based on Column Index

[Matt Cromer]

I have attached the sql to a query that I have developed. My question is
regarding the TraitHistory fields. I have to manually determine Curr and
Prev fields from my table and update the query. Is there a way to have the
query determine the Curr and Prev based on their position in the table. I am
usually wanting to compare the last column to the one before it. Am thinking
(but have not idea how to execute) count the columns in the table and use
that index to reference Max Column and Max Coulmn -1 these would be Curr and
Prev. I realize the table is messy in the respect that time are columns
(YearWKDay) (200835M) instead of rows. Any ideas of how I can do this?


SELECT Weights_measures.Brand, Weights_measures.Segment,
Weights_measures.Size, TraitHistory.[Item Nbr], TraitedItemDesc.[Item Desc
1], StrWhseAlignment.[Whse Nbr], StrWhseAlignment.[Whse Name],
Sum(TraitHistory.[200834M]) AS Prev, Sum(TraitHistory.[200835M]) AS Curr,
[TraitHistory]![200835M]-[TraitHistory]![200834M] AS Diff
FROM ((StrWhseAlignment RIGHT JOIN TraitHistory ON StrWhseAlignment.[Store
Nbr] = TraitHistory.[Store Nbr]) LEFT JOIN TraitedItemDesc ON
TraitHistory.[Item Nbr] = TraitedItemDesc.[Item Nbr]) LEFT JOIN
Weights_measures ON TraitHistory.[Item Nbr] = Weights_measures.[Item Nbr]
WHERE (((StrWhseAlignment.[Whse Nbr])<>0))
GROUP BY Weights_measures.Brand, Weights_measures.Segment,
Weights_measures.Size, TraitHistory.[Item Nbr], TraitedItemDesc.[Item Desc
1], StrWhseAlignment.[Whse Nbr], StrWhseAlignment.[Whse Name],
[TraitHistory]![200835M]-[TraitHistory]![200834M]
HAVING (((TraitedItemDesc.[Item Desc 1]) Is Not Null) AND
(([TraitHistory]![200835M]-[TraitHistory]![200834M])<>0))
ORDER BY Weights_measures.Brand, Weights_measures.Segment,
Weights_measures.Size, TraitHistory.[Item Nbr];

 

[Jeff Boyce]

Matt

It sounds like you are saying the table on which you wish to run this query
is not "fixed", but can change in number of columns.

If so, why? How is it that your table can have one set of columns one time
and a different set of columns another?

I think I'm missing something...

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Matt Cromer]

Jeff -

Thank you. This table has columns added multiple times per month.

Matt

It sounds like you are saying the table on which you wish to run this query
is not "fixed", but can change in number of columns.

If so, why? How is it that your table can have one set of columns one time
and a different set of columns another?

I think I'm missing something...

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have attached the sql to a query that I have developed. My question is
regarding the TraitHistory fields. I have to manually determine Curr and
Prev fields from my table and update the query. Is there a way to have
the
query determine the Curr and Prev based on their position in the table. I
am
usually wanting to compare the last column to the one before it. Am
thinking
(but have not idea how to execute) count the columns in the table and use
that index to reference Max Column and Max Coulmn -1 these would be Curr
and
Prev. I realize the table is messy in the respect that time are columns
(YearWKDay) (200835M) instead of rows. Any ideas of how I can do this?


SELECT Weights_measures.Brand, Weights_measures.Segment,
Weights_measures.Size, TraitHistory.[Item Nbr], TraitedItemDesc.[Item Desc
1], StrWhseAlignment.[Whse Nbr], StrWhseAlignment.[Whse Name],
Sum(TraitHistory.[200834M]) AS Prev, Sum(TraitHistory.[200835M]) AS Curr,
[TraitHistory]![200835M]-[TraitHistory]![200834M] AS Diff
FROM ((StrWhseAlignment RIGHT JOIN TraitHistory ON StrWhseAlignment.[Store
Nbr] = TraitHistory.[Store Nbr]) LEFT JOIN TraitedItemDesc ON
TraitHistory.[Item Nbr] = TraitedItemDesc.[Item Nbr]) LEFT JOIN
Weights_measures ON TraitHistory.[Item Nbr] = Weights_measures.[Item Nbr]
WHERE (((StrWhseAlignment.[Whse Nbr])<>0))
GROUP BY Weights_measures.Brand, Weights_measures.Segment,
Weights_measures.Size, TraitHistory.[Item Nbr], TraitedItemDesc.[Item Desc
1], StrWhseAlignment.[Whse Nbr], StrWhseAlignment.[Whse Name],
[TraitHistory]![200835M]-[TraitHistory]![200834M]
HAVING (((TraitedItemDesc.[Item Desc 1]) Is Not Null) AND
(([TraitHistory]![200835M]-[TraitHistory]![200834M])<>0))
ORDER BY Weights_measures.Brand, Weights_measures.Segment,
Weights_measures.Size, TraitHistory.[Item Nbr];

 

[Jeff Boyce]

Matt

Now I'm even more concerned.

If you were limited to using a spreadsheet, "adding columns" would be about
the only way to handle a changing situation.

But Microsoft Access is a relational database, not a "spreadsheet on
steroids". If your table has to be re-defined "multiple times per month",
then so will your queries, your forms, your reports, your code, ... -- this
is a maintenance nightmare!

Tables in a well-normalized relational database tend to be "deep", not
"wide" ... and from the limited description you've provided so far, yours
sounds "wide" (like a spreadsheet).

If you'll post more specific information about your table, its fields, and
your situation, folks here may be able to offer more specific suggestions
that will let you take better advantage of Access' relationally-oriented
features/functions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff -

Thank you. This table has columns added multiple times per month.

Matt

It sounds like you are saying the table on which you wish to run this
query
is not "fixed", but can change in number of columns.

If so, why? How is it that your table can have one set of columns one
time
and a different set of columns another?

I think I'm missing something...

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have attached the sql to a query that I have developed. My question
is
regarding the TraitHistory fields. I have to manually determine Curr
and
Prev fields from my table and update the query. Is there a way to have
the
query determine the Curr and Prev based on their position in the table.
I
am
usually wanting to compare the last column to the one before it. Am
thinking
(but have not idea how to execute) count the columns in the table and
use
that index to reference Max Column and Max Coulmn -1 these would be
Curr
and
Prev. I realize the table is messy in the respect that time are
columns
(YearWKDay) (200835M) instead of rows. Any ideas of how I can do this?


SELECT Weights_measures.Brand, Weights_measures.Segment,
Weights_measures.Size, TraitHistory.[Item Nbr], TraitedItemDesc.[Item
Desc
1], StrWhseAlignment.[Whse Nbr], StrWhseAlignment.[Whse Name],
Sum(TraitHistory.[200834M]) AS Prev, Sum(TraitHistory.[200835M]) AS
Curr,
[TraitHistory]![200835M]-[TraitHistory]![200834M] AS Diff
FROM ((StrWhseAlignment RIGHT JOIN TraitHistory ON
StrWhseAlignment.[Store
Nbr] = TraitHistory.[Store Nbr]) LEFT JOIN TraitedItemDesc ON
TraitHistory.[Item Nbr] = TraitedItemDesc.[Item Nbr]) LEFT JOIN
Weights_measures ON TraitHistory.[Item Nbr] = Weights_measures.[Item
Nbr]
WHERE (((StrWhseAlignment.[Whse Nbr])<>0))
GROUP BY Weights_measures.Brand, Weights_measures.Segment,
Weights_measures.Size, TraitHistory.[Item Nbr], TraitedItemDesc.[Item
Desc
1], StrWhseAlignment.[Whse Nbr], StrWhseAlignment.[Whse Name],
[TraitHistory]![200835M]-[TraitHistory]![200834M]
HAVING (((TraitedItemDesc.[Item Desc 1]) Is Not Null) AND
(([TraitHistory]![200835M]-[TraitHistory]![200834M])<>0))
ORDER BY Weights_measures.Brand, Weights_measures.Segment,
Weights_measures.Size, TraitHistory.[Item Nbr];

 

[Matt Cromer]

Jeff -
You are correct in your assesment. It is a maintenance nightmare, I am
forever rebuilding queries to review what changed from one upload to the
next. I built the process several years ago using Access exactly as you
described "spreadsheet on steroids". On a weekly basis, I was evaluating
more than 65,536 records, thus turned to Access. I completely understand
this is poor database design, and am now at a cross-road...redesign or work
around.

Basic issue is...I can download location/item combination active flag as of
right now. What this table does is compare this active flag based on
different points in time that I have download. Business Question Is "What is
active today and how does that compare to some other point in time?"

Table currently looks like this, where 1 is Active and 0 is Inactive:
Loc Item 200801 200802 200803
001 123 1 1 0
002 123 1 1 1
001 124 0 0 1
002 124 0 0 1

For the most part the table, grows wider not deeper as 200801, 200802 etc.
represent weeks that I downloaded the flags in 2008. I am going to persue
restructuring my data. I am thinking of structuring the same data described
above like this in a new table:
Loc Item Week Flag
001 123 200801 1
002 123 200801 1
001 123 200802 1
002 123 200802 1
002 123 200803 1
001 124 200803 1
002 124 200803 1

Would this be more in line with proper db structure?

 

[John Spencer]

Pardon me for jumping in, but YES that is a much better structure.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

Would this be more in line with proper db structure?

I'll chime in and agree with John and Jeff: *YES* this is a MUCH better
structure.

Let me just add a couple of points. You may not need the "Active" field - just
the existance of a record indicates that the item is active. And, your
existing data is salvagable - you can use a "Normalizing Union Query" to
migrate it into the properly normalized table.

 

[Matt Cromer]

Thank you all!

I have migraged all of my 2008 data to the new structure. Not sure it
really matters, but db size increased by 3X. Previous version was 75Mb, now
200+ Mb. I am appending between 45-65,000 records each week, just wondering
if I dont purge data it may get out of control.

John V. - agreed, don't really need the Flag of "1", because presence of a
record says active. That may be a way to increase effiency.

Any best practices of tracking status across time? Now that I have
structured the data properly, I no longer know how to query when Time is not
a field and is now part of the record.

 

[Jeff Boyce]

Matt

You will get a lot more "eyes" on your question if you post it as a new
item, rather than "burying" it down inside this thread.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

Thank you all!

I have migraged all of my 2008 data to the new structure. Not sure it
really matters, but db size increased by 3X. Previous version was 75Mb, now
200+ Mb. I am appending between 45-65,000 records each week, just wondering
if I dont purge data it may get out of control.

Try using Tools... Database Utilities... Compact and Repair.

John V. - agreed, don't really need the Flag of "1", because presence of a
record says active. That may be a way to increase effiency.

Any best practices of tracking status across time? Now that I have
structured the data properly, I no longer know how to query when Time is not
a field and is now part of the record.

Normally if you want to track status across time you would have fields for
StartDate and EndDate; if EndDate is NULL then you can assume that the
specified status is still in effect. Perhaps you could post your current table
structure, fieldnames and datatypes, and indicate what you want to do with it.