Two top-level one-to-many relationships in one form

[rbrady]

Hello,

I am trying to make a form from a query [query.png], and I would like the form to be organized by the table, Fund, where Fund is a table on the “many” side of two one-to-many relationships [relationships.png]. That is, I would like the form to look similar* to the form in the attached screenshot [form.png], with the only subform being the one to do with Fund performance. Can this be done while keeping the data of related controls linked? I cannot figure-out how to get this to work:

When I enter a new record, and I enter the “Asset class” and “Manager” (fields), new records in their respective tables are created, which I don’t want. I don’t understand why this happens because, for both:

• Row source = a SELECT statement to select only what already exists (I have directly entered some records in the table)
• Limit To List = Yes
• Allow Value List Edits = No

I suppose I could set the field to Indexed = Yes (No duplicates), right? but doesn’t indexing a lot of things, especially text fields, start to slow things down?

To rephrase, I have two main questions:

1. Why are new records being created in my form when, as far as I can see, am telling it not to?
2. Can I make a single form that will take care of all the fields in my query—a form with two top-level superforms (for FundManager and AssetClass)?

Thank you very much for your help!

Ryan


PS
Wow, another question: Why does Access add a relationship line from AssetClass to FundPerformance in the query builder?


* I would actually like to delete the ID fields once everything is working; can I do this safely?

 

[Banana]

rbrady

1) Access doesn't add a relationship in query builder. It just automatically creates joins, based on the relationship you created between the tables. You could remove it if you wanted. Be sure to understand the difference between joins and relationships; they're not same.

2) Do not burden your users with IDs. Just show your users AssetClass name and Manager name, hide the ID (by having two columns in the combobox, and setting the first column width to zero).

3) The recordsource should be a query that selects records from Fund table only. The combobox can have its own recordsource to display the name.

4) If you do the #3, you won't have to worry about having AssetClass and FundManager and I suspect this is also why records were created when you didn't want it to be.

5) No need to delete the IDs and it's not recommended. Just hide it.

 

[rbrady]

It works

Excellent! Thank your very much. I got it working from your suggestions. I have attached screenshots of the the form now and the query I used. For the query, I took out the AssetClass and FundManager tables. I pulled the fields Fund like you suggested, but I added FundPerformance as a subform, which is OK with me.

On the form, the two combo boxes are two columns, but the ID is parts are hidden like you said. To get the contents of the combo boxes in alphabetical order instead of ID number, I created new, simple queries per the Access help and sorted them there, and then used the queries as the Row source instead of the table.

However, I did this for both, and it worked (the A–Z sorting), but now it sorts only for one combo box. The sorting does not work for the “Asset class” combo box, where it started sorting by ID number again after I manually edited some records in the AssetClass table. … I was just about to ask about this, then I played with it again and fixed it by setting it to the table and then setting it back to the query again.

Thank you again for you quick and helpful reply!

 

[CEH]

Glad to hear you got it working.... But I'm kind of surprised.... One thing I wanted to add when looking at your tables... I would use something a little better than just "id" for EVERY primary key. This is going to get VERY confusing! USe something like... "FundID" "AssetClssID" ....so on... And is that a compound primary key? Is there a reason for that?

 

[rbrady]

Glad to hear you got it working.... But I'm kind of surprised.... One thing I wanted to add when looking at your tables... I would use something a little better than just "id" for EVERY primary key. This is going to get VERY confusing! USe something like... "FundID" "AssetClssID" ....so on... And is that a compound primary key? Is there a reason for that?

Yes, I have seen from others that using something like, “FundId,” is the more common way to go. I chose to use only, “Id,” because I like how it isn’t redundant, and it is almost always clear from the context which “Id” is meant. I also like how it makes a SQL statement more simple, like Fund.Id instead of Fund.FundId. This is my first big database project, and, as you can see, it is still pretty young, but I would like to give the simple name a try—it has been working-out OK so far.

The table, FundPerformance, has a compound primary key to prevent a fund from having more than one performance record for the same date. It is a tricky one, and someone else caught it for me in a different thread.

Thank you for your input!

 

[Banana]

rbrady, just as FYI-

You can write the SQL statement as:

SELECT FundID FROM tblFunds

No need to say "Fund.FundID".

The bitch about naming scheme, though, is once we've set up a scheme, it's nigh impossible to revise it at a later time (at least not without breaking everything else). I hope it works out for you.

 

[rbrady]

Oh, good to know; thanks!