Search results

Okay, that's what i ended up doing previously when I used the Access query builder to do this. I was trying to do this in vba with recordsets and was hoping for something more elegant in the sql. So, say I'm building sql statements to use with record sets on vba and I figure on saving the min...

Just a follow up note: I also tried adding: GROUP BY t_Assets.AssetID, t_AllMonths.MonthStartDate; to the end of the query. When I do that, the query treats [MinOfStartDate] and [MaxOfEndDate] as parameters to be entered by the user, rather than using the values generated by the Min and Max...

I'm trying to select a range of relevant dates for an amortization calculation (see my earlier thread on this subject here), but I'm having a bit of trouble making the SQL work. I have a table called "t_AllMonths" that has only one field: MonthStartDate which contains the first day of the month...

Yeah, I'd agree. If I do decide to keep the old amort data, it'll be tagged with some sort of unique versioning identifier that I can use to query on later. That would be purely for the purpose of "what was the amort for the prior version of this asset acquisition?" But since I'm still in...

If what you want is output that includes the designation "Both" then you'll need to do something a little extra. (If all you care about is getting the list of Acc_Num, then the notes I highlighted above will get you that.) To get that "Both" designation, I believe what you'll need is to create...

Got it. I had a feeling they must've been declared in some other public module, but wanted to make sure I wasn't missing something. I'm not sure yet whether I want to erase Amort Data from the table when generating new amort data or retain previously calculated data, but for now it doesn't...

In this example, I believe you can achieve what you want by: 1) Unchecking "Show" under Stat_Code and Cat_Code. 2) Open the Property Sheet for the query, find the field "Unique Values" and make sure it says "Yes". That should generate a list only of the unique Acc_Num values that match the...

Hey, a couple questions on this: In the code above, I don't see where you Dim td, db, rs or rsMtg. Is that normal? (My first thought was: wouldn't that generate an error?) tblAmortization is a table that already exists within the database, with the structure already defined, right? (Or, in...

Thanks. I'm cool with doing this in VBA (just hadn't thought to do that). I'll have to give this a look-over to make sure I can parse it and reproduce something similar.

Good afternoon, I'm working on a database where I enter a series of assets and their costs, and I'm trying to build a query that will generate an amortization schedule for each asset. I'm using an amort methodology that amortizes a certain amount each period based on a calculation. I've got a...

Good afternoon, I have an existing report with a number of fields arranged horizontally in a tabular fashion in the detail section. The first field is an identifying line of text and the remaining fields are numerical calculations. The report is basically a simple profitability estimate report...

Thanks for the response. I'd been going back and forth about whether I want to keep a running record of past goals or just overwrite with the current goal whenever the goals changed. There are, I think, theoretically uses for maintaining the historical data... so it might make sense to...

Sure, here's the structure I've built (although none of this is set-in-stone yet as I'm developing this from the ground up, so I can make changes if needed): Settings Table: *SettingID (a numeric ID key) *GoalSetting (the selected goal setting) *Active (a Y/N where Y indicates the active...

I'm not sure I'm going about this the right way, but here's the gist of what I'm working on: I'm trying to track a certain activity (let's say number of calls made, although that's not the actual activity I'm tracking) and progress toward a goal related to that activity. Tracking the activity...

Of course. I should've thought of that. Thanks! It seems to be working pretty well, so far. I still have to sift through the data to make sure it's reporting as I expected, but so far so good.

Hi, I got some great help here on a related issue that really dramatically improved the efficiency of some queries I was running. But now I'm running into a new problem on the same project. For background: what I'm trying to do is compare profitability on a given set of products between two...

I just wanted to say thanks again. Doing it this way has so far proven literally thousands of times more efficient - I've got a table with the correct values stored in a matter of seconds instead of many, many hours. I've got some more work to do tying everying together, but the problem that...

Thanks. Do I have to worry about whether the query is updatable? If I loop through the records in a query this way, can I update the fields back to the table?

Based on the feedback above, I started fiddling around with a Make Table query and some VBA to try to accomplish what I want in a more efficient manner. I set up a make-table query that takes the data I was pulling in the query above but without the nested subqueries to translate all the 0s...

While my knowledge and understanding of Access has increased dramatically the last year over what it was before, I'm afraid I'm still too much of a neophyte to imagine how to go about doing either of the suggestions you've made. How would you approach putting this data into a temporary table...