Search results

So you want to have a field that does running subtotals based on the NOMPROGRESSIF value. I somewhat get what you're looking to do (although I'm not sure how the jackpot table fits into the calculation). I can tell you that you won't be able to get a query to generate this in this order. You...

I'm guessing that a non-spouse dependent has a dep_relation code of A, D, F, G, S, or X. Based on that, I would do this: SELECT [fields] FROM (partfile INNER JOIN depfile AS Spouse ON partfile.prt_ss_nbr = Spouse.dep_ss_nbr AND Spouse.dep_relation = [Spouse code]) WHERE partfile.prt_ss_nbr NOT...

Two possibilities? 1) I'm trying to recall, but if your SELECT query is joining multiple tables, but you still want your users to be able to modify the data, that Access can't figure out how to save certain fields in one table and other fields in another. Kinda depends on what your query looks...

Okay, that's what I figured you meant. I'll bring it up to my team as the alternative solution. I'm doubtful it would be as scalable or as efficient as allowing Access to calculate the sum for me. We'll weigh the performance against modifying the report (risky if multiple users are running...

I'm not sure I follow your reply. If my Charge table has Person(FK), Date, Type, Amount, and I want to aggregate the Amounts by Person but filter by Date and Type, the report can only display Person and Sum(Amount). If I include Date and Type in the SELECT, the values won't aggregate...

I think this is what you want: SELECT table1.names, table1.[Start date], table1.[End date] FROM table1 WHERE [Specify Year] BETWEEN Year(table1.[Start date]) AND Year(table1.[End date])

I would use a UNION to combine the SELECT, but I'm not familiar with the term MERGE.

I'd look into why the record is getting deleted. I'll presume your query is based on Access tables (not linked tables nor other Access queries). If it isn't, that might explain why your records are disappearing. Or you might have a user on the same database as you that is deleting the records...

If you want all the Person rows, LEFT JOIN Person with the Investments table, as in FROM Person LEFT JOIN Investments ON Person.person_id = Investments.person_id You could also refer to the Investments table twice, using a different alias for each copy, rather than use a separate/inner query...

I created a References table with only ID and Reference as its columns and populated it with your data. I then wrote this query that obtains the output you requested: SELECT References.Reference FROM [References] WHERE (((Mid([Reference],2)) Not In (SELECT Mid([Reference],2) FROM [References]...

Hi, Thank you for the reply. I tried referring to the prompts and embedding the values directly (separate attempts, same result). The problem is that the user is prompted for the field I'm actually trying to compare to. So by way of your example, if the code was: stwhere = "fieldfromsql =...

I have a report that does an aggregate (Total Charges by Person), and I want to apply dynamic filters (user-entered on a form) that refer to fields not in the report (Ex: Date, Type). In SQL, it might look like this: SELECT [Person].[FullName], SUM([Charge].[Total]) AS TotalAmount FROM...