Return non-existing records for input

[rbrady]

Hello,

My database holds the monthly returns of mutual funds and financial indicies. After the end of a month, I put in the values in with the Datasheet View of a form where I have to expand each parent row to access the subform records. (I use Datasheet View instead of Form View because it is easier to find a fund because I don't necesarily do all of them at once or in and order.)

Is it possible to create a query (or form) that will display all of the funds that do not have a value for a given month/date so that easily find and fill-in those that are missing? It would be nice to avoid using placeholder value for the returns (because the return is a required field).

I have included a few screenshots: the goal.png one is from Excel and is what I’m shooting for, but it isn’t necessary to display funds that do have returns (like Fund 1).

Thank you very much!
Ryan

 

[jal]

Assuming you need to do this for only one return date at a time, you could have a textboxReturnDate on your form where the user types in that one date. Then use this query to list funds which had no return on that date:

SELECT F.* From Funds as F
LEFT JOIN
(SELECT FundID FROM Funds
WHERE ReturnDate = Forms!YourForm!textboxReturnDate)
As FundsWithAReturnOnThisDate
ON F.FundID = FundsWithAReturnOnThisDate.FundID
AND FundsWithAReturnOnThisDate.FundID IS NULL

And then use a similar query (and a similar textbox) to likewise display Indexes with no return on a given date.

 

[jal]

I am assuming that the FundPerformance table has no entry at all (not even zero values nor even null values) for those funds with no return on that date.

 

[rbrady]

Hi jal,

Yes, there will be no zero or null values as you thought. I will give your response a try and let you know how it works out. Thank you!

 

[rbrady]

Hi jal,

Access is giving an error, “JOIN expression not supported,” and then highlights, “FundsWithAReturnOnThisDate.FundId IS NULL,” from the last line; can this be fixed? I’m sorry, I know only very basic SQL!

 

[jal]

Oh, sorry, the last line:


AND FundsWithAReturnOnThisDate.FundID IS NULL


should probably have WHERE instead of AND.


WHERE FundsWithAReturnOnThisDate.FundID IS NULL

 

[rbrady]

Hi jal,

Sorry to come back to this two months later, but I never got it working and am trying again now.

I have been leaving the form out of the mix and only working just with the query to get that right first. When I run this query,

SELECT F.*  FROM Fund AS F
LEFT JOIN
(SELECT FundID FROM Fund
WHERE ReturnDate=#10/31/2008#)
AS FundsWithAReturnOnThisDate
ON F.FundID=FundsWithAReturnOnThisDate.FundID
WHERE FundsWithAReturnOnThisDate.FundID IS NULL;

I am first prompted to enter a value for ReturnDate (but why am I asked if it is already explicitly set in the SQL?), and then I get a result that contains all of the fields from the table, Fund. I get the same result whether I enter a date or not.

How can the fields from the FundPerformance table be included in the query so as to produce a result similiar to the goal.png from my original post?

Also, I am not familiar with temporary tables and aliases like F; how can fields be added or removed from it? I would like to remove the “Asset class ID” and “Manager ID” columns, but more importantly, I would also like to add the “Manager” field from the FundManager table.

Thank you again.
Ryan

 

[rbrady]

Close but still need help

I haven’t quite got it yet, but I managed to get a result close to my goal by changing some of the SQL and also using design view. The query result is basically what I want, except I can’t edit the rows (which would actually create new records). When I try to enter data, the status bar says, “This Recordset is not updateable.” Also, when I run the query, I am prompted for the the “DateInQuestion” parameter twice, though that isn’t a huge set-back.

Here is the SQL used (I tried to format it make it easier to read, but I hope I didn’t make it more difficult!):

SELECT F.*, AssetClass.Class, FundManager.Manager, FundPerformance.ReturnDate, FundPerformance.Return
FROM FundManager
INNER JOIN
  (AssetClass
  INNER JOIN
      ((Fund AS F
      LEFT JOIN
      (SELECT FundID FROM FundPerformance WHERE ReturnDate=DateInQuestion)
      AS FundsWithAReturnOnThisDate
      ON F.FundID=FundsWithAReturnOnThisDate.FundID)
    LEFT JOIN
    FundPerformance
    ON FundsWithAReturnOnThisDate.FundID=FundPerformance.FundId)
  ON AssetClass.AssetClassId=F.AssetClassId)
ON FundManager.FundManagerId=F.FundManagerId
WHERE (((FundsWithAReturnOnThisDate.FundID) Is Null));

I am trying to return an editable recordset that shows funds that do not have an associated record in the FundPerformance table for the “ReturnDate” used as a parameter in that query. The query looks good right now, but the records are not editable. (It would also be helpful if the “ReturnDate” field was automatically populated with the date parameter, but that’s not the important part.)

Any help would be greatly appreciated. Thank you!

 

[jal]

Complex queries are typically non-editable. I wish life were simpler. Typically I use the complex query to pull some kind of info that I need, and then I use that info in a simple UPDATE query. Sometimes I need several UPDATE queries to do the trick. Maybe I'll have a look later.

 

[jal]

I'm not sure what you mean by autopopulate the DateInQuestion. Maybe what you'd like is dropdown list of return dates where the user can select one at a time. Whenever he selects one, that date runs the query. Is that what you want?
Or maybe let the user type the date into a textbox on the form - this route already I mentioned earlier:

WHERE ReturnDate = Forms!YourForm!textboxReturnDate

I'm not sure how to make your query updateable. My advice is to use it as a make-table query (output the result into a new table) and then base your form on that. This table will naturally be updateable. When the user is finished typing his info onto the form - thereby updating the new table - write UPDATE queries that move this info from the new table into the other tables. Maybe I'll have time to help you write UPDATE queries, but one step at a time here.

A make-table query is a matter of adding an "INTO NewTable" clause before the FROM clause. For example:

SELECT * INTO TempTable1 FROM Customers

The above creates a new table called TempTable1.

Let me know which direction you want to go with this, and then maybe I can help you with the next step.

 

[boblarson]

It looks to me that you are trying to put on one form what should be on a Main Form and Subform combination.

 

[jal]

Rbrady, if Bob advises you further on this, take to heart what he says. He's an expert on this kind of thing. I am very ignorant about subforms.

 

[boblarson]

I don't know if I would go so far as to say "Expert" but I have been around a while.

The very first screenshot you posted shows a table and subtable. That would normally translate into Main Form (table) and Subform (subtable).

 

[jal]

The very first screenshot you posted shows a table and subtable. That would normally translate into Main Form (table) and Subform (subtable).

Bob, I'd like to see a tutorial explaining how to relate two tables using a form and subform. I'll try a quick Google for it, but if you have a link at hand please post it.

 

[rbrady]

Thank you for the responses!

Complex queries are typically non-editable.

Ah, that is good to know so I don’t get my hopes up!

I'm not sure what you mean by autopopulate the DateInQuestion.

I mean the kind that you mentioned where the user enters the date in a form and all of the returned records are filled-in with that date. I did actually forget about what you said about using a form for that :[ but was hoping to be able to insert that value through the SQL, but now as I type this I see why that doesn’t make sense.

My advice is to use it as a make-table query and then…write UPDATE queries that move this info from the new table into the other tables.

I will try doing this as a make-table query and an UPDATE query like you suggest. Thank you!

It looks to me that you are trying to put on one form what should be on a Main Form and Subform combination.

Yes, I think that is exactly what I am trying to do. That first screenshot is actually the Datasheet View of the Main form/Subform combination—I have attached a new screenshot of the Design View of the same form. The reason I would like to put these together the way we are trying is so it can be easily seen where there is missing data. (e.g., Some Fund has performance data for 9/30/08, 10/31/08, and 12/31/08, but when this query is run for 11/30/08, a record with shows-up with no data in the “FundPerformance” field because it has none, and then it can be entered.)

I have attached updated screenshots of what I am trying to get to (made in Excel)—either A or B. A shows funds with or without a return record for a particular date; B shows only funds with no return for that date. In both cases, the rows with no value in the “Return” column need the value in the “Date” column to be sort of temporarily faked because the table design is such that the ReturnDate and Return are both required fields. I am trying to achieve this because it would often be easier to enter data in a fill-in-the-holes kind of manner instead of navigating through subforms and existing data. What do you think?

Thank you both for your help!