Posts by Blue_Hornet

Hi all, and thanks again in advance.

Can anyone direct me to (or just tell me) the general rules for creating simple queries in Access that allow appending records? (I'd like to know where this is in Access' own Help file, because I know it must be there, but I'm hanged if I know how to ask for it.)

For example, I have a simple two-table query, joined on one column. The column is part of a Key in each table (but not the whole Key in the second table). Table_1 is "Proposals" and Table_2 is "Proposal_Revs". The Key to the first is PropNum, and the Key to the second is PropNum & RevNum.

I've joined other tables before and happily discovered that I could use the resulting queries to add records (had not known before that that was possible in Access, aside from an 'Append Query', which is NOT what I want here) ... but I can't do that with this query. I'm wondering what the rule / rules may be which govern that, but I can't find it in Access Help.

Thanks,
Chris

Re: Make Table Query

Once again, thanks to both of you, I think I have finally managed to take what you were saying and combine it with something I figured out on my own, and I am approaching an answer now.

What I did was to manually create a 'calendar' table, filled with one column of dates. These are 'potential project dates', and they range from Now to six years from now. Eventually I'll have to modify or add to that as time goes by, or if we get exceptionally long projects, but this works for now. So the single-column table has "Project Month" values that run from 1-Nov-2004, 1-Dec-04, 1-Jan-05 ... 1-Dec-2010.

Now I can combine that table with my tables for Personnel and Equipment and examine whether "Project Month" >= StartMonth (for a person or thing) AND "Project Month" <= EndMonth (for the person or thing) and enter the appropriate cost for the month if the condition is met. (It also checks to see if Project Month = StartMonth, for example, then is there a Transportation or Setup adder, etc., but you get the idea.)

This will give me a table of 72 (months) times the number of people or equipment items I may have. So, potentially a huge table, but I'm confident that Access can create it and calculate it with more facility than Excel (and I don't need to keep it forever, anyway). From this table I should be able to make my reports and exports to suit the other data requirements that are sure to crop up.

But I've got this far, and it's working more or less as I had planned. My problems were that I was
1) trying to automate the 'calendar table' capability, and that just wasn't needed, and
2) I thought I'd have to grow the number of columns to suit the number of 'person' and 'item' adders. I don't; I just grow the rows instead, with another set of 72 rows for every discretely added person or thing. No big deal. It's actually pretty simple, once I got the idea how to proceed.

I'm going to bookmark this thread, because I'm sure to come back to it when I get into more of my reporting, summarizing, etc. ... but that's for tomorrow.

I want to thank you both again for your help.

Chris

Re: Make Table Query

Scott and Alan,

Thanks both for your input, and I understand most of what you're saying here. (Though I confess I have only a vague notion of what you meant exactly by Query 4, Scott; maybe the answer is there but I'm too dense to see it. It seems like by aggregating and grouping date buckets, I'm getting a single line for "total cost".) Unless there's something I'm missing, I still don't see how this can deliver month-by-month cash flow information.

I guess the easiest way to state this is that I'm trying to do something in Access that would be as simple as breathing in Excel: Write a table that shows each month of the project (and no more), then show total projected cost for the month by listing and adding each expected element of cost for that month, each in its own column. I just don't want to do it manually. I could do it fairly 'simply' with formulas in Excel, but then it bogs the spreadsheet.

Chris

Hi all, and thanks in advance for reading.

I'm developing a cost estimating application in Access and I have (among others) tables for "Proposal", "Personnel" and "Equipment".

To simplify, the "Proposals" table has the basic information on the job, including Proposal Number, Project Name, Start Date and End Date.

The "Personnel" and "Equipment" will link via queries to the "Proposals" key field/s to form "Prop_Personnel" and "Prop_Equipment" tables with the specific people and items on the job, and their individual costs, escalation factors, setup and transportation, etc. We'll have from 0 to X people on each job, each with independent rates, Start and End dates. Same with equipment items: 0 to Y items at various rates and times.

This is all fine for coming up with Total Cost for each person and equipment item, and total cost to the project, but now I want to generate a Cash Flow table, and I'm having some trouble there.

What I want is a query that will build a table with N number of rows, where N is the number of months on the project, from Start Date to End Date, and the first column is a date column of Month / Year. Then I would like to build X + Y individual columns, one for each discrete Person and Equipment item. As I've stated there will be variable numbers of entries, but I haven't said they'll also be varying titles and names (items). I can't just label one column "Manager" and another "Subordinate" because some large projects may have two or more managers, for example. Some equipment will be used on one project but not another; on another project the same equipment may be entered multiple times. I don't want to build huge tables with pre-labeled columns that may or may not be used; I'd prefer to have a make-table query do this on the fly for each specific project.

