Tricky Delete Problem

  • Thread starter Thread starter Ida
  • Start date Start date
I

Ida

Hi there, I've got another tricky problem.

I have a table with 5 columns. Columns are called 1, 2, 3, 4, 5.

One case:

Columns 1 and 3 have numbers in them. Columns 2, 4 and 5 do not any
data at all.

I want to be able to delete columns 4 and 5 because those are the last
two columns without data. Although column 2 does not have data, I want
to keep it because it's before a column that does have data.

Another case might be that only column 2 has data. I then would want to
delete columns 3, 4 and 5. I want to keep column 1.

Any ideas on doing this with CODE?

I probably want to sum the columns and find the ones that equal 0. Then
have a variable that keeps track of the column name that last had no
data. Then I would delete the columns from that point on to the end of
the table.
 
So, if you have two rows with values
1;2;_;4;_
2;_;_;_;_

Do you want to delete just column 5 (based on the first row) or do you want
to delete columns 2 through 5 (based on the second row).
In a table (or query), you can only "delete" entire columns.
 
Thanks for the help. Yes I only want to delete column 5. And in the
example you just created, lets say there were 10 more columns after
column #5. And all 10 columns had NO data. Then I would want to delete
column #5,#6,7,8,9 and 10 too.
 
I can't think of a way to do this with a query. It would require some VBA
code to permanently delete the column.

I'm not being critical, but this seems like something that wouldn't be done
in a database. Can you describe why you feel the need to do this? Also,
what version of Access are you using?
 
Hi John, thanks for helping me this far.

I am using Access 2000. The reason why I want to do this actually is
for formatting reasons. I created a table to represent a timesheet that
has 24 columns to represent 24 months. Each column is labeled
1,2,3.......24. Some of the columns will have data, others will not
since some times employees might not be working during a particular
month.

I just want to be able to export this file to excel, or create a report
that will look nice. I can't use a simply query to filter each column
because I want to be able to keep some empty columns (as I explained
above). So I want it to automatically cut off the rest of the columns
that have no months.
 
Ahhh. Now I understand that what we have is a table design issue. Your
table design is good for a spreadsheet, but not the best design for a
relational database. Assuming a table that looks something like

PersonID
Month1
Month2
....
Month24

Your table should look more like

PersonID
MonthNumber
HoursWorked

Then you would have one record for each person for each month that they
worked. For example,
John 1 48
John 3 24
John 5 96

With that design it would be a simple matter to determine the largest Month
Number being used in the table and then it would be relatively simple to
build a crosstab query to display the information.

If you can't redesign your structure, post back and I'll consider working on
a solution over the weekend.
 
Yeah I was realizing that too. I structured the table originally like
that because it's easier for somebody in Excel to see and use (if we
were to export to Excel). But for access, it's the wrong way to go
about it.

What would really be helpful, is if you knew of a function I could use
in VB that can give me the sum of a column in a table?
 
Okay, I just realized that there is no function to do this correct?
Seems like all of my research points to using queries. But of course we
wouldnt' be able to do the selective deleting in it. Ack, this one is
tricky, lol.
 
Ida said:
Yeah I was realizing that too. I structured the table originally like
that because it's easier for somebody in Excel to see and use (if we
were to export to Excel). But for access, it's the wrong way to go
about it.
Click to expand...

Well, not necessarily. Yes, it's not a great way to organizae your
Tables. But no, there are lots of things you can do with Queries and
Reports that might look close to what you want. I think you should do
what John Spencer suggested and revise your Tables for data storage but
use a Crosstab Query to display the calendar.
What would really be helpful, is if you knew of a function I could use
in VB that can give me the sum of a column in a table?
Click to expand...

The Sum() or DSum() function can do that.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Ida said:
Okay, I just realized that there is no function to do this correct?
Seems like all of my research points to using queries. But of course we
wouldnt' be able to do the selective deleting in it. Ack, this one is
tricky, lol.
Click to expand...

The main thing you seem to want to do is to suppress displaying the
column header for the rightmost empty column(s). That's something that
you can probably do in a Report, with an expression attached to the
column header in the Report that checks the column total, and if it's
zero, don't display that label in the group header. The individual data
in the detail section of the report you'd already display as blank if
the value is zero.

If you want the non-empty columns to automatically expand to fill the
width of the page, that'd be a bit tricky unless you want to use VBA
code, but if you don't mind having some blank paper at the right edge
when some columns are suppressed, that's probably relatively easy.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Alright I managed to write a simple algorithim to delete the unused
columns

Thanks for all the help.

by the way, if you want to pass a variable to the dsum syntax, it's

value = dsum("[" & fieldname & "]", "tablename")

this should work.
 
Back
Top