 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2005Tricky Delete Problem
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. > Hi there, I've got another tricky problem. > [quoted text clipped - 18 lines] > data. Then I would delete the columns from that point on to the end of > the table. 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? > 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. 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. > Hi John, thanks for helping me this far. > [quoted text clipped - 10 lines] > above). So I want it to automatically cut off the rest of the columns > that have no months. 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. Perhaps using excel functions in access? creating objects? > 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. 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 <vjohns@alumni.caltech.edu> Please feel free to quote anything I say here. > 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. 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? The Sum() or DSum() function can do that. -- Vincent Johns <vjohns@alumni.caltech.edu> Please feel free to quote anything I say here. Can you pass a variable to the fieldname in the dsum function? > > 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 [quoted text clipped - 14 lines] > -- Vincent Johns <vjohns@alumni.caltech.edu> > 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.