Delete Empty Columns

  • Thread starter Thread starter Michael Conroy
  • Start date Start date
M

Michael Conroy

I have a query that (finally) gives me the results I want, however, I am
having trouble using the results. The query gives me eleven fields, Up5, Up4,
Up3, Up2, Up1, Target, Down1, Down2, Down3, Down4, Down5. The up and down are
sub queries where the Target (inner join) is the criteria for both. The basic
idea is to generate a hierarchial table based on whatever target is selected
in a combo box. It does this perfectly. If the target is the top or level
one, it will appear in the target field and the five down fields will be
populated and all the up fields will be empty. If the target is a level three
it will appear in the target with three ups populated and one down. If this
were Excel, I would simply delete the empty columns. In Access I need to do a
query with just Up5 to see if it is empty, then Up4, etc, until I get a field
that has some records in it. I can set up a querydef, but that "makes" a
query. How can I set up the query as a recordset and check the recordcount or
is EOF to see if there are any results? Thanks for your help
 
The problem here is a non-normalized data structure. You have repeating
fields, which violates the most basic relational rules.

Say these are 11 possible targets for a store.
Create a related table with fields like this:
- StoreID relates to the table you currently have
- TargetID indicates if this value is an Up5, Up4, ... Down5 record
- TheValue whatever the contents of your field are meant to store.

The TargetID field could be a number from 5 to -5, with zero as target.

Now you can retrive the value from the for the highest target that exists
for any store.
 
Allen, thanks for your response. That the data structure is flawed is
somewhat beyond my control as I am being asked to use existing data to
generate a hierarchial report. All I have in the data is a "code", and a
"sweep code" with nothing to indicate what level they are at. By doing a
recursive query I can see that code ABCD sweeps to WXYZ and then that WXYZ
sweeps to QRST, etc, for usually four or five levels. The problem arises
because I never know if the target is the top level, somewhere in the middle
or the bottom

Let me give you a more descriptive example of what I am trying to do.
Imagine a family tree and the user puts in a "code" for a parent. The result
we want is that parent's parents, no aunts or uncles, that parent's
grandparents, no grand aunts or uncles, and finally that parent's great grand
parents. So just the line from the target to the top with no other branches
of the family tree. That is what the Up query acomplishes. Going the other
way, we want the opposite: every branch on the tree. We want all the children
and all the grand children with no cousins. That is what the Down query
acomplishes. I do know we only have five generations. What I don't know is
whether the target will be a grand parent, a parent or a grand child. The
query that combines both the Up and Down gets me the results, but I don't
know out of the five up and five down, where the data is sitting, hence my
original question.

Your suggestion to normalize the levels in a seperate table is a good idea,
however, I can't determine the level until this recursive query runs. And
even then the level is determined by visually noting how many up parents and
down dependents there are. I have no way to label the code with a level. My
crude method was to check Up5 for any records, then Up4, then when I found
records in Up3, well, that became the great grand parent and the next four
were the dependants. I will take your suggestion and see if I can incorporate
some sort of level indicator.

Thanks again for your help, and on a different note, I like the reformatted
web site, the code is easy to read and find. I seem to be on your site about
once or twice a week. I am glad it's there. Cheers.
--
Michael Conroy
Stamford, CT


Allen Browne said:
The problem here is a non-normalized data structure. You have repeating
fields, which violates the most basic relational rules.

Say these are 11 possible targets for a store.
Create a related table with fields like this:
- StoreID relates to the table you currently have
- TargetID indicates if this value is an Up5, Up4, ... Down5 record
- TheValue whatever the contents of your field are meant to store.

The TargetID field could be a number from 5 to -5, with zero as target.

Now you can retrive the value from the for the highest target that exists
for any store.
Click to expand...
 
Okay, so the ideal structure would probably be a self-join like this:
http://allenbrowne.com/ser-06.html

A bill-of-materials example may also help:
http://www.mvps.org/access/modules/mdl0027.htm

For more powerful (but convoluted) approach, Joe Celko's stuff may be worth
reading:
http://www.intelligententerprise.com/001020/celko.shtml
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html

The table as you describe it is something I do use as a temporary table to
verify that the data is resolvable. There's always the chance of infinite
recursion (e.g. where an item is its own grandparent.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Michael Conroy said:
Allen, thanks for your response. That the data structure is flawed is
somewhat beyond my control as I am being asked to use existing data to
generate a hierarchial report. All I have in the data is a "code", and a
"sweep code" with nothing to indicate what level they are at. By doing a
recursive query I can see that code ABCD sweeps to WXYZ and then that WXYZ
sweeps to QRST, etc, for usually four or five levels. The problem arises
because I never know if the target is the top level, somewhere in the
middle
or the bottom

Let me give you a more descriptive example of what I am trying to do.
Imagine a family tree and the user puts in a "code" for a parent. The
result
we want is that parent's parents, no aunts or uncles, that parent's
grandparents, no grand aunts or uncles, and finally that parent's great
grand
parents. So just the line from the target to the top with no other
branches
of the family tree. That is what the Up query acomplishes. Going the other
way, we want the opposite: every branch on the tree. We want all the
children
and all the grand children with no cousins. That is what the Down query
acomplishes. I do know we only have five generations. What I don't know is
whether the target will be a grand parent, a parent or a grand child. The
query that combines both the Up and Down gets me the results, but I don't
know out of the five up and five down, where the data is sitting, hence my
original question.

Your suggestion to normalize the levels in a seperate table is a good
idea,
however, I can't determine the level until this recursive query runs. And
even then the level is determined by visually noting how many up parents
and
down dependents there are. I have no way to label the code with a level.
My
crude method was to check Up5 for any records, then Up4, then when I found
records in Up3, well, that became the great grand parent and the next four
were the dependants. I will take your suggestion and see if I can
incorporate
some sort of level indicator.

Thanks again for your help, and on a different note, I like the
reformatted
web site, the code is easy to read and find. I seem to be on your site
about
once or twice a week. I am glad it's there. Cheers.
Click to expand...
 
Thanks for the articles, I will read them over the weekend and let you know
how it turns out next week.

You were right about the infinite recursion possibility, I have identified a
few hundred instances where the codes repeat not only to themselves, but loop
back after one level. The common problem is that ABCD sweeps to ABCD,
however, this data also has ABCD sweeps to WXYZ, which sweeps to ABCD. The
second example looks OK at face value, but the recursive query really brings
out the problem.
 
Back
Top