Once I have all of the columns set, I'd like the table to continue filling in with applicable costs in each monthly bucket. (Even though the data will seldom be used this way, it provides transparency when someone will want to drill down to a particular month and see all of the individual cost components. They always do; it makes my own troubleshooting easier, too, when I want to find out why something's out of whack.)

Finally, I'd like to include separate subtotal columns where I can group total monthly costs by various category, such as "Construction" and "Commissioning", for one category breakdown, "Personnel" vs. "Equipment" subtotals, as well as others that may apply (based on other fields in the Proposal / Personnel / Equipment tables).

My intent in the application is that this table will only be accessed through the make-table query. It's not for general editing, but only a repository for costs as determined by other setup and entry tables. (I'll probably have it deleted after each use to avoid even the possibility of misuse.)

I hope this is a simple enough explanation. It seems like it should be a straightforward task, but I'm just not getting untracked on it.

Thanks again for reading.

Chris

I'd like to use the Worksheet's "Data / Form ..." functionality in a macro. I know the command for that is
Sheets.ShowDataForm
but I also recognize that this is based on the first data table found in the form, starting from A1.

Is there a way around this? I have more than one table on a worksheet, and I'd like to use some code that could recognize where the ActiveCell is (for example) and use THAT form. (This works fine, manually, on the WorkSheet, but in code the command always reverts to the first table it finds, from Cell A1.)

I'm using Excel '97 (still).

Thanks,
Chris

Because the answer is really 1.36111111111111 (and on and on), not just "1.36".

Chris

Hi, Andrey

