Search results

CJ - good idea on the accounts table with a left join. Do additional joins (especially a left or right join) like this slow the database down (assuming database is optimized). As a newbie I always thought 1) the fewer tables/queries I include in each query the faster they run (again assuming...

OK Thanks.

GM all. Hoping you are all safe these days. Question on the best practice for plugging a value in a Union All query I need to Union All the following two tables: Table 1 Has an Account's Market Value for each day TABLE 1 FIELDS AccountNumber Date MarketValue Table 2 has an Accounts Activity...

Unfortunately I'm stuck with a few union queries because of the source data structures - which are not optimized. I'm using the ALL clause to simply merge all related records in one query result, the writing the logic from there. I'm not a VBA guy so that part I'm a bit lost on. If your general...

Thanks for this. Good explanation on query types. If there is a post on best practice you can send me that would be great.

CJ - thanks. Not sure if this helps, but... for my application the table data will grow each day as it is accounting data. Appreciate the help. I really want to learn how to do this the right way. I'll try to find some articles to tell me how/when performance is impacted.

All set - worked. Thanks

All, Here are 2 best practice question for sequencing queries. Question 1) Is it best to create queries off queries so they run in sequence (Flow #1 below) or "Parallel" queries from the same source and then merge them later? The logic in Union Query 1 & 2 is similar but not the same, and not...

CJ - I did not try your suggestion because I was not clear what you meant (newbie). If that why Access is inserting the "[%$##@_Alias]" text? How exactly do I fix this? SELECT [%$##@_Alias].AsOf, [%$##@_Alias].AcctKey, [%$##@_Alias].SecKey, [%$##@_Alias].PositionRef...

Thanks all- perfectly simple.... I was a victim of syntax errors.... aren't we all.

Hi all. Hope you are all doing well. Easy one... In the Union query below (below the XX's) I need to Group by: AsOf, AcctKey, SecKey, PositionRef and Sum By: Quantity_Impact, MarketVal_Impact, Cash_Impact, Thanks XXXXXXXXXXXXX SELECT AsOf, AcctKey, SecKey, Quantity_Impact...

All, I'm trying to replicate accounting logic in Access. Here is the goal: Take an opening Position - Say 20 Shares of IBM on 1/1/2019 and calculate the market value for each business day of the rest of the month. The math for each day is ( Prior Day-end Shares + The Share impact (+/-) of...

All - for the sake of the project I'm working on I figured it out. I rewrote some of the queries and changed a bit of clunky logic and it works now. I still have the same basic question that I cant get past. How can a query produce a value but when Totals/Sum does the aggregation the values...

Thanks. Let me come back to this a little later today Really appreciate the time

Isla - thanks for your work on this. I see [qryCommissionOwed_CR] points to Commission_5 and that it works. Only issue is [qryCommissionOwed_CR] needs to point to Comission6 because that one includes the Trigger_2 query that is needed. Trigger_2 is the one giving me a problem. Hopefully my...

Isla - just saw your note. let me check. I also did the NZ removal but has the issue just posted.

I'm getting closer to the problem but still cannot figure out why it is happening. Any help is welcome. In the attached database - still focusing on the [EarnedBy] = 287 problem: First See [Commission_6]. The underlying source are 2 Queries: 1) [Commission_5] with LEFT JOINS to [Trigger_2]...

Appreciate that and I'll continue diagnosing today. But no matter any issues I can find upstream from the query, how is it possible that the values are there before the Total and 0 after? On the face of it that makes no sense to me.

All, Attached is the DB. To find the issue with Totals.... Run the CommissionOwed_1 Query (Totals are turned off). Filter EarnedBy = 287 and you will see 3 values, 1 for each month EarnedBy YYYYMM CommissionEarnedAfterTrigger 287 2019.08 8880 287 2019.09 6450 287 2019.07 3570 Now in Design...

Yes I understand it's strange. I'll post the example.