This modified code addresses the 'Register' issue you mentioned. For any cell in A:A that contains the full word 'Register' (wherever it appears in the cell), there will be a page break inserted. (Notice that this won't insert a page break for 'registration', or 'registry', but it will insert a break for 'Register', 'register', 'REGISTER', 'registers', 'registering', etc.)

The code also resolves the issue you mailed me about, where an error message was generated because you had no existing page breaks installed. I should have tested for that. (And it's okay to point out errors in the public posting; other people may pick up code here from time to time, and if you already know it generates an error, they'd want to know also. I appreciate your apparent concern for my sensitivity in this regard.)

Chris

Sub InsertBreaks()
'   Insert PageBreaks in A:A wherever 'Register' appears


    Application.ScreenUpdating = False
    ' Set the area to be worked (This section borrowed from J. Walkenbach.)
    Set WorkRange = Columns("A:A").EntireColumn
    Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
    CellCount = WorkRange.Count


    ' Remove existing PageBreaks from bottom to top
    On Error Resume Next
    For myBreaks = ActiveSheet.HPageBreaks.Count To 1 Step -1
        ActiveSheet.HPageBreaks(myBreaks).Delete
    Next myBreaks


    ' Insert PageBreaks at any cell in A:A containing "Hello", starting at the top of 'WorkRange'
    ' and working down to the bottom
    For myCounter = WorkRange.Row To WorkRange.Row + CellCount
        If Not (Application.IsErr(Application.Find("REGISTER", UCase(Cells(myCounter, 1))))) Then
            ActiveWindow.SelectedSheets.HPageBreaks.Add before:=Cells(myCounter, 1)
        End If
    Next myCounter
    Application.ScreenUpdating = True
End Sub

VBA Solution

Hi, Andrey, and welcome to the site.

I don't know of a way to do what you want through formatting. I do have a macro that will do it for you ...

This macro looks for the word "Hello" (by itself, if you want it to find "Hello, World" or "Hello, Bob" and treat them the same, the code would require modification) in the working cells in Column A:A and, when found, inserts a PageBreak at that point.

You can look up elsewhere on this site and find out how to paste code into a module.

Sub InsertBreaks()
'   Insert PageBreaks in A:A wherever 'Hello' appears


    Application.ScreenUpdating = False
    ' Set the area to be worked
    Set Workrange = Columns("A:A").EntireColumn
    Set Workrange = Intersect(Workrange.Parent.UsedRange, Workrange)
    CellCount = Workrange.Count


    ' Remove existing PageBreaks from bottom to top
    For myBreaks = ActiveSheet.HPageBreaks.Count To 1 Step -1
        ActiveSheet.HPageBreaks(myBreaks).Delete
    Next myBreaks


    ' Insert PageBreaks at "Hello", starting at the top of 'WorkRange' _
        and working down to the bottom
    For myCounter = Workrange.Row To Workrange.Row + CellCount
        If Cells(myCounter, 1) = "Hello" Then
            ActiveWindow.SelectedSheets.HPageBreaks.Add before:=Cells(myCounter, 1)
        End If
    Next myCounter
    Application.ScreenUpdating = True
End Sub

Chris

I've never seen this on a tip sheet before, and I don't know where I learned it myself. It may be one of those (rare) things I learned all on my own.

If you want to create a title for a chart that updates with your data, it's pretty simple.

Create a formula (one line only, but you can use CHAR(10) to create multi-line titles) that refers to the information you want in the chart title.

I often do this on a hidden sheet, so that users don't inadvertently mess it up. Something like:

Let's say that this is on sheet "Hide Me", cell A1:
=Sheet1!A1 & " as of " & CHAR(10) & TEXT( NOW(), "d-Mmm-yyyy")

In the chart, select the existing title and IN THE FORMULA BAR enter:
= 'Hide Me'!A1 (You can't enter this formula in the 'Chart Title' block in the dialog box, or it will come out as plain text. But do enter 'something' there, so that you have a title to 'edit' in the formula bar.)

Your chart will print with a new date each day, and you (or your user) won't have to remember to update the title.

Dave,

Many thanks! I should have stated in the first posting that what I was finding was AFTER a complete re-install of Excel in an attempt (failed) to correct the problem. So I didn't think that going through that exercise would fix the problem. But your advice did, quickly and easily.

Thanks again,
Chris

I've been playing with some menu and toolbar customizations lately. I understand that these changes are captured each session in an .xlb file (in my case, C:\WINNT\cln018.xlb ... where cln01 is my username on this computer and 8 is my version of Excel--'97). So the file is automatically named how it is. (WINNT in this case = Windows 2000.)

However, for some reason the menu changes that I make that are other than 'plain vanilla' Excel commands (by which I mean buttons linked to macros in Personal.xls, for example, instead of 'Excel generic' command buttons) cause Excel to not start. I get various 'unable to read memory' errors and Excel just plain will not start.

To get around this, I've found that if I move the .xlb file created after my customization into my XLStart directory, then Excel starts, reads that file, and it works just fine. Fine, that is, until I close Excel with the changes that the .xlb file in XLStart creates and ... saves again in WINNT by default. So after each Excel session I need to either wipe out cln018.xlb from WINNT or move it to XLStart. If I don't do that, then Excel crashes on start again, and I get that rude reminder that I should have fixed things up after the last session. So if I'm starting and stopping Excel a number of times during the day, I've got a big nuisance having to do all this housekeeping all the time.

Both of these options are a pain. What can I do to let Excel create and store its .xlb file normally and let me use the program without this workaround? I would prefer not to have to use a macro to re-create the toolbar and menu changes each time, but if that's what it takes then I guess I'll get started. I definitely want to keep the modifications!

Thanks,
Chris

Thanks both. I knew that there was a method to link the checkboxes to cells, but I was hoping for a shortcut, like some kind of way to select all the boxes at once and set them to link to an array of cells ... sigh.

Dave, I think I would like your method of just checking in the cells themselves, and forgetting about the Forms checkboxes, but I didn't want to fool with the coding for the 'exclusive' buttons. (For example, check ONLY ONE from column A, B, C or D.)

So I'm using the 'Forms' checkboxes, and working through the forumulas I need to sum and count what's checked.

Chris

Hi all.

I have a simple log sheet with columns of exclusive option boxes (in each row, pick only one from column A-D) and checkboxes (also in each row, pick any number of boxes from columns E-J). I'd like to sum the count of boxes that are checked at the bottom of each column.

The simple question is: How is that accomlished? Does each box need its own code, or what?

Thanks,
Chris

Will this work for you?

Function myLookup(HLU As Range, VLU As Range, AGMASS As Range)

myLookup = Application.HLookup(HLU, AGMASS, Application.VLookup(VLU, AGMASS, 2, False), False)
End Function

Chris

Unfortunately I couldn't get Roy's macro to work. (I'm in Excel 97, if that makes a difference.)

I did improve my earlier code, and I can give you a metric on it, too. It ran out the dups in a 1000 row x 5 column sheet in under a minute. (It's still slow, because I can't figure out how to do this in an array yet. I wouldn't mind learning that, if anyone wants to improve this code.)

I also have another macro that sorts all the data, from right to left and top to bottom. (So if you have 200 columns of significant data and need to sort the whole sheet, you don't have to do it three columns at a time.) Just ask, if that's of any use.

Option Explicit

Sub DuplicateRowDelete()

Dim ThisRow As String, NextRow As String
Dim ColCounter As Integer
Dim RowCounter As Single
Dim ColsToCheck As Single

'This example assumes that:
' 1. The sheet is sorted ... we'll not be looking for duplicates that are not in neighboring rows
' 2. There is data in every cell in Column A:A ... or the process stops when the first empty cell is found in A:A.
' 3. User can specify how many columns of significance to check.

StartHere:
On Error Resume Next
ColsToCheck = InputBox("How many columns should be checked? 0 - 255", "Columns to Check", 255)
If ColsToCheck &gt; 255 Or ColsToCheck <= 0 Then GoTo StartHere

Application.ScreenUpdating = False
For RowCounter = ActiveCell.Row To 65000
' If the first cell in the row is blank, then exit
If IsEmpty(Cells(RowCounter, 1)) Then Exit Sub

ThisRow = ""
NextRow = ""
For ColCounter = 1 To ColsToCheck
ThisRow = ThisRow & Cells(RowCounter, ColCounter).Value & "."
NextRow = NextRow & Cells(RowCounter + 1, ColCounter).Value & "."
If ThisRow <> NextRow Then GoTo NextRowNow
If ColCounter = ColsToCheck And ThisRow = NextRow Then
Cells(RowCounter, 1).EntireRow.Delete
RowCounter = RowCounter - 1
End If
Next ColCounter
NextRowNow:
Next RowCounter

Application.ScreenUpdating = True
End Sub

Can I suggest that you provide a better example sheet and specification?

You have all of your bins in order in Sheet2, and there are no gaps. Bins are numbered from 1 to 24, and sort order is 1 to 24. (In any case, gaps wouldn't matter if the sequence is still preserved. My bank statements come back each month with gaps in sequence where some checks have not yet cleared, but the check numbers themselves are in sequence for me to match in my register.) Do you REALLY intend to have bins out of alphanumeric sequence, or is it just gaps you're worried about? Please give examples of the data that we need to work with (and leave out the columns we don't need to work on). If we don't have to account for out-of-sequence bins then we'd be wasting time and processing power trying to deal with it.

In addition, the data on Sheet1 also shows a one-for-one match with the bins on Sheet2. Presumably this will not happen with your live data, I understand. So why provide your sample that way?

What do you want to have happen with the bins that aren't listed on Sheet2? Delete the rows? Segregate them on the same sheet? on another sheet? Hide them?

Please think about the description and example of what you need. We're just guessing, otherwise.

Chris

PS: Sorry if I seem to be getting on your case here; it's been a long day and I've been dealing with a lot of poor specifications.

This will get you started. Be sure to check out the assumptions that I've started with. Change the end value for RowCounter to run on the hundreds or thousands of rows you need to examine.

TEST IT FIRST! I haven't had the time to debug it that I would like, but it's a start.

Option Explicit
Dim ThisRow As Integer
Dim ThatRow As Integer
Dim ColCounter As Integer
Dim RowCounter As Integer

Sub DuplicateRowDelete()

'This example assumes that:
' 1. The sheet is sorted ... we'll not be looking for duplicates that are not neighboring rows
' 2. There is data in every cell in Column A:A ... or the process stops.
' 3. We'll look at cells from column A until first blank cell, and compare to next row down;
' if the next row down has ADDITIONAL cells of data, those won't be compared.

For RowCounter = 1 To 10
ColCounter = 1
If IsEmpty(Cells(RowCounter, ColCounter)) Then Exit Sub
Do While Not IsEmpty(Cells(RowCounter, ColCounter))
If Cells(RowCounter, ColCounter) <> Cells(RowCounter + 1, ColCounter) Then GoTo NextRow:
ColCounter = ColCounter + 1
Loop
Cells(RowCounter, 1).EntireRow.Delete
RowCounter = RowCounter - 1

NextRow:
Next RowCounter
End Sub

Be careful with Jame's macro, as it will only compare the values in column A:A to see if there's a match between THAT CELL and its next row neighbor before deleting the following row.

I'll post some new code shortly.

Chris

I'm having trouble understanding your insistence on "sorting over column B" in sheet 2. The order is the same as column A ... it's all sorted on Bin Number. Are you saying that you may arbitrarily change the order in column B so that the Bins might sort out of their current order, such as: A5, A3, A6, A8, A2, etc.? If so, I suggest that you show this in your example. And if not ... then what do you mean?

What exactly is the point of Sheet2, other than to show a list of some bins that may not appear on Sheet1? (We can deal with that separately, I think.)

Chris

Jen:

Along the lines of Barrie's suggestion--funny that I'd never run across this need myself before:

Assuming you have a range named "Database":

=SUM(OFFSET(Database, 0, 4)) - SUM(OFFSET( Database, 0, 5))

Sums the fifth column of the range. Basically, it sums the entire right side of the database, from column 5 onward, then subtracts the non-relevant columns from 6 onward.

HTH,
Chris

PS: I'll be interested in the final, cleaner answer to this, as well.